Hello
When I try to export a DBAdvGrid to Excel (linked to a sqlite DB) using AdvGridExcelIO1.XLSExport procedure no matter what I try, Excel always detects a decimal value (24,34) as a string.
The target is that the value is shown in Excel as: ? 24,34.
I tried:
procedure TShowQuotationForm.AdvGridExcelIO1CellFormat(Sender: TAdvStringGrid;
const GridCol, GridRow, XlsCol, XlsRow: Integer; const Value: WideString;
var Format: TFlxFormat);
begin
If (GridCol = 11) then Format.Format := '"?"\ #,##0.00';
end;
The cell format is visible and shown as Money in Excel, but ignored because Excel sees the column as a string.
The result is that the value is shown as "24,34"
Also I tried the FloatFormat property of the column in the ADVDBGrid but this shows ""?"\ #,##0.00" or "0.00" for all rows.
Could somebody point me in a direction to solve this?
Remark:
the ? are euro signs in the above post
Hi,
Can you check that Options.ExportCellFormats is false?
ExportCellFormats is a terrible name, and I wish we could change it without breaking a lot of old code, but we are stuck with it. (in the newer AdvExcelExport we have a much more logical name for the property: "CellsAsStrings"
But well, the short story is that:
ExportCellFormats = true will export the cells as strings so they resemble what you see on the grid. But they will be strings, so Excel format won't apply.
ExportCellFormats = false will export the cells as numbers, and you need to use an event to format the cells (as you already do).
So check with ExportCellFormats = false and let me know. If it doesn't fix it we'll dig deeper.
Hello Adrian
Thank you for your quick help.
Options.ExportCellFormats was already false since the format was transferred and visible in Excel.
But the format is not applied (to a string).
When I type exactly the same value in the cell, the format is applied.
Thanks
Sorry, my confusion: ExportCellFormats should be true, not false.
ExportCellFromats has nothing to do with transferring the formats to Excel, or with anything to do with cell formats at all actually. That's why it is a bad name, and I can't really remember the reason we choose it (back in 2002, almost 15 years ago)
ExportCellFormats actually means "not ExportCellsAsStrings" (I forgot in my last answer that it was reversed, that is why I said it should be false). But you want to not export cells as strings, so you need to set ExportCellFormats to true.
Hello Adrian
That solved the issue!
But introduced another issue,
In one cell there is a string "04-04". This is now converted to "4-4-2016" (a date)
Do you have a solution for this as well?
Sorry for asking
If you know which columns should be converted to numbers and which should be keep as text, there is an event:
OnExportColumnFormat
this will be called for each cell exported, and it has a parameter "ExportCellAsString" (correctly named this time :). Set it to true for the values you want to have exported as strings no matter if they can be converted to a number, and to false if you want AdvGridExcelIO to try to convert them.
Perfect!!! Thanks