Creating an Excel spreadsheet from scratch using OpenXML
After spending a work day scouring for some quality examples on how to create an excel spreadsheet, it was pretty obvious there weren't any. I am not sure if I should have been surprised or the fact that folks just use the tons of examples where they use an existing Excel spreadsheet file. For the novice getting into it, it could be pretty daunting. Especially now since most examples written are a mix of the old Open XML 1.0 using the XmlDocument/XmlElement/XmlTextWriter classes with the Open XML 2.0 stuff.
For any developer with half a brain, knows that trying to write excel documents using the old Xml classes approach is just not feasible. You have to explicitly write out each of the <tags> properly formatted. There could be thousands of these nestled in some 5 levels of for loops with hard coded strings littered all over the code. Yeah.... NO
Open XML 2.0 provided us with a little more abstraction with typed classes. So instead of Xml-ing <Cell> , you would create a Cell object.
So now lets try and create an Excel document from scratch using Open XML 2.0 (April 2008 CTP) in C#. I am going to assume you have some knowledge the OpenXML specification. If you are reading this cold turkey, stop now, go learn about the spec. Theres videos, no complaints. www.openxmldeveloper.org
Have a look at a basic Excel 2007 spreadsheet by creating one in excel. Make sure to delete the 2 other sheets below and put some value into the A1 cell, say 100. Save the file, then rename the extension to .zip and have a look around. Specifically jump into the xl folder and open up all the .xml files in it.
workbook.xml holds all the sheets info and the sheet.xml in the worksheet folder holds the actual data. the rels file is whats links the worksheet to the workbook. There are more stuff but you can play with that on your own.
Programming:
Just to make this a little easier, I am going to reference everything using the full object path. I know sometimes working with object references can get confusing. It is nice to know exactly where you are and what you are referencing without having to hold it in your head. The heirarchy we need to follow is simple.
- Create Spreadsheet
- Create Workbook
- Create Worksheet
- Create SheetData
- Create Row
- Create Cell
- Link Worksheet to Workbook
- Enjoy Ice cream
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(
System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, SPREADSHEET_NAME),
SpreadsheetDocumentType.Workbook))
{
// create the workbook
spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook (); // create the worksheet
spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
// create sheet data
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
// create row
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
// create cell with data
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(
new Cell() { CellValue = new CellValue("101") });
// save worksheet
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
// create the worksheet to workbook relation
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "test"
});
spreadSheet.WorkbookPart.Workbook.Save();
}
Much like Xml usage, we navigate the spreadsheet document by calling it's children. You can append the child elements in any order. As long as it is properly id/referenced it should show up in the correct spot. In this example I knew that the first element being returned is exactly what I wanted since I placed it there. For your automated system you may want to add checks or use the type system to sort out child elements you really want to manipulate.
spreadSheet.WorkbookPart.WorksheetParts.First<WorksheetPart>().Worksheet = new Worksheet();
Better yet, use Linq.
// edit: fixed a spelling error
Comments
- Anonymous
July 28, 2009
How do I insert a string instead of a number in the cell? - Anonymous
July 29, 2009
I answered my own question:public void HelloWorldXlsx(string docName) { // Create a Wordprocessing document. using (SpreadsheetDocument package = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook)) { // Add a new workbook part. package.AddWorkbookPart(); package.WorkbookPart.Workbook = new Workbook(); // Add a new worksheet part. package.WorkbookPart.AddNewPart<WorksheetPart>(); //Create the Spreadsheet DOM. package.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet( new SheetData( new Row( new Cell( new InlineString( new DocumentFormat.OpenXml.Spreadsheet.Text("Hello World!"))) { DataType = CellValues.InlineString }))); // Save changes to the spreadsheet part. package.WorkbookPart.WorksheetParts.First().Worksheet.Save(); // create the worksheet to workbook relation package.WorkbookPart.Workbook.AppendChild(new Sheets()); package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet() { Id = package.WorkbookPart.GetIdOfPart(package.WorkbookPart.WorksheetParts.First()), SheetId = 1, Name = "Hello World!" }); package.WorkbookPart.Workbook.Save(); } } - Anonymous
July 31, 2009
It's very usefull! Especially about adding Sheet to workbook. Thank you for this post! - Anonymous
August 23, 2009
I love you !(I'm kidding but you really saved me a long time searching and reading a lot of useless explanations...) - Anonymous
September 01, 2009
Thanks so much. This is the first time I starting using the OpenXML stuff. You saved me a lot of time. - Anonymous
September 27, 2009
Hello,Thanks for the example!I did run into some trouble adding a SECOND spreadsheet to document.My code will execute but there will be no sheet data in "Sheet2" using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(@"C:TEMPTEST_BOOK.xlsx", SpreadsheetDocumentType.Workbook)) { // create the workbook spreadSheet.AddWorkbookPart(); spreadSheet.WorkbookPart.Workbook = new Workbook(); // create the worksheet WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(); // create sheet data newWorksheetPart.Worksheet.AppendChild(new SheetData()); // create row newWorksheetPart.Worksheet.First().AppendChild(new Row()); // create cell with data newWorksheetPart.Worksheet.First().First().AppendChild( new Cell() { CellValue = new CellValue("101") }); // save worksheet newWorksheetPart.Worksheet.Save(); // create the worksheet to workbook relation spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets()); spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = 1, Name = "Sheet1" }); //CREATE SECOND SHEET //Create the worksheet WorksheetPart newPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); newPart.Worksheet = new Worksheet(); // create sheet data newPart.Worksheet.AppendChild(new SheetData()); // create row newPart.Worksheet.AppendChild(new Row()); // create cell with data newPart.Worksheet.AppendChild( new Cell() { CellValue = new CellValue("World") }); // save worksheet newPart.Worksheet.Save(); // create the worksheet to workbook relation spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(newPart), SheetId = 2, Name = "Sheet2" }); spreadSheet.WorkbookPart.Workbook.Save(); }Any suggestions? - Anonymous
September 27, 2009
I found the problem, it was an error on my part:newPart.Worksheet.AppendChild(new Row());should benewWorksheetPart.Worksheet.First().AppendChild(new Row());A bit of naming makes things a little less obscure, so I have used object names more generously and its become alot less confusing :) //Create the worksheet WorksheetPart newWorksheetPart2 = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart2.Worksheet = new Worksheet(); // create sheet data SheetData sheetData = newWorksheetPart2.Worksheet.AppendChild(new SheetData()); // create row Row row = sheetData.AppendChild(new Row()); // create cell with data row.AppendChild( new Cell() { CellValue = new CellValue("102") }); // create the worksheet to workbook relation Sheets sheets = new Sheets(); spreadSheet.WorkbookPart.Workbook.AppendChild(sheets); // create the worksheet to workbook relation sheets.AppendChild(new Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart2), SheetId = 2, Name = "Sheet2" }); spreadSheet.WorkbookPart.Workbook.Save(); - Anonymous
October 15, 2009
Dude thank you so much for making sence out of all the garbage examples out there. The only think i havnt been able to figure out, YET, is how to apply font formats (ie bold, underline, font color). But I will. - Anonymous
November 22, 2009
Could you put some example on how to recalculate the spreadsheet?I Have a spreadsheet, i change some values, but when i open it the chart and formulas don´t get recalculated. - Anonymous
March 24, 2010
Can you give an example of how to write multiple worksheets in to a workbook?i have the data coming from a XMLdata sheet.The names of the worksheets is also an attribute in XML.is there a way to do that? - Anonymous
July 26, 2010
I don't even have a '.First()' method of WorkSheetParts. I'm using open xml 2.0. Is this not the correct version for this sample code? - Anonymous
December 13, 2010
Mike,Add directiveusing Syste.Linq; - Anonymous
August 25, 2011
How do i add DataValidation? - Anonymous
October 16, 2011
When i try to a add a colunm range from [a1:az1] the fie becomes unreadable... does anyone know why this happens and how to solve it...tkx in advance - Anonymous
March 07, 2012
The comment has been removed - Anonymous
September 11, 2012
Thank you for this very helpful post. Like Mark Conway above, I also needed to add a second sheet to my workbook. I tried to follow Marks example above but got a corruption error when I tried to open the workbook in excel. Turns out the problem is adding the sheets element twice. The code below works... on my machine.// code showing document creation and first worksheet creation is ommited.WorksheetPart summarySheet = workbookPart.AddNewPart<WorksheetPart>(); // Add second sheet summarySheet.Worksheet = new Worksheet(); SheetData data = summarySheet.Worksheet.AppendChild(new SheetData()); Row r = data.AppendChild(new Row()); Cell cell = new Cell { CellValue = new CellValue("100") }; r.AppendChild(cell); Sheets sheets = doc.WorkbookPart.Workbook.Sheets; // Dont re-create sheets object, use the existing one sheets.AppendChild(new Sheet { Id = doc.WorkbookPart.GetIdOfPart(summarySheet), SheetId = 2, Name = "Summary" }); summarySheet.Worksheet.Save(); doc.WorkbookPart.Workbook.Save(); doc.Close(); - Anonymous
September 26, 2012
Is there anyway to alter the cell properties here? Say for e.g. Highlight the Row headers etc,. - Anonymous
October 29, 2013
How to format fonts(make them big and bold) for a cell or group of cells in a worksheet. I'm trying to format a title at the top of my spreadsheet. - Anonymous
February 02, 2014
thanks Mark Conway for your helpful post! - Anonymous
March 25, 2014
I can't open the excel file created using your code. It says the file is corrupted. - Anonymous
May 13, 2014
How to autofit contents in the cell?