Display cell string value as Exel

Aspose.Cells Product Family
1

Hi,
I’ve Regional language settings set on “Italian” so negative number formatted with built-in format number 39, is displayed with the minus symbol. Instead using Aspose (Java cells v17.1.0), once i’ve the cell and get the displayStringValue i always get the braket “(” symbol.
Here it the piece of code
Workbook workbook = new Workbook();
WorkbookSettings settings = workbook.getSettings();
settings.setLocale(new Locale(“it”));

        int sheetIndex = workbook.getWorksheets().add();
        Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
        Cells cells = worksheet.getCells();


        Cell cell = cells.get("A1");
        cell.setValue(1234);
        Style style = cell.getStyle();
        style.setNumber(39);
        cell.setStyle(style);
        System.out.println(cell.getDisplayStringValue());

        cell = cells.get("A2");
        cell.setValue(-1234);
        style = cell.getStyle();
        style.setNumber(39);
        cell.setStyle(style);
        System.out.println(cell.getDisplayStringValue());

        workbook.save("C:\\output.xls");

the output.xls display the number as i want “-1.234”, instead the cell.getDisplayStringValue() return “(1.234)”, there is way to return the minus symbol also with cell.getDisplayStringValue?
Thanks
M.

2

@m4tte,

Please try the following sample code instead, it would work fine:
e.g
Sample code:

 Workbook workbook = new Workbook();
            WorkbookSettings settings = workbook.getSettings();
            //settings.setLocale(Locale.ITALIAN); 
            settings.setLocale(new Locale("it"));

        int sheetIndex = workbook.getWorksheets().add();
        Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
        Cells cells = worksheet.getCells();


        Cell cell = cells.get("A1");
        cell.setValue(1234);
        Style style = cell.getStyle();
        style.setNumber(3); 
        //or
        //style.setCustom("#,##0");
        cell.setStyle(style);
        System.out.println(cell.getDisplayStringValue());

        cell = cells.get("A2");
        cell.setValue(-1234);
        style = cell.getStyle();
        style.setNumber(3);
        //or
        //style.setCustom("#,##0");
        cell.setStyle(style);
        System.out.println(cell.getDisplayStringValue());

        workbook.save("out1.xls");

Hope, this helps a bit.

Thank you.

3

Hi,
the solution you have provided works, but it is not general.
I would like to know if there is a way to get the same results that excel show with cell.getDisplayStringValue() (or another method). I’ve the user language, and i have to be sure that the display string that i read from aspose cells is the same of the one is shown by excel.
What about style.setCultureCustom ?
M.

4

@m4tte,

Seeing your original line of code from your sample code, it looks you are specifying Accounting numbers formatting which actually refers to the formatting string “#,##0.00;(#,##0.00)” that contains bracket “(” symbol, see the API reference page (check the formatting string for 39):

style.setNumber(39); → it refers to “#,##0.00;(#,##0.00)”

So, I asked you to kindly use 3 or 4 for Style.Number attribute value for your needs:
i.e.,

style.setNumber(4);

Well, it is better you should use Cell.getDisplayStringValue() although you may also use cell.getStringValue() method.

Well, if you need to get/set the localized formattings, you have to use the certain Style method (e.g you need to use your custom formatting string that contains local accounting or currency symbols or chars). For example, you can use getCultureCustom()/setCultureCustom(java.lang.String value) to get/set the culture custom (locale oriented) number formatting . Otherwise, you may use getCustom() / setCustom(java.lang.String value) to get/set the custom number formatting string of a cell.

Hope, this helps a bit.

Thank you.

5

@m4tte

We have looked into this issue further and found you are right and it should be a bug of Aspose.Cells.

We have logged this issue in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42355 - For Number 39 MS Excel formats negative value with ‘-’ instead of ‘()’ for Italy
6

@shakeel.faiz
Thank you, i think all the currency and accounting formatting doesn’t follow the locale. If i set on the workbook the “it” locale, and format the cell with one of the formatting string that handle negative number (37, 5, 6, 39, 40, 41…) i expected that the cell.getDisplayStringValue() for negative number return “minus” symbol and not brackets. Right?

7

Another point,
with this piece of code:

Workbook workbook = new Workbook();
WorkbookSettings settings = workbook.getSettings();
settings.setLocale(new Locale("it"));
 int sheetIndex = workbook.getWorksheets().add();
 Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
 Cells cells = worksheet.getCells();
 Cell cell = cells.get("A1");
 Style style = cell.getStyle();
 style.setNumber(37);
 cell.setStyle(style);
 System.out.println(cell.getStyle().getCultureCustom());

In this case, when the “it” locale is set on workbook, i expected that cell.getStyle().getCultureCustom() return “#,##0_);-#,##0”, instead it returns “#,##0_);(#,##0)” like “en” locale does.
Is there a way to get the correctly localized formattig?
thanks.
M.

8

@m4tte,

I have logged it against your existing issue. Our concerned developer from product team will also consider this (if possible) as per your demand while fixing the issue.

We will evaluate if your are right and it does not give correct formatting strings using Style.getCultureCustom() method. Once we have an update on it, we will let you know here.

Thank you.

9

@m4tte,

This is to inform you that we have fixed your issue “CELLSJAVA-42355” now. We will soon provide the fix after performing QA and including other enhancements and fixes.

Thank you.

10

@m4tte

Please download and try the following fix and let us know your feedback. It fixes the issue CELLSJAVA-42355.

11

The issues you have found earlier (filed as CELLSJAVA-42355) have been fixed in Aspose.Cells for Java 17.8.