之前做了一个对将数据导出到Excel的性能优化。
原有程序对格式的控制比较精致,但是缺点在于它是逐cell写数据的,效率是在是太低了,200万数据导了三个小时。但好处是这样子进度比较好跟踪。
要注意,excel每页最大允许记录数是65536行,列为256列,所以最好要定义个全局常量来限制。一般来说列是不会超过256的,行是很容易超过。
目前我所找到的最快的方法有两种
1.利用OLE编程中Excel自带的querytables属性,将查询SQL输入,直接得到结果
2.利用Excel的剪贴板属性,一页粘贴
这两种方法都涉及到分页。这里介绍下第一种,这一种相对而言还要快一些。
分页时采用 ceil(记录总数/每页最大记录数),round()也是可以的,以这个结果作为excel的页号。注意使用querytables时它是将所有数据一次性写入excel,如果你查询SQL选择了所有数据,然后想再用ADOQuery自带的filter属性是不对的,导完后就会报数据超出最大限制。因为它始终只读取最原始的SQL。所以过滤应该在原始SQL上就进行了。
贴代码
{1.获取查询记录行数}
with qryADOExport do
begin
if qryADOExport.Active then Close;
qryADOExport.SQL.Text:='select count(1) cnt from ('+mmo1.Lines.Text+' )';//根据输入的SQL(末尾勿加分号)查询记录总数
try
qryADOExport.Open;
except
on E:Exception do
begin
Application.MessageBox(PChar('执行SQL错误,原因为'+E.Message),'错误',0);
Exit;
end;
end;
end;
if qryADOExport.FieldByName('cnt').asinteger>MaxPerSheet-2 then //每一页留出页首和页尾,供写标题行与统计行
begin
iPageCount := Round(qryADOExport.FieldByName('cnt').asinteger/(MaxPerSheet-2));
iRecordCount := MaxPerSheet-2;
end
else begin
iPageCount := 1;
iRecordCount := qryADOExport.FieldByName('cnt').asinteger;
end;
if qryADOExport.Active then
qryADOExport.Close;
StartTime:=Now;
ExcelApp.SheetsInNewWorkbook := 1;
while iPageCount>0 do
begin
if SheetNo>0 then ExcelApp.Sheets.Add;
Inc(SheetNo);
//stringreplace,第四个参数为空,默认替换第一个
strSql:='select * from ('+StringReplace(UpperCase(mmo1.Lines.Text),'SELECT','SELECT CEIL(ROWNUM/'+Inttostr(MaxPerSheet-2)+') L_PAGENO,',[])+')';//替换第一个select,加上序号字段,为分页做好准备。
strFilter:=' where l_pageno='+inttostr(iPageCount);
ExcelApp.ActiveSheet.Name := '第'+IntToStr(iPageCount)+'页';
xlSheet := ExcelApp.ActiveSheet;
j := 0;
iNextRow := 0;
if iCount < iPageCount then
begin
Dec(iPageCount);
Inc(iNextRow);
strTmp:= 'OLEDB;'+qryADOExport.ConnectionString;
xlQuery := xlSheet.QueryTables.Add(strTmp ,xlSheet.Range[Format('A%d', [iNextRow])],strSql + strFilter);//此方法重点,使用QueryTables属性,依次传入数据库连接串,列标题,查询SQL。注意此处连接串应该使用OLEDB类型的
xlQuery.FieldNames := false;
xlQuery.Refresh;
Inc(iNextRow, iRecordCount);
end
else Break;
end;
效率方面测了一下,20万数据集,十几个字段,导了一分钟左右,效率有非常显著的提高。
分享到:
相关推荐
这是我在公司实习的时候研究的课题,主要将大量数据从数据库导出到Excel文件。(一般是值Excel2007及以后版本)因为Excel2003及以前的版本中能保持的数据量是有限制的。Excel2007及以后版本中的数据能保持100万条...
C#大量数据导出到Excel,超过65536行时自动分页导出
如果碰到需要导出大量数据又该如何操作呢? 本文主要解决以上两个问题。 (2)具体步骤如下: 1.第一步,安装openpyxl, 使用pip install openpyxl即可,但是在windows下安装的是2.2.6版本,但是centos自动安装的是...
java中将数据和图片导出到Excel文件
根据Microsoft.Office.Interop.Excel,优化大数据量快速导出到 Excel 的方法
POI3.8版本之前的版本处理大量数据的导出Excel效果不是很理想,主要在与Excel2003版本单个Sheet的行限制为65536,大量数据的导出得分多个Sheet,针对这一点,客户就不会满意。其次,在实验过程中,大数据量的导出很...
见到导出大量数据 自己定义Excel表格样式 用html当时定义见到导出大量数据 自己定义Excel表格样式 用html当时定义见到导出大量数据 自己定义Excel表格样式 用html当时定义见到导出大量数据 自己定义Excel表格样式 用...
C#中把查询数据结果轻松导出到EXCEL表格中.
C# datatable直接导出数据到Excel,(数据量百万级只需3秒)
java poi导出大量数据到Excel
导出大量数据到excel,本人已亲测,对几百万数据已测试过,导出数据比较乐观,里面自带压缩文件功能。
js 导出 Excel 【支持大量数据导出,亲测】、支持IE噢
解决大批量数据导出Excel产生内存溢出的方案
前端导出数据为excel(兼容chrome 和 IE10以上版本),列举了网上常用的两种方法外,新增了可以导出超过5W行数据的方法
主要为大家详细介绍了js实现数据导出为EXCEL,支持大量数据导出,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
C#快速导出Excel文件,3秒可导10万行数据
Excel数据批量导入与Excel文件的导出,Excel数据批量导入与Excel文件的导出
这是我在公司实习的时候研究的课题,主要将大量数据从数据库导出到Excel文件。(一般是值Excel2007及以后版本)因为Excel2003及以前的版本中能保持的数据量是有限制的。Excel2007及以后版本中的数据能保持100万条...
1,用NPOI导出数据到Excel。 2,微软Excel,Com组件技术。 3,VBA+ASPX技术实现。 4,OleDb数据访问技术。