export delphi stringgrid to excel

Question

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?

Rosenkranz

thanks for the quick response, guys. I think the array method is best for my situation right now because I'm not going to use the .csv file. how do I flag this question as "Solved"? – Rosenkranz

Answer 1

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;

Perretta

thanks for the quick response. very clear steps for a beginner like me. this solved my problem. it's a shame I can't rate an answer yet.. – Rosenkranz

+1, just wondering whether quickest refers to the time needed to code, or the time needed to do the actual "copy"? Another way would for example be to put everything on the clipboard in a csv format and then paste the clipboard contents to Excel. – Waring

@MarjanVenema: The clipboard belongs to the user, not the programmer, so IMO it's not an option. Going straight from the array of variant to the Excel range is extremely fast, and doesn't screw with anything the user has put into the clipboard expecting it to be there later. – Perretta

@KenWhite: Clipboard is indeed the user's. It is an option if named appropriately so user knows what to expect (then and only then). option if command is named appropriately so user knows what to expect. For example we allow ranges to be selected and copy it to the clipboard on Ctrl-C. – Waring

Answer 2

The problem is that you are calling the Excel object for every cell; this is a slow operation at the best of times, so doing this for a large number of cells is going to take a long time. 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 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 comes from Mike Shkolnik which I am quoting as is:

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 that you try both methods and see which is faster for your purposes.

Guillotine

thanks for the quick response. since I don't think I'll be using the csv file, I'll just use the array solution for now. unfortunately I can't rate an answer yet. – Rosenkranz

@dapidmini: The advantage of the csv method is that it can be used when the number of rows is not known in advance. Obviously this isn't going to happen with a stringgrid but it will happen if you want to pass the results of a query to Excel. It's a shame that you marked the answer which did not reference its source. – Homogenesis

@dapidmini: using csv format you could also use the clipboard, if so desired without writing it to file first. – Waring

@MarjanVenema: I have criticised in the past for using the clipboard programmatically. The feeling was that the clipboard belongs to the user, not to a program, so storing something in the clipboard might overwrite something that the user has manually stored there. (I didn't see Ken's comment when I wrote this but he's saying the same thing). – Homogenesis

@No'amNewman: yes, clipboard belongs to the user. It is only an option if command is named appropriately so user knows what to expect. For example we allow ranges to be selected and copy it to the clipboard on Ctrl-C. – Waring

@No'amNewman: The code snippet I posted came from a file of snippets I've collected since I started using Delphi with version 1. I have no idea where I got it, so it would be difficult to provide a reference for it's source, and your insinuation is offensive. – Perretta

@KenWhite: THat's what comes from being a doctoral candidate: everything must be attributed. – Homogenesis

Answer 3

procedure WriteToExcel();
var
  txt : TextFile;
  Str : string;
  i : integer;
begin
  try
    SaveDialog1.FileName := 'excelFile('+FormatDateTime('yyyy-dd-mm hh-nn-ss' ,(Now))+')';
    if SaveDialog1.Execute then
      begin
        AssignFile(txt, SaveDialog1.FileName+'.csv');
        try
          if FileExists(SaveDialog1.FileName) then
            Append(txt)
          else
            ReWrite(txt);
          Str := 'title1, title2, title3, title4, title5';
          WriteLn(txt, Str);
          ShowQuery.First();
          for i:=1 to StringGrid1.RowCount do
            begin
              Str := StringGrid1.Cols[i][1] + ',';
              Str := Str + StringGrid1.Cols[i][2] + ',';
              Str := Str + StringGrid1.Cols[i][3] + ',';
              Str := Str + StringGrid1.Cols[i][4] + ',';
              Str := Str + StringGrid1.Cols[i][5];
              WriteLn(txt,  Str);
            end;
        finally
          CloseFile(txt);
        end;
      end;
  except

  end;
end;

Fragmentary