My Visual Database — [Sort of Solved]Problem exporting table grid to Excel

Re: [Sort of Solved]Problem exporting table grid to Excel

null@example.com (CDB) — Sun, 24 Jan 2021 07:26:44 +0000

Due to a problem somewhere in my DCU file I'm unable at this time to use Derek's answer.

However, I have finally managed to get my code above to work by tweaking the for loops. At this time though I'm still unable to include column headers in the spreadsheet, unless I put them at the bottom of the form, which would be a bit unusual!

The tweaked code is below - note the substitution of a for loop to an inc(rownumber,1).

procedure frmMain_btnExcel_OnClick (Sender: TObject; var Cancel: boolean);
var
  xls, wb, range : Variant;
  arrData : Variant;
  rowCnt : integer;
 ExcelRowNum:integer;
begin
  if frmMain.tgOrderPlace.RowCount > 0 then
  begin
   
     {allocate 2d space to excel array}
     arrData := VarArrayCreate([0,frmMain.tgOrderPlace.RowCount , 0,frmMain.tgOrderPlace.Columns.Count ], 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; }

     ExcelRowNum := 0;

    {fill array with cell contents by row}
    for  rowCnt := 1 to frmMain.tgOrderPlace.RowCount  do
    begin
      arrData[ExcelRowNum,0] := frmMain.tgOrderPlace.Cells[1,rowCnt-1];
      arrData[ExcelRowNum,1] := frmMain.tgOrderPlace.Cells[2,rowCnt-1];
      arrData[ExcelRowNum,2] := frmMain.tgOrderPlace.Cells[4,rowCnt-1];
      inc (ExcelRowNum,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, frmMain.tgOrderPlace.Columns.Count]];

    Range.Value := arrData;
    Range.Columns.Autofit();  //In Excel columns must be filled first and then autofit called
    xls.Visible := true;
  end
  else
   MessageBox('Supplier has no items to order, unable to open spreadsheet.'+#13+'Please select a supplier with order items', 'INFORMATION', MB_OK);

  xls.Quit;
end;

Re: [Sort of Solved]Problem exporting table grid to Excel

null@example.com (CDB) — Mon, 18 Jan 2021 09:40:09 +0000

Thanks Derek,

I had considered using your suggestion before I decided to go the difficult route. I'm going to follow your suggestion.

Re: [Sort of Solved]Problem exporting table grid to Excel

null@example.com (derek) — Sun, 17 Jan 2021 15:35:05 +0000

Hi CDB,
In the past when I don't want to export all columns, I've used a hidden grid - please see attached (you might need to change the 'Export to Spreadsheet' button configuration - it currently calls LibreOffice Calc rather than Excel - see screenshot1 in the attachment).
Is this approach an option for you?
Derek.

[Sort of Solved]Problem exporting table grid to Excel

null@example.com (CDB) — Sun, 17 Jan 2021 10:50:39 +0000

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.