How to get cell value with applied formatting (formatted cell value) with OpenXML SDK

Question

I've been googling and searching on the site for the answer, but I couldn't find a solution - everywhere people mostly discuss how to add new number format to the document and apply it.

What I need is to get the cell value as a string with applied formatting - i.e. same string as would be displayed by Excel.

I already figured that there's no easy way or built-in function which would return the readymade formatted value for a cell.

So it seems to me that to get the value I need to do two things: 1. Get the format string. 2. Format the cell value using this string.

But I have problems with both steps.

One can easily get CellFormat instance which would contain NumberFormatId:

CellFormat cellFormat = (CellFormat) document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(cell.StyleIndex);

But how to get the format string with this NumberFormatId, if the id corresponds to one of standard predefined formats? (i.e. is below 160) They are not in the spreadsheet document and I can't believe that they should be hardcoded in the application.

Also, once the format string is somehow obtained, how to apply it to the cell value? So far I understand, the code should check the type of the cell value and if is Number - convert it to string using the format string.

I found this page which mentions using Microsoft.Office.Excel.Interop, but I would prefer to stay with OpenXML SDK only.

Overall, I'm very surprised that it's so difficult to find a definitive answer to this question on the Web as I thought that this would be something which many developers need in their daily work.

Answer 1

Men, this is a hard one... I will be adding here things that i found that could be worth..

First is to get the numbering format of the cell (once you have the CellFormat:

string format = excel.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Elements<NumberingFormat>()
            .Where(i => i.NumberFormatId.ToString() == cellFormat.NumberFormatId.ToString())
            .First().FormatCode;

For more information about this you can go to: NumberingFormats

Im trying to find out how to apply this format to the cell.CellValue property... I think thats the way you have to go!

Ok, reading the ClosedXml code (its open source), seems to be easy to get the format.

Simply convert the value text to its type (int, double, etc) and call the ToString method passing the format. I was trying do that with the String.Format and didnt work. Ive tested the ToString and it works, but something still missing.

I recommend to you to look at this class and get the code from the method GetFormattedString() as @El G tell in his comment.

Bassicaly you will have to add something like this:

double d = double.Parse(cell.CellValue.InnerText);
string val = d.ToString(format);

Hope it helps you...