[FIXED] CSV to XLSX Conversion

[rca]

I need to convert a CSV file to XLSX.

The content of the 'fruits.csv' file is as follows:

Code: Text  [Select][+][-]

1. ITEM;REFERENCE;FRUIT;UNITS;PRICE
2. 1;E015;PEAR;24;100
3. 2;E024;APPLE;48;200
4. 3;E012;ORANGE;24;100
5. 4;E032;PINEAPPLE;12;150
6. 5;E018;LEMON;24;80
7. 6;E045;STRAWBERRY;24;120
8.  

The problem I have is with the content of the 'REFERENCE' column:
* By default,

Code: Pascal  [Select][+][-]

1. CSVParams.DetectContentType := true;

and when opening the converted file 'fruits.xlsx', it shows a value of zero. I attach image.

* When changing

Code: Pascal  [Select][+][-]

1. CSVParams.DetectContentType := false;

  and when opening the converted file 'fruits.xlsx', it correctly shows the values, but now the cells containing numbers are full of warnings. I attach image.

I appreciate the help you can give me.

[wp]

The problem is that the procedure "val" (on which TryStrToFloat is based) assumes that a string like "E015" is a valid number. Delphi has the same bug while turbo Pascal does not... I modified the automatic number detection in FPSpreadsheet such that any string beginning with 'E' or 'e' cannot be a valid number - see current version on ccr.

If you use a release version from OPM you can fix it easily by yourself:

• Open file "fpspreadsheet.pas"
• Find procedure "TsWorksheet.WriteCellValueAsString"
• Find the line with "TryStrToFloat" and replace it by this:

Code: Pascal  [Select][+][-]

1.   if not (AValue[1] in ['E', 'e']) and TryStrToFloat(AValue, number, AFormatSettings) then

• Save and recompile the IDE

[rca]

@wp

Thanks a lot!. Now is working.