Share via

Creating an Excel spreadsheet from scratch using OpenXML

  • Article

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