How to create an Excel file in .NET using OpenXML – Part 1: Basics
Overview
This is the 1st part of OpenXML Spreadsheet Tutorial Series:
-
Part 1: Basic
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
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.
🙂
Hi Don. You are welcome. I am happy it was helpful.
Nice tutorial, helped me a lot.
One question, how can I create and open next the file? Do you know?
Thanks
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); rows = sheetPart .Descendants
().ToList();
var spreadSheetDocument = SpreadsheetDocument.Open(fileStream, false);
WorkbookPart workBookPart = spreadSheetDocument.WorkbookPart;
WorksheetPart workSheetPart = workBookPart.WorksheetParts.First();
Worksheet sheetPart = workSheetPart .Worksheet;
List
foreach (var row in rows )
{
// do whatever you want with the row
}
Saludos desde Colombia, Muchas gracias por el tutorial.
De nada.
Thanks Behnam. I was struggling to kick start my openXML journey. your example helped a lot.
Hi Basha. You are welcome. I am happy the article helps you.
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?
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.
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.
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.
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
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.
It’s an OpenXmlElement. Had a mashup of different tutorials going on. Sure wish I could delete or edit my 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
If we want to protect the spreadsheet with password, what we need to do?
I want to create cell with checkbox in excel using openxml. Does anyone has idea about this.
Need help for creating dynamic excel ie; dropdown in multiple columns with datavalidation
How do we prevent our computers region settings from overriding the date format specified in c# ?
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
How to create line chart?
Thanks, for large file production what do you suggest ?
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.
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.
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.
Using SpreadsheetDocument class how to create xls file type. SpreadsheetDocumentType.Workbook is for xlsx