Author: Jonas Bilinkevicius

How to put data from a TStringGrid into an Excel spreadsheet

Answer:
1   
2   procedure TTradingForm.Button1Click(Sender: TObject);
3   var
4     i, j: Integer;
5     r, c: Integer;
6     v: OleVariant;
7     sRange: string;
8     WorkBook: _Workbook;
9     WorkSheet: _Worksheet;
10  
11    function ColToStr(ilCol: integer): string;
12    var
13      FirstLetter: integer;
14      lastLetter: integer;
15    begin
16      result := '';
17      firstLetter := (ilCol - 1) div 26 - 1;
18      lastLetter := (ilCol - 1) mod 26;
19      if firstLetter >= 0 then
20        result := chr(ord('A') + firstletter);
21      result := result + chr(ord('A') + lastLetter);
22    end;
23  
24  begin
25    v := VarArrayCreate([0, Grid.RowCount - 1, 0, Grid.ColCount - 1], varVariant);
26    for i := 0 to Grid.RowCount - 1 do
27    begin
28      for j := 0 to Grid.ColCount - 1 do
29        if i = 0 then
30          v[i, j] := Grid.Columns[j].Caption
31        else
32          v[i, j] := Grid.Cells[j, i];
33    end;
34    Screen.Cursor := crHourglass;
35    try
36      Excel.Connect;
37      Excel.Visible[GetUserDefaultLCID] := False;
38      WorkBook := Excel.Workbooks.Add(EmptyParam, GetUserDefaultLCID);
39      WorkBook.Activate(GetUserDefaultLCID);
40      Worksheet := Excel.ActiveWorkbook.Worksheets.Add(EmptyParam, EmptyParam, 2,
41        xlWBATWorksheet, GetUserDefaultLCID) as _Worksheet;
42      Excel.XLSelectWorksheet(1);
43      r := VarArrayHighBound(v, 2) + 1;
44      c := VarArrayHighBound(v, 1) + 1;
45      sRange := 'A1..' + ColToStr(r) + IntToStr(c);
46      Excel.XLSetRangeValue(sRange, v);
47    finally
48      Excel.Visible[GetUserDefaultLCID] := True;
49      Excel.Disconnect;
50      v := null;
51      Screen.Cursor := crDefault;
52    end;
53  end;