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

Free Support Forum - aspose.com

Generated Pdf file converting data from wrong Workbook

Hello there!

I’m facing an issue regarding the convertion of a xlsm file into pdf.

The code consists of two steps:


1. I copy one worksheet from one excel file to another (both at the same location).
2. Then, I generate a pdf file based only on the first worksheet of the second excel file.

Important notes:
- The second file contains three sheets, one of them with graphs and the others with data, in which the graphs are based on.

The thing is, when I copy the worksheet from one excel file to the other, it works file. I use the Workbook.Save() method to save and all the data and graphs are OK!

However, when I save this sheet into pdf, it kinda “misunderstand” its references and some values contains old inputs. I then used a method called CalculateFormula() that forces the update of those values, but it causes the other values to get #REF values.

I noticed that those #REF values are referencing the other sheet, like ‘D:\Bizagi\Projects\1…!sheetnameCellvalue’.

I don’t want to reference the other sheet, all I wanted was to copy the data from one sheet to another.

One point to take into consideration is that the conversion works fine locally, but when I try to use it on a remote server, this issue happens.

Things are kinda hectic in this project, so I didn’t have much spare time to do a better search through the forums in order to find a way out of this. But so far, I couldn’t find anything that helped.

Here’s the code:

string caminhoDe = “Relatório De.xlsx”;
string caminhoPara = “Relatorio_teste.xlsm”;

Aspose.Cells.Workbook excelWorkbook1 = new Aspose.Cells.Workbook(caminhoDe);
Aspose.Cells.Workbook excelWorkbook2 = new Aspose.Cells.Workbook(caminhoPara);

excelWorkbook2.Worksheets[1].Copy(excelWorkbook1.Worksheets[“SaidaMatlab”]);

excelWorkbook2.CalculateFormula(true);

excelWorkbook2.Save(caminhoPara);

for (int i = 1; i < excelWorkbook2.Worksheets.Count; i++)
{
excelWorkbook2.Worksheets[i].IsVisible = false;
}

PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.OnePagePerSheet = true;
excelWorkbook2.Save(“Relatório Final.pdf”, pdfSaveOptions);

Hi,


Thanks for providing us details and screenshots.

Well, Aspose.Cells follows Ms Excel standards and specifications. I think you should manually copy that worksheet from one workbook to another (in MS Excel) to save the spreadsheet and render to PDF to evaluate if you see the similar issue or not. Moreover, to evaluate your issue precisely, we need your template XLSM files, so kindly provide the template files, we will check it soon.

Thank you.

Hi, Amjad! Thanks for the quick reply!

I followed your instruction and tried copying and pasting the sheet manually, and it worked once I rendered to PDF.


Attached are the files I’m using - dados.xlsx is the first Workbook in which I copy the worksheet from.


Thanks in advance!


Hi Fernando,


Thank you for sharing the samples. There could be 2 solutions as detailed below.

  1. First options is not to copy the external formulas but their available values. Please check the snippet at the bottom of this post.
  2. Update the data from the external links while using Workbook.UpdateLinkedDataSource method as demonstrated at the bottom of this post. Please note, if the path to Relatório De.xlsx is correct then you do not need copying operation, simply call Workbook.UpdateLinkedDataSource method.

Code snippet for copying the formula values (not formula) is as follow.

<span style=“background-color: rgb(255, 255, 255); font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”>CopyOptions options = <span class=“kwrd” style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”>new<span style=“background-color: rgb(255, 255, 255); font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”> CopyOptions();
options.CopyInvalidFormulasAsValues = true;
excelWorkbook2.Worksheets[1].Copy(excelWorkbook1.Worksheets[“SaidaMatlab”], options);

Code snippet for updating the external links is as follow.

excelWorkbook2.UpdateLinkedDataSource(new Workbook[] { excelWorkbook1 });
excelWorkbook2.CalculateFormula(true);

Hello! How are you?

Thank you! The first solution you suggested did the job. Only one value came with #VALUE!, but it’s probably a matter of reference within the excel template it’s based on.


I’m going to test it in the customer’s environment now.

Regards,

Fernando.

Hi Fernando,


I am fine and hope your are doing good as well. Please feel free to test the proposed solution and get in touch if you require any further assistance with Aspose APIs.

Hello!

Everything is working fine, there’s only one detail in which I couldn’t find how to fix.


Two of the graphs are having their data misplaced when the pdf is generated.

I attached two images comparing the graphs in the excel file and when the pdf is rendered.
Also, I uploaded the samples I’m using.


*The file modelo.xlsm is the file whose pdf is generated from.

*If I attempt to Save As pdf manually from the excel file, it’s normal.

Regards,

Fernando.

Hi Fernando,


Thank you for writing back. I have evaluated your recently shared scenario against the latest version of Aspose.Cells for .NET 16.11.3, and I am able to notice the said issue. In order to further investigate the matter, I have raised this incident with the product team under the ticket CELLSNET-44934. Please spare us little time to properly analyze the case and revert back with updates in this regard.

Alright!

Thank you for you assistance.

I’ll wait for further updates.

Fernando.


Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.12.3 and let us know your feedback.

Please note the above fix is compiled with .NET 2.0, if you need the fix compiled with .NET 4.0, then please download Aspose.Cells for .NET v16.12.3 (.NET 4.0).
The label “0” on the legend is fixed. The second issue, legend’s items are 4 in excel, while they are 5 in pdf. This issue is too difficult. We cannot fix it now. Actually, the items of legend are 5. If you enlarge the size of the chart in excel, you will find the fifth item.

Hello!

The fix provided did solve the issue we were having. Thanks for the support!

Regards,

Fernando.

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.