TadvGridExcelExport

Hi,
TAdvStringExcelExport can nicely export formulas, which is cool.
I have use case where I would like to create formula which references to other cell in same row.
very simplified example:
i have query: select id, name, price, '=A3*0.80' as LoyaltyCustomerPrice from products . I preview query result in TAdvDbGrid and export it to xlsx. Of course formula's A3 should be changed to B3 on 2nd row, C3 in 3rd row etc. Can this be done?
note: Of course in above example I could do calculations in sql, but as I told this is simplified example. in my real case data in column 3 is already precalculated in sql

An another question,
is there a way to force column to be string column. In resulting excel phone numbers and zip codes are handled like numbers. +3580172620441 -> +3.580173e+11 and zip code 00270 -> 270
in database those fields are stringfield (other than ExportOptions.CellsAsString)

Hi,
I am not an AdvDbGrid expert, but from what I asked, you would need to use "moveformula", like:

begin
 advspreadgrid1.Cells[1,1] := '1';
 advspreadgrid1.Cells[1,2] := '2';
 advspreadgrid1.Cells[2,1] := '=A1';

 advspreadgrid1.MoveFormula(GridCoord(2,1), GridCoord(2,2));

 advspreadgrid1.Recalc;
end;

Now, as what I mostly know about is FlexCel, I can tell you that another way you could do this is to use FlexCel reports to create the Excel file directly from the SQL (see https://doc.tmssoftware.com/flexcel/vcl/tips/dumping-a-dataset.html ), then once you created the file use TAdvGridExcelImport to load it into the grid and preview it.

Yet another way would be to use R1C1 notation so you could make the SQL something like:

select id, name, price, '=RC[-1]*0.80' as LoyaltyCustomerPrice

(In this case, as "price" the previous column, you can refer to it as "Current row:Previous column" which is what RC[-1] means.

The good thing about R1C1 is that it isn't something that is saved in the sheet: You can create a file using R1C1 notation, but Excel can still show the file with the more common A1 notation.
The bad thing about R1C1 is that if you switch to that mode, you would have to enter all formulas in R1C1 notation, not just the ones you care about expanding.(which might be something you want anyway, since A1 formulas won't adapt like R1C1 formulas do)

To use R1C1 notation, you would use the property FormulaReferenceStyle and some code like this:

implementation
uses VCL.FlexCel.Core, FlexCel.XlsAdapter;

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  xls: TXlsFile;
begin
  AdvStringGrid1.Cells[2,2] := '=RC[-1]*2';
  xls := TXlsFile.Create(1, TExcelFileFormat.v2019, true);
  try
    xls.FormulaReferenceStyle := TReferenceStyle.R1C1;
    AdvGridExcelExport1.Export(xls);
    xls.Save('c:\temp\result.xlsx');
  finally
    xls.Free;
  end;
end;

Make sure to read https://doc.tmssoftware.com/flexcel/vcl/tips/expanding-formulas.html

Yet another way could be to adapt the SQL to generate =A3 for the first row, =A4 for the second, etc., something like:

select id, name, price, '=A' + CurrentRow + '*0.80' as LoyaltyCustomerPrice

But I think CurrentRow is db-dependant. (I don't know if there is a standard SQL way to get the current row)

You can choose which columns to export as strings on a column-by-column basis using the event ExportCell in AdvGridExcelImport. For example, to have the column 2 exported as text, but only column 2 and not all of them, you could use:

procedure TForm1.AdvGridExcelExport1ExportCell(Sender: TObject;
  var Args: TExportCellEventArgs);
begin
  if Args.GridCol = 2 then Args.ExportDataType := TExportDataType.Text;

end;

If you use reports and the grid to preview the report as one of the suggested solutions for the other question, then this should be automatic, as long as the db has them as stringfields. The problem with exporting the db to the grid and then the grid to Excel is that AdvExcelExport has lost all the information in the db; it can only see the grid (which has everything stored as strings). So it can't know which are "real" strings, and which are actually numbers and by default will try to convert everything to numbers (or not, depending in ExportOptions.CellsAsStrings).

On the other side, if you export directly the db to Excel, FlexCel can then see the db, and know if a field is a varchar or a number, and export accordingly.

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.