Russian name of month using function TEXT

Aspose.Cells Product Family
1

Dear colleagues.

We have a new problem in Excel 2 PDF conversion.

In our Excel file we use a function TEXT(dataZaklucenia,""[$-FC19]ММММ"") applying it to the date value.

If we run the conversion on a Windows Server with the Russian language pack, it returns a Russian name of month. But on a Windows Server without the Russian language pack it returns English name of a month.

Is there any way to get the Russian name of month using function TEXT without installing the language pack?

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us some simple excel file containing your format as you have shown in the post and its pdf generated on the Russian and English machines. We will then investigate this issue and see if it is possible to achieve your requirements on English machines where Russian language pack is not installed.

3

Here 2 samples of PDF generated on server with or without the language pack installed and a source Excel file.

There is another problem with this Excel function, even if the language pack is installed. The name of the month is written in a wrong case. See some screenshots attached.

4

Hi Vladimir,


Thank you for sharing the sample spreadsheet.

I have worked with this scenario a bit, and I think you do not need to install the specific language pack in order to get the month names in different languages because you can specify the language code for Excel’s TEXT function. Following is the simple Java code that I have executed against the latest version of Aspose.Cells for Java 8.6.3.2 to receive expected results in XLSX as well as PDF formats.

Java

Workbook book = new Workbook();
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
Cell cell = cells.get(“A1”);
cell.putValue(“26/09/2013”, true, true);
cell = cells.get(“A2”);
cell.setFormula(“=TEXT(A1,"[$-419]MMMM")”);
book.calculateFormula();
sheet.autoFitColumns();
book.save(dir + “output.xlsx”);
book.save(dir + “output.pdf”);


In case you still do not get the desired results against your sample then please share the password for the provided spreadsheet as it seems to be protected, and we need the password & reference to the cell containing the desired formula for thorough investigation.
5

Thanks for the advice, we did proceed to our goal using additional function arguments. But we still have a problem with the wrong case of month name as you could see in png attached to the previous post.

Here you can find an unprotected version of the source Excel file.

6

Hi Vladimir,


Thank you for sharing another sample, however, this new sample has some problems which aren’t allowing me to conduct tests on it. First of all, there is some macro error as shown in the attached snapshot, secondly, when I changed the formula in cell BW85 (=IF(dataZaklucenia="","",TEXT(dataZaklucenia,"[$-FC19]ММММ"))) for required language code the resultant PDF contained only the format, that is; MMMM in spite of the actual calculated value. Due to the above mentioned reasons, I have performed all test on a sample of my own. Anyway, regarding the capitalization of the calculated value of TEXT, I believe the aforementioned function returns the value with only first letter of the value in capital case. Please note, I am getting the same result with latest revision of the API while using your provided spreadsheet as input (however, language code has to be changed for Russian). Please check the attached original.pdf. In case you wish to make all the letters capitalized, you may use the function UPPER along with the existing TEXT function as follow.

Java

Workbook book = new Workbook();
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
Cell cell = cells.get(“A1”);
cell.putValue(“26/09/2013”, true, true);
cell = cells.get(“A2”);
cell.setFormula("=UPPER(TEXT(A1,"[$-0419]MMMM"))");
book.calculateFormula();
sheet.autoFitColumns();
book.save(dir + “custom.pdf”);
7

I think there was a kind of misunderstanding. We dont worry about the capitalization of the first letter. The problem is in the last letter of the word. See pictures attached before. “Январь” but must be “Января”.

8

Hi Vladimir,


Thank you for explaining the concerns.

Unfortunately, I have zero proficiency in Russian language so I cannot tell the difference between the two highlighted characters. However, I have performed tests using Excel application 2013 to see what it returns for the formula (=TEXT(“01/26/2015”,“[$-0419]MMMM”); which is Январь. Please check the attached spreadsheet and its resultant PDF produced by Excel application along with the PDF generated with Aspose.Cells for Java 8.6.3.4 (latest), which corresponds to the Excel results. Based on the discussion as above, I believe the results produced by Aspose.Cells APIs are correct because these APIs follow Excel’s guidelines & specifications in order to match the results.

9

Seems strange that you changes our formulas. We use =TEXT(“01/26/2015”,"[$-FC 19]MMMM" and it provides us the correct result. But your sample uses different language code 0419 and it really returns «Январь» even in Russian version of MS Excel. An example of an attachment.

10

Hi Vladimir,


Thank you for writing back.

Please note, I have changed the language code from -FC19 to -419 because I was not able to find any reference to the code -FC19 when I searched the language/locale codes over the internet. Below are a few document links that I have used for reference. If you have a document/link that could provide more details on the code -FC19, I would certainly like to review it for future reference.

  • http://blog.csdn.net/2066/article/details/45555
  • http://excel.tips.net/T003299_Specifying_a_Language_for_the_TEXT_Function.html
  • http://www.science.co.il/Language/Locale-Codes.asp

Regarding the new sample, I have performed the conversion on a system with English (United States) locale and corresponding formats while using the latest version of the Aspose.Cells for Java 8.6.2.4 (link provided previously) and following piece of code to get the desired results; that is, the last character of the month name is я however, if un-comment the 3rd statement (Workbook.calculateFormula) then the resultant PDF has the last character of the month name as ь. We need to log this case for further investigation by the product team. Before we move forward to raise this issue, we need the confirmation on how you generated the PDF attached to your last post. Did you call the Workbook.calculateFormula method before rendering the spreadsheet to PDF? Could you please also give a try to the latest version of the API and share the resultant PDF (against input.xlsx) here along with your code?

Java

Workbook book = new Workbook(dir + “input.xlsx”);
book.getSettings().setRegion(CountryCode.RUSSIA);
// book.calculateFormula();
book.save(dir + “output.pdf”);
11

We cannot use your sample because we do need to calculate formulas before converting Excel 2 PDF.

Considering FC19, unfortunately we cannot find official Microsoft reference to this code. But it works. So I think it’s a part of Excel specification, even nobody can find it over the internet (undocumented feature).

I think it would be better for you to contact Microsoft support to investigate, what’s the code FC19 and why it’s not specified in common documentation.

I’ll try to explain why we’re trying so hard to find the solution.

The English written version of date “01/19” looks like “The 19th of January”. Imagine, that your product would write “The 19 January” instead of the correct form of this phrase. I think you’ll be considering such thing as a bug.

We face the same problem having “Январь” instead of “Января”.

We really need your help to work out this problem.

I understand that there might be a workaround to use such Excel functions as “REPLACE” or something to get the correct result, but this is bad taste. Isn’t it?

12

Hi Vladimir,


If the the language code FC19 isn’t documented anywhere then I am afraid, we cannot consider it valid. However, I have logged an investigative ticket (CELLSJAVA-41680) for the product team in order to review the scenario to find out the cause for the different last character being rendered for the month name January (in Russian), that is; before & after calling the Workbook.calculateFormula method. Please spare us little time for analysis. As soon as we have completed the investigation, we will share the results here.
13

Hi again,


This is to update you that we have resolved the problem logged earlier as CELLSJAVA-41680. We will shortly provide the fix here after ensuring the quality and incorporating other enhancements.
14

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.6.3.6 and let us know your feedback.

15

Hello, dear colleagues.

Thank you for fixing this problem. Now our functionality really works fine.

So where did you find the documentation for the code FC19. Is there any official Microsoft document that describes this code?

16

Hi,


Thank you for the confirmation on fix. It is good to know that you are up & running again. I am afraid, product team hasn’t provided any details regarding the code FC19. I have requested them to share any reference documentation regarding this code. As soon as I hear anything, I will post here for your kind reference.
17

Hi,


We have received the response from product team. Unfortunately, we could not find any document about the language code FC19. So, we just make special formatting logic for this special language code to make it give same output with MS Excel.

Thank you.
18

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

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

Hi!

We have an urgent problem in production environment.

There is a problem in the function text which convert date value into text.

Month name is wrong. Instead of July we’ve got June. In Russian it’s a difference in 1 letter “н” or “л”.

See the marked places in picture attached.

20

Hi Vladimir,


Thank you for writing back.

We have evaluated the presented scenario while using the latest version of Aspose.Cells for Java 8.7.0.3 and we are able to observe the said issue by comparing the Aspose.Cells’ result against Excel’s. We have logged this incident in our bug tracking system as CELLSJAVA-41748 for further investigation & correction. Please spare us little time to properly analyze this case and get back to you with updates in this regard.