大量数据导出为Excel文件的问题解决

2010-12-20 10:53

大量数据导出为Excel文件的问题解决(C#,ASP.NET)

依据SZW朋友的提醒:本文所说的Excel文件指.xls文件,不包括Excel2007的默认保存文档格式.

大量数据导出到Excel文件，一般情况有以下三个麻烦：
1.导出的执行速度要比较快，否则有http请求超时的问题；
    当然如果您是使用winForm方式就不在此列。
2.Excel文件有最多65535行数据的限制；
3.数字会有格式变成指数方式的麻烦；
    比如身份证号码，以数字方式保存后，信息末几位有丢失的情况。

解决思路：
1.使用oledb，把excel文件当作数据库处理，效率相对其他五花八门的导出方案来说，算比较高的。虽然不是最高的，但得到的excel文件是真正的二进制Excel文件，如果用户好要把导出的数据整理后导回到系统，那么恭喜了，这种方法是你最好的选择。
2.每个sheet最多最多65535行数据，但一个文件可以有很多个sheet文件，如果您要导出的数据超过65535个，那么需要设法把多余的数据放到第二、三....个sheet中去。
3.一个sheet我们可以看作是一个数据库的表，那么我们是不是可以通过定义表的字段的数据属性来保证数据的正确格式呢？

关键代码：
备注：完整的代码是偶写的工程代码，版权所有的。但大家可以根据我的思路和关键代码的提示自己完成想要的功能，呵呵
代码片段一 sheet创建：
//生成创建表的脚本
        StringBuilder sb = new StringBuilder();
        sb.Append("CREATE TABLE ");
        sb.Append("[" + tableName + "] ( ");

        for (int i = 0; i < headers.Length; i++)
        {
            string datatype;
            switch (dt.Columns[columns[i]].DataType.Name.ToLower())
            {
                case "float": datatype = "float"; break;
                case "int32": datatype = "int"; break;
                case "double": datatype = "double"; break;
                case "decimal": datatype = "float"; break;
                default: datatype = "text"; break;
            }
            if (i < headers.Length - 1)
            {
                sb.Append(string.Format("[{0}] {1},", headers[i], datatype));
            }
            else
            {
                sb.Append(string.Format("[{0}] {1})", headers[i], datatype));
            }
        }
        return sb.ToString();
大家看到了把，我们可以使用create语句创建sheet。我们使用的表的名称，将变成sheet名称。
建议大家导出的名称可以这样依次起：比如产品信息数据，表名可以分别叫 产品信息(第1页)、产品信息(第2页)等等。

代码片段二 按照需要自动创建多个sheet：
for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建sheet
                if (i % 65535 == 0)
                {
                    tableName = string.Format("{0}Page{1}", dt.TableName, (i / 65535) + 1);
                    objCmd.CommandText = GetCreateSheetSQL(dt, headers, columns, tableName);
                    objCmd.ExecuteNonQuery();
                }
                插入语句#region 插入语句
                sb.Append("INSERT INTO ");
                sb.Append("[" + tableName + "] ( ");
.
最后提醒大家：excel作为数据库的时候，是可以支持事务处理的哦，大家别忘了使用事务机制。

如果大家处理的数据量比较小，要求的格式非常复杂，比如多重表头，有统计图形等，那么请使用其他的excel导出方法。本解决方法适用范围前面已经说过了，请大家参考。
http://piedpiper.cnblogs.com
本文是偶原创，想转贴的朋友劳驾写明转贴二字。呵呵

 

 

/*--数据导出EXCEL
 
 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
 ,如果文件不存在,将自动创建文件
 ,如果表不存在,将自动创建表
 基于通用性考虑,仅支持导出标准数据类型
作者：邹建
--*/

/*--调用示例

 p_exporttb @sqlstr='select * from 地区资料'
  ,@path='c:\',@fname='aa.xls',@sheetname='地区资料'
--*/



create proc p_exporttb
@tbname sysname,    --要导出的表名
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(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)

--参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.xls'

--检查文件是否已经存在
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 lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

/*--如果覆盖已经存在的表,就加上下面的语句
--创建之前先删除表/如果存在的话
select @sql='drop table ['+@tbname+']'
exec @err=sp_oamethod @obj,'execute',@out out,@sql
--*/

--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case when b.name in('char','nchar','varchar','nvarchar') then
     'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
   when b.name in('tynyint','int','bigint','tinyint') then 'int'
   when b.name in('smalldatetime','datetime') then 'datetime'
   when b.name in('money','smallmoney') then 'money'
   else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
 and object_id(@tbname)=id
select @sql='create table ['+@tbname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
   ;DATABASE='+@path+@fname+''',['+@tbname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)

return

lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist

GO


========================================
NinGoo注：
 Excel文件每个工作表不能超过65536条记录


解决办法：

.如果数据量大于65536，可以在调用存储过程前先将要导出的table拆分成几个小的临时table，然后
在分别导出到不同的工作表中