Topic: [Sort of Solved]Problem exporting table grid to Excel
I am attempting to write my own tablegrid to Excel function due to the fact I don't want all the columns exported to Excel as the inbuilt function does, and decided that hiding columns and then using the inbuilt function could raise further problems. How wrong I have been!
To that end I 'borrowed' some code I found on various websites by Mike Shkolnik ,
I am obviously not understanding something, as no matter what I try I can only get 1 item to appear in the spreadsheet in addition to the header.
I filter the table grid, so that only the rows (variable in number) are exported. The tablegrid row count reflects the current number of rows correctly.
procedure frmMain_btnNothing_OnClick (Sender: TObject; var Cancel: boolean);
var
xls, wb, range : Variant;
arrData : Variant;
rowCnt : integer;
numOfRows,numCol:integer;
begin
if frmMain.tgOrderPlace.RowCount > 0 then
begin
numOfRows := frmMain.tgOrderPlace.RowCount ;
arrData := VarArrayCreate([0,frmMain.tgOrderPlace.RowCount, 0,3], varVariant);
//excel format = row,column table grid = column, row
{row 0 contains tablegrid headers}
arrData[0,0] := frmMain.tgOrderPlace.Columns[1].Header.Caption;
arrData[0,1] := frmMain.tgOrderPlace.Columns[2].Header.Caption;
arrData[0,2] := frmMain.tgOrderPlace.Columns[4].Header.Caption;
{fill array with cell contents by row}
for rowCnt := 1 to numOfRows do
begin
arrData[rowCnt,0] := frmMain.tgOrderPlace.Cells[1,rowCnt-1];
arrData[rowCnt,1] := frmMain.tgOrderPlace.Cells[2,rowcnt-1];
arrData[rowCnt,2] := frmMain.tgOrderPlace.Cells[4,rowcnt-1];
end;
xls := CreateOLEObject('Excel.Application');
wb := xls.WorkBooks.add;
range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1,1],wb.WorkSheets[1].Cells[frmMain.tgOrderPlace.RowCount, 3]];
Range.Value := arrData;
Range.Columns.Autofit(); //In Excel columns must be filled first and then autofit called
xls.Visible := true;
end;
//wb.Close;
xls.Quit;
end;
The link to the original code is https://stackoverflow.com/questions/166 … d-to-excel .
I've attached a snipshot of what I want to appear in Excel and what actually appears in Excel.