Recent

Author Topic: FPSpreadsheet - how to set cell Category [Solved]  (Read 616 times)

Vodnik

  • Full Member
  • ***
  • Posts: 138
FPSpreadsheet - how to set cell Category [Solved]
« on: January 24, 2020, 09:32:41 am »
Hello,
I'm using db_export_import example from fpspreadsheet component to export spreadsheet to Excel (using virtual mode)
https://sourceforge.net/p/lazarus-ccr/svn/5820/tree//components/fpspreadsheet/examples/db_import_export/main.pas

Example sets different cell styles for different cells, but when resulting file is opened in Excel, all cells have Category set to "General". This do not indicate any problems for this example.

But when I have applied the code to my Dataset, then an annoying effect occured: if Excel user double-clicks cell with number 6011579805402010 in it, its display changes to 6,01158E+15 and stay forever. Changing cell Category to "Text" (there may be letters, too) eliminates the effect.

After reading WiKi my expectation was that cell Category will be set automatically in accordance to the type of field in Dataset.

So question is: is it possible to set cell Category in fpspreadsheet?
« Last Edit: January 25, 2020, 09:52:19 pm by Vodnik »

wp

  • Hero Member
  • *****
  • Posts: 7236
Re: FPSpreadsheet - how to set cell Category
« Reply #1 on: January 24, 2020, 11:37:17 am »
Which kind of database are you exporting from? What is the DataType of the "Contact ID" field? With ftString it should work correctly out of the box. With ftInteger I wonder how numeric strings of 16 digits were accepted -- 32-bit integer goes up to 4E9, 64-bit integer does go up to 1.8E19, but I don't know the field type for it.
« Last Edit: January 24, 2020, 12:02:52 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

rvk

  • Hero Member
  • *****
  • Posts: 4160
Re: FPSpreadsheet - how to set cell Category
« Reply #2 on: January 24, 2020, 11:52:42 am »
I also see a green triangle in the cell which means a contents violation.
What does this error say if you hover over the triangle (or click it)?

Quote
The green triangle appears in a cell when the cell's contents violate one of Excel's error checking rules.

Vodnik

  • Full Member
  • ***
  • Posts: 138
Re: FPSpreadsheet - how to set cell Category
« Reply #3 on: January 24, 2020, 12:34:56 pm »
@wp, database is informix, DataType of "Contact ID" field is ftString

@rvk, "The number in this cell is formatted as text or preceded by an apostrophe"

Excel finds out that Number is stored as Text and converts it to Number on double-click?
When I change cell Category from General to Text, green triangle stays on with the same error message, but double-clicking to it now do not cause display change to exp.

wp

  • Hero Member
  • *****
  • Posts: 7236
Re: FPSpreadsheet - how to set cell Category
« Reply #4 on: January 24, 2020, 12:50:08 pm »
database is informix, DataType of "Contact ID" field is ftString
Then you should try to explicitely format the cells in this column as nfText. Just follow the example which contains code to format a cell as nfCurrency. For virtual mode you must create a template cell which you must format as text -- WriteNumberFormat(..., nfText) -- and use when the ContactID field is read.

I updated the db_import_export demo showing the principle for a "Social Security Number" field which is a string field but contains only numerical digits.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Vodnik

  • Full Member
  • ***
  • Posts: 138
Re: FPSpreadsheet - how to set cell Category
« Reply #5 on: January 25, 2020, 09:51:42 pm »
Thanks, wp, this solved the problem!

 

TinyPortal © 2005-2018