Open in app

Sign in

Write

Sign in

Create Excel using OpenXML in .NET 6

Jason Ge
17 min readFeb 2, 2023

--

Often times, we need to export data into external file. We can use .csv file, but it does not have the capability to format the data; highlight the data, etc. In .NET framework, we can use Office Interop. However, in .NET 6, Office Interop is not supported anymore. In order to export data into Excel format, we can use OpenXML to achieve it.

In Excel 2007 and above, the .xlsx file is an XML based format, called Office Open Xml (OpenXML). An .xlsx file is a zip package containing XML files for each major part of an Excel file (sheets, styles, charts, pivot tables, etc.). You can change the extension of the file from .xlsx to .zip and then you would see the internal file/folder structure in the .zip file.

Internal structure of .xlsx file

Install Nuget package

In order to use OpenXML in .NET 6 application, you need to add following nuget package:

Install-Package DocumentFormat.OpenXml -Version 2.19.0

Once the package is installed, you can add following assembly directives to use OpenXML.

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

OpenXML Excel Objects Structure

I found the objects structures/relations defined in OpenXML SDK are quite confusing and hard to understand. Following is the class diagram for better understanding the object model of OpenXML

OpenXML SpreadSheet class diagram

SpreadsheetDocument

The SpreadsheetDocument class represents an Excel document package. You can call the static Create() method to create a SpreadsheetDocument object. This is the first object you have to create.

/// <summary>
/// Creates a new instance of the SpreadsheetDocument class from the specified file.
/// </summary>
/// <param name="path">The path and file name of the target SpreadsheetDocument.</param>
/// <param name="type">The type of the SpreadsheetDocument.</param>
/// <returns>A new instance of SpreadsheetDocument.</returns>
/// <exception cref="ArgumentNullException">Thrown when "path" is null reference.</exception>
public static SpreadsheetDocument Create(string path, SpreadsheetDocumentType type)
{
    return Create(path, type, true);
}

SpreadsheetDocument excel = SpreadsheetDocument.Create("demo.xlsx", SpreadsheetDocumentType.Workbook);

The SpreadsheetDocumentType enum can be following values.

/// <summary>
/// Defines SpreadsheetDocumentType - type of SpreadsheetDocument.
/// </summary>
public enum SpreadsheetDocumentType
{
    Workbook,              /// Excel Workbook (*.xlsx).
    Template,              /// Excel Template (*.xltx).
    MacroEnabledWorkbook,  /// Excel Macro-Enabled Workbook (*.xlsm).
    MacroEnabledTemplate,  /// Excel Macro-Enabled Template (*.xltm).
    AddIn,                 /// Excel Add-In (*.xlam).
}

WorkbookPart

This is the top level container for Excel document. Only one instance is allowed.

WorkbookPart workbookpart = excel.AddWorkbookPart();

Workbook

Workbook is the child element inside WorkbookPart. Workbook and WorkbookPart has one to one relationship.

workbookpart.Workbook = new Workbook();

WorksheetPart

You can create WorksheetPart by calling WorkbookPart object’s AddNewPart<WorksheetPart>() method. You can create as many WorksheetParts as you want.

WorksheetPart worksheetPart1 = workbookpart.AddNewPart<WorksheetPart>();
WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();

Worksheet

Each WorksheetPart has only one Worksheet.

worksheetPart.Worksheet = new Worksheet();

Columns

Worksheet contains Columns child element which is the collection of Column objects. Column object defines the meta data of the column of the sheet, for example, column width.

SheetData

Worksheet contains SheetData child element which is the collection of Row objects. Each row has a collection of Cell objects. SheetData is the actual data in the Excel document.

string[] Sheet2Headers = new string[] { "Timestamp", "Workstation", "Unit", "Amount" };
string[][] Sheet2Data = new string[][] {
    new string[] { "2023-01-26T15:10:32.1339754-05:00", "Workstation 1", "MB", "300.40" },
    new string[] { "2023-01-26T15:13:59.3092106-05:00", "Workstation 2", "Minutes", "17808.40" },
    new string[] { "2023-01-26T15:52:43.7308915-05:00", "Workstation 3", "Percentage", "7.47" },
    new string[] { "2023-01-26T15:31:43.8670790-05:00", "Workstation 4", "Minutes", "1.40" },
    new string[] { "2023-01-26T15:32:15.0373524-05:00", "Workstation 5", "Percentage", "0.19" }
};

SheetData data = new SheetData();
int rowId = 0;
Row row = new Row();
for (int i = 0; i < Sheet2Headers.Length; i++)
{
    row.InsertAt<Cell>(new Cell()
    {
        DataType = CellValues.InlineString,
        InlineString = new InlineString() { Text = new Text(Sheet2Headers[i]) },
    }, i);
}
data.InsertAt(row, rowId++);

for (int i = 0; i < Sheet2Data.Length; i++)
{
    row = new Row();
    for (int j = 0; j < Sheet2Data[i].Length; j++)
    {
        row.InsertAt<Cell>(new Cell()
        {
            DataType = CellValues.InlineString,
            InlineString = new InlineString() { Text = new Text(Sheet2Data[i][j]) },
        }, j);
    }
    data.InsertAt(row, rowId++);
}

 worksheetPart2.Worksheet.Append(data);

Cell data type can be following values:

public enum CellValues
{
    Boolean,
    Number,
    Error,
    SharedString,
    String,
    InlineString,
    [OfficeAvailability(FileFormatVersions.Office2007)]
    Date
}

The Date type is only supported in Office2007 and above. The difference between String and InlineString is the CellValues.Stringshould be used when inserting a formula in the cell; while CellValues.InlineString is for reguarl string literal and should be used if you don't want to store the string in the SharedStringTable.

Sheets and Sheet

One Workbook have one Sheets object which is the collection of many Sheet objects. Each Sheet object is associated with each WorksheetPart created before by theId property.

// Add Sheets to the Workbook.
Sheets sheets = excel.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
sheets.Append(new Sheet()
{
    Id = excel.WorkbookPart.GetIdOfPart(worksheetPart1),
    SheetId = 1,
    Name = "Sheet 1"
});
sheets.Append(new Sheet()
{
    Id = excel.WorkbookPart.GetIdOfPart(worksheetPart2),
    SheetId = 2,
    Name = "Sheet 2"
});

WorkbookStylesPart

WorkbookStylesPart is the container to holder style information for the Excel document. You can create one using WorkbookPart object’s AddNewPart<WorkbookStylesPart>() method.

var stylesPart = excel.WorkbookPart.AddNewPart<WorkbookStylesPart>();

StyleSheet

WorkbookStylesPart has one StyleSheet property, which holds all the style related collections.

You can add following elements to the style sheet.

  • Fonts
  • Borders
  • Fills
  • NumberingFormats
  • CellStyleFormats
  • CellFormats
  • CellStyles

The order to add these elements is important (Strange, I agree). Insert them in the wrong order would cause the error when you open the Excel file.

Format Excel Cells

You define the fonts, fills, borders, numbering formats, cell formats, etc. in StyleSheet object. When you insert Cells into Row object, you can reference these defined styles by StyleIndex property.

Following is the code snippet to create the StyleSheet object:

ForegroundColor TranslateForeground(System.Drawing.Color fillColor)
{
    return new ForegroundColor()
    {
        Rgb = new HexBinaryValue()
        {
            Value =
                      System.Drawing.ColorTranslator.ToHtml(
                      System.Drawing.Color.FromArgb(
                          fillColor.A,
                          fillColor.R,
                          fillColor.G,
                          fillColor.B)).Replace("#", "")
        }
    };
}

Stylesheet CreateStyleSheet()
{
    Stylesheet stylesheet = new Stylesheet();
    #region Number format
    uint DATETIME_FORMAT = 164;
    uint DIGITS4_FORMAT = 165;
    var numberingFormats = new NumberingFormats();
    numberingFormats.Append(new NumberingFormat // Datetime format
    {
        NumberFormatId = UInt32Value.FromUInt32(DATETIME_FORMAT),
        FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
    });
    numberingFormats.Append(new NumberingFormat // four digits format
    {
        NumberFormatId = UInt32Value.FromUInt32(DIGITS4_FORMAT),
        FormatCode = StringValue.FromString("0000")
    });
    numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
    #endregion

    #region Fonts
    var fonts = new Fonts();
    fonts.Append(new DocumentFormat.OpenXml.Spreadsheet.Font()  // Font index 0 - default
    {
        FontName = new FontName { Val = StringValue.FromString("Calibri") },
        FontSize = new FontSize { Val = DoubleValue.FromDouble(11) }
    });
    fonts.Append(new DocumentFormat.OpenXml.Spreadsheet.Font()  // Font index 1
    {
        FontName = new FontName { Val = StringValue.FromString("Arial") },
        FontSize = new FontSize { Val = DoubleValue.FromDouble(11) },
        Bold = new Bold()
    });
    fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
    #endregion

    #region Fills
    var fills = new Fills();
    fills.Append(new Fill() // Fill index 0
    {
        PatternFill = new PatternFill { PatternType = PatternValues.None }
    });
    fills.Append(new Fill() // Fill index 1
    {
        PatternFill = new PatternFill { PatternType = PatternValues.Gray125 }
    });
    fills.Append(new Fill() // Fill index 2
    {
        PatternFill = new PatternFill { 
            PatternType = PatternValues.Solid, 
            ForegroundColor = TranslateForeground(System.Drawing.Color.LightBlue),
            BackgroundColor = new BackgroundColor { Rgb = TranslateForeground(System.Drawing.Color.LightBlue).Rgb }
        }
    });
    fills.Append(new Fill() // Fill index 3
    {
        PatternFill = new PatternFill
        {
            PatternType = PatternValues.Solid,
            ForegroundColor = TranslateForeground(System.Drawing.Color.LightSkyBlue),
            BackgroundColor = new BackgroundColor { Rgb = TranslateForeground(System.Drawing.Color.LightBlue).Rgb }
        }
    });
    fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
    #endregion

    #region Borders
    var borders = new Borders();
    borders.Append(new Border   // Border index 0: no border
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder(),
        BottomBorder = new BottomBorder(),
        DiagonalBorder = new DiagonalBorder()
    });
    borders.Append(new Border    //Boarder Index 1: All
    {
        LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
        RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
        TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
        BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
        DiagonalBorder = new DiagonalBorder()
    });
    borders.Append(new Border   // Boarder Index 2: Top and Bottom
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
        BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
        DiagonalBorder = new DiagonalBorder()
    });
    borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
    #endregion

    #region Cell Style Format
    var cellStyleFormats = new CellStyleFormats();
    cellStyleFormats.Append(new CellFormat  // Cell style format index 0: no format
    {
        NumberFormatId = 0,
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0
    });
    cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
    #endregion

    #region Cell format
    var cellFormats = new CellFormats();
    cellFormats.Append(new CellFormat());    // Cell format index 0
    cellFormats.Append(new CellFormat   // CellFormat index 1
    {
        NumberFormatId = 14,        // 14 = 'mm-dd-yy'. Standard Date format;
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 2: Standard Number format with 2 decimal placing
    {
        NumberFormatId = 4,        // 4 = '#,##0.00';
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell formt index 3
    {
        NumberFormatId = DATETIME_FORMAT,        // 164 = 'dd/mm/yyyy hh:mm:ss'. Standard Datetime format;
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 4
    {
        NumberFormatId = 3, // 3   #,##0
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat    // Cell format index 5
    {
        NumberFormatId = 4, // 4   #,##0.00
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 6
    {
        NumberFormatId = 10,    // 10  0.00 %,
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 7
    {
        NumberFormatId = DIGITS4_FORMAT,    // Format cellas 4 digits. If less than 4 digits, prepend 0 in front
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 8: Cell header
    {
        NumberFormatId = 49,
        FontId = 1,
        FillId = 3,
        BorderId = 2,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true),
        Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center }
    });
    cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
    #endregion

    stylesheet.Append(numberingFormats);
    stylesheet.Append(fonts);
    stylesheet.Append(fills);
    stylesheet.Append(borders);
    stylesheet.Append(cellStyleFormats);
    stylesheet.Append(cellFormats);

    #region Cell styles
    var css = new CellStyles();
    css.Append(new CellStyle
    {
        Name = StringValue.FromString("Normal"),
        FormatId = 0,
        BuiltinId = 0
    });
    css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
    stylesheet.Append(css);
    #endregion

    var dfs = new DifferentialFormats { Count = 0 };
    stylesheet.Append(dfs);
    var tss = new TableStyles
    {
        Count = 0,
        DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
        DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
    };
    stylesheet.Append(tss);

    return stylesheet;
}

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

Note:

  1. The order to create and append these child element inside StyleSheet object is important. Doing it wrong would cause the error when open the Excel file generated.
  2. The StyleIndex property of the Cell object refers to the index of CellFormat object inside cellFormats array. For example, the StyleIndex 8 means to apply the CellFormat
  3. All the Count property of Fonts, Fills, Borders and CellFormats etc. are type of UInt32Value. It would be null and would not be updated when you append elements. You have to manually assign the value to this property (Stragne again). However, I did found out even if I commented out these Count assignment statement, the Excel generated is working fine.

NumberingFormat

Here is a list of system-defined Excel NumberFormatIds:

ID  FORMAT CODE
0   General
1   0
2   0.00
3   #,##0
4   #,##0.00
9   0%
10  0.00%
11  0.00E+00
12  # ?/?
13  # ??/??
14  d/m/yyyy
15  d-mmm-yy
16  d-mmm
17  mmm-yy
18  h:mm tt
19  h:mm:ss tt
20  H:mm
21  H:mm:ss
22  m/d/yyyy H:mm
37  #,##0 ;(#,##0)
38  #,##0 ;[Red](#,##0)
39  #,##0.00;(#,##0.00)
40  #,##0.00;[Red](#,##0.00)
45  mm:ss
46  [h]:mm:ss
47  mmss.0
48  ##0.0E+0
49  @

System-defined numbering format id is less than 164. You can define your customized numbering format. The format id of your customized has to be 164 and above.

The most common characters in the number format code are ?, # and 0.

  • 0 will display either a digit from the source, or a zero. This is primarily used to prefix 0 to the numbers. For example, if the number is 124 and the format code is “0000”, the final display would be 0124.
  • # will display a digit from the source, or a null space.
  • ? will display a digit from the source, or an empty space. This is primarily used to align numbers in adjacent rows.
  • , will separate every multiple of 1000 with a locale specific separator.
var numberingFormats = new NumberingFormats();
numberingFormats.Append(new NumberingFormat // Datetime format
{
    NumberFormatId = UInt32Value.FromUInt32(164),
    FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
});
numberingFormats.Append(new NumberingFormat // four digits format
{
    NumberFormatId = UInt32Value.FromUInt32(165),
    FormatCode = StringValue.FromString("0000")
});
numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);

CellFormat

This is kind of core part of StyleSheet. The StyleIndex property of the Cell object refers to the index of CellFormat object inside cellFormats array. The first CellFormat inside cellFormats array should always be an empty one.

  • The FontId property refers to the index of Font object inside fonts array.
  • The FillId property refers to the index of Fill object inside fills array.
  • The BorderId property refers to the index of Border object inside borders array.
  • The NumberFormatId property refers to the NumberFormatId of NumberFormat object. It can be either system defined NumberFormatId (less than 164) or customized NumberFormatId (164 and above).

Date Format

There are 2 ways to format date in OpenXml:

  1. Set the CellDataType to CellValues.Number and CellValueto a number from using ToOADate() method). Setting date format this way would be compatible with Excel 2007.
  2. Set the CellDataType to CellValues.Dateand CellValueto an ISO 8601 formatted date. CellValues.Date only supported in Office 2007 and above.

Calculate and Set Column Width

The Excel generated has default column width for each column. It would not be able to auto resize to fit its content. You have to calculate the width yourself to fit the content.

The formula to calculate the width is:

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256

Accurately calculate the content width is very hard and unnecessary. For example, the number formatting would add extra characters (such as comma, dollar sign, percentage sign, etc.).

Following code snippet can calculate the column width and it is good enough to use:

Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
{
    //iterate over all cells getting a max char value for each column
    Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
    var rows = sheetData.Elements<Row>();
    UInt32[] numberStyles = new UInt32[] { 5, 6, 7, 8 }; //styles that will add extra chars
    UInt32[] boldStyles = new UInt32[] { 1, 2, 3, 4, 6, 7, 8 }; //styles that will bold
    foreach (var r in rows)
    {
        var cells = r.Elements<Cell>().ToArray();

        //using cell index as my column
        for (int i = 0; i < cells.Length; i++)
        {
            var cell = cells[i];
            var cellValue = cell.CellValue == null ? cell.InnerText : cell.CellValue.InnerText;
            var cellTextLength = cellValue.Length;

            if (cell.StyleIndex != null && numberStyles.Contains(cell.StyleIndex))
            {
                int thousandCount = (int)Math.Truncate((double)cellTextLength / 4);

                //add 3 for '.00' 
                cellTextLength += (3 + thousandCount);
            }

            if (cell.StyleIndex != null && boldStyles.Contains(cell.StyleIndex))
            {
                //add an extra char for bold - not 100% acurate but good enough for what i need.
                cellTextLength += 1;
            }

            if (maxColWidth.ContainsKey(i))
            {
                var current = maxColWidth[i];
                if (cellTextLength > current)
                {
                    maxColWidth[i] = cellTextLength;
                }
            }
            else
            {
                maxColWidth.Add(i, cellTextLength);
            }
        }
    }

    return maxColWidth;
}

Columns AutoSizeCells(SheetData sheetData)
{
    var maxColWidth = GetMaxCharacterWidth(sheetData);

    Columns columns = new Columns();
    //this is the width of my font - yours may be different
    double maxWidth = 7;
    foreach (var item in maxColWidth)
    {
        //width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
        double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;
        Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
        columns.Append(col);
    }

    return columns;
}

worksheetPart1.Worksheet = new Worksheet();
SheetData data1 = CreateShee1Data();
Columns columns1 = AutoSizeCells(data1);
worksheetPart1.Worksheet.Append(columns1);
worksheetPart1.Worksheet.Append(data1);

The full source code is as following. Happy coding!

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.Runtime.CompilerServices;
using System.Runtime.Intrinsics.X86;
using System.Linq;

string[] Sheet1Headers = new string[] { "Transaction Date", "Transaction Code", "Transaction Description", "Transaction Amount" };
string[][] Sheet1Data = new string[][] {
    new string[] { "2022-08-15", "SF", "SERVICE FEE", "9808.40" },
    new string[] { "2022-09-15", "DS", "DEPOSIT", "17808.40" },
    new string[] { "2022-10-15", "DS", "SERVICE FEE", "9808.40" },
    new string[] { "2022-11-15", "DS", "SERVICE FEE", "1508.40" },
    new string[] { "2022-12-15", "DS", "SERVICE FEE", "13208.40" }
};

string[] Sheet2Headers = new string[] { "Timestamp", "Transit Number", "Unit", "Amount" };
string[][] Sheet2Data = new string[][] {
    new string[] { "2023-01-26T15:10:32.133-05:00", "124", "MB", "300.40" },
    new string[] { "2023-01-26T15:13:59.309-05:00", "278", "Minutes", "17808.40" },
    new string[] { "2023-01-26T15:52:43.730-05:00", "3693", "Percentage", "7.47" },
    new string[] { "2023-01-26T15:31:43.867-05:00", "4893", "Minutes", "1.40" },
    new string[] { "2023-01-26T15:32:15.037-05:00", "536", "Percentage", "0.19" }
};

void InsertTextCell(Row row, string content, int cellIndex)
{
    row.InsertAt<Cell>(new Cell() { DataType = CellValues.InlineString, InlineString = new InlineString() { Text = new Text(content) } }, cellIndex);
}

void InsertHeaderCell(Row row, string content, int cellIndex)
{
    row.InsertAt<Cell>(new Cell() { 
        DataType = CellValues.InlineString, 
        InlineString = new InlineString() { Text = new Text(content) },
        StyleIndex = 8
    }, cellIndex);
}

void InsertTransitCell(Row row, string content, int cellIndex)
{
    row.InsertAt<Cell>(new Cell() { DataType = CellValues.Number, CellValue = new CellValue(content), StyleIndex = 7 }, cellIndex);
}

void InsertNumberCell(Row row, string content, int cellIndex)
{
    row.InsertAt<Cell>(new Cell() { DataType = CellValues.Number, CellValue = new CellValue(content), StyleIndex = 5 }, cellIndex);
}

void InsertDateCell(Row row, string content, int cellIndex)
{
    row.InsertAt<Cell>(new Cell() { DataType = CellValues.Date, CellValue = new CellValue(content), StyleIndex = 1 }, cellIndex);
}

void InsertDateTimeCell(Row row, string content, int cellIndex)
{
    row.InsertAt<Cell>(new Cell() { DataType = CellValues.Number, CellValue = new CellValue(DateTime.Parse(content).ToOADate().ToString()), StyleIndex = 3 }, cellIndex);
}

SheetData CreateShee1Data()
{
    SheetData data = new SheetData();
    int rowId = 0;
    Row row = new Row();
    for(int i = 0; i < Sheet1Headers.Length; i++)
    {
        InsertHeaderCell(row, Sheet1Headers[i], i);
    }
    data.InsertAt(row, rowId++);

    for(int i=0; i < Sheet1Data.Length; i++)
    {
        row = new Row();
        InsertDateCell(row, Sheet1Data[i][0], 0);
        InsertTextCell(row, Sheet1Data[i][1], 1);
        InsertTextCell(row, Sheet1Data[i][2], 2);
        InsertNumberCell(row, Sheet1Data[i][3], 3);
        data.InsertAt(row, rowId++);
    }

    return data;
}

SheetData CreateShee2Data()
{
    SheetData data = new SheetData();
    int rowId = 0;
    Row row = new Row();
    for (int i = 0; i < Sheet2Headers.Length; i++)
    {
        InsertHeaderCell(row, Sheet2Headers[i], i);
    }
    data.InsertAt(row, rowId++);

    for (int i = 0; i < Sheet2Data.Length; i++)
    {
        row = new Row();
        InsertDateTimeCell(row, Sheet2Data[i][0], 0);
        InsertTransitCell(row, Sheet2Data[i][1], 1);
        InsertTextCell(row, Sheet2Data[i][2], 2);
        InsertNumberCell(row, Sheet2Data[i][3], 3);
        data.InsertAt(row, rowId++);
    }

    return data;
}

Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
{
    //iterate over all cells getting a max char value for each column
    Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
    var rows = sheetData.Elements<Row>();
    UInt32[] numberStyles = new UInt32[] { 5, 6, 7, 8 }; //styles that will add extra chars
    UInt32[] boldStyles = new UInt32[] { 1, 2, 3, 4, 6, 7, 8 }; //styles that will bold
    foreach (var r in rows)
    {
        var cells = r.Elements<Cell>().ToArray();

        //using cell index as my column
        for (int i = 0; i < cells.Length; i++)
        {
            var cell = cells[i];
            var cellValue = cell.CellValue == null ? cell.InnerText : cell.CellValue.InnerText;
            var cellTextLength = cellValue.Length;

            if (cell.StyleIndex != null && numberStyles.Contains(cell.StyleIndex))
            {
                int thousandCount = (int)Math.Truncate((double)cellTextLength / 4);

                //add 3 for '.00' 
                cellTextLength += (3 + thousandCount);
            }

            if (cell.StyleIndex != null && boldStyles.Contains(cell.StyleIndex))
            {
                //add an extra char for bold - not 100% acurate but good enough for what i need.
                cellTextLength += 1;
            }

            if (maxColWidth.ContainsKey(i))
            {
                var current = maxColWidth[i];
                if (cellTextLength > current)
                {
                    maxColWidth[i] = cellTextLength;
                }
            }
            else
            {
                maxColWidth.Add(i, cellTextLength);
            }
        }
    }

    return maxColWidth;
}

Columns AutoSizeCells(SheetData sheetData)
{
    var maxColWidth = GetMaxCharacterWidth(sheetData);

    Columns columns = new Columns();
    //this is the width of my font - yours may be different
    double maxWidth = 7;
    foreach (var item in maxColWidth)
    {
        //width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
        double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;
        Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
        columns.Append(col);
    }

    return columns;
}

ForegroundColor TranslateForeground(System.Drawing.Color fillColor)
{
    return new ForegroundColor()
    {
        Rgb = new HexBinaryValue()
        {
            Value =
                      System.Drawing.ColorTranslator.ToHtml(
                      System.Drawing.Color.FromArgb(
                          fillColor.A,
                          fillColor.R,
                          fillColor.G,
                          fillColor.B)).Replace("#", "")
        }
    };
}

Stylesheet CreateStyleSheet()
{
    Stylesheet stylesheet = new Stylesheet();
    #region Number format
    uint DATETIME_FORMAT = 164;
    uint DIGITS4_FORMAT = 165;
    var numberingFormats = new NumberingFormats();
    numberingFormats.Append(new NumberingFormat // Datetime format
    {
        NumberFormatId = UInt32Value.FromUInt32(DATETIME_FORMAT),
        FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
    });
    numberingFormats.Append(new NumberingFormat // four digits format
    {
        NumberFormatId = UInt32Value.FromUInt32(DIGITS4_FORMAT),
        FormatCode = StringValue.FromString("0000")
    });
    numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
    #endregion

    #region Fonts
    var fonts = new Fonts();
    fonts.Append(new DocumentFormat.OpenXml.Spreadsheet.Font()  // Font index 0 - default
    {
        FontName = new FontName { Val = StringValue.FromString("Calibri") },
        FontSize = new FontSize { Val = DoubleValue.FromDouble(11) }
    });
    fonts.Append(new DocumentFormat.OpenXml.Spreadsheet.Font()  // Font index 1
    {
        FontName = new FontName { Val = StringValue.FromString("Arial") },
        FontSize = new FontSize { Val = DoubleValue.FromDouble(11) },
        Bold = new Bold()
    });
    fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
    #endregion

    #region Fills
    var fills = new Fills();
    fills.Append(new Fill() // Fill index 0
    {
        PatternFill = new PatternFill { PatternType = PatternValues.None }
    });
    fills.Append(new Fill() // Fill index 1
    {
        PatternFill = new PatternFill { PatternType = PatternValues.Gray125 }
    });
    fills.Append(new Fill() // Fill index 2
    {
        PatternFill = new PatternFill { 
            PatternType = PatternValues.Solid, 
            ForegroundColor = TranslateForeground(System.Drawing.Color.LightBlue),
            BackgroundColor = new BackgroundColor { Rgb = TranslateForeground(System.Drawing.Color.LightBlue).Rgb }
        }
    });
    fills.Append(new Fill() // Fill index 3
    {
        PatternFill = new PatternFill
        {
            PatternType = PatternValues.Solid,
            ForegroundColor = TranslateForeground(System.Drawing.Color.LightSkyBlue),
            BackgroundColor = new BackgroundColor { Rgb = TranslateForeground(System.Drawing.Color.LightBlue).Rgb }
        }
    });
    fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
    #endregion

    #region Borders
    var borders = new Borders();
    borders.Append(new Border   // Border index 0: no border
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder(),
        BottomBorder = new BottomBorder(),
        DiagonalBorder = new DiagonalBorder()
    });
    borders.Append(new Border    //Boarder Index 1: All
    {
        LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
        RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
        TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
        BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
        DiagonalBorder = new DiagonalBorder()
    });
    borders.Append(new Border   // Boarder Index 2: Top and Bottom
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
        BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
        DiagonalBorder = new DiagonalBorder()
    });
    borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
    #endregion

    #region Cell Style Format
    var cellStyleFormats = new CellStyleFormats();
    cellStyleFormats.Append(new CellFormat  // Cell style format index 0: no format
    {
        NumberFormatId = 0,
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0
    });
    cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
    #endregion

    #region Cell format
    var cellFormats = new CellFormats();
    cellFormats.Append(new CellFormat());    // Cell format index 0
    cellFormats.Append(new CellFormat   // CellFormat index 1
    {
        NumberFormatId = 14,        // 14 = 'mm-dd-yy'. Standard Date format;
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 2: Standard Number format with 2 decimal placing
    {
        NumberFormatId = 4,        // 4 = '#,##0.00';
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell formt index 3
    {
        NumberFormatId = DATETIME_FORMAT,        // 164 = 'dd/mm/yyyy hh:mm:ss'. Standard Datetime format;
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 4
    {
        NumberFormatId = 3, // 3   #,##0
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat    // Cell format index 5
    {
        NumberFormatId = 4, // 4   #,##0.00
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 6
    {
        NumberFormatId = 10,    // 10  0.00 %,
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 7
    {
        NumberFormatId = DIGITS4_FORMAT,    // Format cellas 4 digits. If less than 4 digits, prepend 0 in front
        FontId = 0,
        FillId = 0,
        BorderId = 0,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true)
    });
    cellFormats.Append(new CellFormat   // Cell format index 8: Cell header
    {
        NumberFormatId = 49,
        FontId = 1,
        FillId = 3,
        BorderId = 2,
        FormatId = 0,
        ApplyNumberFormat = BooleanValue.FromBoolean(true),
        Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center }
    });
    cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
    #endregion

    stylesheet.Append(numberingFormats);
    stylesheet.Append(fonts);
    stylesheet.Append(fills);
    stylesheet.Append(borders);
    stylesheet.Append(cellStyleFormats);
    stylesheet.Append(cellFormats);

    #region Cell styles
    var css = new CellStyles();
    css.Append(new CellStyle
    {
        Name = StringValue.FromString("Normal"),
        FormatId = 0,
        BuiltinId = 0
    });
    css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
    stylesheet.Append(css);
    #endregion

    var dfs = new DifferentialFormats { Count = 0 };
    stylesheet.Append(dfs);
    var tss = new TableStyles
    {
        Count = 0,
        DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
        DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
    };
    stylesheet.Append(tss);

    return stylesheet;
}

using (SpreadsheetDocument excel = SpreadsheetDocument.Create("demo.xlsx", SpreadsheetDocumentType.Workbook))
{
    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = excel.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();
    WorksheetPart worksheetPart1 = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart1.Worksheet = new Worksheet();
    SheetData data1 = CreateShee1Data();
    Columns columns1 = AutoSizeCells(data1);
    worksheetPart1.Worksheet.Append(columns1);
    worksheetPart1.Worksheet.Append(data1);

    WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart2.Worksheet = new Worksheet();
    SheetData data2 = CreateShee2Data();
    Columns columns2 = AutoSizeCells(data2);
    worksheetPart2.Worksheet.Append(columns2);
    worksheetPart2.Worksheet.Append(data2);

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

    // Add Sheets to the Workbook.
    Sheets sheets = excel.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    // Append a new worksheet and associate it with the workbook.
    sheets.Append(new Sheet()
    {
        Id = excel.WorkbookPart.GetIdOfPart(worksheetPart1),
        SheetId = 1,
        Name = "Sheet 1"
    });
    sheets.Append(new Sheet()
    {
        Id = excel.WorkbookPart.GetIdOfPart(worksheetPart2),
        SheetId = 2,
        Name = "Sheet 2"
    });

    //Save & close
    workbookpart.Workbook.Save();
    excel.Close();
}
Openxml
Excel Export
Net 6
Csharp

--

--

Jason Ge
Jason Ge

Written by Jason Ge

61 Followers
1 Following

Software developer with over 20 years experience. Recently focus on Vue/Angular and asp.net core.

Responses (5)

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams