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.

Inhibit

Find some nice code here: joymonscode.blogspot.se/2013/10/… – Tabbi

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...

Semiyearly

Thanks a lot for sharing this info, Guido! Please let me know if you'll find a way to apply the format. – Inhibit

On a side note, if in your solution you don't have to be limited to just OpenXML SDK, you can use ClosedXML library where all actions seem to be a lot easier than in pure OpenXML (and it's built on top of OpenXML). In ClosedXML documentation in the cell values example there is this line: 'String booleanFormattedString = cellBoolean.GetFormattedString();' Apparently it does what we are looking for. Also digging into the code of this method might help understanding. – Inhibit

I have added some usefull info! think that your problem will be solved at this point! – Semiyearly

@GuidoZanon - Can you assist me on the following question: #15792232 – Boner

The above example did not show how to get the Cell format object to get the NumberFormatID. If you need to get the Cell format the following shows how; joymonscode.blogspot.com/2013/10/… – Amoy

Answer 2

If you want to take cell value with applied formatting, same as displayed in Excel, use .Text property of Cell object. Like this:

String formattedValue = cell.Text

Endo

There doesn't appear to be a Text property in the Cell class, only under CellValue (which isn't what's displayed in Excel) – Shulamite