Currency Formatting Issue!

Hi,
We are using aspose cells for .NET version 4.7.1.0.

We are trying to format the currency value in a cell using this custom string - “#.##0,00€”.
This means that the thousand separator character is “.” and the decimal point is a comma value. This is for the Netherlands region.
While the currency symbol and the decimal point is recognized, the thousand separator value is ignored and a value of “1.654,00” comes out as “1654,0000”.
When i go the the exported cell in excel and look at the formatting, it is not the intended custom string, if i manually type the above formatting string and save it, the cell value is corrected.

Is there something i am missing while adding the custom currency format with Aspose?
Please let me know.

thanks,
Jaideep.

Hi,

Well, when you apply a custom formatting in an English version of MS Excel i.e…, “#.##0,00€” the comma will be deleted by default as MS Excel takes the first “.” as decimal point and ignores the “,” in the format. Please try setting the Regional/Language Settings to Netherlands. And, we will further check and get back to you soon.

Thank you.

Hi Amjad,
thanks for the quick reply.

I have set the regional/Language settings for MS office to German too, but i see the same behavior.

thanks,
Jaideep.

Hi,

Please post a demo application to show the issue. We also want to check which method you are using to put values into the cells.

Thank you.

Hi Amjad,
Thanks for the reply. Here is what we are doing.

I am getting numeric values from the DB such as 167.5, 1696. I use the following snippet to insert these values individually into cells -
sheet.Cells(nRow, nCol).PutValue(CType(sCellValue, Double))

Also for each cell (nRow,nCol), i am using the following snippet to input custom formatting -
sheet.Cells(nRow, nCol).Style.Custom = "#.##0,00 €"

Now when this is output to a browser, they come out as intended (“167,5 €”,“1.696,00 €”), but the excel output displays (“167,5 €”,“1696,0000 €”).
Hope this helps.

thanks,
Jaideep.

Hi Jaideep,

Thanks for providing us further details.

We will check and get back to you soon.

Thank you.

Hi,

Please use “#,##0.00 €” as the number format of the cell.

For your information, MS Excel only accepts the Invariant Culture number format in the file.
MS Excel will auto-change the display format based on the language setting applied.
We will look into how to set the number format for different cultures.


Thank you.

Hi,

Please try the fix 4.8.1.8 (attached) with the following sample code:

Workbook workbook = new Workbook();
workbook.Region = CountryCode.Germany;
Cells cells = workbook.Worksheets[0].Cells;
Cell cell = cells[“A1”];
cell.PutValue(167.5);
Style style = cell.GetStyle();
style.CultureCustom = “#.##0,00 €”;
workbook.Save(@“C:\book1.xls”);



Thank you.

Hi Amjad,
Thanks for the reply.
I tried to use the test build that you have attached, but i get the following error message -
"The subscription included in this license allows free upgrades until 08 Jul
2009, but this version of the product was released on 11 Dec 2009. Please renew
the subscription or use a previous version of the product"

Please suggest a workaround.

thanks,
Jaideep.

Hi Jaideep,

Well, I am afraid, you have to choose any one option from the following options (as you cannot use the latest fix v4.8.1.8 with your license file):

1) You should upgrade your subscription to use new/latest versions/fixes, please contact sales/purchase team: http://www.aspose.com/community/forums/aspose.purchase/220/showforum.aspx

2) As a workaround, you may use “#,##0.00 €” as the number format of the cell if it fits your need.


Thank you.