Removing thousands separator

Is there a way that one can automatically tell Excel via Flexcel to omit all thousands separators from all fields? I or users of my software sometimes have Excel spreadsheets with thousands separators. When I import data from these spreadsheets for use in Delphi programs (to do various calculation) I invariabley have conversion problems from string to value so it would be useful if I could force this off for all columns before importing.


Are you speaking about formatted numbers or strings?

If the values are stored as numbers, even if they are formatted with thousand separators, FlexCel will report the numbers.
xls.GetCellValue(1,1) will return 4000 even if the number displays as 4,000.000
So there is no need to convert any value from a string to a number (which is something you really don't want to do, because as you say, you will have conversion issues).

If the numbers are stored as strings like "1.2", then well, sadly there is no more way than to guess the locale and try to convert it, but this shouldn't be the normal case.

How are you importing those files? Do you use xls.GetCellValue? If you do, then the numeric format, thousands separators, etc, won't matter. You will get a TCellValue with a double precision floating point number inside.


  I have the problem because I use GetStringFromCell. The reason I use this is that I often have a mixture of string and float or interger cells and it has been easier to standardise on the lowest common version of read operation which can then be transferred into a database (using a .AsString assignment) after which I can treat the float and string values as appropriate.
Maybe I must start trying to define what type I have before reading from spreadsheet and then operate accordingly.

GetStringForCell is for mostly for display purposes, for most work, GetCellValue is what you want.

GetCellValue will return a TCellValue, which you can convert to a variant, or to a string, or to whatever is needed. And the conversion is automatic most of the time. For example, to assign the value to a database, if you have persistent fields, you would use:

   ClientDataSet1LastName.Value := xls.GetCellValue(1, 1);

Dynamically, you would do something like:
   ClientDataSet1.FieldByName('LastName').Value := xls.GetCellValue(1, 1);

Note that in the first case, a predefined field would be for example of type integer, while in the second it is a variant. In both cases, you can assign directly a TCellValue (what xls.GetCellVlue returns), and it will be assigned correctly. But there won't be any conversion from and to a string, you read a double from the xls file, you write a double to the database. There is no read double->convert to string->convert to double->assign to the database.

If you need more control assigning the result, just do something like this:
   case CellValue.ValueType of
     TCellValueType.Empty: ;
     TCellValueType.Number: ;
     TCellValueType.DateTime: ;
     TCellValueType.StringValue: ;
     TCellValueType.Boolean: ;
     TCellValueType.Error: ;
     TCellValueType.Formula: ;

And do what's needed depending on the data type of the cell.

In a last case, if you really want to convert to string, still don't use GetCellFromString. As said, GetCellFromString is designed mostly for display purposes, so it will format the cell exactly as Excel will show it (including stuff like decimal separators). You can use CellValue.ToString to get a "simple string" without the formatting, but as said, you most likely don't want to convert to a string at all.

Note: The thing that can give you some headaches using GetCellValue is dates, and probably this is why you went with GetCellFromString in the first place. The thing with dates is that Excel treats the dates as numbers, so you will get a number in TCellValue (not a TDateTime). If you are assigning to a database and the database field is a TDateTime this should't be an issue, the TCellValue will be converted to a number. But in the most general case, you need to explicitly check for a Format that includes dates. Look at the "Reading files" demo to see how to handle dates.



  thanks for the additional information.