Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

export delphi stringgrid to excel

I'm trying to export data from a stringgrid in delphi 7 to microsoft excel. I have been using this code to do it:

  objExcel := TExcelApplication.Create(nil);
  objExcel.Visible[LOCALE_USER_DEFAULT] := true;
  objWB := objExcel.workbooks.add(null,LOCALE_USER_DEFAULT);
  lineNumber := 1;

  for i:=1 to stringgrid1.rowcount-1 do begin
    for j:=0 to stringgrid1.ColCount-1 do begin
      objWB.Worksheets.Application.Cells.Item[i+lineNumber,j+1] := ''''+stringgrid1.Cells[j,i];
    end;
  end;

but when the data is big, it takes a very long time to finish. is there other faster way to export data from delphi 7 stringgrid to excel?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The quickest way is to use an array of Variant,and just pass the entire array to Excel:

uses OleAuto;

var
  xls, wb, Range: OLEVariant;
  arrData: Variant;
  RowCount, ColCount, i, j: Integer;
begin
  {create variant array where we'll copy our data}
  RowCount := StringGrid1.RowCount;
  ColCount := StringGrid1.ColCount;
  arrData := VarArrayCreate([1, RowCount, 1, ColCount], varVariant);

  {fill array}
  for i := 1 to RowCount do
    for j := 1 to ColCount do
      arrData[i, j] := StringGrid1.Cells[j-1, i-1];

  {initialize an instance of Excel}
  xls := CreateOLEObject('Excel.Application');

  {create workbook}
  wb := xls.Workbooks.Add;

  {retrieve a range where data must be placed}
  Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
                                  wb.WorkSheets[1].Cells[RowCount, ColCount]];

  {copy data from allocated variant array}
  Range.Value := arrData;

  {show Excel with our data}
  xls.Visible := True;
end;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...