How to put data from a TStringGrid into an Excel spreadsheet (Views: 27)
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; |