Mirror

How to put data from a TStringGrid into an Excel spreadsheet (Views: 100)


Problem/Question/Abstract:

How to put data from a TStringGrid into an Excel spreadsheet

Answer:

procedure TTradingForm.Button1Click(Sender: TObject);
var
  i, j: Integer;
  r, c: Integer;
  v: OleVariant;
  sRange: string;
  WorkBook: _Workbook;
  WorkSheet: _Worksheet;

  function ColToStr(ilCol: integer): string;
  var
    FirstLetter: integer;
    lastLetter: integer;
  begin
    result := '';
    firstLetter := (ilCol - 1) div 26 - 1;
    lastLetter := (ilCol - 1) mod 26;
    if firstLetter >= 0 then
      result := chr(ord('A') + firstletter);
    result := result + chr(ord('A') + lastLetter);
  end;

begin
  v := VarArrayCreate([0, Grid.RowCount - 1, 0, Grid.ColCount - 1], varVariant);
  for i := 0 to Grid.RowCount - 1 do
  begin
    for j := 0 to Grid.ColCount - 1 do
      if i = 0 then
        v[i, j] := Grid.Columns[j].Caption
      else
        v[i, j] := Grid.Cells[j, i];
  end;
  Screen.Cursor := crHourglass;
  try
    Excel.Connect;
    Excel.Visible[GetUserDefaultLCID] := False;
    WorkBook := Excel.Workbooks.Add(EmptyParam, GetUserDefaultLCID);
    WorkBook.Activate(GetUserDefaultLCID);
    Worksheet := Excel.ActiveWorkbook.Worksheets.Add(EmptyParam, EmptyParam, 2,
      xlWBATWorksheet, GetUserDefaultLCID) as _Worksheet;
    Excel.XLSelectWorksheet(1);
    r := VarArrayHighBound(v, 2) + 1;
    c := VarArrayHighBound(v, 1) + 1;
    sRange := 'A1..' + ColToStr(r) + IntToStr(c);
    Excel.XLSetRangeValue(sRange, v);
  finally
    Excel.Visible[GetUserDefaultLCID] := True;
    Excel.Disconnect;
    v := null;
    Screen.Cursor := crDefault;
  end;
end;

<< Back to main page