大批量数据导出到Excel的实现

在平时的项目中，将数据导出到Excel的需求是很常见的，在此对一些常见的方法做以总结，并提供一种大数据量导出的实现。

• OLEDB

　　  使用OLEDB可以很方便导出Excel，思路很简单，处理时将Excel当做Access处理，利用SQL建表、插入数据。不多说了，直接看代码 　　

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本身提供com组件来实现对Excel的操作，它的优点是显而易见的，可以具体控制到操作excel中的任意一个单元格（内容+格式），利用oledb是做不到这一点的。当项目中需要使用现有模板生成Excel的时候使用该方法是很方便的。但该方法性能上慢，而且需要安装Excel相关组件，生成文件后内存中有时仍旧有Excel进程。如果是web的话不建议使用该方法，否则管理员和服务器都会疯掉的。 

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;）

• Open xml

　　使用微软提供的Open XML SDK也可以很方便的生成excel。（将office2007（Word、Excel、PPT） 的文件后缀名修改为”.zip”,将得到的压缩包解压，发现里面就是xml文件。）,具体代码我就不贴了，在使用中发现初始加载的时候也是比较慢的。

　　需要注意的是，该方法只能操作office 2007以上的版本，不支持0ffice 2003.

• NPOI

　　这个是朋友介绍的，一个开源的组件，使用时不需要安装office（极大的优势），也能具体到对一个单元格的控制。NPOI是POI的.NET版本，POI是一套用Java写成的库，能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。官网（已迁移到googlecode）上提供了许多实际例子，而且atao也给了介绍和入门教程，代码我就不复制了。

　　需要注意的是，目前该方法只能操作office2003。

• 大批量数据

　　上面几种方法是比较常见的，但是当遇到大批量数据时，前两种就太不给力了，特别是使用com组件那种，它们都是一条一条的生成。不过，com中可以使用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

select    *   from  product
in  ""  [ ODBC;Driver=SQL Server;Server=.;uid=sa;pwd=sa;database=sample; ]

　　在Access中运行上面的代码后，就可以查询中SQL Server数据库中的数据，不过不支持连接查询（用视图呗），而且要注意保留字的处理（字段添加“[]”）。 当然，使用in不仅仅支持SQL Server，对其他数据库也是支持的，可以看MSDN ，或者ACMAIN_CHM的这篇文章。

　　刚开始的时候已经说过，在用Oledb处理Excel时可以把excel当做access，那么只需要将OLEDB导出Excel中的创建表、插入记录的SQL语句替换上面的查询语句就可以了（得将代码改为select into 才行，不然是生不成excel文件的），看看代码

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。