How To Get The Custom Format String For An Excel/OpenXml Cell

[1 minute read]

If there was a problem, Yo, I’ll solve it – Vanilla Ice, Ice Ice Baby

Getting the custom number format string for an Excel cell using OpenXml is not entirely intuitive. Or, at least, it wasn’t intuitive to me. Once you understand how this information is laid out, it starts making sense, but it’s definitely not as straight forward as using Excel interop or even VBA macros. The information isn’t available by interrogating a Cell object. Instead, cells have a StyleIndex which can be used to search through wholly separate collections of CellFormats and NumberingFormats.

Here’s a little example:

using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
	WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
	var cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
	var numberingFormats = workbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats;

	IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();

	foreach (Sheet sheet in sheets)
	{
		var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));

		foreach (var row in worksheetPart.Worksheet.Descendants<Row>())
		{
			foreach (var cell in row.Descendants<Cell>())
			{
				var styleIndex = (int)cell.StyleIndex.Value;
				var cellFormat = (CellFormat)cellFormats.ElementAt(styleIndex);

				if (cellFormat.NumberFormatId != null)
				{
					var numberFormatId = cellFormat.NumberFormatId.Value;
					var numberingFormat = numberingFormats.Cast<NumberingFormat>()
						.Single(f => f.NumberFormatId.Value == numberFormatId);

					// Here's yer string! Example: $#,##0.00_);[Red]($#,##0.00)
					string formatString = numberingFormat.FormatCode.Value;
				}
			}
		}
	}
}

Word to your mother.