Why does the cell style index returning wrong CellFormat value when parsing excel cells using OpenXml SDK?

Question

Consider a General format cell having a value of 39.95. When I fetch the CellFormat of the cell it returns wrong cell format-

CellFormat thisCellFormat = ((CellFormat)cellFmts.ChildElements[(int)thisCell.StyleIndex.Value]);
int fmtId = Convert.ToInt32(thisCellFormat.FormatId.Value);

The value of fmtId returned is 1, and in some cases 38, where it should be 2 or 39.

This issue is only for General format cells, I am getting correct id for Number format cells.

Below is the Standard ECMA-376 Office Open XML File Formats specified set of implied cell formats for reference-

ID Format Code
0 General
1 0
2 0.00
3 #,##0
4 #,##0.00
9 0%
10 0.00%
11 0.00E+00
12 # ?/?
13 # ??/??
14 mm-dd-yy
15 d-mmm-yy
16 d-mmm
17 mmm-yy
18 h:mm AM/PM
19 h:mm:ss AM/PM
20 h:mm
21 h:mm:ss
22 m/d/yy h:mm
37 #,##0 ;(#,##0)
38 #,##0 ;Red
39 #,##0.00;(#,##0.00)
40 #,##0.00;Red
45 mm:ss
46 [h]:mm:ss
47 mmss.0
48 ##0.0E+0
49 @

@pnuts using the system separators, . for decimal and , for thousands. — Thunderstruck
Answer for the question comes with how Spreadsheet styles are applied to cells — Kavindu Dodanduwa
@KCdod The spreadsheet is created using Excel, not OpenXML SDK. And I tried with very basic spreadsheet having just one cell of General format with a decimal value. Still gives the wrong FormatID — Thunderstruck

Kavindu Dodanduwa Kavindu Dodanduwa · Accepted Answer · 2015-01-14T11:22:17

The formats you are talking about are defined as styles and when Cell is created style index is set accordingly using style index. The number format is embedded inside these styles by referring NumberFormatId

Ex : Making CellFormats in Open XML , note difference

   CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // default                        
   CellFormat cellformat1 = new CellFormat() { NumberFormatId = 0 };
   CellFormat cellformat2 = new CellFormat() { NumberFormatId = 49 };

Now cellformat1can be used for general cells and cellformat2 can be used for forced string cells.

So when you try to extract format code in a general cell, it might return null since NumberFormatId is not set or 0 if number format is set to general type.

This explains why you get correct id for number formatted cells but not for general cells.

Why does the cell style index returning wrong CellFormat value when parsing excel cells using OpenXml SDK?

1 Answers