Create Excel file with style tag using OpenXmlWriter SAX

Question

I am trying to write an Excel Xlsx spreadsheet with styling using OpenXmlWriter (SAX).

I am able to create the file with rows and columns (populate them as strings). I am looking for a simple code on how to make the first row (header) with bold font.

I do not have a template file to start with as the file will be dynamic.

I found a few articles on how to add WorkbookStylesPart, but they are all using the DOM. As i need to write large number of rows, the DOM will not work for me.

Could anyone point me at the right direction? Simple code to add a header row as bold when using WriteStartElement and OpenXmlAttribute.

Thanks, odansky

Articulate

Answer 1

Adding StyleSheet is one time work. After that you just need to simply refer the defined style ID when creating new cells.

Hot to add a stylesheet to spreadsheet [With Bold text style]

private WorkbookStylesPart AddStyleSheet(SpreadsheetDocument spreadsheet)
 {
       WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();

       Stylesheet workbookstylesheet = 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);

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

       CellFormat cellformat1 = new CellFormat() { FontId = 1 };  // Style with Bold text ; Style ID = 1


      // <APENDING CellFormats>
      CellFormats cellformats = new CellFormats();
      cellformats.Append(cellformat0);
      cellformats.Append(cellformat1);


       // Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER>
      workbookstylesheet.Append(fonts);
      workbookstylesheet.Append(fills);
      workbookstylesheet.Append(borders);
      workbookstylesheet.Append(cellformats);

      // Finalize
      stylesheet.Stylesheet = workbookstylesheet;
      stylesheet.Stylesheet.Save();

      return stylesheet;
}

Now when you create a cell do following to refer to the Bold text

Cell c1 = new Cell(){StyleIndex = Convert.ToUInt32(1)}; // Assign our defined style with Bold text ; Style ID 1

Additional Note : You need to add stylesheet after adding workbookpart of the spreadsheet.

More regarding SAX approach : You can define styles when you first create the template file which you gonna open to insert data cells. And when adding data cells refer to the defined styles using ID.

A simple working spreadsheet with style (MSDN)

public static void CreateSpreadsheetWorkbook(string filepath)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

   AddStyleSheet(spreadsheetDocument) // <== Adding stylesheet using above function

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
    sheets.Append(sheet);

    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
}

Crinkle

Thanks. Any chance you could write a small but full method which will create the spreadsheet with a style, then uses the openXmlWriter in one single method? it will help understand what is fully needed. Thanks – Articulate

@Articulate I added a code snippet for a excel sheet with style sheet defined.. mark the answer as correct if this solves your problem :) – Crinkle

Answer 2

You set the style index the same way you set the datatype for the cell:

oxa.Add(new OpenXmlAttribute("s", null, "1"));

Cadwell

This also works in conjunction with the accepted answer – Proto