DataTable dt = new DataTable();
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("班级", typeof(string));
dt.Columns.Add("年龄", typeof(string));
dt.Columns.Add("身份", typeof(string)); string name1 = tbName.Text;
string banji1 = ddlBanji.SelectedItem.Text;
string sql1 = "and name like '" + name1 + "'or class like'" + banji1 + "'";
table = ada.GetDataBySql(sql1); //DataRow dr = dt.NewRow();
for (int i = 1; i < table.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
dr["姓名"] = table.Rows[i]["name"].ToString();
dr["班级"] = table.Rows[i]["class"].ToString();
dr["年龄"] = table.Rows[i]["age"].ToString();
dr["身份"] = table.Rows[i]["shenfen"].ToString(); dt.Rows.Add(dr);
}
DataSet ds = new DataSet();
ds.Tables.Add(dt); string ofname = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string filename = "C:\\Documents and Settings/Administrator/桌面/" + ofname;
string result = LgwOutFile.exportToExcel(ds, filename);
总是第一行信息不显示
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("班级", typeof(string));
dt.Columns.Add("年龄", typeof(string));
dt.Columns.Add("身份", typeof(string)); string name1 = tbName.Text;
string banji1 = ddlBanji.SelectedItem.Text;
string sql1 = "and name like '" + name1 + "'or class like'" + banji1 + "'";
table = ada.GetDataBySql(sql1); //DataRow dr = dt.NewRow();
for (int i = 1; i < table.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
dr["姓名"] = table.Rows[i]["name"].ToString();
dr["班级"] = table.Rows[i]["class"].ToString();
dr["年龄"] = table.Rows[i]["age"].ToString();
dr["身份"] = table.Rows[i]["shenfen"].ToString(); dt.Rows.Add(dr);
}
DataSet ds = new DataSet();
ds.Tables.Add(dt); string ofname = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string filename = "C:\\Documents and Settings/Administrator/桌面/" + ofname;
string result = LgwOutFile.exportToExcel(ds, filename);
总是第一行信息不显示
你慢慢对比下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Data.OleDb;namespace Excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} //导入命名空间,using Microsoft.Office.Interop.Excel;
//引入组件Microsoft.Office.Interop.Excel;
//打开Excal并且往里面写入东西
private void btn_基本操作_Click(object sender, EventArgs e)
{
//创建一个Excal应用程序
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
//创建工作薄
app.Workbooks.Add();
//创建工作表,为2个
app.SheetsInNewWorkbook = 2; //获取工作表,下标从1开始
Worksheet w1 = (Worksheet)app.ActiveWorkbook.Worksheets[1];
//设置工作表的名字
w1.Name = "First";
//设置单元格的类容,下标从1开始
w1.Cells[1, 1] = "你好,这里是第一行"; //Worksheet w2 = (Worksheet)app.ActiveWorkbook.Worksheets[2];
////设置工作表的名字
//w2.Name = "Second";
////设置单元格的类容,下标从1开始
//w2.Cells[1, 1] = "ok"; app.Visible = true;//设置可见 //线程睡眠
//System.Threading.Thread.Sleep(2000);//2秒 //保存-------获取exe文件所在的目录,文件名,保存的文件类型
app.ActiveWorkbook.SaveAs(Environment.CurrentDirectory + "/test.xls", XlFileFormat.xlWorkbookNormal); //关闭当前活动的工作簿
//app.ActiveWorkbook.Close();
//退出应用程序
//app.Quit();
} //读取数据库,并且导出到Excal
private void btn_读取数据库_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=MyCinema;Integrated Security=True");
SqlDataAdapter adapter = new SqlDataAdapter("select * from Movie", con);
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
//数据库的数据导入Excal
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Workbooks.Add();
app.SheetsInNewWorkbook = 1;
Worksheet w1 = (Worksheet)app.ActiveWorkbook.Worksheets[1];
w1.Name = "First";
for (int i = 1; i < dataGridView1.Rows.Count; i++) {
for (int j = 1; j < dataGridView1.Rows[i].Cells.Count; j++)
{
w1.Cells[i, j] = dataGridView1.Rows[i - 1].Cells[j - 1].Value.ToString();
}
}
//给Excal设置样式,range表示区域,有格式属性,r.NumberFormat = "yyyy-m-d";事件格式
Range r = app.get_Range(app.Cells[1, 1], app.Cells[17, 5]);
r.Font.Color = Color.Green;
r.Borders.LineStyle = XlLineStyle.xlContinuous; app.Visible = true;
app.ActiveWorkbook.SaveAs(Environment.CurrentDirectory + "/test.xls", XlFileFormat.xlWorkbookNormal);
} //导入包using System.Data.OleDb;
private void 从Excal中导入数据_Click(object sender, EventArgs e)
{
//Excel的连接字符串,只需要更改Data Source就OK
string path = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\net\Excel\Excel\bin\Debug\test.xls;Extended Properties='Excel 8.0;HDR=YES,IMEX=1'";
OleDbConnection con = new OleDbConnection(path);
//First是表区间名,$后面代表区域,也可不写[First$]
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [First$D1:D8]", con);
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
}
}
从0开始,第一条索引是0