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

Question

I have an excel template that has preset formulas, then have my wpf application fills in data in other sheets then the preset formulas takes the data from the other sheet and shows it on the main page.

The problem is when I automatically PDF the excel most formulas go through but other's give me a System.Object[][] or some other errors. But when I access the excel file with the dataset it works.

The difference between the formulas that go through and the ones that don't are the ones that have an if() statement to remove all non zeroes in a range like this.

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

Functions like this works:

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

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

Answer 1

As you tested the Excel file and found it working fine, the problem is likely located inside the Spire rendering.

You can of course report a bug over there but that might not get resolved instantly.

You may want to try applying a number format to the cells for zero values instead of using that if part in the formula.

Something like this could do when writing with ClosedXML if implemented correctly by Spire:

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

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