C# Operate Excel
A>Read
b.调用Com组件 需要添加Microsot.Excel 9.0 Object Library组件。
测试正确。
B>Write
I.调用com组件(Access),导出access数据到Excel,就是直接调用access的导出功能,此方法速度超级快
III.用Ado.net 此方法速度较以上两个显得慢了一些,数据量越大越明显
IV.此方法调用com组件(Excel),速度都慢于以上3个方法
V.利用剪贴板 ,有人说此方法很快,不使用Web,web可以用二维数组
有两种方法:
a.OleDbConnection 示例代码如下: 1 String connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strFilePath+";Extended Properties=Excel 8.0;";
2 System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(connStr);
3 System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]",con);
4 System.Data.DataSet ds=new System.Data.DataSet();
5 oda.Fill(ds);
6 String[] SrcterminalId=new String[ds.Tables[0].Rows.Count];
7 String[] DestterminalId=new String[ds.Tables[0].Rows.Count];
8 String[] SendContent=new String[ds.Tables[0].Rows.Count];
9 for(Int32 i=0;i<ds.Tables[0].Rows.Count;i++)
10 {//从第0行,第0列开始
11 if(ds.Tables[0].Rows[i][0].ToString()!="")
12 { SrcterminalId[i]=ds.Tables[0].Rows[i][0].ToString();
13 }
14 if(ds.Tables[0].Rows[i][1].ToString()!="")
15 { DestterminalId[i]=ds.Tables[0].Rows[i][1].ToString();
16 }
17 if(ds.Tables[0].Rows[i][2].ToString()!="")
18 { SendContent[i]=ds.Tables[0].Rows[i][2].ToString();
19 }
20 }
21
2 System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(connStr);
3 System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]",con);
4 System.Data.DataSet ds=new System.Data.DataSet();
5 oda.Fill(ds);
6 String[] SrcterminalId=new String[ds.Tables[0].Rows.Count];
7 String[] DestterminalId=new String[ds.Tables[0].Rows.Count];
8 String[] SendContent=new String[ds.Tables[0].Rows.Count];
9 for(Int32 i=0;i<ds.Tables[0].Rows.Count;i++)
10 {//从第0行,第0列开始
11 if(ds.Tables[0].Rows[i][0].ToString()!="")
12 { SrcterminalId[i]=ds.Tables[0].Rows[i][0].ToString();
13 }
14 if(ds.Tables[0].Rows[i][1].ToString()!="")
15 { DestterminalId[i]=ds.Tables[0].Rows[i][1].ToString();
16 }
17 if(ds.Tables[0].Rows[i][2].ToString()!="")
18 { SendContent[i]=ds.Tables[0].Rows[i][2].ToString();
19 }
20 }
21
大家会发现数据会丢失的现象, 将EXCEL数据导出的时候如果同一列数据中既有文字,又有数字!读取时一列中要么文字丢失只剩下数字,要么数字丢失,只剩下文字,这是由第一行的数据类型决定的。出现这种问题是由于数据类型不统一造成的。原来的连接字符串为 String connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strFilePath+";Extended Properties=Excel 8.0;";查阅资料后问题解决:String connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strFilePath+";Extended Properties='Excel 8.0;IMEX=1'";
其中参数解释:
HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名
b.调用Com组件 需要添加Microsot.Excel 9.0 Object Library组件。
1 //创建Application对象
2 Excel.Application app=new Excel.ApplicationClass();
3 //是否显示Excel,默认为false
4 app.Visible=false;
5 Object o=System.Reflection.Missing.Value;
6 Excel.WorkbookClass w=(Excel.WorkbookClass)app.Workbooks.Open(strFilePath,o,o,o,o,o,o,o,o,o,o,o,o);
7 Excel.Sheets sheets=w.Worksheets;
8 Excel.Worksheet datasheet=null;
9 foreach(Excel.Worksheet sheet in sheets)
10 {
11 if(sheet.Name=="Sheet1")
12 { datasheet=sheet;
13 break;
14 }
15 }
16 if(datasheet!=null)
17 { //ds还是采用上面的取行写法,如果不这样,不知道读几行
18 for(Int32 i=0;i<ds.Tables[0].Rows.Count;i++)
19 { //从第二行,第一列开始
20 //读取,通过Range对象,但使用不同的接口得到Range Excel.Range range=(Excel.Range)datasheet.Cells[i+2,1];
21 if(range.Value2!=null &&range.Value2.ToString()!="") { SrcterminalId[i]=range.Value2.ToString();
22 } range=(Excel.Range)datasheet.Cells[i+2,2];
23 if(range.Value2!=null && range.Value2.ToString()!="") { DestterminalId[i]=range.Value2.ToString();
24 }
25 range=(Excel.Range)datasheet.Cells[i+2,3]; if(range.Value2!=null && range.Value2.ToString()!="")
26 { SendContent[i]=range.Value2.ToString();
27 }
28 }
29 }
30 datasheet=null;
31 sheets=null;
32 app.Quit();
33 app=null;
34
2 Excel.Application app=new Excel.ApplicationClass();
3 //是否显示Excel,默认为false
4 app.Visible=false;
5 Object o=System.Reflection.Missing.Value;
6 Excel.WorkbookClass w=(Excel.WorkbookClass)app.Workbooks.Open(strFilePath,o,o,o,o,o,o,o,o,o,o,o,o);
7 Excel.Sheets sheets=w.Worksheets;
8 Excel.Worksheet datasheet=null;
9 foreach(Excel.Worksheet sheet in sheets)
10 {
11 if(sheet.Name=="Sheet1")
12 { datasheet=sheet;
13 break;
14 }
15 }
16 if(datasheet!=null)
17 { //ds还是采用上面的取行写法,如果不这样,不知道读几行
18 for(Int32 i=0;i<ds.Tables[0].Rows.Count;i++)
19 { //从第二行,第一列开始
20 //读取,通过Range对象,但使用不同的接口得到Range Excel.Range range=(Excel.Range)datasheet.Cells[i+2,1];
21 if(range.Value2!=null &&range.Value2.ToString()!="") { SrcterminalId[i]=range.Value2.ToString();
22 } range=(Excel.Range)datasheet.Cells[i+2,2];
23 if(range.Value2!=null && range.Value2.ToString()!="") { DestterminalId[i]=range.Value2.ToString();
24 }
25 range=(Excel.Range)datasheet.Cells[i+2,3]; if(range.Value2!=null && range.Value2.ToString()!="")
26 { SendContent[i]=range.Value2.ToString();
27 }
28 }
29 }
30 datasheet=null;
31 sheets=null;
32 app.Quit();
33 app=null;
34
测试正确。
B>Write
I.调用com组件(Access),导出access数据到Excel,就是直接调用access的导出功能,此方法速度超级快
1 private void AccessExport(String strFilePath)
2 {
3 Access.ApplicationClass oAccess=new Access.ApplicationClass();
4 oAccess.Visible=false;
5 try
6 {
7 //Access9 oAccess.OpenCurrentDatabase(strFilePath,false);
8 //导出到Excel oAccess.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport,Access.AcSpreadSheetType.acSpreadsheetTypeExcel9,"test1","D:\\test1.xls",true,null,null);
9 //导出到txt oAccess.DoCmd.TransferText(Access.AcTextTransferType.acExportDelim,"","test1","D:\\test1.txt",true,"",0); oAccess.CloseCurrentDatabase(); oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone); System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
10 oAccess=null;
11 MessageBox.Show("导入成功");
12 }
13 catch(Exception ex)
14 {
15 MessageBox.Show(ex.Message);
16 }
17 finally
18 {
19 GC.Collect();
20 }
21 }
22
II.此方法速度也是超级快,只不过导出的格式非标准的Excel格式,默认工作表名与文件名相同2 {
3 Access.ApplicationClass oAccess=new Access.ApplicationClass();
4 oAccess.Visible=false;
5 try
6 {
7 //Access9 oAccess.OpenCurrentDatabase(strFilePath,false);
8 //导出到Excel oAccess.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport,Access.AcSpreadSheetType.acSpreadsheetTypeExcel9,"test1","D:\\test1.xls",true,null,null);
9 //导出到txt oAccess.DoCmd.TransferText(Access.AcTextTransferType.acExportDelim,"","test1","D:\\test1.txt",true,"",0); oAccess.CloseCurrentDatabase(); oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone); System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
10 oAccess=null;
11 MessageBox.Show("导入成功");
12 }
13 catch(Exception ex)
14 {
15 MessageBox.Show(ex.Message);
16 }
17 finally
18 {
19 GC.Collect();
20 }
21 }
22
1 private void DataTableToExcel(String strDBPath)
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath; System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString); System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
4 System.Data.DataTable dt=new System.Data.DataTable();
5 oda.Fill(dt);
6 con.Close();
7
8 String FilePath="D:\\test1.xls";
9 System.IO.FileStream objFileStream;
10 System.IO.StreamWriter objStreamWriter;
11 objFileStream=new System.IO.FileStream(FilePath,System.IO.FileMode.Create,System.IO.FileAccess.Write);
12 objStreamWriter=new System.IO.StreamWriter(objFileStream,System.Text.Encoding.Unicode);
13 String strLine="";
14 for(Int32 i=0;i<dt.Columns.Count;i++)
15 {//Convert.ToChar(9) \t
16 strLine=strLine+dt.Columns[i].ColumnName.ToString()+Convert.ToChar(9);
17 } objStreamWriter.WriteLine(strLine);
18 strLine="";
19 for(Int32 i=0;i<dt.Rows.Count;i++)
20 { strLine=strLine+(i+1)+Convert.ToChar(9);
21 for(Int32 j=1;j<dt.Columns.Count;j++)
22 { strLine=strLine+dt.Rows[i][j].ToString()+Convert.ToChar(9);
23 } objStreamWriter.WriteLine(strLine);
24 strLine="";
25 }
26 objStreamWriter.Close();
27 objFileStream.Close();
28 }
29
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath; System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString); System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
4 System.Data.DataTable dt=new System.Data.DataTable();
5 oda.Fill(dt);
6 con.Close();
7
8 String FilePath="D:\\test1.xls";
9 System.IO.FileStream objFileStream;
10 System.IO.StreamWriter objStreamWriter;
11 objFileStream=new System.IO.FileStream(FilePath,System.IO.FileMode.Create,System.IO.FileAccess.Write);
12 objStreamWriter=new System.IO.StreamWriter(objFileStream,System.Text.Encoding.Unicode);
13 String strLine="";
14 for(Int32 i=0;i<dt.Columns.Count;i++)
15 {//Convert.ToChar(9) \t
16 strLine=strLine+dt.Columns[i].ColumnName.ToString()+Convert.ToChar(9);
17 } objStreamWriter.WriteLine(strLine);
18 strLine="";
19 for(Int32 i=0;i<dt.Rows.Count;i++)
20 { strLine=strLine+(i+1)+Convert.ToChar(9);
21 for(Int32 j=1;j<dt.Columns.Count;j++)
22 { strLine=strLine+dt.Rows[i][j].ToString()+Convert.ToChar(9);
23 } objStreamWriter.WriteLine(strLine);
24 strLine="";
25 }
26 objStreamWriter.Close();
27 objFileStream.Close();
28 }
29
III.用Ado.net 此方法速度较以上两个显得慢了一些,数据量越大越明显
1 private void AdoExportExcel(String strDBPath)
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath; System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString); System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
4 System.Data.DataTable dt=new System.Data.DataTable();
5 oda.Fill(dt);
6 con.Close();
7
8 String strFilePath="D:\\test.xls";
9 Int64 totalCount=dt.Rows.Count;
10 Int64 rowRead=0;
11 float percent=0; System.Data.OleDb.OleDbParameter[] parm=new System.Data.OleDb.OleDbParameter[dt.Columns.Count];
12 String connString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strFilePath+";Extended Properties='Excel 8.0;'"; System.Data.OleDb.OleDbConnection objCon=new System.Data.OleDb.OleDbConnection(connString); System.Data.OleDb.OleDbCommand objCom=new System.Data.OleDb.OleDbCommand();
13 objCom.Connection=objCon;
14 objCon.Open();
15 //建立表结构 objCom.CommandText=@"create table Sheet1(序号 Integer,名称 varchar)";
16 objCom.ExecuteNonQuery();
17 //建立插入动作的Command,OleDbCommand是支持parameters的,但是使用的时候和SqlCommand有所区别 //sqlCom.CommandText=@"insert into Sheet1 values(@Id,@Name)"; objCom.CommandText=@"insert into Sheet1 values(?,?)";
18 parm[0]=new System.Data.OleDb.OleDbParameter("@Id",System.Data.OleDb.OleDbType.Integer);
19 objCom.Parameters.Add(parm[0]);
20 parm[1]=new System.Data.OleDb.OleDbParameter("@Name",System.Data.OleDb.OleDbType.VarChar);
21 objCom.Parameters.Add(parm[1]);
22 //便利DataTable将数据插入新建的Excel文件中
23 for(Int32 i=0;i<dt.Rows.Count;i++)
24 {
25 parm[0].Value=dt.Rows[i][0];
26 for(Int32 j=1;j<parm.Length;j++)
27 parm[j].Value=dt.Rows[i][j];
28 objCom.ExecuteNonQuery();
29 rowRead++;
30 percent=((float)(100*rowRead))/totalCount;
31 Console.WriteLine("正在导出数据,已导出["+percent.ToString("0.00")+"%]
");
32 if(i==dt.Rows.Count-1)
33 Console.WriteLine("请稍后
.");
34 System.Windows.Forms.Application.DoEvents();
35 }
36 objCon.Close();
37 }
38
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath; System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString); System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
4 System.Data.DataTable dt=new System.Data.DataTable();
5 oda.Fill(dt);
6 con.Close();
7
8 String strFilePath="D:\\test.xls";
9 Int64 totalCount=dt.Rows.Count;
10 Int64 rowRead=0;
11 float percent=0; System.Data.OleDb.OleDbParameter[] parm=new System.Data.OleDb.OleDbParameter[dt.Columns.Count];
12 String connString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strFilePath+";Extended Properties='Excel 8.0;'"; System.Data.OleDb.OleDbConnection objCon=new System.Data.OleDb.OleDbConnection(connString); System.Data.OleDb.OleDbCommand objCom=new System.Data.OleDb.OleDbCommand();
13 objCom.Connection=objCon;
14 objCon.Open();
15 //建立表结构 objCom.CommandText=@"create table Sheet1(序号 Integer,名称 varchar)";
16 objCom.ExecuteNonQuery();
17 //建立插入动作的Command,OleDbCommand是支持parameters的,但是使用的时候和SqlCommand有所区别 //sqlCom.CommandText=@"insert into Sheet1 values(@Id,@Name)"; objCom.CommandText=@"insert into Sheet1 values(?,?)";
18 parm[0]=new System.Data.OleDb.OleDbParameter("@Id",System.Data.OleDb.OleDbType.Integer);
19 objCom.Parameters.Add(parm[0]);
20 parm[1]=new System.Data.OleDb.OleDbParameter("@Name",System.Data.OleDb.OleDbType.VarChar);
21 objCom.Parameters.Add(parm[1]);
22 //便利DataTable将数据插入新建的Excel文件中
23 for(Int32 i=0;i<dt.Rows.Count;i++)
24 {
25 parm[0].Value=dt.Rows[i][0];
26 for(Int32 j=1;j<parm.Length;j++)
27 parm[j].Value=dt.Rows[i][j];
28 objCom.ExecuteNonQuery();
29 rowRead++;
30 percent=((float)(100*rowRead))/totalCount;
31 Console.WriteLine("正在导出数据,已导出["+percent.ToString("0.00")+"%]
![](https://www.cnblogs.com/Images/dot.gif)
32 if(i==dt.Rows.Count-1)
33 Console.WriteLine("请稍后
![](https://www.cnblogs.com/Images/dot.gif)
34 System.Windows.Forms.Application.DoEvents();
35 }
36 objCon.Close();
37 }
38
IV.此方法调用com组件(Excel),速度都慢于以上3个方法
1 private void ComExportToExcel(String strDBPath)
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath;
4 System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString);
5 System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
6 System.Data.DataTable dt=new System.Data.DataTable();
7 oda.Fill(dt);
8 con.Close();
9
10 String strFilePath="D:\\test.xls";
11 Int64 totalCount=dt.Rows.Count;
12 Int64 rowRead=0;
13 float percent=0;
14 Excel.Application xlApp=null;
15 xlApp=new Excel.ApplicationClass();
16 Excel.Workbooks workbooks=xlApp.Workbooks;
17 Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
18 Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
19 Excel.Range range;
20 //写入字段
21 for(Int32 i=0;i<dt.Columns.Count;i++)
22 {
23 worksheet.Cells[1,i+1]=dt.Columns[i].ColumnName;
24 range=(Excel.Range)worksheet.Cells[1,i+1];
25 }
26 for(Int32 r=0;r<dt.Rows.Count;r++)
27 {
28 for(Int32 i=0;i<dt.Columns.Count;i++)
29 { worksheet.Cells[r+2,i+1]=dt.Rows[r][i];
30 }
31 rowRead++;
32 percent=((float)(100*rowRead))/totalCount; Console.WriteLine("正在导出数据,已导出["+percent.ToString("0.00")+"%]
");
33 if(r==dt.Rows.Count-1) Console.WriteLine("请稍后
."); System.Windows.Forms.Application.DoEvents();
34 } range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+1,dt.Columns.Count]);
35 workbook.Saved=true;
36 workbook.SaveCopyAs(strFilePath);
37 }
38
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath;
4 System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString);
5 System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
6 System.Data.DataTable dt=new System.Data.DataTable();
7 oda.Fill(dt);
8 con.Close();
9
10 String strFilePath="D:\\test.xls";
11 Int64 totalCount=dt.Rows.Count;
12 Int64 rowRead=0;
13 float percent=0;
14 Excel.Application xlApp=null;
15 xlApp=new Excel.ApplicationClass();
16 Excel.Workbooks workbooks=xlApp.Workbooks;
17 Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
18 Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
19 Excel.Range range;
20 //写入字段
21 for(Int32 i=0;i<dt.Columns.Count;i++)
22 {
23 worksheet.Cells[1,i+1]=dt.Columns[i].ColumnName;
24 range=(Excel.Range)worksheet.Cells[1,i+1];
25 }
26 for(Int32 r=0;r<dt.Rows.Count;r++)
27 {
28 for(Int32 i=0;i<dt.Columns.Count;i++)
29 { worksheet.Cells[r+2,i+1]=dt.Rows[r][i];
30 }
31 rowRead++;
32 percent=((float)(100*rowRead))/totalCount; Console.WriteLine("正在导出数据,已导出["+percent.ToString("0.00")+"%]
![](https://www.cnblogs.com/Images/dot.gif)
33 if(r==dt.Rows.Count-1) Console.WriteLine("请稍后
![](https://www.cnblogs.com/Images/dot.gif)
34 } range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+1,dt.Columns.Count]);
35 workbook.Saved=true;
36 workbook.SaveCopyAs(strFilePath);
37 }
38
V.利用剪贴板 ,有人说此方法很快,不使用Web,web可以用二维数组
1 private void ExportToExcel(String strDBPath)
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath; System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString); System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
4 System.Data.DataTable dt=new System.Data.DataTable();
5 oda.Fill(dt);
6 con.Close();
7
8 String strFilePath="D:\\test.xls";
9 Object oMissing=System.Reflection.Missing.Value;
10 Excel.ApplicationClass xlApp=new Excel.ApplicationClass();
11 try
12 { xlApp.Visible=false; xlApp.DisplayAlerts=false; Excel.Workbooks oBooks=xlApp.Workbooks; Excel.Workbook xlWorkbook=null; xlWorkbook=oBooks.Open(strFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing); Excel.Worksheet xlWorksheet;
13 //添加一个新的Sheet页
14 xlWorksheet=(Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
15 //以TableName作为新加的Sheet页名 xlWorksheet.Name="企业";
16 //取出这个DataTable中的所有值,暂存于stringBuffer中
17 String stringBuffer="";
18 for(Int32 j=0;j<dt.Rows.Count;j++)
19 {
20 for(Int32 k=0;k<dt.Columns.Count;k++)
21 { stringBuffer+=dt.Rows[j][k].ToString(); if(k<dt.Columns.Count-1) stringBuffer+="\t";
22 } stringBuffer+="\n";
23 }
24 //利用系统剪贴板 System.Windows.Forms.Clipboard.SetDataObject("");
25 //将stringBuffer放入剪贴板 System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
26 //选中这个sheet页中的第一个单元格
27 ((Excel.Range)xlWorksheet.Cells[1,1]).Select();
28 //粘贴 xlWorksheet.Paste(oMissing,oMissing);
29 //清空系统剪贴板 System.Windows.Forms.Clipboard.SetDataObject("");
30
31 //保存并关闭这个工作薄 xlWorkbook.Close(Excel.XlSaveAction.xlSaveChanges,oMissing,oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook); xlWorkbook=null;
32 xlApp.Quit();
33 xlApp=null;
34 }
35 catch(Exception ex)
36 {
37 MessageBox.Show(ex.Message);
38 }
39 }
40
2 {
3 String AccessConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDBPath; System.Data.OleDb.OleDbConnection con=new System.Data.OleDb.OleDbConnection(AccessConnectionString); System.Data.OleDb.OleDbDataAdapter oda=new System.Data.OleDb.OleDbDataAdapter("select * from test1",con);
4 System.Data.DataTable dt=new System.Data.DataTable();
5 oda.Fill(dt);
6 con.Close();
7
8 String strFilePath="D:\\test.xls";
9 Object oMissing=System.Reflection.Missing.Value;
10 Excel.ApplicationClass xlApp=new Excel.ApplicationClass();
11 try
12 { xlApp.Visible=false; xlApp.DisplayAlerts=false; Excel.Workbooks oBooks=xlApp.Workbooks; Excel.Workbook xlWorkbook=null; xlWorkbook=oBooks.Open(strFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing); Excel.Worksheet xlWorksheet;
13 //添加一个新的Sheet页
14 xlWorksheet=(Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
15 //以TableName作为新加的Sheet页名 xlWorksheet.Name="企业";
16 //取出这个DataTable中的所有值,暂存于stringBuffer中
17 String stringBuffer="";
18 for(Int32 j=0;j<dt.Rows.Count;j++)
19 {
20 for(Int32 k=0;k<dt.Columns.Count;k++)
21 { stringBuffer+=dt.Rows[j][k].ToString(); if(k<dt.Columns.Count-1) stringBuffer+="\t";
22 } stringBuffer+="\n";
23 }
24 //利用系统剪贴板 System.Windows.Forms.Clipboard.SetDataObject("");
25 //将stringBuffer放入剪贴板 System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
26 //选中这个sheet页中的第一个单元格
27 ((Excel.Range)xlWorksheet.Cells[1,1]).Select();
28 //粘贴 xlWorksheet.Paste(oMissing,oMissing);
29 //清空系统剪贴板 System.Windows.Forms.Clipboard.SetDataObject("");
30
31 //保存并关闭这个工作薄 xlWorkbook.Close(Excel.XlSaveAction.xlSaveChanges,oMissing,oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook); xlWorkbook=null;
32 xlApp.Quit();
33 xlApp=null;
34 }
35 catch(Exception ex)
36 {
37 MessageBox.Show(ex.Message);
38 }
39 }
40