Export delphi stringgrid to excel

Question

I am trying to export data from stringgrid to delphi 7 in microsoft excel. I used this code for this:

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 large, it takes a very long time to complete. is there any other faster way to export data from delphi 7 stringgrid to excel?

Answer 1

The fastest way is to use a Variant array and just pass the entire array to Excel:

 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; 

Answer 2

The problem is that you are calling an Excel object for each cell; this is slow work in better times, so it will take a long time to do this for a large number of cells. I had a case of this not so long ago: 4000 rows with 9 columns took about 44 seconds to transfer to Excel.

My current solution involves creating a csv file and then importing that csv into Excel.

 const fn = 'c:\windows\temp\csv.csv'; var csv: tstringlist; row, col: integer; s: string; begin csv:= tstringlist.create; for row:= 1 to stringgrid1.rowcount do begin s:= ''; for col:= 0 to stringgrid1.ColCount-1 do s:= s + stringgrid1.Cells[col, row-1] + ','; csv.add (s) end; csv.savetofile (fn); csv.free; objExcel := TExcelApplication.Create(nil); objExcel.workbooks.open (fn); deletefile (fn); end; 

Another way: Mike Schoolboy , which I quote as:

 var xls, wb, Range: OLEVariant; arrData: Variant; begin {create variant array where we'll copy our data} arrData := VarArrayCreate([1, yourStringGrid.RowCount, 1, yourStringGrid.ColCount], varVariant); {fill array} for i := 1 to yourStringGrid.RowCount do for j := 1 to yourStringGrid.ColCount do arrData[i, j] := yourStringGrid.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[yourStringGrid.RowCount, yourStringGrid.ColCount]]; {copy data from allocated variant array} Range.Value := arrData; {show Excel with our data} xls.Visible := True; end; 

I suggest you try both methods and see which is faster for your goals.