How to create an Excel file in .NET using OpenXML – Part 2: Export a collection to Spreadsheet

19. November 2015 Tutorial 26

Overview

This is the 2nd part of OpenXML Spreadsheet Tutorial Series:

We will continue based on the basic sample we have created in part 1.

In the part 1 we have learnt how to create an empty spreadsheet and save it to the physical disc. The purpose of the part 1 of this tutorial was to understand the hierarchy of the elements constructing an Excel document and the relationship between them.

In this tutorial we are going to dive deeper in the SDK and create rows and columns of date retrieved from a list.

Step 1: Create an Entity as the data structure

  • Create a new class and name it Employee.cs
  • Modify the code as bellow:
public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime DOB { get; set; }
    public decimal Salary { get; set; }
}

public sealed class Employees
{
    static List<Employee> _employees;
    const int COUNT = 15;

    public static List<Employee> EmployeesList {
        private set { }
        get
        {
            return _employees;
        }
    }

    static Employees()
    {
        Initialize();
    }

    private static void Initialize()
    {
        _employees = new List<Employee>();

        Random random = new Random();

        for (int i = 0; i < COUNT; i++)
        {
            _employees.Add(new Employee()
            {
                Id = i,
                Name = "Employee " + i,
                DOB = new DateTime(1999, 1, 1).AddMonths(i),
                Salary = random.Next(100, 10000)
            });
        }
    }
}

This class represents an Employee which has Id, Name, Date of Birth, and Salary. I have chosen these properties so we see how to define the different data types for a cell in a spreadsheet.

The second class Employees is just a wrapper which initialize a list of employees.

Step 2: Create an Empty Spreadsheet Document

Note: If you have created the sample project in part 1 skip to Step 3. Just take note of 2 changes:

  1. The sheet name changed to “Employees”.
  2. We are initializing the workbook.Worksheet without passing a SheetData in.

 

  • Create a class called Report.cs and add the following method inside the class.
public void CreateExcelDoc(string fileName)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = document.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet();

        Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

        Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet" };

        sheets.Append(sheet);

        workbookPart.Workbook.Save();
    }
}

Please check the part 1 for more explanation on this block code.

Step 3: Constructing the Excel spreadsheet with the List of Employees

  • Get the list of employees from the Employees class
List<Employee> employees = Employees.EmployeesList;
  • Append a SheetData class to the worksheet. The SheetData acts as the container where all the rows and columns will be going.
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
  • The Row class represent a row in an Excel spreadsheet. Each Row can contain one or more Cell. Each Cell will have a CellValue which contains the actual value in the cell.

    Add the following method which gets a value and cell data type and returns a Cell object:

private Cell ConstructCell(string value, CellValues dataType)
{
    return new Cell()
    {
        CellValue = new CellValue(value),
        DataType = new EnumValue<CellValues>(dataType)
    };
}

You can see a Cell has a CellValue which is the actual value of the Cell and DataType which is of CellValues enum.

  • Add a header row to the spreadsheet.
// Constructing header
Row row = new Row();

row.Append(
    ConstructCell("Id", CellValues.String),
    ConstructCell("Name", CellValues.String),
    ConstructCell("Birth Date", CellValues.String),
    ConstructCell("Salary", CellValues.String));

// Insert the header row to the Sheet Data
sheetData.AppendChild(row);

This block first creates a Row object. Then it appends for Cells as its child which represents the header row. After constructing the Row, it will add it to the SheetData object.

Notice that all the datatypes we are passing is string.

  • Loop through Employees and insert them into the SheetData
// Inserting each employee
foreach (var employee in employees)
{
    row = new Row();

    row.Append(
        ConstructCell(employee.Id.ToString(), CellValues.Number),
        ConstructCell(employee.Name, CellValues.String),
        ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String),
        ConstructCell(employee.Salary.ToString(), CellValues.Number));

    sheetData.AppendChild(row);
}

The code loops through the list of Employees and construct a row the same we have done with header and add each row to the SheetDate object. Note that the different here is or cell values are read from the Employee object and the datatype for Id and Salary is Number.

You might wonder why we are casting the Id and Salary to string but defining the datatype as Number. This is because the CellValue can only accept string. But we are telling Excel to interpret that as Number.

  • Save the worksheet at the end.

worksheetPart.Worksheet.Save();

Complete Code

public void CreateExcelDoc(string fileName)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = document.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        WorksheetPart worksheetPart = workbookPart.AddNewPart();
        worksheetPart.Worksheet = new Worksheet();

        Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

        Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" };

        sheets.Append(sheet);

        workbookPart.Workbook.Save();

        List employees = Employees.EmployeesList;

        SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

        // Constructing header
        Row row = new Row();

        row.Append(
            ConstructCell("Id", CellValues.String),
            ConstructCell("Name", CellValues.String),
            ConstructCell("Birth Date", CellValues.String),
            ConstructCell("Salary", CellValues.String));

        // Insert the header row to the Sheet Data
        sheetData.AppendChild(row);

        // Inserting each employee
        foreach (var employee in employees)
        {
            row = new Row();

            row.Append(
                ConstructCell(employee.Id.ToString(), CellValues.Number),
                ConstructCell(employee.Name, CellValues.String),
                ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String),
                ConstructCell(employee.Salary.ToString(), CellValues.Number));

            sheetData.AppendChild(row);
        }

        worksheetPart.Worksheet.Save();
    }
}

private Cell ConstructCell(string value, CellValues dataType)
{
    return new Cell()
    {
        CellValue = new CellValue(value),
        DataType = new EnumValue<CellValues>(dataType)
    };
}

Test the code

Open Program.cs and modify the main method as below:

static void Main(string[] args)
{
    Report report = new Report();

    report.CreateExcelDoc(@"D:\DispatcherTimer\Report.xlsx");

    Console.WriteLine("Excel file has created!");
}

 

Let’s run the code and see the result:

Well done!

Download

Download the sample code


26 thoughts on “How to create an Excel file in .NET using OpenXML – Part 2: Export a collection to Spreadsheet”

  • 1
    jon on April 23, 2016 Reply

    This code contains errors

    First, this line:
    Sheets sheets = workbookPart.Workbook.AppendChild();
    will give you a compilation error since AppendChild() needs at least one argument
    You need to use this line:
    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

    Second this line:
    worksheetPart.Worksheet = new Worksheet(new SheetData());
    will make your excel document unreadable. You need to use this instead
    worksheetPart.Worksheet = new Worksheet();

    If you care about your credibility…please fix

    • 2
      Behnam on April 24, 2016 Reply

      Thanks Jon for noticing the typo. I have fixed it.
      By the way the code was correct from the source code as well as the individual sections. Only the complete code was with that mistake.

  • 3
    axel on November 12, 2016 Reply

    I tried to do the example given, but in my case i have OpenOffice(from Apache), and it does not work because this code show me nothing, but it does in excel, do you know some way to make it work for OpenOffice??

    • 4
      Behnam on November 28, 2016 Reply

      I haven’t tried it with OpenOffice. I will try and will let you know if I found anything.

  • 5
    John Mihaljevic on February 3, 2017 Reply

    I’ve been struggling with creating Excel workbooks with OpenXML in C#, and couldn’t find anything online that explained it well, while keeping it simple, until I found your page. Thank you VERY much for posting this! You’ve answered all the questions I had, and I’m now banging out workbooks without any trouble.

    Thanks again!

    • 6
      Behnam on February 3, 2017 Reply

      You are welcome. Happy it helped you.

  • 7
    Vaduga on March 3, 2017 Reply

    Thanks a lot!!!!

    You saved lot of my time.

  • 8
    Jignesh Patel on March 21, 2017 Reply

    Hi,

    I’m generating an excel file using OpenXML as you provided code. But after generating If I upload same an excel file then I received “External table is not in the expected format. ” error. If without making any change in generated excel file just I save and reupload then it’s working fine.

    Below is the sample code to read an excel file.
    private static void Upload()
    {
    OleDbConnection objXConn = null;
    string connectionString = string.Empty;
    string destinationPath = string.Empty;
    connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\”Excel 12.0;\””;
    destinationPath = @”C:\Users\test\Desktop\Report.xlsx”;
    connectionString = string.Format(connectionString, destinationPath);
    objXConn = new OleDbConnection(connectionString);
    objXConn.Open();
    DataTable dtSchema = null;
    dtSchema = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    }

  • 9
    Victoria on April 20, 2017 Reply

    Hi! I am a junior-level software engineer, and I’ve been trying to write my first unit tests for the past week or so. You have NO idea how helpful the above (and parts 1,3, and 4, for that matter) has been in helping me generate spreadsheets to test my code. Your tutorials were so simple, easy to follow, and contained the perfect amount of details and context to provide sufficient background information but not be overwhelming for a beginner developer like me. I really can’t thank you enough. Cheers!

    • 10
      Behnam on April 21, 2017 Reply

      Hi Victoria. Thanks for your inspiring comment. I am happy these blog post were helpful for you. Cheers!

  • 11
    dodegaard on May 29, 2017 Reply

    Great tutorial. Well written and followed. Thank you very much.

  • 12
    Tapan on August 12, 2017 Reply

    Nice article but after it completed writing the file, I am not able to open the content.

    It shows an error like below:

    error029720_01.xmlErrors were detected in file ‘path\filename.xlsx’Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Load error. Line 1, column 0.

    • 13
      Bra'Tac on November 24, 2017 Reply

      I got the same error and found why :

      The line in first part :
      worksheetPart.Worksheet = new Worksheet(new SheetData());

      should be changed to :
      worksheetPart.Worksheet = new Worksheet();

    • 14
      papierus on December 1, 2017 Reply

      Revise the code from Part 1, this:
      WorksheetPart worksheetPart = workbookPart.AddNewPart();
      worksheetPart.Worksheet = new Worksheet(new SheetData());
      Should be:
      WorksheetPart worksheetPart = workbookPart.AddNewPart();
      worksheetPart.Worksheet = new Worksheet();

  • 15
    Ananthan on August 22, 2017 Reply

    Hi
    Thanks a lot I was struggling a lot for using excel in my server.
    Regards
    Ananthan Iyer

    • 16
      Behnam on August 27, 2017 Reply

      You are welcome. I am happy it could help you.

  • 17
    José Manuel Nieto (SuperJMN) on December 27, 2017 Reply

    Big thanks for this great post. You saved my life 🙂

    • 18
      Behnam on January 2, 2018 Reply

      Welcome, happy it helps 🙂

  • 19
    Snehal on January 23, 2018 Reply

    awesome post 🙂 Thanks a lot now I am struggling for adding data to existing excel. could you help me.

    • 20
      Behnam on January 24, 2018 Reply

      You are welcome. I would do it if I had time. But currently I am very busy with some other stuff. If you tweak a bit around the examples you should be able to do that. The whole idea is XML manipulation. Getting the cell you want and do what you want with it.

  • 21
    Mesut P. on February 7, 2018 Reply

    thanks for this post but I have a problem. When opening excel file, I get the following error message. Can you help me?
    error message:
    “we found a problem with some content in “excel file name.xlsx” do you want us to try to recover as much as we can? if you trust the source of this workgbook”

    • 22
      erhan on September 8, 2018 Reply

      Revise the code from Part 1, this:
      WorksheetPart worksheetPart = workbookPart.AddNewPart();
      worksheetPart.Worksheet = new Worksheet(new SheetData());
      Should be:
      WorksheetPart worksheetPart = workbookPart.AddNewPart();
      worksheetPart.Worksheet = new Worksheet();

  • 23
    Guchi on April 3, 2018 Reply

    Hello, How I Can set AutoFit to cell?
    thanks

  • 24
    Roof on November 2, 2018 Reply

    hi,

    thank you for your effort,

    finally i did it with this document

  • 25
    Cody Gal on May 22, 2020 Reply

    Hi,

    Can i create multiple rows in a particular cell using openxml. Please post code for the same.

    • 26
      Behnam on June 21, 2020 Reply

      You can’t have multiple rows in a cell. Such concept doesn’t exist. What you need to do is merge cells. I don’t have any plan to write about that at the moment.

Leave a Reply to Mesut P. Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.