Creating a simple XLSX from scratch using the Open XML SDK

[アーティクル]
08/18/2011

Recently I've been scouting around Stack Overflow looking for Open XML-related questions - it's always interesting to see what real people are doing with technologies you've been involved in.

One topic that often comes up is the question of how to create SpreadsheetML files from scratch. The way most people go about this is to save a blank file directly from Excel and then, using the Open XML SDK, open that template file, insert your necessary data into it and save it as the destination output file. While this works fine I can't help feeling it's a little inelegant. Why not create a file from scratch? The Open XML SDK comes with a great tool called the Reflector. You can open a file, click "Reflect code" and it'll generate the code necessary to create that file. I saved a blank file from Excel, and then opened it in the reflector. It generated 900 lines of code. Which wasn't exactly what I had in mind.

It turns out that a "blank" file saved by Excel contains a lot of data that isn't required by the standard. As well as styles, font data, margins and other layout information, it also contains many application properties and some theme definitions created by Microsoft. These are useful when you want your annual report to look great in non-Microsoft products, but they're not so helpful if you just want a grid of numbers to show up. In addition, the reflector is geared towards helping you write code to structure your workbook, not really to write large amounts of static data into it.

Nearly five years ago, my colleague Doug wrote a blog post, "CreateXlsx sample program", which walked the user through creation of a minimalist SpreadsheetML file using the System.IO.Packaging library. This was before the Open XML SDK existed, so I thought I'd write an updated version of that code using the SDK.

Without further ado, here is the code. To run it:

Get a copy of Visual Studio 2010, and download and install the Open XML SDK
Click File..New..Project and select Visual C# \ Windows \ Console Application
Add a reference to the DocumentFormat.OpenXml library
Replace the entire text of Program.cs with the following code:

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace MakeXLSX
{
    public class MostlyGeneratedClass
    {
        // Creates a SpreadsheetDocument

         public void CreatePackage(string filePath)

         {
            using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
            {
                CreateParts(package);
            }
        }

        // Adds child parts and generates content of the specified part

         private void CreateParts(SpreadsheetDocument document)

             WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1);

            WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPart1Content(worksheetPart1);
        }

        // Generates content of workbookPart1.

         private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)

         {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "https://schemas.openxmlformats.org/officeDocument/2006/relationships");

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            sheets1.Append(sheet1);

            workbook1.Append(sheets1);
            workbookPart1.Workbook = workbook1;
        }

        // Generates content of worksheetPart1.

         private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)

         {
            Worksheet worksheet1 = new Worksheet();
            SheetData sheetData1 = new SheetData();

            Row row1 = new Row();
            Cell cell1 = new Cell() { CellReference = "A1", DataType = CellValues.InlineString };
            InlineString inlineString1 = new InlineString();
            Text text1 = new Text();
            text1.Text = "hello";
            inlineString1.Append(text1);
            cell1.Append(inlineString1);
            row1.Append(cell1);

            sheetData1.Append(row1);
            worksheet1.Append(sheetData1);
            worksheetPart1.Worksheet = worksheet1;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            MostlyGeneratedClass x = new MostlyGeneratedClass();
            x.CreatePackage("c:\\chris.xlsx");
        }
    }
}

Running this code will create a c:\chris.xlsx which contains one worksheet, with "hello" in cell A1. Using this template and the reflector you should be able to build onwards to develop whatever solution you're after.

Of course, not everyone is using the SDK. In case you're generating spreadsheet files using another platform, I've attached the file that this code creates. As you probably know, you can look at the content of this file by simply renaming it to .zip and then viewing it with a normal zip file editor. It contains only five Parts, with around 32 lines of XML in total, so it should be easy enough to port this to your platform.

Code is provided under the Apache License 2.0 - I hope this is permissive enough to allow reuse in any circumstances; please get in touch if you would like to use this code under a different license for any reason.

minimalistfile.xlsx

Comments

Anonymous
March 21, 2012
Is it possible to imeplement this in vc++ with vs2003 or vs2010?
Anonymous
March 15, 2013
The comment has been removed
Anonymous
September 04, 2013
Thanks for the post Chris it has been helpful.I am working on a solution where I would like to transform an existing XML document into the xlsx format using an xslt transform. Are there any tools available in the sdk which will assist in doing this.I aim to create the worksheet using xslt then inject it into a document which I build up using the SDK. Is this possible?
Anonymous
September 10, 2013
Hi Ross,There is a way to work with Open XML using the Open XML SDK and XSLT:blogs.msdn.com/.../transforming-open-xml-documents-using-xslt.aspxReading between the lines, you may be interested in this:openxmldeveloper.org/.../generating-open-xml-documents-using-t4-text-templates.aspx-Eric
Anonymous
September 12, 2013
If I have to install Open XML SDK then why not just install Office libraries for .Net? I had always believed and hoped that the simplicity of Excel as XML would be that I could create a simplet set of XML and open it in Excel as an XLSX file.For example, what would be the minimum amount of XML that has to be generated from scratch in order to end up with a working XLSX file with 10 rows of 10 columns, all saying "Hello World"?
Anonymous
September 24, 2013
The comment has been removed
Anonymous
April 28, 2014
Thank you! This is by far the best example of how to create an excel spreadsheet using OpenXML. I was easily able to take this example and expand upon it to do what I needed!
Anonymous
July 14, 2014
Hi!I'm french, and I would know how can I have more sheets ?I have try : Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)1U, Id = "rId1" }; sheets1.Append(sheet2);but it doesn't work...Thank you for this tutorial
Anonymous
July 28, 2014
Hi Antoine - you need to replicate the entire sheet declaration and subsequent Workbook append - you can't append a sheet to a sheet.
Anonymous
February 01, 2015
Hi ChrisIs there any method to generate dynamic xsl for xml spreadsheet?
Anonymous
February 02, 2015
Hi Divya - I don't think so.
Anonymous
February 26, 2015
I am having the same issue as Antoine. Workbook workbook1 = new Workbook(); workbook1.AddNamespaceDeclaration("r", "schemas.openxmlformats.org/.../relationships"); Sheets sheets1 = new Sheets(); Sheet sheet1 = new Sheet() { Name = "About you (The Carer)", SheetId = (UInt32Value)1U, Id = "rId1" }; sheets1.Append(sheet1); Sheets sheets2 = new Sheets(); Sheet sheet2 = new Sheet() { Name = "Identifying Young Carers", SheetId = (UInt32Value)1U, Id = "rId2" }; sheets2.Append(sheet2); workbook1.Append(sheets1); workbook1.Append(sheets2); workbookPart1.Workbook = workbook1; }thanks in advance
Anonymous
February 27, 2015
You only want one sheets collection. So instead of having a sheets2, you should just do sheets1.Append(sheet2) and then workbook1.Append(sheets1). Apologies, I think I said something somewhat contrary to this in my earlier comment about replicating the workbook append.
Anonymous
February 27, 2015
I understand nowThanks for your help with this,it works fine now.
Anonymous
March 03, 2015
Hi ChrisI am currently trying to set the width of a column and cannot seem to see how this is done.thanks in advance
Anonymous
March 04, 2015
Hi,For some reason, whenever i create a brand new xlsx file using this example, and try to open it using Excel 2010, i get the "Excel found some unreadable data ..." error, and then i have to "Attempt to recover it". The strange part is that the log shown afterwards says that there really was nothing fixed or changed.Keep in mind that i am doing this via MVC, creating the file in a MemoryStream, then pushing it to the view/browser via a FileContentResult. I am assuming that this really is the problem and not the code to generate the file itself?With that in mind, here is my entire code for the ActionResult method (hopefully it fits here):var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var filename = "SearchRecherche_" + results.Terms + "_" + results.RanOn + ".xlsx"; System.IO.MemoryStream stream = new System.IO.MemoryStream(); using (SpreadsheetDocument package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart1 = package.AddWorkbookPart(); Workbook workbook1 = new Workbook(); workbook1.AddNamespaceDeclaration("r", "schemas.openxmlformats.org/.../relationships"); Sheets sheets1 = new Sheets(); Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" }; sheets1.Append(sheet1); workbook1.Append(sheets1); workbookPart1.Workbook = workbook1; WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1"); Worksheet worksheet1 = new Worksheet(); SheetData sheetData1 = new SheetData(); Row row1 = new Row(); Cell cell1 = new Cell() { CellReference = "A1", DataType = CellValues.InlineString }; InlineString inlineString1 = new InlineString(); Text text1 = new Text(); text1.Text = "hello"; inlineString1.Append(text1); cell1.Append(inlineString1); row1.Append(cell1); sheetData1.Append(row1); worksheet1.Append(sheetData1); worksheetPart1.Worksheet = worksheet1; } var result = new FileContentResult(stream.GetBuffer(), contentType); result.FileDownloadName = filename; return result;Thanks a million in advance for any help!
Anonymous
April 29, 2015
@Matt: Use stream.ToArray() instead of stream.GetBuffer(). stackoverflow.com/.../when-is-getbuffer-on-memorystream-ever-useful

次の方法で共有

Creating a simple XLSX from scratch using the Open XML SDK

Comments

その他のリソース