Convert Microsoft Excel Spreadsheet to PDF in Java using Aspose.Cells REST API has date format issue

Aspose.Cells Cloud Product Family
,
1

Hi Aspose’s Support,

I have converted Excel to Pdf as following sample :
http://www.aspose.com/docs/display/cellscloud/Convert+Excel+Workbook+with+Additional+Settings

In my sample excel tests, the result of cell G1 is not the same between Excel and PDF
(formula : =A3+8-WEEKDAY(A3,2) )

Can you show me the root of this issue and how to fix it ?

Thanks,
Chien

2

Hi Chien,


Thank you for contacting Aspose support.

We are currently evaluating your presented scenario and we will shortly get back to you with updates in this regard. We are sorry for the inconvenience.
3

Hi again,


I have checked this scenario by converting your provided spreadsheets with following XML, and I believe that the results calculated by the Aspose.Cells for Cloud service for the cells G1 are exactly the same as calculated by Excel application. Please check the attached resultant PDF files as well as the snapshots showing the comparison.

XML

<PdfSaveOptions>
<CalculateFormula>False</CalculateFormula>
<CheckFontCompatibility>False</CheckFontCompatibility>
<Compliance>None</Compliance>
<OnePagePerSheet>True</OnePagePerSheet>
<desiredPPI>90</desiredPPI>
<jpegQuality>70</jpegQuality>
<SaveFormat>Pdf</SaveFormat>
</PdfSaveOptions>
4

Hi Babar,

Thank you for your detail response. I have checked it again with your suggestion XML and I have another error. I’m using Java codes.

In my excel, I have two cells G1 (as previous examples), and G3 is another cell with having same formula as G3. Please see the attachment excel file and the result PDF with different options :

False
False
None
90
70
true
Pdf


and

True
False
None
90
70
true
Pdf


I’d prefer the option True but it doesn’t work perfect in this case.

Thanks,
Chien

5

Hi Chien,


Thank you for sharing new sample.

We have re-evaluated the presented scenario while using the recently shared sample spreadsheet, and we are able to notice the said problem. The formula in cells G1 & G3 seems to return #VALUE! in resultant PDF file therefore we have logged this incident in our database under the ticket SAASCELLS-112 for further investigation. Please spare us little time to properly investigate the matter. In the meanwhile, we will keep you posted with updates in this regard.
6

Hi again,

This is to inform you that we have further investigated the presented scenario and we are able to figure out the problem cause. Please note, the problem is caused due to the casing of the parameter true, where the string ‘true’ needs to be in lowercase when Boolean values serialize. Please check the following piece of code that produces the correct output as attached.

Java

String infilename = “ExcelTemplate.xlsx”;
String outfilename = “ExcelTemplate(JavaSDK).pdf”;

String xml = “” +
“true” +
“300” +
“70” +
“true” +
“Pdf” +
“”;

//Convert the spreadsheet on storage
String strURI = “http:[//api.aspose.com/v1.1/cells/"+infilename+"/saveAs?newfilename=](https://api.aspose.com/v1.1/cells/%22+infilename+%22/saveAs?newfilename=)” + outfilename;
String signedURI = Utils.sign(strURI);
Utils.processCommand(signedURI, “POST”, xml, “xml”);
//Download the converted file
strURI = “http:[//api.aspose.com/v1.1/storage/file/](https://api.aspose.com/v1.1/storage/file/)” + outfilename;
signedURI = Utils.sign(strURI);
InputStream responseStream = Utils.processCommand(signedURI, “GET”);
Folder.saveStreamToFile(“D:/” + outfilename, responseStream);
responseStream.close();
System.out.println(“Done”);
7

Hi Babar,

I have checked with your suggestion XML again and I can see the better results. But the issue about different data between Excel and PDF still remains (in G1, G3) .

In excel : 10-08-15
In PDF : 7/9/2015

The XML for this test case as following :


true
false
None
90
70
true
Pdf


Thanks,
Chien



8

Hi Chien,


Thank you for the confirmation on formula evaluation problem. Regarding the format of the date, I can see the exact format in Excel as it rendered in the resultant PDF, that is; 7/13/2015. Please note, my system’s locale is set to US English and probably you have different locale that is why you are seeing the format differently in Excel on your side. Could you please state the Locale/Region of your system so we could investigate the matter further?
9

Hi Babar,

Thank you for your response. Please see the attachment image for my system’s locale and format cell in Excel. Should I change the locale in my Excel SpreadSheet ?

Thanks,
Chien

10

Hi Chien,


Thank you for providing the screenshots.

Please note, Excel application formats the number, currency and date-time values according to the format patterns specified in the system’s Region & Language settings. I believe you have altered the formats on your machine because by default the date format should be M/d/yyyy for English (United States) region. If you change it accordingly, you will be able to match the Excel format with the one in generated PDF.

I have attached the screenshots of the date format in Excel as well as the Region & Language settings of my machine for your reference.
11

Hi Babar,

Thank you for your helpful response.

I have changed my system’s locale and I can see the data in Excel GUI is correct (show result date same as yours). But when I tested it again, I still see the issues in cell G1, G3 :

In excel file : 7/13/2015

In pdf file :

+ With calculation parameter = true
=> different result : 7/9/2015

true
true
false
None
400
95
true
Pdf


+ With calculation parameter = false
=> different result : #VALUE!

false
true
false
None
400
95
true
Pdf


Thanks,
Chien



12

Hi Chien,


Thank you for writing back.

I have executed the process again on your recently shared spreadsheet and I am able to replicate the said problem on my side. The Excel application calculates the formula (=A3+8-WEEKDAY(A3,2)) as 7/13/2015 whereas the Aspose.Cells for Cloud service is calculating it as 7/9/2015. I will perform a few more tests before logging it for the product team’s review, while keeping you posted with updates in this regard.

Please note, you have to set the CalculateFormula parameter to true otherwise the formulas will not be calculated before rendering process and you will get the #VALUE! error in cells having formulas.
13

Hi again,


This is to inform you that I have logged this incident in our bug tracking system under the ticket SAASCELLS-116 for further investigation. Our product team will further look into the details of this problem and we will keep you updated on the status of correction. We apologize for your inconvenience.
14

Hi Babar,

Do you have any update on this ?

Thanks,
Chien

15

Hi Chien,


I am afraid, we haven’t yet received updates on the ticket logged earlier as SAASCELLS-116. We have requested the concerned team to provide the analysis results along with estimated time frame for the fix. As soon as we receive any updates in this regard, we will post here for your kind reference.
16

Hi again,


This is to inform you that the platform team is working on integrating the changes to the live service, and hopefully complete the process in a week or so. As soon as the changes have been integrated, we will notify you here.
17

Hi Chien,


This is to inform you that the fix has been integrated to the live service so you may now give this scenario another a try on your side. Please check the attachment for the resultant PDF generated on my end.

Thank you for your patience with us.
18

The issues you have found earlier (filed as SAASCELLS-116;SAASCELLS-112) have been fixed in this update and deployed to live service.

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