C# OpenXML: Number Format is not applied

Question

I'm trying to format decimal and integer numbers like "1,000.00" in my .xlsx file.

The code for generating stylesheet:

private Stylesheet GenerateStylesheet()
{
    //styling and formatting
    var cellFormats = new CellFormats();
    uint iExcelIndex = 164;

    //number formats
    var numericFormats = new NumberingFormats();
    var nformat4Decimal = new NumberingFormat
    {
        NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
        FormatCode = StringValue.FromString("#,##0.00")
    };
    numericFormats.Append(nformat4Decimal);    

    //cell formats
    var cellFormat = new CellFormat
    {
        NumberFormatId = nformat4Decimal.NumberFormatId,
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    };
    cellFormats.Append(cellFormat);

    numericFormats.Count = UInt32Value.FromUInt32((uint)numericFormats.ChildElements.Count);
    cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);

    var stylesheet = new Stylesheet();
    stylesheet.Append(numericFormats);

    return stylesheet;
}

The code for adding stylesheet to the document:

WorkbookStylesPart stylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStylesheet();
stylesPart.Stylesheet.Save();

And this is how cell is generated:

var numberCell = new Cell
{
    DataType = CellValues.Number,
    CellReference = header + index,
    CellValue = new CellValue(text),
    StyleIndex = 0
};

Via OpenXML productivity tool I can see that number style is there and it's "applied" to the cell.




But when opening generated document value in the cell is not formatted as expected.

Also, I've discovered that #,##0.00 is one of the default Excel formats with ID = 4. But changing NumberFormatId = nformat4Decimal.NumberFormatId to NumberFormatId = 4 has no affect.

Answer 1

As it found out, you can't just put only number format. You have to specify Fonts, Fills, Borders. After doing that number format finnaly got applied. End up with this:

private static Stylesheet GenerateStylesheet2()
{
    Stylesheet ss = new Stylesheet();

    Fonts fts = new Fonts();
    DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
    FontName ftn = new FontName();
    ftn.Val = "Calibri";
    FontSize ftsz = new FontSize();
    ftsz.Val = 11;
    ft.FontName = ftn;
    ft.FontSize = ftsz;
    fts.Append(ft);
    fts.Count = (uint)fts.ChildElements.Count;

    Fills fills = new Fills();
    Fill fill;
    PatternFill patternFill;
    fill = new Fill();
    patternFill = new PatternFill();
    patternFill.PatternType = PatternValues.None;
    fill.PatternFill = patternFill;
    fills.Append(fill);
    fill = new Fill();
    patternFill = new PatternFill();
    patternFill.PatternType = PatternValues.Gray125;
    fill.PatternFill = patternFill;
    fills.Append(fill);
    fills.Count = (uint)fills.ChildElements.Count;

    Borders borders = new Borders();
    Border border = new Border();
    border.LeftBorder = new LeftBorder();
    border.RightBorder = new RightBorder();
    border.TopBorder = new TopBorder();
    border.BottomBorder = new BottomBorder();
    border.DiagonalBorder = new DiagonalBorder();
    borders.Append(border);
    borders.Count = (uint)borders.ChildElements.Count;

    CellStyleFormats csfs = new CellStyleFormats();
    CellFormat cf = new CellFormat();
    cf.NumberFormatId = 0;
    cf.FontId = 0;
    cf.FillId = 0;
    cf.BorderId = 0;
    csfs.Append(cf);
    csfs.Count = (uint)csfs.ChildElements.Count;

    uint iExcelIndex = 164;
    NumberingFormats nfs = new NumberingFormats();
    CellFormats cfs = new CellFormats();

    cf = new CellFormat();
    cf.NumberFormatId = 0;
    cf.FontId = 0;
    cf.FillId = 0;
    cf.BorderId = 0;
    cf.FormatId = 0;
    cfs.Append(cf);

    NumberingFormat nf;
    nf = new NumberingFormat();
    nf.NumberFormatId = iExcelIndex++;
    nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
    nfs.Append(nf);
    cf = new CellFormat();
    cf.NumberFormatId = nf.NumberFormatId;
    cf.FontId = 0;
    cf.FillId = 0;
    cf.BorderId = 0;
    cf.FormatId = 0;
    cf.ApplyNumberFormat = true;
    cfs.Append(cf);

    nf = new NumberingFormat();
    nf.NumberFormatId = iExcelIndex++;
    nf.FormatCode = "#,##0.0000";
    nfs.Append(nf);
    cf = new CellFormat();
    cf.NumberFormatId = nf.NumberFormatId;
    cf.FontId = 0;
    cf.FillId = 0;
    cf.BorderId = 0;
    cf.FormatId = 0;
    cf.ApplyNumberFormat = true;
    cfs.Append(cf);

    // #,##0.00 is also Excel style index 4
    nf = new NumberingFormat();
    nf.NumberFormatId = iExcelIndex++;
    nf.FormatCode = "#,##0.00";
    nfs.Append(nf);
    cf = new CellFormat();
    cf.NumberFormatId = nf.NumberFormatId;
    cf.FontId = 0;
    cf.FillId = 0;
    cf.BorderId = 0;
    cf.FormatId = 0;
    cf.ApplyNumberFormat = true;
    cfs.Append(cf);

    // @ is also Excel style index 49
    nf = new NumberingFormat();
    nf.NumberFormatId = iExcelIndex++;
    nf.FormatCode = "@";
    nfs.Append(nf);
    cf = new CellFormat();
    cf.NumberFormatId = nf.NumberFormatId;
    cf.FontId = 0;
    cf.FillId = 0;
    cf.BorderId = 0;
    cf.FormatId = 0;
    cf.ApplyNumberFormat = true;
    cfs.Append(cf);

    nfs.Count = (uint)nfs.ChildElements.Count;
    cfs.Count = (uint)cfs.ChildElements.Count;

    ss.Append(nfs);
    ss.Append(fts);
    ss.Append(fills);
    ss.Append(borders);
    ss.Append(csfs);
    ss.Append(cfs);

    CellStyles css = new CellStyles();
    CellStyle cs = new CellStyle();
    cs.Name = "Normal";
    cs.FormatId = 0;
    cs.BuiltinId = 0;
    css.Append(cs);
    css.Count = (uint)css.ChildElements.Count;
    ss.Append(css);

    DifferentialFormats dfs = new DifferentialFormats();
    dfs.Count = 0;
    ss.Append(dfs);

    TableStyles tss = new TableStyles();
    tss.Count = 0;
    tss.DefaultTableStyle = "TableStyleMedium9";
    tss.DefaultPivotStyle = "PivotStyleLight16";
    ss.Append(tss);

    return ss;
}

And for cell creation:

var numberCell = new Cell
{
    DataType = CellValues.Number,
    CellReference = header + index,
    CellValue = new CellValue(text),
    StyleIndex = 3
};

Style index 3 is reference to the cell style with number format "#,##0.00".