Spire When Pdf-ing an excel file it leaves some formulas as System Object

Question

My WPF programme fills in data in other sheets using an excel template with preset formulae, and the preset formulas then use the data from the other sheet to display it on the main page.

The issue is that while most formulas pass when I automatically PDF the excel, some others give me a System.

errors such as object[][] or others. However, it operates when I view the excel file containing the dataset.

The formulas with an if() statement to eliminate all non-zero values in a range like this are the ones that work and those without.

=TEXTJOIN("
", TRUE, TEXT(IF(Details!O:O>0,Details!O:O,""), "HH:MM"))

Functions like this work:

=TEXTJOIN("
",TRUE,Details!D:D)

How do I get Spire.xls to PDF the right format?

Answer 1

Since you tested the Excel file and determined that it functions well, the issue is probably with the Spire rendering.

Of course, you can submit a bug there, but it might not be fixed right away.

Instead of utilizing that if the part in the formula, you might want to consider applying a number format to the cells that contain zero values.

If Spire implements ClosedXML correctly, something like this could occur when writing with it:

worksheet.Cell(row, column).Style.NumberFormat.Format = "#,##0;[Red]-#,##0;\"\";@";

Formating possibilities are somewhat documented by Microsoft, take a look over there.