How to make excel work sheet header row bold using OpenXML

Question

I am using openXML, Asp.net and c# to create an Excel workbook, I have a requirement that to make Header row of all sheets should be bold.

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

    }
    private static Stylesheet CreateStylesheet()
    {
        Stylesheet ss = new Stylesheet();
        Fonts fts = new Fonts();
        DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
        Bold fbld = new Bold();
        FontName ftn = new FontName();
        ftn.Val = "Calibri";
        DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
        ftsz.Val = 11;
        ft.FontName = ftn;
        ft.FontSize = ftsz;
        ft.Bold = fbld;
        fts.Append(ft);
        fts.Count = (uint)fts.ChildElements.Count;
        ss.Append(fts);
        return ss;
    }

It is making all the cells bold, I am missing the code that apply this to a particular row/cells

Thanks in Advance, AR

can you show the code, so that we can understand what exaclty we can do according to your requirement — Nad, Apr 28 '15 at 07:36
You can use StyleIndex on the Cell. Take a look at: https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/ — Daniel Stackenland, Aug 27 '18 at 13:59

score 4 · Accepted Answer · edited May 23 '17 at 12:26

I got the Answer from another post. Create Excel file with style tag using OpenXmlWriter SAX

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

        Font font0 = new Font();         // Default font

        Font font1 = new Font();         // Bold font
        Bold bold = new Bold();
        font1.Append(bold);

        Fonts fonts = new Fonts();      // <APENDING Fonts>
        fonts.Append(font0);
        fonts.Append(font1);

        // <Fills>
        Fill fill0 = new Fill();        // Default fill

        Fills fills = new Fills();      // <APENDING Fills>
        fills.Append(fill0);

        // <Borders>
        Border border0 = new Border();     // Defualt border

        Borders borders = new Borders();    // <APENDING Borders>
        borders.Append(border0);

        CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0

        CellFormat cellformat1 = new CellFormat(){FontId = 1};
        CellFormats cellformats = new CellFormats();
        cellformats.Append(cellformat0);
        cellformats.Append(cellformat1);


        ss.Append(fonts);
        ss.Append(fills);
        ss.Append(borders);
        ss.Append(cellformats);


        return ss;
}

This answer is not as relevant as you think. It only updates a style sheet and nothing else. So - problem. Also it is a straight copy from that answer you referenced. Where the question was about the style sheet. Please don't post code without at least understanding. — Terrance00, Jul 27 '17 at 11:28
Though I agree with the previous comment, I still upvoted this answer because it led me to the more detailed response that I may not have found otherwise. Also, the author did provide a link right from the beginning of this answer. It's true that this answer itself is incomplete. — Nimblejoe, Apr 10 '20 at 21:27

How to make excel work sheet header row bold using OpenXML

1 Answers1