xlsx to pdf datetime conversion error

[j75]

marian3.xlsx

Converting the attached file on a French computer produces incorrect PDF:

• simply running my program (java -jar <my fat jar>) all data rows are displayed in the PDF as ######
• however, if I run my program with the -Duser.language=fr_FR option the dates are displayed, but in English way (month/day/year) - what I want is to display them French style: day/month/year - as they are displayed by Microsoft Excel or Libre Office.

My Java code is basically this one:

Workbook workbook = new Workbook("marian3.xlsx");
int sheetCount = workbook.getWorksheets().getCount();
for (int s = 0; s < sheetCount; s++) {
            Worksheet sheet = workbook.getWorksheets().get(s);
            PageSetup pageSetup = sheet.getPageSetup();
            pageSetup.setLeftMargin(MARGIN_CM);  // private static final double MARGIN_CM = 0.4;
            pageSetup.setRightMargin(MARGIN_CM);
            pageSetup.setTopMargin(MARGIN_CM);
            pageSetup.setBottomMargin(MARGIN_CM);
            pageSetup.clearHeaderFooter();
}
PdfSaveOptions options = new PdfSaveOptions();
options.setOnePagePerSheet(true);
options.setCompliance(PdfCompliance.PDF_A_1_B); // NONE, PDF_A_1_A, PDF_A_1_B
options.setCustomPropertiesExport(PdfCustomPropertiesExport.STANDARD);
workbook.save("out.pdf", options);

Did I miss any option/parameter/... ?

[amjad-sahi]

Which version of the product you are using? I tested using latest version/fix (Aspose.Cells for Java v20.8.x - please try it if you are not already using it), it works fine and I do not find any issue. Please find attached the output PDF for your reference.
out1.pdf

If you still find the issue with latest version/fix, please attach your output file, we will check it soon.

[j75]

I am afraid that version does not matter too much, with 20.03 and 20.08 I get the same result - see the attached file! :-(
out.pdf.
All these tests were performed on a Ubuntu 18.04 system.

[j75]

Here is my test project, if this may help (it includes the executable jar too).
aspose-test.zip

[j75]

And now the result get when running (Ubuntu 18.04 too)
java -Duser.language=fr_FR -jar aspose-tests-1.0-SNAPSHOT.jar marian3.xlsx - quite different, isn't it ? and not in French style...

out.pdf

[j75]

On another Linux system (Ubuntu 20.04) I have another result,but still in English format (month/day/year)!

openjdk version "1.8.0_265"
OpenJDK Runtime Environment (build 1.8.0_265-8u265-b01-0ubuntu2~20.04-b01)
OpenJDK 64-Bit Server VM (build 25.265-b01, mixed mode)

Here at least with or without -Duser.language=fr_FR option is the same result!

out.pdf

[j75]

Seems to be somehow related to the LANG environment variable - when unset or set to en_US the conversion took place and I have a result with dates in American style (1/7 for January the 7th), when set to fr_FR (or fr_FR.UTF-8) then I only have # signs instead of dates ...

[j75]

However, the attached file is correctly converted, with or without LANG variable, and is correctly converted in French dates style!
So it might be related the way you are parsing the document...
marian.xlsx

[amjad-sahi]

We think the possible reason for your issue is related to font(s) (that are used in the Workbook) which might cause the column width calculated differently and sometimes the column width is not enough to display the formatted results so they are shown as "#". For "marian3.xlsx", even when we opened it in MS Excel on some other machines, we also found A5:A7 range are shown as "###". We recommend you to expand/extend the width of column A a bit and then check whether you can get the correct results or not.

Alternatively you may try to use Worksheet.autoFitColumn(0) to auto-fit the first column in the workbook before rendering to PDF file format. This will also fix your issue.

Should you still have the issue or further queries, feel free to post in the forums.

[j75]

Yes, it seems that this fixed the issue; in reality I have used sheet.autoFitColumns(); (so for all columns) => could that have side effects I cannot foresaw ?

Anyhow, thank you very much for help.

[amjad-sahi]

Good to see that your issue is sorted out by the suggested approaches.

The only side effect for using Worksheet.autoFitColumns() method is a little time consuming if there are lots of data in the columns. If there is not much data in the worksheet (just a few hundreds or some thousands of rows), then it will be worth using it. It will give you consistent look and feel for the data. Moreover, if you need to auto-fit specific columns, you may try using Worksheet.autoFitColum() method instead for your desired columns only.