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

16. November 2015 Tutorial 27

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:
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

  • Create a new SpreadsheetDocument and pass the file name and document as the parameters.
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
}
  • A SpreadsheetDocument must have at least a WorkbookPart and a WorkSheetPart.

Add the following code within the using block.

// Add a WorkbookPart to the document.
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());

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.

Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet" };
sheets.Append(sheet);
  • 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.
static void Main(string[] args)
{
    Report report = new Report();
    report.CreateExcelDoc(@"D:\DispatcherTimer\Report.xlsx");
}
  • Run the Project and check the generated Excel file:

Complete Code:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
 
namespace OpenXMLSample
{
    public class Report
    {
        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(new SheetData());
 
                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();
            }
        }
    }
}

Farther reading

Download

Download the source code


27 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
      }

  • 5
    Carlos F Vargas on July 19, 2017 Reply

    Saludos desde Colombia, Muchas gracias por el tutorial.

    • 6
      Behnam on July 19, 2017 Reply

      De nada.

  • 7
    Basha Himmatgori on August 7, 2017 Reply

    Thanks Behnam. I was struggling to kick start my openXML journey. your example helped a lot.

    • 8
      Behnam on August 8, 2017 Reply

      Hi Basha. You are welcome. I am happy the article helps you.

  • 9
    Cahit on September 19, 2017 Reply

    Thank you for tutorial.
    I ran the code it worked but excel file didnt open. There were no error on running. Do you know why?

    • 10
      Behnam on September 21, 2017 Reply

      Hi. The part 1 of the tutorial series only creates an empty sheet. It is just to teach the basic architecture and an Excel sheet. If it opens blank that’s correct. If it gives some error, let me know that.

  • 11
    Jimmy on February 15, 2018 Reply

    I don’t understand what this filename is. Whenever I enter it says access it denied to the file. Why do we even need a file name? I thought this was only going to create and open up a blank worksheet.

    • 12
      Behnam on April 6, 2018 Reply

      You are right, this section only demonstrates how to create an empty sheet and save it to a file on disc. Hence it needs a file name. Why it gives you an access denied message is where you are pointing your program to creates the file. You might check if necessary accesses have given to that folder.

  • 13
    Manu on April 12, 2018 Reply

    Will this work on Xamarin (Android and IOS)?
    If yes, what type is List in your code example?:
    “List rows = sheetPart .Descendants().ToList();”

    Visual Studio doesn’t recognize it. Do you mean List of something?

    Thanks

    • 14
      Zach on July 10, 2018 Reply

      Haven’t gotten this to work quite yet but OpenXmlType is the data type referenced.

      “List rows = sheetPart.Descendants().ToList();”

      Buyer beware, I am an idiot.

      • 15
        Zach on July 10, 2018 Reply

        It’s an OpenXmlElement. Had a mashup of different tutorials going on. Sure wish I could delete or edit my reply.

  • 16
    Martha Ng on April 18, 2018 Reply

    Hola,
    Muy interesante, estoy intentando hacer un XML y este pasarlo a Excel, pasándole un dataset con varias tablas de sql, tengo la duda de ¿cómo le podría hacer para que cada tabla fuese una hoja en el XML ? y así ver cada tabla en una hoja al abrirlo en el Excel

  • 17
    Geetha on June 19, 2018 Reply

    If we want to protect the spreadsheet with password, what we need to do?

  • 18
    mayura on August 6, 2018 Reply

    I want to create cell with checkbox in excel using openxml. Does anyone has idea about this.

  • 19
    sanjaytyata on December 24, 2018 Reply

    Need help for creating dynamic excel ie; dropdown in multiple columns with datavalidation

  • 20
    Jkke Sparrow on January 11, 2019 Reply

    How do we prevent our computers region settings from overriding the date format specified in c# ?

  • 21
    Robert on June 26, 2019 Reply

    Hi Behnam,
    first of all thanks for your effort. That was grate for as OpenXML starter.
    I have two questions:
    1. Why you have not defined any RowIndex for your rows? This is also the case with the cells created with “ConstructCell” as there is no definition for “CellReference” there. If the rows and cells are created so, then there is no chance to refere them later.
    My sugesstion is:
    for the rows:
    private Row CreateRow(uint rowIndex)
    {
    return new Row() { RowIndex = rowIndex };
    }
    and the for cells at least “CellReference” exists in the your “ConstructCell” parameter list.
    2. I have relplaced your construction for columns with the folowing. But my column definitions cannot be seen in the result.
    var columns = new Columns();
    var column1 = new Column { Min = 1, Max = 1, Width = 4, CustomWidth = true };
    var column2 = new Column { Min = 1, Max = 1, Width = 20, CustomWidth = true };
    var column3 = new Column { Min = 1, Max = 1, Width = 30, CustomWidth = true };
    var column4 = new Column { Min = 1, Max = 1, Width = 10, CustomWidth = true };

    columns.Append(column1);
    columns.Append(column2);
    columns.Append(column3);
    columns.Append(column4);

    //worksheetPart.Worksheet.Append(columns);

    worksheetPart.Worksheet.AppendChild(columns);
    Kind regards

  • 22
    Sandeep Singh on July 12, 2019 Reply

    How to create line chart?

  • 23
    Pep on September 12, 2019 Reply

    Thanks, for large file production what do you suggest ?

  • 24
    Reader on November 19, 2019 Reply

    A mistake on the image, that the WorksheetPart should be WorksheetParts -> WorksheetPart. Each sheet relates to one WorksheetPart, and each WorksheetPart has one Worksheet, each Worksheet has one SheetData.

  • 25
    Brijen Makwana on May 19, 2020 Reply

    Thank you for this tutorial. It was quite helpful.
    One more thing I want to ask that Can we have Import and Export Excel file in OpnXML with configurable part? Like suppose I am importing one excel with some styles and data and I try to read the data from it and apply some calculations on it.
    Now suppose one day someone just update the excel template and add one new row in it. So without changing the code I just need to update the configuration file and change the cell names (suppose from C5 to C6) and now calculations should be done on the C6 value.
    Could you please throw some light on it if you have ever come across some kind of situation?

    Thanks in advance.

    • 26
      Behnam on June 21, 2020 Reply

      Hi, sorry for late reply. I haven’t came across this problem. I’m sure there should be a way using reflection and DI. But I don’t have any solution at the moment.

  • 27
    Raj on September 11, 2020 Reply

    Using SpreadsheetDocument class how to create xls file type. SpreadsheetDocumentType.Workbook is for xlsx

Leave a 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.