Excel currency format issue

Aspose.Cells Product Family
1

I have to format currency in Excel in this format within the double quotes "_($* #,##0.000_);_($* (#,##0.000);_($* " - "??_);_(@_)".

After applying the aspose styles to the required excel columns successfully, I get a warning message when opening the excel "Excel found unreadable content. Do you want to recover the contents of this workbook." Please advise?

Style ColumnDateStyle = null;

StyleFlag ColumnDateStyleFlg = null;

ColumnDateStyleFlg = new StyleFlag() { All = false };

ColumnDateStyleFlg.HorizontalAlignment = true;

ColumnDateStyleFlg.NumberFormat = true;

ColumnDateStyle.Custom = "_($* #,##0.000_);_($* (#,##0.000);_($* \"-\"??_);_(@_);";

foreach (DataColumn col in dtDataTable.Columns)

{

wsXLSWorksheet.Cells.Columns[col.Ordinal].ApplyStyle(ColumnDateStyle, ColumnDateStyleFlg);

}

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells for .NET 8.0.2 and see if it makes any difference and resolve your problem.

If your problem still persists, then please provide us your console application replicating this issue for our investigation. Please also provide us a sample xls/xlsx file which should contain your desired currency format applied on some cells (e.g on cell A1, A2 etc). It will help us look into this issue.

We will investigate your issue at our end and help you asap.

As an example, I have attached Book1.xlsx where the currency format $#,##0.00;[Red]$#,##0.000 has been applied on cell A1. Please provide a similar file to us so that we could know how your currency format looks like in Excel

3

Hi,

Thanks for using Aspose.Cells.

We have looked into your issue further. You need to set your custom currency format style like the following line.

ColumnDateStyle.Custom = “("$"* #,##0.000);("$"* \(#,##0.000\);(”$"* " - “??);(@)";

Please see the following sample code, that creates a workbook and apply this currency format to column A. I have also attached the output file generated by this code and screenshot showing the formatted values in cells A1 and A2.

C#


Workbook workbook = new Workbook();


Worksheet wsXLSWorksheet = workbook.Worksheets[0];


wsXLSWorksheet.Cells[“A1”].PutValue(23);

wsXLSWorksheet.Cells[“A2”].PutValue(-23);



Style ColumnDateStyle = workbook.CreateStyle();


StyleFlag ColumnDateStyleFlg = null;


ColumnDateStyleFlg = new StyleFlag() { All = false };


ColumnDateStyleFlg.HorizontalAlignment = true;


ColumnDateStyleFlg.NumberFormat = true;


ColumnDateStyle.Custom = "(”$"* #,##0.000_);("$"* \(#,##0.000\);("$"* " - “??);(@_)”;


wsXLSWorksheet.Cells.Columns[0].ApplyStyle(ColumnDateStyle, ColumnDateStyleFlg);


workbook.Save(“output.xlsx”);