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 (21)

  1. Jakob Flygare says:

    How do I insert a string instead of a number in the cell?

  2. Jakob Flygare says:

    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();

         }

       }

  3. Sergey Shatzkiy says:

    It’s very usefull! Especially about adding Sheet to workbook. Thank you for this post!

  4. pinacolada says:

    I love you !

    (I’m kidding but you really saved me a long time searching and reading a lot of useless explanations…)

  5. Thanks so much. This is the first time I starting using the OpenXML stuff. You saved me a lot of time.

  6. Mark Conway says:

    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?

  7. Mark Conway says:

    I found the problem, it was an error on my part:

    newPart.Worksheet.AppendChild(new Row());

    should be

    newWorksheetPart.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();

  8. Mike Underwood says:

    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.

  9. Tony says:

    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.

  10. Raghu says:

    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?

  11. Mike says:

    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?

  12. Zendu says:

    Mike,

    Add directive

    using Syste.Linq;

  13. Sas says:

    How do i add DataValidation?

  14. Ruy says:

    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

  15. Mike Gledhill says:

    Anyone looking for a simple, free C# class to export data to an Excel 2007, with all Source Code provided, should have a look here:

    mikesknowledgebase.com/…/ExportToExcel.htm

    (That should save a lot of head-scratching !)

  16. Sam Wheat says:

    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();

  17. Karthik says:

    Is there anyway to alter the cell properties here? Say for e.g. Highlight the Row headers etc,.

  18. mark says:

    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.

  19. Mohamad_Naseri says:

    thanks  Mark Conway for your helpful post!

  20. N D says:

    I can't open the excel file created using your code. It says the file is corrupted.

  21. Sovik says:

    How to autofit contents in the cell?