Generate XLSX file using C# and OpenXml package

The Following features are implemented:

• Cell formatting
• Cell colouring
• Borders
• Columns custom width
• Formulas
• IgnoredErrors

The resulting xlsx file looks like this:

First of all, we hve to install the following NuGet package: DocumentFormat.OpenXml.

The code:

// Data example used to generate xlsx file
string csv = @"Program Name,Start Date,Start Time,End Date,End Time,Series Number,Run
WEEKEND TODAY,01/12/19,07:00,01/12/19,10:00,19,1
SPORTS SUNDAY,01/12/19,10:00,01/12/19,11:00,19,1
CROSS COURT,01/12/19,11:00,01/12/19,11:30,19,1
THE HOLD DOWN,01/12/19,11:30,01/12/19,12:00,5,1
EXPLORE TV-VIKING,01/12/19,16:30,01/12/19,17:00,1,1
NINE NEWS: FIRST AT FIVE,01/12/19,17:00,01/12/19,17:25,19,1
NINE NEWS SUNDAY,01/12/19,17:59,01/12/19,19:00,19,1
A CURRENT AFFAIR,10/12/19,19:00,10/12/19,19:38,19,1
TODAY,11/12/19,06:00,11/12/19,09:00,19,1
TODAY EXTRA,11/12/19,09:00,11/12/19,11:30,19,1
NINE'S MORNING NEWS,11/12/19,11:30,11/12/19,12:00,19,1
DESTINATION HAPPINESS,11/12/19,12:59,11/12/19,13:31,3,2
GIVING LIFE,11/12/19,13:31,11/12/19,14:02,1,3
NINE'S AFTERNOON NEWS,11/12/19,16:00,11/12/19,17:00,19,1
MILLIONAIRE HOT SEAT,11/12/19,17:00,11/12/19,17:59,19,2
,,,,,,";
            
GenerateXlsx(csv, "D:\\text.xlsx");

public GenerateXlsx(string csv, string path)
{
    string[] lines = csv.Replace("\r", "").Split('\n');
    //Creating XLSX document and filling it with the data
    using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
    {
        // Add a WorkbookPart to the document.
        WorkbookPart workbookPart = document.AddWorkbookPart();
        //Initialize a SheetData
        SheetData sheetData = InitializeSheetData(workbookPart, lines.Length);
        //Add the headers row
        AddHeadersRow(lines[0].Split(','), sheetData);
        //Add data rows
        for (int i = 1; i < lines.Length; i++)
        {
            if (String.IsNullOrEmpty(lines[i])) continue;
            String[] columnData = lines[i].Split(',');
            //Add row to the sheet
            AddDataRow(columnData, i + 1, sheetData, i == lines.Length - 2);
        }
        //Add the totals row
        AddTotalsRow(lines, sheetData);
        workbookPart.Workbook.Save();
    }
}

/// 

/// Processes whole the routines to create the workbook, sheet parts, sheets, data
/// 
/// 
/// 
/// 
private SheetData InitializeSheetData(WorkbookPart workbookPart, int rowsNumber)
{
    workbookPart.Workbook = new Workbook();
    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookPart.AddNewPart();
    worksheetPart.Worksheet = new Worksheet(new SheetData());
    
    //Create a new sheet
    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
    Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report" };
    sheets.Append(sheet);
    // Get the sheetData cell table.
    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();
    //Add columns scheme
    worksheetPart.Worksheet.InsertBefore(GenerateColumnsScheme(), sheetData);
    // Add styles to the sheet
    WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart();
    workbookStylesPart.Stylesheet = CreateStylesheet();
    workbookStylesPart.Stylesheet.Save();
    //Add IgnoredErrors Element
    var ignoredErrorsElement = new IgnoredErrors();
    ignoredErrorsElement.AppendChild(new IgnoredError()
    {
        SequenceOfReferences = new ListValue()
        {
            InnerText = $"B2:B{rowsNumber} D2:D{rowsNumber}"
        },
        TwoDigitTextYear = true
    });
    worksheetPart.Worksheet.AppendChild(ignoredErrorsElement);
    //Add Totals
    
    return sheetData;
}

/// 

/// Generates columns with custom widths
/// 
/// 
private Columns GenerateColumnsScheme()
{
    Columns columns = new Columns();
    columns.AppendChild(new Column() { Min = 1, Max = 1, Width = 34, CustomWidth = true });
    columns.AppendChild(new Column() { Min = 2, Max = 2, Width = 10, CustomWidth = true });
    columns.AppendChild(new Column() { Min = 3, Max = 3, Width = 10, CustomWidth = true });
    columns.AppendChild(new Column() { Min = 4, Max = 4, Width = 10, CustomWidth = true });
    columns.AppendChild(new Column() { Min = 5, Max = 5, Width = 10, CustomWidth = true });
    columns.AppendChild(new Column() { Min = 6, Max = 6, Width = 17, CustomWidth = true });
    columns.AppendChild(new Column() { Min = 7, Max = 7, Width = 8, CustomWidth = true });
    
    return columns;
}

/// 

/// Add headers row to the sheet
/// 
/// 
/// 
private void AddHeadersRow(string[] headers, SheetData sheetData)
{
    Row row = new Row() { RowIndex = 1 };
    foreach (string header in headers)
    {
        row.AppendChild(new Cell()
        {
            CellValue = new CellValue(header),
            DataType = new EnumValue(CellValues.String),
            StyleIndex = 1
        });
    }
    sheetData.AppendChild(row);
}

private void AddDataRow(String[] rowData, int rowNumber, SheetData sheetData, bool isLastRow)
{
    Row row = new Row() { RowIndex = (UInt32)rowNumber };
    for (int columnIndex = 0; columnIndex < rowData.Length; columnIndex++)
    {
        string cellData = rowData[columnIndex];
        CellValues cellType = CellValues.String;
        TimeSpan timeSpanCellData = TimeSpan.Zero;
        if (columnIndex == 2 || columnIndex == 4)
        {
            TimeSpan.TryParse(cellData, out timeSpanCellData);
        }
        switch (columnIndex)
        {
            case 5: //Series Number
            case 6: //Run
            cellType = CellValues.Number;
                break;
        }
        Cell cell = new Cell()
        {
            CellValue = new CellValue(cellData),
            DataType = new EnumValue(cellType)
        };
        //Add bottom line under the last row
        if (isLastRow) cell.StyleIndex = 2;
        row.AppendChild(cell);
    }
    sheetData.AppendChild(row);
}

/// 

/// Add totals row to the sheet
/// 
/// 
/// 
private void AddTotalsRow(string[] lines, SheetData sheetData)
{
    Row row = new Row() {RowIndex = (UInt32Value) (lines.Length + 1U)};
    for (int columnIndex = 0; columnIndex < 7; columnIndex++)
    {
        Cell cell = new Cell()
        {
            StyleIndex = 3,
            DataType = new EnumValue(CellValues.String)
        };
        switch (columnIndex)
        {
            case 0:
                cell.CellValue = new CellValue("Totals");
                break;
            case 6:
                cell.DataType = new EnumValue(CellValues.Number);
                cell.CellFormula = new CellFormula($"SUM(G2:G{lines.Length - 1})");
                break;
        }
        row.AppendChild(cell);
    }
    
    sheetData.AppendChild(row);
}

/// 

/// Creates the stylesheet
/// 
/// 
private static Stylesheet CreateStylesheet()
{
    //Fonts
    Font font0 = new Font();
    Font font1 = new Font(new FontSize() {Val = 10}, new Bold(), new Color() {Rgb = "FF000000"});
    Font font2 = new Font(new FontSize() { Val = 12 }, new Bold());
    Fonts fonts = new Fonts(font0, font1, font2);
    //Fills
    Fill fill0 = new Fill();
    Fill fill1 = new Fill();
    Fill fill2 = new Fill(new PatternFill()
    {
        PatternType = PatternValues.Solid,
        ForegroundColor = new ForegroundColor {Rgb = "FFA9CEE8"},
        BackgroundColor = new BackgroundColor() {Indexed = 64}
    });
    Fill fill3 = new Fill(new PatternFill()
    {
        PatternType = PatternValues.Solid,
        ForegroundColor = new ForegroundColor {Rgb = "FFC6EFCE"},
        BackgroundColor = new BackgroundColor() {Indexed = 64}
    });
    Fills fills = new Fills(fill0, fill1, fill2, fill3);
    //Borders
    Border border0 = new Border();
    Border border1 = new Border(new BottomBorder(new Color() {Auto = true}) {Style = BorderStyleValues.Double});
    Borders borders = new Borders(border0, border1);
    
    //CellFormats
    CellFormat cellFormat0 = new CellFormat {FontId = 0, FillId = 0};
    CellFormat cellFormat1 = new CellFormat {FontId = 1, FillId = 2, Alignment = new Alignment() {Horizontal = HorizontalAlignmentValues.Center}};
    CellFormat cellFormat2 = new CellFormat { FontId = 0, FillId = 0, BorderId = 1};
    CellFormat cellFormat3 = new CellFormat { FontId = 2, FillId = 3 };
    CellFormats cellFormats = new CellFormats(cellFormat0, cellFormat1, cellFormat2, cellFormat3);
    //Compose the Style Sheet
    Stylesheet styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);
    return styleSheet;
}