[FIXED] CSV to XLSX Conversion
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
rca · 2024-10-18 13:20

I need to convert a CSV file to XLSX.

The content of the 'fruits.csv' file is as follows:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ITEM;REFERE
NCE;FRUIT;UNITS;PRICE1;E015;PEAR;24;1002;E024;APPLE;48;2003;E012;ORANG
E;24;1004;E032;PINEAPPLE;12;1505;E018;LEMON;24;806;E045;STRAWBERRY;24;
120 

The problem I have is with the content of the 'REFERENCE' column:
* By default, Code: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i 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][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i 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 · 2024-10-18 14:36

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][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i   if not (AValue[1] in ['E', 'e']) and TryStrToFloat(AValue,
number, AFormatSettings) thenSave and recompile the IDE

----------------------------------------------------------------------
rca · 2024-10-18 18:18

@wp

Thanks a lot!. Now is working.