We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Not able to set Accounting Custom format

I am using Aspose Cells Java 7.4.3 and trying to set Accounting format via API using following code


cell = cells.get(“A5”);
cell.setValue(-27034498.26);
currencySymbol = AsposeCellValueFormatUtilTest.getCurrencySymbol(Currency.getInstance(Locale.FRANCE).getCurrencyCode());
currencyFormat = “_ $* #,##0.00_ ;_ $* “??_ ;_ @_”;//same as style number 44
style = cell.getStyle();
style.setCustom(currencyFormat.replace(”@", currencySymbol));
cell.setStyle(style);

Excel gives error doing above. But when I do style.setNumber(44) it formats correctly.

Sample code showing the behavior is at https://github.com/firefoxNX/aspose-cells-accounting

Hi Vineet,

Thanks for your posting and using Aspose.Cells.

Please set the custom format like this

style.setCustom("("$"* #,##0.00);("$"* \(#,##0.00\);("$"* “-”??);(@)");

It will then set the format of cell as number 44 built-in format.

Please see the following code, it sets the built-in number 44 with custom property. I have attached the output file generated with this code for your reference.

Java


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cell = worksheet.getCells().get(“A1”);


cell.putValue(-27034498.26);


Style style = cell.getStyle();

style.setCustom("("$"* #,##0.00_);("$"* \(#,##0.00\);("$"* “-”??);(@_)");

cell.setStyle(style);


worksheet.autoFitColumns();


workbook.save(“output.xlsx”);

Thanks that worked!


Quick follow up question - What is the difference between what you mentioned
("$"* #,##0.00);("$"* (#,##0.00);("$"* “-”??);(@)
and
$* #,##0.00_ ;_ $* "??_ ;_ @_
mentioned on documentation page for Style 44 http://www.aspose.com/docs/display/cellsjava/Setting+Display+Formats+of+Numbers++and++Dates

Should the docs reflect this for style 44?

Hi Vineet,

Thanks for your posting and using Aspose.Cells.

I think, documentation article needs to be corrected. As a tip, you can create your desired display format in MS-Excel manually and save it in xls/xlsx file and then read it as a string from Cell.getStyle().getCustom() method. It will then give you the custom string which you can then use in your code.

I am getting “custom style” as blank when I set “style number”


cell = cells.get("A2");

style = cell.getStyle();

style.setNumber(44);

cell.setStyle(style);

cell.putValue(-27034498.26);

System.out.println("A2 custom = " + cell.getStyle().getCustom());

Hi Vineet,

Thanks for your posting and using Aspose.Cells.

Custom property will return custom format only if you set the custom format with Excel. If you set the built-in format using MS-Excel, then Custom property will be empty.

I first set the built-in format using MS-Excel and then made some changes inside it so that built-in format could become custom format and then loaded the workbook with Aspose.Cells API and observed the custom property.

You can use this same approach to find how built-in formats look like in custom formats.