Formulas in XLSX not calculated when exporting to PDF

[Bexel Consulting]

Im using:
var xlsxProvider = new XlsxFormatProvider();
var pdfFormatProvider = new PdfFormatProvider();

byte[] docBytes = File.ReadAllBytes(path);
Workbook workbook = xlsxProvider.Import(docBytes, null);

byte[] resultBytes = pdfFormatProvider.Export(workbook, null);
File.WriteAllBytes(resultPath, resultBytes);

to conver xlsx file to pdf. In source xlsx file a have a Table and a formula '=SUBTOTAL(109, Table2[...]) that work in xlsx, but when i export it to PDF i need to have the calculated value, but instead im getting the string  '=SUBTOTAL(109, Table2[...]) . 

Is there a simple way to force calculation of all formulas in the sheet before exporting it to PDF?

[Yoan]

Hello Velijko,

Thank you for reaching out. I am sorry to hear you are experiencing unexpected results.

In order for me to get a better context and understanding of the scenario you are experiencing, would you be able to share with us a sample document and project you are using to reproduce this behavior? This way I can try to achieve the same results on my end, investigate the issue further, try to identify the cause, and get back to you with details and feedback.

I tried replicating the same scenario on my end with a dummy document, however, the result of the formula was successfully exported to PDF. I am attaching my project for your disposal so you can test and examine it for yourself and let me know if I am missing something. You might also notice the disappearing table in my example which is due to missing support by the library - SpreadProcessing: Add support for tables and table styles.

NOTE: While sharing resources please keep in mind that this is a Q&A Forum thread and anything attached here will be publicly accessible so make sure you remove or replace any sensitive data.

Regards,

Yoan

[Bexel Consulting]

Here is the xlsx file.  The file is created from a template xlsx that was filled programmaticaly with EPPlus library. File that i attached is the output file that i want to convert to PDF, and that im using as input file for XlsxFormatProvider. 

When i use your sample file with my code, i get this:

Also i noticed that you are using Telerik.Windows.Documents.Spreadsheet.FormatProviders.Pdf and i am using Telerik.Documents.Spreadsheet.FormatProviders.Pdf (24.4.1106). Am i using the wrong namespace?
here is the method that im using:

private void ConvertXlsxToPdfSingle(string path, string resultPath)
{
    var xlsxProvider = new XlsxFormatProvider();
    var pdfFormatProvider = new PdfFormatProvider();

    FixedExtensibilityManager.FontsProvider = new FontsProvider();

    byte[] docBytes = File.ReadAllBytes(path);
    Workbook workbook = xlsxProvider.Import(docBytes, null);

    foreach (Worksheet? sheet in workbook.Worksheets)
    {
        CellRange usedCellRange = sheet.UsedCellRange;

        if (usedCellRange == null)
            continue;

        pdfFormatProvider.ExportSettings = new PdfExportSettings([usedCellRange]);
    }

    byte[] resultBytes = pdfFormatProvider.Export(workbook, null);
    File.WriteAllBytes(resultPath, resultBytes);
}

[Bexel Consulting]

I have managed to run your example with my file, and the function is not calculated. It must be the way that the table is created or how the function is created. I created the table using Insert Table in Excel and added Total Row by checking the checkbox.
I did the same with the sample file you provided and run the sample code you provided and got this result:

65 is calculated it is the same formula but for the quantity column.  But my formula is not calculated, even thou its the same formula but a different column

 

[Yoan]

Hello Velijko,

Thank you for your cooperation and for following up with additional resources. 

I was able to reproduce the same results on my end and here is my feedback after investigating the issue further:

• Usually, the formula is not expected to work because it references a table (Table2) and tables are not yet supported by the library - SpreadProcessing: Add support for tables and table styles. You can verify this if you try to export the file to XLSX, which will result in a corrupted document.
• In some cases, however, the document can contain cached values of the cell results, which the PDF export manages to obtain and export to PDF (like in this case). This method however cannot be replied upon. 
• Regardless, in this PDF export scenario, I have noticed that if the formula contains the following space, the correct value is exported to PDF:

If it fits your scenario, feel free to manually add this space to your formulas and see if the PDF export results are as expected.

That said, If you happen to be interested in the linked feature request, please feel free to cast your vote for it in order to increase its priority in our backlog and to subscribe so you can get notified about potential status updates in the future.

Additionally, to provide more context:

• Telerik.Windows.Documents.Spreadsheet.FormatProviders.Pdf - .NET Framework assembly/NuGet package.
• Telerik.Documents.Spreadsheet.FormatProviders.Pdf - .NET Standard assembly/NuGet package.

The assembly references of each library come in two versions - .NET Framework and .NET Standard with the only difference being that the .NET Framework ones contain the word "Windows" in their name:

NOTE: Depending on your project you should choose one or the other, but avoid mixing  .NET Framework and .NET Standard references. Also, the namespaces used in the project are always the same and always contain the word "Windows").

I hope this helps.

Regards,
Yoan
Progress Telerik