Predefined Number formats < 164 question RRS feed

  • Question

  • Being that the numFmtId below 164 are predefined and not stored in the numFmts of the stylers.xml file of a spreadsheet, does that mean that I have to create an internal lookup table of the values and corresponding formula values so I know how to apply them to a particular cell value if it has one of those formats?

    As an example, if cell "A1" has a value of "987654321" and has a numeric format of numFmtId="3" (formula: #,##0) and I want to write the value of "987,654,321" to an ASCII flat file, I will have to look this up in my own "lookup" table to produce the proper string.Format statement (parsing for special characters, etc.).

    Or is there another means of doing this?


    Friday, October 31, 2014 7:56 PM

All replies

  • Hello,

    Yes you have to get the numbering format id from the cell, use string.Format or ToString method to format the cell value with the predefined formats. To get the numbering format id and it's format string, try this method:

    string format = excel.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Elements<NumberingFormat>()
                .Where(i => i.NumberFormatId.ToString() == cellFormat.NumberFormatId.ToString())
    some of the format strings won't work for string.Format or ToString method, you have to process it by yourself.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 4, 2014 6:07 AM
  • I believe that code will only work if the numbering format is actually stored in the worksheet.  I ran a test where the NumberFormatId was 14 and received this error:

    {"Sequence contains no elements"}

    I ended up doing the following to look it up if it's less than 164; I'm still not sure that it's the "right way" or not.

    		public static Dictionary<int, string> numberFormatDictionary = new Dictionary<int, string>()
    				{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](#,##0)"},
    				{39, "#,##0.00;(#,##0.00)"},
    				{40, "#,##0.00;[Red](#,##0.00)"},
    				{45, "mm:ss"},
    				{46, "[h]:mm:ss"},
    				{47, "mmss.0"},
    				{48, "##0.0E+0"},
    				{49, "@"}

    Tuesday, November 4, 2014 1:53 PM