Wrong CellFormat Text converts a "30.1" to "30,1"

[AlexD1979]

In Version 2.1.1 I detect an issue with cell formatting as text. My input value is "30.1" and in Excel the Output is "30,1". The Cell in Excel is recordnized as text with Format @. My formatCell input is {"rightborder":"hair","textwrap":false,"topborder":"hair","bold":false,"alignment":"right","bottomborder":"hair","leftborder":"hair","verticalAlignment":"top","dataformat":"@","fgcolor":"255, 147, 147"}

[cfsimplicity]

Hi Alex. I'm afraid I don't have MS Excel to test with, but running the following code using version 2.1.1 results in a value of 30.1 as expected in OpenOffice Calc and Google Sheets:

spreadsheet = New luceeSpreadsheet.Spreadsheet();
workbook = spreadsheet.newXlsx();
spreadsheet.setCellValue( workbook, "30.1", 1, 1 );
format = {"rightborder":"hair","textwrap":false,"topborder":"hair","bold":false,"alignment":"right","bottomborder":"hair","leftborder":"hair","verticalAlignment":"top","dataformat":"@","fgcolor":"255, 147, 147"};
spreadsheet.formatCell( workbook, format, 1, 1 );
spreadsheet.download( workbook, "test" );

Can you please try the result of this code in Excel and report back?

[AlexD1979]

Hi Julian, Thanks a lot for the very quick reply. I tested your snippet on Lucee 5.3.1.102 and MS Office 365 Excel (I assume it is 2016 version) on a german laptop with German Excel. Lucee was configured to locale English. The output is 30,1 instead of 30.1 test.xlsx

[cfsimplicity]

I don't think the Lucee locale will make any difference, but it's possible your German Excel is treating the value as currency despite the specified "text" format.

If you add the value by hand in your Excel sheet and then format it with"@" do you get the same result?

[cfsimplicity]

Could you also try the following?

spreadsheet = New luceeSpreadsheet.Spreadsheet();
workbook = spreadsheet.newXlsx();
spreadsheet.setCellValue( workbook, "30.1", 1, 1, "numeric" );
spreadsheet.setCellValue( workbook, "30.1", 1, 2, "string" );
spreadsheet.download( workbook, "test" );

I'd also recommend upgrading to v2.2.0 which uses a newer version of POI, although I think it's unlikely that's the cause of the problem.

[AlexD1979]

Both 2.1.1 and 2.2.0 returns for your first example anyway 3,1 as cell value with text @ as cellFormat The 2nd example returns for first column 30,1 and Format is Standard and shown as right aligned, 2nd column is 30.1 and the Format is Standard, too! It was left aligned. What does it mean? The formating with FormatCell and ArrayNotation does not work properly in this case? We need the format option to style the cell with thin lines, background color and font color, bold etc.

[cfsimplicity]

Did you try adding the value manually in your German Excel formatting it as @? Does that result in 30.1 or 30,1?

[cfsimplicity]

Try this, which forces the value to be inserted as a string and then formats it:

spreadsheet = New luceeSpreadsheet.Spreadsheet();
workbook = spreadsheet.newXlsx();
spreadsheet.setCellValue( workbook, "30.1", 1, 1, "string" );
format = {"rightborder":"hair","textwrap":false,"topborder":"hair","bold":false,"alignment":"right","bottomborder":"hair","leftborder":"hair","verticalAlignment":"top","dataformat":"@","fgcolor":"255, 147, 147"};
spreadsheet.formatCell( workbook, format, 1, 1 );
spreadsheet.download( workbook, "test" );

[AlexD1979]

The value comes from a query and in METADATA I can see, the type is "VARCHAR". If I add the optional parameter "type" to setCellValue, then it works and the field was formatted as 30.1. This is the solution, I have explicit to classify each cell with a type. So, there is no bug in the component. Many thanks for the good and qualified help Julian. Have a nice weekend.

[cfsimplicity]

I've changed my OpenOffice locale to German and I'm seeing the same results as you using the code above.

But query VARCHAR values should be added as strings, and this seems to be the case for me even with the German locale:

spreadsheet = New luceeSpreadsheet.Spreadsheet();
workbook = spreadsheet.newXlsx();
data = QueryNew( "column1", "VARCHAR", [ [ "30.1" ] ] );
spreadsheet.addRows( workbook, data );
format = {"rightborder":"hair","textwrap":false,"topborder":"hair","bold":false,"alignment":"right","bottomborder":"hair","leftborder":"hair","verticalAlignment":"top","dataformat":"@","fgcolor":"255, 147, 147"};
spreadsheet.formatCell( workbook, format, 1, 1 );
spreadsheet.download( workbook, "test" );

This produces 30.1.