B
B
Benelli2012-01-19 17:45:02
.NET
Benelli, 2012-01-19 17:45:02

OpenXML SDK: how to get formatted cell value?

How can I get the formatted (with number format applied) cell value from an XLSX file? In other words, such a value, as Excel itself would show it?
I understand that there is no ready-made such function and most likely it is necessary to act in two stages - get the format string and format the value using it.
Well, using this piece of code, you can get a CellFormat object that will contain NumberFormatId:

CellFormat cellFormat =<br/>
 (CellFormat) document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(cell.StyleIndex);

But how now, based on the format ID, get the format string if, according to the specification, the first 160 formats are pre-defined (standard) and are not explicitly presented in the document? Do not hardcode all these 160 standard format strings in the program?
Here I found a snippet on how to get the already formatted value right away:
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();<br/>
<br/>
value = excelApp.WorksheetFunction.Text(value, currentNumberingFormat.FormatCode.Value);<br/>

But this solution does not work for me, the code throws an exception. Plus, Office Automation, as far as I know, is installed with the office, but it will not be on the server.
And then, I would like to stay within the OpenXML SDK.
I've been googling this topic for a day now, but haven't found a simple solution yet. And this is very strange, it seemed to me that this would be a very common problem.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question