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

19. November 2015 Tutorial 14

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:

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.

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

  • Append a SheetData class to the worksheet. The SheetData acts as the container where all the rows and columns will be going.

  • 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:

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.

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

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

Test the code

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

 

Let’s run the code and see the result:

Well done!

Download

Download the sample code


14 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
    Ananthan on August 22, 2017 Reply

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

    • 14
      Behnam on August 27, 2017 Reply

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

Leave a Reply

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