How to create an Excel file in .NET using OpenXML – Part 1: Basics

16. November 2015 Tutorial 4

Overview

This is the 1st part of OpenXML Spreadsheet Tutorial Series:

There are two ways which .NET helps you create an Excel file in .NET:

Office Interop needs Excel driver to be installed on the machine which is using it. In this tutorial we use OpenXML to be independent from having Excel driver.

With OpenXML you are simply constructing an XML structure which represents an Excel file. You start with creating a SpreadsheetDocument which represents an Excel document. Then you need to add workbook and worksheet parts in order to construct the document body.

The following diagram demonstrates the components in a Spreadsheet Document and their hierarchy:

Don’t worry if you don’t understand this diagram at this moment. As we are going through the steps you will discover this hierarchy.

Sample: Constructing an Empty Excel File

Open XML 2.5, C#, and .NET 4.6 with Visual Studio 2015 is used in this tutorial. You can still follow with older versions.

In the following sample we will only create an empty excel file without any actual data. It is important to understand the basics of how a document constructed.

  • Open Visual Studio 2015 and create a new C# Console Application under: Installed, Templates, Visual C#

  • You need to add reference to the following libraries:
    • DocumentFormat.OpenXml
    • WindowsBase

  • Add a new class and name it “Report” and create a public method called “CreateExcelDoc”. We are going to create and save our Excel file in this method.
  • Import the following namespaces into the class:

  • Create a new SpreadsheetDocument and pass the file name and document as the parameters.

  • A SpreadsheetDocument must have at least a WorkbookPart and a WorkSheetPart.

Add the following code within the using block.

All the spreadsheet elements will be related via parent/child relationship.

The Workbook will contain our spreadsheet sheets.

The Worksheet will contain SheetData and Columns. It is the Sheet data which the actual values goes in rows and cells. By initializing the Worksheet we can append a SheetData as its child by passing it as argument.

  • Append a “Sheets” to the Workbook. The Sheets will contain one or many Sheet which each associated with a WorksheetPart.


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

Then we can add one or many “Sheet” to the Sheets. These will be our Excelsheets. Take note that the sheet is associated to the WorksheetPart.

  • At the end save the Workbook.

workbookPart.Workbook.Save();

  • In the Main method of the Program class, create an object of the Report class and call the CreateExcelDocument by passing the file path.

  • Run the Project and check the generated Excel file:

Complete Code:

Farther reading

Download

Download the source code


4 thoughts on “How to create an Excel file in .NET using OpenXML – Part 1: Basics”

  • 1
    Don on February 7, 2017 Reply

    Thank you so much for your effort and time to share your knowledge on OpenXML and creating a spreadsheet document. This overview and tutorial has saved me much time and frustration. This is very much appreciated.

    :)

    • 2
      Behnam on February 8, 2017 Reply

      Hi Don. You are welcome. I am happy it was helpful.

  • 3
    Ana on June 15, 2017 Reply

    Nice tutorial, helped me a lot.
    One question, how can I create and open next the file? Do you know?
    Thanks

    • 4
      Behnam on June 19, 2017 Reply

      You are welcome.
      These tutorials are focused on writing into Excel. In order to read you can use the following snippet to start with.:

      var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
      var spreadSheetDocument = SpreadsheetDocument.Open(fileStream, false);
      WorkbookPart workBookPart = spreadSheetDocument.WorkbookPart;
      WorksheetPart workSheetPart = workBookPart.WorksheetParts.First();
      Worksheet sheetPart = workSheetPart .Worksheet;
      List rows = sheetPart .Descendants().ToList();

      foreach (var row in rows )
      {
      // do whatever you want with the row
      }

Leave a Reply

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