我用以下的代码创建EXCEL,用P4 CPU2.7G、内存256M、WIN2K的PC做每1000条耗时1分钟,有时超过6000条会内存不足;配置差的PC则会死机。其实这些数据做成的EXCEL文件只不过1M多。
是否有类似flush(FILE *stream)的函数,让它部分写入硬盘。//-----------------------------------------------------
#include<Comobj.hpp>void __fastcall TQueryForm::SaveToExcel(char * FileName)
{
Variant ex,wk,sht,Range; //ole万能变量,定义excel对象使用try{
ex=CreateOleObject("Excel.Application"); //启动Excel
wk=ex.OlePropertyGet("ActiveWorkBook");//创建工作簿对象
ex.OlePropertyGet("WorkBooks").OleFunction("Add");//添加一个工作薄
sht=ex.OlePropertyGet("ActiveSheet");//创建工作区
}
catch(...)
{
MessageDlg("无法启动Excel,可能尚未安装或文件已经损坏!",mtError,TMsgDlgButtons()<<mbYes,0);
return;
}
int iSRow=CheckBox4->Checked ? 0L: 1L;
int iSCol=CheckBox1->Checked ? 0L: 1L; char *cRange;
cRange=CountCol("A1:", StringGrid1->ColCount - iSCol, 1); Range = sht.OlePropertyGet("Range",cRange);
Range.OleFunction("Merge", false);
//Range.OlePropertySet("VerticalAlignment", Edit1->Text.ToInt());//居中
Range.OlePropertySet("HorizontalAlignment",3);//居中
Range.OlePropertyGet("Interior").OlePropertySet("ColorIndex",24);
Range .OlePropertyGet("Borders",1).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",2).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",3).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",4).OlePropertySet("LineStyle",1); ex.OlePropertySet("Caption","通用查询结果转入EXCEL:");
ex.OlePropertySet("StatusBar"," 【SQL TOOLS】 WGHSoft.ICBC Tel:0571-87924880 Email: [email protected]");
sht.OlePropertySet("Name", "核对单");
sht.OlePropertyGet("Cells",1,1).OlePropertySet("Value",MainForm->QueryName.c_str());
sht.OlePropertyGet("Cells",1,1).OlePropertyGet("Font").OlePropertySet("Name","华文中宋");
sht.OlePropertyGet("Cells",1,1).OlePropertyGet("Font").OlePropertySet("Size",28); int i=2; if(RadioGroup1->ItemIndex==0){ //纵向
for(int row=iSRow;row<StringGrid1->RowCount;row++,i++)
for(int j=1, col=iSCol;col<StringGrid1->ColCount;col++,j++){
if(i==2) ex.OlePropertyGet("Cells",j).OlePropertySet("ColumnWidth", StringGrid1->ColWidths[j-iSRow]/7); //宽
if(i%2==0)
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(213,251,206));
else
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(188,244,254)); switch(FieldType[col-1]){
case ftFloat:
case ftCurrency:
case ftBCD:
if(BigData(StringGrid1->Cells[col][row].c_str()))
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
break;
default:
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
break;
}
sht.OlePropertyGet("Cells",i, j).OlePropertySet("Value", Trim(StringGrid1->Cells[col][row]).c_str()); }
}
else{ //横向
for(int col=iSCol;col<StringGrid1->ColCount;col++, i++)
for(int j=1, row=iSRow;row<StringGrid1->RowCount;row++, j++){
if(i%2==0)
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(213,251,206));
else
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(188,244,254));
if(BigData(StringGrid1->Cells[col][row].c_str()))
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
sht.OlePropertyGet("Cells",i, j).OlePropertySet("Value", StringGrid1->Cells[col][row].c_str());
}
} if(CheckBox4->Checked){ //标题栏色
cRange=CountCol("A2:", StringGrid1->ColCount - iSCol, 2);
Range = sht.OlePropertyGet("Range", cRange);
Range.OlePropertyGet("Interior").OlePropertySet("ColorIndex",24);
}
if(CheckBox1->Checked){ //序号栏色
cRange=CountCol("A3:", 1, i-1);
Range = sht.OlePropertyGet("Range", cRange);
Range.OlePropertyGet("Interior").OlePropertySet("ColorIndex",24);
}
cRange=CountCol("A2:", StringGrid1->ColCount - iSCol, i-1); Range = sht.OlePropertyGet("Range", cRange);
Range .OleProcedure("Select");
Range .OlePropertyGet("Borders",1).OlePropertySet("LineStyle",1);//边框
Range .OlePropertyGet("Borders",2).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",3).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",4).OlePropertySet("LineStyle",1); Range .OlePropertySet("RowHeight", 20); //行高 sht.OleFunction("SaveAs",FileName); //表格保存
//ex.OleFunction("Quit"); //退出Excel
ex.OlePropertySet("Visible",(Variant)true); //使Excel可见}
是否有类似flush(FILE *stream)的函数,让它部分写入硬盘。//-----------------------------------------------------
#include<Comobj.hpp>void __fastcall TQueryForm::SaveToExcel(char * FileName)
{
Variant ex,wk,sht,Range; //ole万能变量,定义excel对象使用try{
ex=CreateOleObject("Excel.Application"); //启动Excel
wk=ex.OlePropertyGet("ActiveWorkBook");//创建工作簿对象
ex.OlePropertyGet("WorkBooks").OleFunction("Add");//添加一个工作薄
sht=ex.OlePropertyGet("ActiveSheet");//创建工作区
}
catch(...)
{
MessageDlg("无法启动Excel,可能尚未安装或文件已经损坏!",mtError,TMsgDlgButtons()<<mbYes,0);
return;
}
int iSRow=CheckBox4->Checked ? 0L: 1L;
int iSCol=CheckBox1->Checked ? 0L: 1L; char *cRange;
cRange=CountCol("A1:", StringGrid1->ColCount - iSCol, 1); Range = sht.OlePropertyGet("Range",cRange);
Range.OleFunction("Merge", false);
//Range.OlePropertySet("VerticalAlignment", Edit1->Text.ToInt());//居中
Range.OlePropertySet("HorizontalAlignment",3);//居中
Range.OlePropertyGet("Interior").OlePropertySet("ColorIndex",24);
Range .OlePropertyGet("Borders",1).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",2).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",3).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",4).OlePropertySet("LineStyle",1); ex.OlePropertySet("Caption","通用查询结果转入EXCEL:");
ex.OlePropertySet("StatusBar"," 【SQL TOOLS】 WGHSoft.ICBC Tel:0571-87924880 Email: [email protected]");
sht.OlePropertySet("Name", "核对单");
sht.OlePropertyGet("Cells",1,1).OlePropertySet("Value",MainForm->QueryName.c_str());
sht.OlePropertyGet("Cells",1,1).OlePropertyGet("Font").OlePropertySet("Name","华文中宋");
sht.OlePropertyGet("Cells",1,1).OlePropertyGet("Font").OlePropertySet("Size",28); int i=2; if(RadioGroup1->ItemIndex==0){ //纵向
for(int row=iSRow;row<StringGrid1->RowCount;row++,i++)
for(int j=1, col=iSCol;col<StringGrid1->ColCount;col++,j++){
if(i==2) ex.OlePropertyGet("Cells",j).OlePropertySet("ColumnWidth", StringGrid1->ColWidths[j-iSRow]/7); //宽
if(i%2==0)
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(213,251,206));
else
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(188,244,254)); switch(FieldType[col-1]){
case ftFloat:
case ftCurrency:
case ftBCD:
if(BigData(StringGrid1->Cells[col][row].c_str()))
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
break;
default:
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
break;
}
sht.OlePropertyGet("Cells",i, j).OlePropertySet("Value", Trim(StringGrid1->Cells[col][row]).c_str()); }
}
else{ //横向
for(int col=iSCol;col<StringGrid1->ColCount;col++, i++)
for(int j=1, row=iSRow;row<StringGrid1->RowCount;row++, j++){
if(i%2==0)
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(213,251,206));
else
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(188,244,254));
if(BigData(StringGrid1->Cells[col][row].c_str()))
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
sht.OlePropertyGet("Cells",i, j).OlePropertySet("Value", StringGrid1->Cells[col][row].c_str());
}
} if(CheckBox4->Checked){ //标题栏色
cRange=CountCol("A2:", StringGrid1->ColCount - iSCol, 2);
Range = sht.OlePropertyGet("Range", cRange);
Range.OlePropertyGet("Interior").OlePropertySet("ColorIndex",24);
}
if(CheckBox1->Checked){ //序号栏色
cRange=CountCol("A3:", 1, i-1);
Range = sht.OlePropertyGet("Range", cRange);
Range.OlePropertyGet("Interior").OlePropertySet("ColorIndex",24);
}
cRange=CountCol("A2:", StringGrid1->ColCount - iSCol, i-1); Range = sht.OlePropertyGet("Range", cRange);
Range .OleProcedure("Select");
Range .OlePropertyGet("Borders",1).OlePropertySet("LineStyle",1);//边框
Range .OlePropertyGet("Borders",2).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",3).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",4).OlePropertySet("LineStyle",1); Range .OlePropertySet("RowHeight", 20); //行高 sht.OleFunction("SaveAs",FileName); //表格保存
//ex.OleFunction("Quit"); //退出Excel
ex.OlePropertySet("Visible",(Variant)true); //使Excel可见}
解决方案 »
- 心情好哦,散散分,呵呵~~~~ 顺便请大家帮我抓抓虫子,谢谢啦!
- 俄罗斯方块
- 请教外部调用成的问题!解决就给分!
- 编译时提示“[Fatal Error] Salary.dpr(9): File not found: 'crypt.dcu'”。
- 再请教大家一个菜的问题!!!!!!!!!!!!-----------在线等
- 求教关于ReportBuilder的问题~~~~~~~
- 如何在delphi源程序中操作sql数据库
- 50分请问HLP型帮助文件用什么软件制作,最好提供下载地址。
- 自定义文件格式的问题
- 今天我大寿,分5贴散分1000(3 of 5),请各位自觉莫重复接分!
- 请教使用DBGIRD的高手中的高手,关于DBGRID的事件的问题,大家都来参与哦!
- 如何把光标放在Dbgrid中的第4列上
没办法,我是写到临时CSV文件中,然后用EXCEL打开此CSV文件
此时速度到是很快,就是格式控制不那么灵活了!
ex.OlePropertyGet("Visible",(Variant)false); ;//创建工作簿对象填充完毕再让他可见!
var
xlApp,xlBook,xlSheet,xlQuery: Variant;
adoConnection,adoRecordset: Variant;
begin
adoConnection := CreateOleObject('ADODB.Connection');
adoRecordset := CreateOleObject('ADODB.Recordset');
adoConnection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Tree.mdb;Persist Security Info=False');
adoRecordset.CursorLocation := adUseClient;
adoRecordset.Open('SELECT * FROM tree',adoConnection,1,3); try
xlApp := CreateOleObject('Excel.Application');
xlBook := xlApp.Workbooks.Add;
xlSheet := xlBook.Worksheets['sheet1'];
xlApp.Visible := True; //把查询结果导入EXCEL数据
xlQuery := xlSheet.QueryTables.Add(adoRecordset,xlSheet.Range['A1']); //关键是这一句
xlQuery.FieldNames := True;
xlQuery.RowNumbers := False;
xlQuery.FillAdjacentFormulas := False;
xlQuery.PreserveFormatting := True;
xlQuery.RefreshOnFileOpen := False;
xlQuery.BackgroundQuery := True;
//xlQuery.RefreshStyle := xlInsertDeleteCells;
xlQuery.SavePassword := True;
xlQuery.SaveData := True;
xlQuery.AdjustColumnWidth := True;
xlQuery.RefreshPeriod := 0;
xlQuery.PreserveColumnInfo := True;
xlQuery.FieldNames := True;
xlQuery.Refresh; xlBook.SaveAs('d:\fromD.xls',xlNormal,'','',False,False); finally
if not VarIsEmpty(XLApp) then begin
XLApp.displayAlerts:=false;
XLApp.ScreenUpdating:=true;
XLApp.quit;
end;
end;
end;
我用OLE把excel导入access,
6K条记录,导了10分钟.
写入数据的时候可以用进度条显示进度,这样不会等得太枯燥.
高人,建议加上一段音乐,节奏快,时间长的那种,或随机播放一段大片的片头。
用delphi 自带的控件感觉好点!
就是用写文本的方式在每一个字段后面加一个TAB,保存时用*.xls。这样可以生成Excel。