Create an Excel file with a style using OpenXmlWriter SAX

Question

I am trying to write an Excel Xlsx spreadsheet using a style using OpenXmlWriter (SAX).

I can create a file with rows and columns (fill them as rows). I am looking for simple code on how to make the first line (title) in bold.

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

I found some articles on how to add WorkbookStylesPart, but they all use the DOM. Since I need to write a large number of lines, the DOM will not work for me.

Can someone point me in the right direction? Simple code to add a title bar bold when using WriteStartElement and OpenXmlAttribute.

Thanks odansky

Answer 1

Adding StyleSheet is a one-time job. After that, you just need to refer to a specific style identifier when creating new cells.

Hot to add a stylesheet to the table [Bold]

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, follow these steps 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 a stylesheet after adding a worksheet to the table.

More about the SAX approach: you can define styles the first time you create a template file that you want to open to insert data cells. And when adding data cells refers to certain styles using an identifier.

Simple Style Worksheet ( 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();
}