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

Free Support Forum - aspose.com

Localized format in formulas

Hi,

Users use MS Excel for report’s building and they have Russian format in Windows OS applied.
According to these settings localized format is used in Excel TEXT function (e.g. “ГГГГ” instead of English “YYYY”). English format cannot be used in reports as MS Excel works in compliance with OS format.
I have attached screenshots and sample report to review the details. We load the reports using Aspose as templates and use the following code:


@Test
public void recalcBook() {
try {
Workbook wb = new Workbook("D:\\in.xlsx");
Cell cell = wb.getWorksheets().get(0).getCells().get(0,0);

System.out.println("Before calculation");
System.out.println(wb.getWorksheets().get(0).getCells().get(1,1).getStringValue());
System.out.println(wb.getWorksheets().get(0).getCells().get(1,1).getFormula());

cell.setValue(new Date());
wb.calculateFormula(true);

System.out.println("\nAfter calculation");
System.out.println(wb.getWorksheets().get(0).getCells().get(1,1).getStringValue());
System.out.println(wb.getWorksheets().get(0).getCells().get(1,1).getFormula());
} catch (Exception e) {
System.out.println("Error");
e.printStackTrace();
}
}

The output looks as follows:

Before calculation
2011
=TEXT(A1,"ГГГГ")

After calculation
ГГГГ
=TEXT(A1,"ГГГГ")


Calculation break the result of TEXT function (correct outcome is "2014" instead of "ГГГГ"). There is no resource in Aspose to define localized formats.

Could you scrutinize this issue and provide the solution of this problem?

Thanks.

Hi,


Well, I noticed that MS Excel (US English) version calculates your formula on B2 cell as “ГГГГ”. Generally, Aspose.Cells follows MS Excel (english) standards to specify/ calculate the formulas in Excel sheets, see the document for your reference:
http://www.aspose.com/docs/pages/viewpage.action?pageId=15565448

You have to specify the format text as “YYYY” as parameter instead of “ГГГГ” to make it work properly for the TEXT formula.

Anyways, we will still evaluate if we can do something for it.

Once we have any update on it, we will let you know here.

Thank you.

Hi,


Let me explain in details.

  1. Your MS Excel (US English) version will calculate on B2 cell as 2011 and C3 as YYYY if you change Regional\Settings\ to Russian in Windows OS and restart MS Excel. The reason is because MS Excel uses localized format to calculate formula.
  2. Users have many applications on workstation and the Russian format in OS is used everywhere, they cannot change it to English.

Users type “=ТЕКСТ(СЕГОДНЯ(); "ГГГГ")” in MS Excel and it calculates as 2014. They can’t type YYYY instead of ГГГГ as it will result as YYYY in MS Excel instead of 2014 and all depended cells will show wrong values, report will be broken for use/developing in MS Excel.

There is no problem with localized function name (ТЕКСТ) or localized delimiter for function parameters (;) that typed by users as MS Excel saves not localized data in Spread sheet Open XML file. It looks as follows in the provided report:

TEXT(A1,"ГГГГ")

...

As you can see from above MS Excel has saved “ТЕКСТ” as “TEXT” and delimiter “;” as “,”.

But format parameter is locale depended in this function. You already have implemented some locale depended API, for example getDisplayStringValue:

@Test

public void formatValue() {

try {

Workbook wb = new Workbook();

Worksheet ws = wb.getWorksheets().add("test");

Cell cell = ws.getCells().get(0,0);

cell.setValue(new Date());

Style style = cell.getStyle();

style.setNumber(22);

cell.setStyle(style);

System.out.println(cell.getDisplayStringValue());

} catch (Exception e) {

System.out.println("Error");

e.printStackTrace();

}

}

Output for VM arguments “-Duser.language=en -Duser.country=US” looks as follows:

6/19/2014 15:21

Output for -Duser.language=ru -Duser.country=RU:

19.06.2014 15:28

I think locale depended resource file can be added to Aspose. According to Java locale Aspose calculation engine will use appropriate resource file for MS Excel standard functions. This file will contain localized formats.

This problem has high priority for us, it makes impossible to process these reports with Aspose. Could you estimate time frame to resolve the issue?


Thanks.

Hi,


Thanks for providing further details.

Well, Aspose.Cells has its own formula calculation engine that supports to create/ set, manipulate, calculate MS Excel formulas but it may work for US-English formulas/functions in most cases.

Also, you know there might be too many different expressions for different locales and settings, so, we cannot fully support all of them at once. But for your requirements, we may investigate and support your most desirable formats and expression for your Russian locale if possible. Could you provide us a list of those localized formats and expressions which you need the most, we will try to support them but one by one according to the list. Also provide complete details about those expressions and formats for your favorite functions, so we could log ticket(s)/ issue(s) into our database accordingly.

Thank you.

Hi,


Thanks for providing us description text and other details regarding expressions/ parameters for the formula(s).

We will check it and get back to you soon.

Thank you.

Hi,


We have logged a ticket with an id “CELLSJAVA-40863” for your requirements i.e. Support for localized formats in the formulas (e.g TEXT) int our database. Our concerned developer will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
Hi,

Could you estimate time frame (or planned date) to resolve this issue?

This problem has high priority for us.

Thanks.

Hi,

Thanks for your posting and using Aspose.Cells.

We have checked your issue status from database. It is resolved. Hopefully, we will provide you a fix. Once, the fix will be available, we will let you know asap.

The issues you have found earlier (filed as CELLSJAVA-40863) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.