Lucee/CF : Problem to convert a value to a monetary dataformat

[sebviatour-iteamlu]

Hello,

I'm trying to convert a value (numeric and/or float) in a monetary format (000.00 €).

I checked in your documentation but nothing works. Each time, I got an error message during the execution.

Could you please help me ?

Thanks in advance.

[cfsimplicity]

Hi. Can you please post some code showing which method(s) you are using and example values that reproduce the problem?

[sebviatour-iteamlu]

Sure.

cellFormula = "AO" & row & "*AP" & row; spreadsheet.setCellFormula(workbook = workbook, formula = cellFormula, row = row, column = column); cellFormat = { dataformat: "$#,##0_);($#,##0)", alignment: "center", verticalalignment: "center", topborder: "THIN", bottomborder: "THIN", leftborder: "THIN", rightborder: "THIN" }; spreadsheet.formatCell(workbook = workbook, format = cellFormat, row = row, column = column);

I tried with dataformat : "0x05" or 5 or "$#,##0.00);($#,##0.00)". It's based with your documentation https://github.com/cfsimplicity/spreadsheet-cfml/wiki/Formatting-options

dataformat (string): One of the POI built-in formats, or your own custom formatting mask.

[cfsimplicity]

dataformat: "$#,##0_);($#,##0)"

Try escaping the pound signs, which have special meaning in CFML, by doubling them up:

dataformat: "$##,####0_);($##,####0)"

[sebviatour-iteamlu]

Hi,

Thanks. It's working.

Do you have an idea to use the symbol € instead of $ ?

[cfsimplicity]

To get the format string you need, I'd recommend opening a new file in Libre Office, right-click any cell then choose Format Cells...

Select the format you want in the Numbers tab then copy the Format Code at the bottom and use that with escaped pound signs.

dataformat: "##,####0.00 [$€-40C];-##,####0.00 [$€-40C]"

You can probably do the same with other software, but LO is what I use.

[sebviatour-iteamlu]

I tried with Microsoft Excel and found the format that you mentionned but the system replaces € to ?

[cfsimplicity]

Here's a full test using the French (France) Euro format from above:

spreadsheet = New spreadsheet.Spreadsheet()
workbook = spreadsheet.newXlsx()
path = ExpandPath( "test.xlsx" )
cellFormat = { dataformat: "##,####0.00 [$€-40C];-##,####0.00 [$€-40C]" }
spreadsheet
    .setCellValue( workbook, "1.23", 1, 1 )
    .formatCell( workbook, cellFormat, 1, 1 )
    .write( workbook, path, true )

I don't have the full Excel, but in MS Excel Viewer this appears as expected:

[sebviatour-iteamlu]

Hello,

I found what was the problem. Now, it works with your idea.

Thanks for your help.