在平时的项目中,将数据导出到Excel的需求是很常见的,在此对一些常见的方法做以总结,并提供一种大数据量导出的实现。
使用OLEDB可以很方便导出Excel,思路很简单,处理时将Excel当做Access处理,利用SQL建表、插入数据。不多说了,直接看代码
使用OLEDB导出Excel public static void Export(DataTable dt, string filepath, string tablename) { // excel 2003格式 string connString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + " ;Extended Properties=Excel 8.0; " ; // Excel 2007格式 // string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;"; try { using (OleDbConnection con = new OleDbConnection(connString)) { con.Open(); StringBuilder strSQL = new StringBuilder(); strSQL.Append( " CREATE TABLE " ).Append( " [ " + tablename + " ] " ); strSQL.Append( " ( " ); for ( int i = 0 ; i < dt.Columns.Count; i ++ ) { strSQL.Append( " [ " + dt.Columns[i].ColumnName + " ] text, " ); } strSQL = strSQL.Remove(strSQL.Length - 1 , 1 ); strSQL.Append( " ) " ); OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con); cmd.ExecuteNonQuery(); for ( int i = 0 ; i < dt.Rows.Count; i ++ ) { strSQL.Clear(); StringBuilder strfield = new StringBuilder(); StringBuilder strvalue = new StringBuilder(); for ( int j = 0 ; j < dt.Columns.Count; j ++ ) { strfield.Append( " [ " + dt.Columns[j].ColumnName + " ] " ); strvalue.Append( " ' " + dt.Rows[i][j].ToString() + " ' " ); if (j != dt.Columns.Count - 1 ) { strfield.Append( " , " ); strvalue.Append( " , " ); } else { } } cmd.CommandText = strSQL.Append( " insert into [ " + tablename + " ]( " ) .Append(strfield.ToString()) .Append( " ) values ( " ).Append(strvalue).Append( " ) " ).ToString(); cmd.ExecuteNonQuery(); } con.Close(); } Console.WriteLine( " OK " ); } catch (Exception ex) { Console.WriteLine(ex.Message); } } 生成的Excel是2003还是2007,就是通过连接字符串来控制的(代码中的connString ),同时传递的文件名也要一致(xls or xlsx),不然会出现运行不成功或者生成的文件打不开的情况。
Excel本身提供com组件来实现对Excel的操作,它的优点是显而易见的,可以具体控制到操作excel中的任意一个单元格(内容+格式),利用oledb是做不到这一点的。当项目中需要使用现有模板生成Excel的时候使用该方法是很方便的。但该方法性能上慢,而且需要安装Excel相关组件,生成文件后内存中有时仍旧有Excel进程。如果是web的话不建议使用该方法,否则管理员和服务器都会疯掉的。
com生成Excel public static void Export(DataTable dt, string filepath) { ExcelApp.Application myExcel = new ExcelApp.Application(); // 新建文件 ExcelApp._Workbook mybook = myExcel.Workbooks.Add(); // 打开现有文件 // ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, // Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Type.Missing,Type.Missing, Type.Missing); // 打开文件在.net4.0中的写法,使用“命名参数”和“可选参数” // ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath); myExcel.Visible = true ; try { mybook.Activate(); ExcelApp._Worksheet mysheet = mybook.Worksheets.Add(); for ( int i = 0 ; i < dt.Rows.Count; i ++ ) { for ( int j = 0 ; j < dt.Columns.Count; j ++ ) { ExcelApp.Range cell = mysheet.get_Range((( char )( 65 + j)).ToString() + (i + 1 ).ToString()); cell.Select(); cell.Cells.FormulaR1C1 = dt.Rows[i][j] ?? "" ; } } // mybook.SaveAs(Filename: filepath); mybook.Save(); } catch (Exception ex) { } finally { mybook.Close(); myExcel.Quit(); GC.Collect(); } } 在使用com组件时,需要先在项目中添加“Microsoft.Office.Interop.Excel”引用。(代码中的ExcelApp是我给相关命名空间提供的别名,using ExcelApp = Microsoft.Office.Interop.Excel;)
使用微软提供的也可以很方便的生成excel。(将office2007(Word、Excel、PPT) 的文件后缀名修改为”.zip”,将得到的压缩包解压,发现里面就是xml文件。),具体代码我就不贴了,在使用中发现初始加载的时候也是比较慢的。
需要注意的是,该方法只能操作office 2007以上的版本,不支持0ffice 2003.
这个是朋友介绍的,一个开源的组件,使用时不需要安装office(极大的优势),也能具体到对一个单元格的控制。NPOI是POI的.NET版本,POI是一套用Java写成的库,能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件,支持的文件格式包括xls, doc, ppt等。(已迁移到)上提供了许多实际例子,而且也给了介绍和入门教程,代码我就不复制了。
需要注意的是,目前该方法只能操作office2003。
上面几种方法是比较常见的,但是当遇到大批量数据时,前两种就太不给力了,特别是使用com组件那种,它们都是一条一条的生成。不过,com中可以使用QueryTable来提高效率。
QueryTable批量导出数据 public static void Export( string filepath, string strSQL) { ExcelApp.Application myExcel = new ExcelApp.Application(); ExcelApp._Workbook mybook = myExcel.Workbooks.Add(); try { mybook.Activate(); ExcelApp._Worksheet mysheet = mybook.Worksheets.Add(); string conn = " ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample; " ; ExcelApp.QueryTable querytable = mysheet.QueryTables.Add(conn, mysheet.get_Range( " A1 " ), strSQL); querytable.Refresh( false ); mybook.SaveAs(Filename : filepath,AccessMode:ExcelApp.XlSaveAsAccessMode.xlNoChange); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { mybook.Close(); myExcel.Quit(); } } 该方法比一条一条的插入不知道要快上多少倍,但是在我这测试时发现不稳定,同样的代码第一天还很快,到第二天就很慢了(相差特别大),同时,也存在excel进程关不掉的问题。
既然这样,换个思路换个法子,既然一条一条的插入比较慢,那么批量插入呢,SQL语句中就有“select into”,能不能利用这个实现呢?查找资料后得知是可以的,在SQL Server中有OPENDATASOURCE(还有OPENROWSET)的 ,可以直接读取excel中 的数据。
SELECT * FROM OPENDATASOURCE ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source=D:\book.xls;Extended Properties=EXCEL 5.0 ' )... [ sheet1$ ] ; 也可以写入,
insert into OPENDATASOURCE ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source=D:\book.xls;Extended Properties=EXCEL 5.0 ' )... [ sheet1$ ] select Customer .name ,Product .fullname from [ v_Order ] 写入的话得要求该文件存在,并且第一行有数据(表头行)。
在使用OPENDATASOURCE前,需要先执行
exec sp_configure ' Ad Hoc Distributed Queries ' , ' 1 ' RECONFIGURE 不然的话会报错:“SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的访问”
但是,使用OPENDATASOURCE是在SQL Server进程中执行的,这也就导致生成的文件时在SQL Server服务器端的,无法在客户端直接生成。
也可以利用 BCP 来导出,速度上非常快,但是导出的Excel并不是标准格式的,如果客户不需要标准的格式,可以用这个来实现。
既然SQL Server中可以这样做,在Access中是不是也可以呢?按照这个思路查找帮助,发现也是可以的,就是用in
In select * from product in "" [ ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample; ] 在Access中运行上面的代码后,就可以查询中SQL Server数据库中的数据,不过不支持连接查询(用视图呗),而且要注意保留字的处理(字段添加“[]”)。 当然,使用in不仅仅支持SQL Server,对其他数据库也是支持的,可以看 ,或者的这篇文章。
刚开始的时候已经说过,在用Oledb处理Excel时可以把excel当做access,那么只需要将OLEDB导出Excel中的创建表、插入记录的SQL语句替换上面的查询语句就可以了(得将代码改为select into 才行,不然是生不成excel文件的),看看代码
oledb执行SQL public static void Export( string strSQL, string filepath) { // string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; string connString = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + filepath + " ;Extended Properties=Excel 12.0 Xml; " ; try { using (OleDbConnection conn = new OleDbConnection(connString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(strSQL, conn); cmd.ExecuteNonQuery(); conn.Close(); } Console.WriteLine( " OK " ); } catch (Exception ex) { Console.WriteLine(ex.Message); } } 使用上面的方法时,传递的SQL
select * into 商品 from product in "" [ ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample; ] 如果需要添加where、orderby的话就放在上面SQL的后面(into后的表名在Excel中就是sheet的名称)
select [ fullname ] as 名称, [ alias ] as 简称, [ price ] as 单价 into 商品 from product in "" [ ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample; ] where id_product > 1 order by fullname
大批量数据导出的时候,需要注意这样的一个问题,Excel2003格式一个sheet只支持65536行,excel 2007 就比较多,是1048576。