大量数据导出EXCEL

还有就是一张EXCEL表只能存 65535条记录吧？？

（到现在还不会怎么引用别人说的话呢，郁闷中就手写吧)To lovefootball
-----------------------
你提供的Rar类，好像不支持标准的Rar协议，压缩后不能用rar软件解开。建议楼主压缩直接调用winRar.exe，很好用的。可压缩文件，文件夹。To 楼主
-------------------------
lovfootball 所说的excel作为connection，通过insert数据加入；
意思就是你把excel文件当做数据库，Sheet1,Sheet2...SheetN当做表，
你用类似打开Access一样操作Excel，分批把数据导入Sheet1...SheetN中就可以了。

最好先把数据放入二维数组中，再一次性写入到excel，速度非常快

kuyalovfootball 所说的excel作为connection，通过insert数据加入；
意思就是你把excel文件当做数据库，Sheet1,Sheet2...SheetN当做表，
你用类似打开Access一样操作Excel，分批把数据导入Sheet1...SheetN中就可以了。因为我是刚毕业，很多不是很清楚的
所以想看看这个的例子不知可不可以提供？？

string   strCon   =   "   Provider   =   Microsoft.Jet.OLEDB.4.0   ;   Data   Source   =   c:\\sample.xls;Extended   Properties=Excel   8.0"   ;
  OleDbConnection   myConn   =   new   OleDbConnection   (   strCon   )   ;
  string   strCom   =   "   SELECT   *   FROM   [Sheet1$]   "   ;
  myConn.Open   (   )   ;
  file://打开数据链接，得到一个数据集
  OleDbDataAdapter   myCommand   =   new   OleDbDataAdapter   (   strCom   ,   myConn   )   ;
  file://创建一个   DataSet对象
  myDataSet   =   new   DataSet   (   )   ;
  file://得到自己的DataSet对象
  myCommand.Fill   (   myDataSet   ,   "[Sheet1$]"   )   ;
  file://关闭此数据链接
  myConn.Close   (   )   ;
－－－－－－－－－－－－－－－－－－－－－－
随手从网上摘的，没有测试，大概意思就是这样，你用oledb连接就可以了。可以从网上找一些代码，有好多呢。

static void ImportToExcel()
        {
            string strConnect = "Provider=SQLOLEDB;Password=TWSystem;Persist Security Info=false;User ID=sa;Initial Catalog=Fetion;Data Source=192.168.1.110";
            string strSelect = "Select VID,VTID FROM VoteInfo";
            System.Data.OleDb.OleDbDataAdapter myOle = new System.Data.OleDb.OleDbDataAdapter(strSelect, strConnect);
            DataSet ds = new DataSet();
            myOle.Fill(ds, "VoteInfo");
            string strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E:\\Testtt\\sample.xlsx;Extended Properties=Excel8.0";
            //System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
            string strCom = "insert into [Sheet1$] value(" + ds.ToString() + ")";
            //myConn.Open();
            //打开数据链接，得到一个数据集
            System.Data.OleDb.OleDbDataAdapter myCommand =new     System.Data.OleDb.OleDbDataAdapter(strCom, strCon);
            //创建一个       DataSet对象
            DataSet myDataSet = new DataSet();
            //得到自己的DataSet对象
            myCommand.Fill(myDataSet, "[Sheet1$]");
            //关闭此数据链接
            //myConn.Close();
        }
为什么我这样写会提示   "找不到可安装的 ISAM。"什么意思？？？我的目的是要从 VoteInfo 这个表中取出数据然后插入到EXCEL中，还有怎么让 EXCEL分页？？？

static void ImportToExcel()
        {
            string strConnect = "Provider=SQLOLEDB;Password=TWSystem;Persist Security Info=false;User ID=sa;Initial Catalog=Fetion;Data Source=192.168.1.110";
            string strSelect = "Select FailedID,FetionSIP FROM ServiceFailed";
            System.Data.OleDb.OleDbDataAdapter myOle = new System.Data.OleDb.OleDbDataAdapter(strSelect, strConnect);
            DataSet ds = new DataSet();
            myOle.Fill(ds, "VoteInfo");            string str = ds.Tables[0].Rows[0][0].ToString();
            int icount = ds.Tables[0].Rows.Count;            string strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E:\\Testtt\\sample.xlsx; Extended Properties = Excel 8.0; ";
            //string strCom = "insert into [Sheet1$](FailedID,FetionSIP,age) values('" + ds.Tables[0].Rows[0][0].ToString() + "','" + ds.Tables[0].Rows[0][1].ToString() + "','') ";
            for (int i = 0; i < icount; i++)
            {                string strCom = " insert into [Sheet1$](FailedID,FetionSIP,age) values('" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','');";                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, strCon);
                DataSet myDataSet = new DataSet();
                myCommand.Fill(myDataSet, "[Sheet1$]");
            }
        }这样可以实现我题目要求，，可是效率极低  就是在for (int i = 0; i < icount; i++)这个地方
如果批量插入：string strCom = "insert into [Sheet1$] select * from " + ds.Tables[0] + "\"";
这样的话提示错误：Microsoft Jet 数据库引擎找不到对象'VoteInfo'。请确定对象是否存在，并正确地写出它的名称和路径。
应该是说在Microsoft.Jet.OLEDB.4.0这个里面找不到 SQLOLEDB数据表'VoteInfo'
请教该如何解决？？？？？

怎么个读法？~？····
还有
insert into
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\Testtt\test.xlsx;',[Sheet1$])
Select FailedID,FetionSIP FROM ServiceFailed;我这个语句为什么报以下错误：
消息 7399，级别 16，状态 1，第 1 行
OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Windows.Forms;namespace lzy
{
    class ExportToExcel
    {
        Excel.Range m_objRange = null;
        Excel.Application m_objExcel = null;
        Excel.Workbooks m_objBooks = null;
        Excel._Workbook m_objBook = null;
        Excel.Sheets m_objSheets = null;
        Excel._Worksheet m_objSheet = null;
        Excel.QueryTable m_objQryTable = null;
        object m_objOpt = System.Reflection.Missing.Value;
        //DataBase-used variable
        private System.Data.SqlClient.SqlConnection sqlConn = null;
        private string strConnect = string.Empty;
        private System.Data.SqlClient.SqlCommand sqlCmd = null;        //Sheets variable
        private double dbSheetSize = 65535;//the hight limit number in one sheet
        private int intSheetTotalSize = 0;//total record can divied sheet number
        private double dbTotalSize = 0;//record total number
        /// <summary>
        /// 建构函数
        /// </summary>
        public void ExcelManager(){}        /// <summary>
        /// 建构函数
        /// </summary>
        /// <param name="dbHL">一个Excel表格的最大记录数</param>
        /// <param name="dbTotal">该数据库表共查询出多少条记录</param>
        /// <param name="intDivide">查询出的记录可分成几个Excel</param>
        /// <param name="conn">sqlConnection</param>
        public void ExcelManager(Double dbHL,Double dbTotal,int intDivide,SqlConnection conn )
        {
            dbSheetSize = dbHL;
            intSheetTotalSize = intDivide;
            dbTotalSize = dbTotal;
            sqlConn = conn;
        }
        /// <summary>
        /// 建构函数
        /// </summary>
        /// <param name="dbHL">一个Excel表格的最大记录数</param>
        /// <param name="strTableName">需查询的数据库的表名</param>
        /// <param name="conn">sqlConnection</param>
        public void ExcelManager(Double dbHL,string strTableName,SqlConnection conn)
        {
            dbSheetSize = dbHL;
            sqlConn = conn;
            intSheetTotalSize = GetTotalSize(strTableName,sqlConn);
        }        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        private void Dispose(bool disposing)
        {
            if(disposing)
            {
                // Dispose managed resources.
                Marshal.FinalReleaseComObject(m_objExcel);
                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                m_objBooks = null;
                m_objBook = null;
                m_objExcel = null;
            }
        }
        /// <summary>
        /// 取得总记录数跟可分成几个Excel sheet.
        /// </summary>
        /// <param name="strTableName">被查询的数据库的表名</param>
        /// <param name="sqlConn">sqlConnection</param>
        /// <returns>可分成Excel Sheet的个数</returns>
        private int GetTotalSize(string strTableName,SqlConnection sqlConn)
        {
            //sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);
            sqlCmd = new System.Data.SqlClient.SqlCommand("Select Count(*) From "+strTableName, sqlConn);
            if(this.sqlConn.State.ToString() == "Closed") sqlConn.Open();
            dbTotalSize = (int)sqlCmd.ExecuteScalar();
            sqlConn.Close();
            return (int)Math.Ceiling(dbTotalSize / this.dbSheetSize);
        }

        /// <summary>
        /// 新建一个Excel实例
        /// </summary>
        /// <param name="strTitle">Excel表头上的文字</param>
        public void DeclareExcelApp(string[] strTitle,string strSql,string strTableName,string strMastTitle)
        {
            m_objExcel = new Excel.ApplicationClass();
            m_objExcel.Visible = true;
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            if (intSheetTotalSize <= 3)
            {
                if (this.dbTotalSize <= this.dbSheetSize)
                {
                    this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
                    return;
                }
                else if (this.dbTotalSize <= this.dbSheetSize * 2)
                {
                    this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
                    this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle );
                    return;
                }
                else
                {
                    this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
                    this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle );
                    this.ExportDataByQueryTable(3, true,strTitle,strSql,strTableName,strMastTitle );
                    return;
                }
            }
            for (int i = 3; i < intSheetTotalSize; i++)
            {
                m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
            }
            ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
            for (int i = 2; i <= m_objSheets.Count; i++)
            {
                ExportDataByQueryTable(i, true,strTitle,strSql,strTableName,strMastTitle );
            }
        }
        /// <summary>
        /// 以用户输入的文件名保存文件
        /// </summary>
        public void SaveExcelApp()
        {
            string excelFileName = string.Empty;
            SaveFileDialog sf = new SaveFileDialog();
            sf.Filter = "*.xls|*.*";
            if (sf.ShowDialog() == DialogResult.OK)
            {
                excelFileName = sf.FileName;
            }
            else
            {
                return;
            }
            m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt,m_objOpt);
            if (m_objExcel != null)
                m_objExcel = null;
        }
        /// <summary>
        /// 利用Excel的QueryTable导出数据
        /// </summary>
        /// <param name="intSheetNumber">导出第几个sheet</param>
        /// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param>
        /// <param name="strTitle">表头,需与查询sql语句对齐一致。</param>
        /// <param name="strSql">查询的sql语句，表头的文字需与该sql语句对齐一致。</param>
        /// <param name="strTablName">查询的表名</param>
        /// <param name="strMastTitle">主标题</param>
        /// </summary>
        public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan,string[] strTitle,string strSql,string strTablName,string strMastTitle)
        {
            string strQuery = string.Empty;
            if (blIsMoreThan)
            {
                strQuery = "Select Top " +
                    this.dbSheetSize + strSql + "  From " + strTablName + " Where Not  OrderID In (Select Top " +
                    dbSheetSize * (intSheetNumber - 1) + "  OrderID From " + strTablName + ")";
            }
            else
            {
                strQuery = "Select Top " + this.dbSheetSize + strSql+ "  From "+strTablName;            }
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));            m_objSheet.Cells[1,1] = strMastTitle;
            m_objSheet.Cells[2,1] = "打印日期"+DateTime.Now.ToShortDateString();
            for(int i = 1;i<=strTitle.Length;i++)
            {
                m_objSheet.Cells[4,i] = strTitle[i-1].ToString();
            }
            m_objRange = m_objSheet.get_Range("A5", m_objOpt);
            m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + sqlConn.ConnectionString, m_objRange, strQuery);
            m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
            m_objQryTable.FieldNames = false;
            m_objQryTable.Refresh(false);
        }
    }
}

private void button1_Click(object sender, EventArgs e)
        {            String strConnet = "Data Source=ziyu;Initial Catalog=ddllocal;Persist Security Info=True;User ID=sa;Password=123456;";
            System.Data.SqlClient.SqlConnection sqlConn =
                new System.Data.SqlClient.SqlConnection(strConnet);
            lzy.ExportToExcel a = new ExportToExcel();
            a.ExcelManager(65530, "Products", sqlConn);
            try
            {
                a.DeclareExcelApp(new string[] { "编号", "供应商编号" }, "ProductsID,ProductsName ", "Products", "报表标题");
                //exc.SaveExcelApp();
            }
            catch (Exception E)
            {
                MessageBox.Show(E.ToString());
            }
            finally
            {
                a.Dispose();
            }        }

当添加Sheet2的时候出错。..执行到下面这句就报错了.是什么原因???
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));

提示无效索引号
捕捉到 System.Runtime.InteropServices.COMException
  Message="无效索引。 (异常来自 HRESULT:0x8002000B (DISP_E_BADINDEX))"
  Source="Interop.Excel"
  ErrorCode=-2147352565
  StackTrace:
       在 Excel.Sheets.get_Item(Object Index)
       在 WindowsApplication1.ExportToExcel.ExportDataByQueryTable(Int32 intSheetNumber, Boolean blIsMoreThan, String[] strTitle, String strSql, String strTablName, String strMastTitle, String OrderID) 位置 D:\Study\ExportToExcel\WindowsApplication1\ExportToExcel.cs:行号 185
       在 WindowsApplication1.ExportToExcel.DeclareExcelApp(String[] strTitle, String strSql, String strTableName, String strMastTitle) 位置 D:\Study\ExportToExcel\WindowsApplication1\ExportToExcel.cs:行号 125
       在 WindowsApplication1.Program.Main() 位置 D:\Study\ExportToExcel\WindowsApplication1\Program.cs:行号 27

修改类里的sql语句，哪里有个字段主鍵要修改在你表的主键                strQuery = "Select Top " +
                    this.dbSheetSize + strSql + "  From " + strTablName + " Where Not  ProductsID In (Select Top " +
                    dbSheetSize * (intSheetNumber - 1) + "  ProductsID From " + strTablName + ")";

我改成用参数传进来  就是到建立Sheet2的时候出错,,,数据不够你可以把分页条件改小啊我的数据才100多条我把分页改为50条就可以分3页了~ ~`
strQuery = "Select Top " +
                    this.dbSheetSize + strSql + "  From " + strTablName + " Where Not "+ OrderID + " In (Select Top " +
                    dbSheetSize * (intSheetNumber - 1) +   OrderID +" From " + strTablName + ")";

哈，楼上的，用数组的话可不是一行一行的插，而是一插到底Xlssheet.Range("a1").Resize(m, n).Value = arr

用多个数组，定死了数组的大小为一个Sheet的条数。一插到底也可以实现的嘛。

/*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型--邹建 2003.10(引用请保留此信息)--
增加分页功能
.5w条一页
--Add by 谢小漫--
*//*--调用示例 p_exporttb @sqlstr='select * from 地区资料'
  ,@path='c:\',@fname='aa.xls',@sheetname='地区资料'--*/
CREATE        proc p_exporttb
@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250),   --文件名
@sheetname varchar(250)=''  --要创建的工作表名,默认为文件名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000),@tmpsql varchar(8000)
declare @sheetcount int,@sheetnow int, @recordcount int, @recordnow int
declare @sheetsql varchar(8000)--创建页的sqldeclare @pagesize int
set @pagesize = 65000--sheet分页的大小
--set @pagesize = 1000--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
    +';DATABASE='+@sql+'"'--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr--创建表的SQL
declare @tbname sysnameset @tbname='##tmp_'+convert(varchar(38),newid())declare @tbtmpid nvarchar(50)
set @tbtmpid ='tmp_'+convert(varchar(38),newid())+''
--有序列ID @tbtmpid的临时表
set @sql='select Identity(int,1,1) as ['+@tbtmpid+'], a.* into ['+@tbname+'] from ( select top 100 percent b.* from ( '+@sqlstr+') b) a'
exec(@sql)--print(@sql)
--取得记录总数
set @recordcount= @@rowcount
if @recordcount=0 return
--print @recordcountselect @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
,@sql=@sql+',['+a.name+'] '
  +case
   when b.name like '%char'
   then case when a.length>255 then 'memo'
    else 'text('+cast(a.length as varchar)+')' end
   when b.name like '%int' or b.name='bit' then 'int'
   when b.name like '%datetime' then 'datetime'
   when b.name like '%money' then 'money'
   when b.name like '%text' then 'memo'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
and a.name <> @tbtmpidset @fdlist=substring(@fdlist,2,8000)
--print @fdlist--列数为零
if @@rowcount=0 return
set @sheetsql = @sql--print @sheetsql
--导入数据
--页数
set @sheetcount = CEILING(@recordcount/CAST(@pagesize as float))
--print @sheetcount
--只是一个页而已
IF @sheetcount = 1 BEGIN
    --print '只是一个页而已'    set @sql='create table ['+@sheetname
     +']('+substring(@sheetsql,2,8000)+')'


    exec @err=sp_oamethod @obj,'execute',@out out,@sql
    if @err<>0 goto lberr
        set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
       ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

    exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
END--多个页set @sheetnow = @sheetcount
set @recordnow= 0
IF @sheetcount > 1 BEGIN
    --print '多个页'
WHILE @sheetnow > 0 BEGIN    --创建页
    set @sql='create table ['+@sheetname+'_'+ convert(nvarchar(80),@sheetcount - @sheetnow + 1)
    +']('+substring(@sheetsql,2,8000)+')'


    exec @err=sp_oamethod @obj,'execute',@out out,@sql
    if @err<>0 goto lberr

    --print @sql
    --创建页end
    IF @sheetnow = @sheetcount BEGIN
        set @tmpsql ='select top '+str(@pagesize)+' '+@fdlist+' from ['+@tbname+']'
        set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
    ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])'

        exec('insert into '+@sql+'('+@fdlist+') '+ @tmpsql)
    END
    IF @sheetnow < @sheetcount BEGIN
        set @tmpsql='select top '+str(@pagesize)+' '+@fdlist+' from ['+@tbname+'] where ['+@tbtmpid
    +'] not in ( select top '+str(@recordnow-@pagesize)+' ['+@tbtmpid+'] from ['+@tbname+'])'        set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
    ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+ convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])'

        exec('insert into '+@sql+'('+@fdlist+') '+ @tmpsql)
        --print (@tmpsql)
        --exec(@tmpsql)
    END

    --print (@tmpsql)        --exec (@tmpsql)        set @recordnow = @pagesize*(@sheetcount-@sheetnow+2)
    set @sheetnow = @sheetnow -1
END
ENDset @sql='drop table ['+@tbname+']'
exec(@sql)
exec @err=sp_oadestroy @obj--结束返回
returnlberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist

调试易

大量数据导出EXCEL

解决方案 »