How to create an Excel file in .NET using OpenXML – Part 2: Export a collection to Spreadsheet
Overview
This is the 2nd part of OpenXML Spreadsheet Tutorial Series:
-
Part 2: Export a Collection to Spreadsheet
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:
-
The sheet name changed to “Employees”.
-
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!
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
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.
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??
I haven’t tried it with OpenOffice. I will try and will let you know if I found anything.
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!
You are welcome. Happy it helped you.
Thanks a lot!!!!
You saved lot of my time.
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);
}
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!
Hi Victoria. Thanks for your inspiring comment. I am happy these blog post were helpful for you. Cheers!
Great tutorial. Well written and followed. Thank you very much.
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.
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();
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();
Hi
Thanks a lot I was struggling a lot for using excel in my server.
Regards
Ananthan Iyer
You are welcome. I am happy it could help you.
Big thanks for this great post. You saved my life 🙂
Welcome, happy it helps 🙂
awesome post 🙂 Thanks a lot now I am struggling for adding data to existing excel. could you help me.
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.
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”
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();
Hello, How I Can set AutoFit to cell?
thanks
hi,
thank you for your effort,
finally i did it with this document
Hi,
Can i create multiple rows in a particular cell using openxml. Please post code for the same.
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.