I have a table of data (calculation results) that the user should be able to export to different formats. I use Interop.Excel to prepare a table with the data and use the data and format it using visual formatting (fonts, colors etc.) and NumberFormat. Example:
cellRange.NumberFormat = "#,##0";
When I save the table as an Excel file all formatting is ok when exporting to .xlsx and .xls:
excelWorkBook.SaveAs(exportFileName, Excel.XlFileFormat.xlOpenXMLWorkbook); // for .xlsx
excelWorkBook.SaveAs(exportFileName, Excel.XlFileFormat.xlExcel8); // for .xls
I also want to give the user the possibility to export this table to .pdf and .xps from the application without having to open the Excel file. As I have prepared the tables in Interop.Excel, I tried exporting the same table to those file formats:
excelWorkBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF,exportFileName); // for .pdf
excelWorkBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypeXPS,exportFileName); // for .xps
Both of these result in good documents except that all NumberFormats are lost resulting in long decimal values of doubles. This is not appropriate for the customer's summary of the data. (Colors and fonts remain as defined in .pdf and .xps.)
I have tried setting .Styleand .Styles to "Number" or the like. This does not solve the problem.
I have also tried to protect the Range of cells or the excelWorkSheet. This does not solve the problem either.
Someone suggested calling a VBA macro / sub through C# but after some looking into that, I get the impression that it's not a very straight forward (or stable) path.
I am looking for any help in resolving this issue through Interop.Excel or in another way.
lucn
After some testing it seems clear that the property I named in my comment must be set to false:
Microsoft.Office.Interop.Excel.Application.ActiveWindow.DisplayFormulas = false;
It is not evident why this influences the export to other formats such as *.pdfbut this is clearly the case and setting the .DisplayFormulas = false solves the issue.
Hope this helps somebody.