CODEWITHSUNDEEP
c#excelopenxmlopenxml-sdk
Thunderstruck
Thunderstruck

Reputation: 345

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

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 @

Upvotes: 1

Views: 9773

Answers (1)

Kavindu Dodanduwa
Kavindu Dodanduwa

Reputation: 13059

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.

Upvotes: 1

Related Questions