Despite attempts at setting different calculation options (CalculationOptions, recursive to true, etc.), the attached file fails to properly calculate before a pdf file generation.
Typically, the principle of what is applied is (C#, .NET):
workbook.CalculateFormula(); //with various options settings attempts
var fs = new MemoryStream();
workbook.Save(fs, SaveFormat.Pdf);
fs.Seek(0, SeekOrigin.Begin);
return fs;
Note:
- except for the first tab, all other Excel tabs are meant to be hidden when generating the PDF
- file macros only exist to manage the file but are in no way used for any of the calculations
Thanks for providing us temlate file and some details.
Well, I am afraid, Aspose.Cells currently does not support to execute/run VBA codes/ macros in the template file. As your macro code does hide other worksheets in the workbook and Aspose.Cells does not support this feature, so it would render all the worksheets in the output PDF file. If you perform the task and hide the worksheets using normal MS Excel features (and not by your macros/ vba codes) then Aspose.Cells would render the PDF file from Excel file in accordance with your requirements.
As I however pointed out, the macros have nothing to do with either the calculation or anything I expect to be executed from the code. They’re essentially there to allow configuring the file manually from Excel before it is used by our application.
I nevertheless gave it a try and made the file fully macro-free and experience the exact same issue of the file not being properly calculated.
Please find the updated Excel file (with macros removed) that still highlights the problem.
Well, I have tested your scenario/ case using your newly attached file via Aspose.Cells APIs, I got the attached output PDF file (“out1template_XLSX1.pdf”) which has 37 pages in total. I am surprised to find that when I rendered it to PDF file (“template_XLSX1.pdf”) via MS Excel manually, it only has one page, now this is strange. Could you give us details why MS Excel renders to one PDF page only and what has brought MS Excel to hide all other worksheets except the first? Did you set some properties for the sheets in Visual Basic Editor in Ms Excel manually or something else? Please give us complete details, so that we may evaluate your issue further and if this is supposed to be an issue with the product, we may log a ticket for it into our database. Furthermore, the charts do not match when comparing the first page of PDF (via Aspose.Cells) with the only page of PDF file by MS Excel
When actually used in our platform, we hide all tabs except for the first one. The fact that you have 37 pages comes from the fact that other tabs are unhidden. This part is expected.
As long as you having a single page when directly creating the pdf from Excel, I don’t really now but that’s not the issue. There’s no macro in the file. Just a print zone set on the first page to have nice display in the PDF.
The fact that the charts do not match is the issue we’re discussing. With some files, I do indeed observe differences in what Excel calculates and what Aspose calculates. On some other occasions, many cells seem not to be calculated at all. What you see in the Excel file (and in the PDF generated from Excel) is what I expect to see when creating the PDF with Aspose (i.e. exactly the same as in Excel).
As long as you having a single page when directly creating the pdf from Excel, I don't really now but that's not the issue. There's no macro in the file. Just a print zone set on the first page to have nice display in the PDF.
Well, there was a problem on my end regarding rendering single PDF page. Actually there was an MS Excel option "Active sheet(s)" set, I changed it to "Entire workbook" while saving the workbook to PDF in MS Excel manually and now I got 37 pages now which is expected.
gle:
The fact that the charts do not match is the issue we're discussing. With some files, I do indeed observe differences in what Excel calculates and what Aspose calculates. On some other occasions, many cells seem not to be calculated at all. What you see in the Excel file (and in the PDF generated from Excel) is what I expect to see when creating the PDF with Aspose (i.e. exactly the same as in Excel).
Well, this is not an issue with the component as Aspose.Cells renders the charts and data same as per MS Excel, you may even check the charts and data in the template file and compare it with output PDF by Aspose.Cells. I have also tested by converting your Excel file while making "Entire workbook" option on to PDF, the output PDF (attached) looks to me exactly the same as per my output PDF (which I attached in my previous post) via Aspose.Cells APIs. Please do render PDF file via MS Excel as I did manually and check if you see any discrepancies or differences regarding charts or data. If you find any difference regarding charts, please highlight them via screen shots. Also attach your output PDF file via MS Excel, we will check it soon.
Well, in attachment, you’ll see the PDF file I get out using Aspose. When extracted as an Excel file, the file shows up everything correctly (same as what you can see in Excel file I sent earlier).
The exact process through which this file is generated is that we have code filling in the data tabs (see Electricity, Gas, Sources and Sites tab in the previously shared Excel file). We have a set of intermediate calculation tabs in the file leading up to the front page, which we extract in PDF.
An option in our application allows either extracting the file as an Excel file (everything is fine) or as PDF, in which case, we have the sample code I wrote down above to first calculate the file and then save it as PDF. As you can see from the attached PDF file, that procedure somehow fails and the PDF does not show the calculated data.
It may very well be when you take the Excel file I sent, everything works fine (as you seem to say) as it already contains the data and does not go through the filling process. Would there be something to implement in addition to the CalculateFormula() to ensure recently code-filled-in data is accounted for upon calculation. Could that be the issue?
Symptom is very clear. Workbook exists in code and is perfectly fine when extracted in Excel. When extracted in PDF, not all elements are calculated.
I’d like to further point out, as I only just had the chance to look at the PDF file you attached to your post, that it precisely highlights the problem as the charts are not filled-in while they are in the Excel file when calculating the file (see attachment).
Note that, in order to share a filled-in XLS file with you, I populated it and turned the calculation to Manual (in order for the file to be in the non-calculated state). As your test shows, the file is not calculated when extracted to PDF. If you however calculate the Excel file, you’ll see that all the charts are filled-in. Hence the problem I report.
Note - just saying this to avoid a useless exchange - that we always use the file in the AutomaticCalculation mode on our side. I only turned it to manual in order for you to have a data-filled Excel file not calculated and hence have the opportunity to force the calculation from the code prior to the PDF generation, which seems to fail on your side too based on the PDF you attached.
Thanks for providing us template file, screen shots and details.
After further investigation, I observed the issue as you mentioned. I confirmed the calculation options are set to “Manual” in your template file “template_XLSX.xlsx”. I also observed that charts are not calculated even calculating formulas manually in MS Excel 2007. But I can see charts (in the first sheet) are calculated fine after calculating worksheet in MS Excel 2010/2013. As I was previously using MS Excel 2007 which consequently does not produce the issue regarding charts. But using MS Excel 2010/2013, I can see charts are calculated fine. I suspect Aspose.Cells does not calculate formulas and charts fine, it tends to follow MS Excel 2007 which is not calculating charts fine and should follow MS Excel 2010/2013. I even manually tried to calculate charts in the Workbook in my code but to no avail, I used the following sample code with your template file: e.g Sample code:
Workbook wb = new Workbook(@“e:\test2\template_XLSX.xlsx”); wb.CalculateFormula();
foreach (Worksheet wks in wb.Worksheets) { foreach (Chart chart in wks.Charts) { chart.Calculate(); } }
wb.Save(“out1.pdf”);
I have logged a ticket with an id “CELLSNET-44135” for your issue. Our concerned developer from product team will look into it to figure it out soon.
Once we have any update on it, we will let you know here.
There’s another file file for which we do experience what seems to us, as a user, a identical issue (Excel file not calculated properly upon PDF creation). As it may be (from a technical perspective) a different root cause, it may be worthwhile I share it with you as well. It contains some more confidential data. The check box “Keep this post private” however seems ineffective. How can I share this file with you privately?
Please start a new thread with your template files (Excel file and output PDF file etc.) and complete details about your new issue as it will be better for us to evaluate and manage your issue precisely to consequently figure it out soon. And, as your template file contains confidential data, so while writing your new post (via New Post button) and attaching your attachments, please set the “Keep this post private” checkbox on, this will make the thread private for your requirements. The private threads with attachments can only be seen by Aspose staff members and the owner of the thread, no other user can see or download your attachments.
I am afraid, your issue is not resolved yet. However, I have logged your concerns against your issue “CELLSNET-44135” into our database and asked the concerned developer from product to update on it or provide an eta for it.
Once we receive an update on it, we will let you know here.
We are afraid, there is no news for you regarding this issue at the moment. However, we have requested product team for an update and logged your comment. Once, there is some news for you, we will let you know asap.
Have you at the very least identified what in the Excel file could not be handled by Aspose. I urgently need to have this export working, either through you fixing the bug or me adjusting the file to comply with Aspose limitations.
Thanks for keeping me posted. I can provide information on the Excel file and help your developers locate the issue.
We have logged your comment in our database against this issue for product team consideration. Please spare us some time. Once there is some news for you, we will update you asap.
This is to inform you that we have fixed your issue CELLSNET-44135 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
gle: