通过

How to: Use the Open XML SDK 2.0 in Visual Studio 2010 to create a simple Excel Spreadsheet (in C#)

  • 项目

There are plenty of examples out on the internet to use the Open XML SDK 2.0 and Visual Studio 2010 to create Excel workbooks. I wanted to create my own how-to because the other examples I found seemed overly complex and some lacked basic information such as what references and namespaces need to be included.

1. Install the Open XML SDK 2.0 from here. You will want to download and install both the OpenXMLSDKTool.msi and OpenXMLSDKv2.msi

2. Once installed, Launch Visual Studio 2010. Create a new Windows Console Application under C#. Give your Console Application a name and click OK

3. The project is then created. Now you need to add references. Click Project > Add Reference and under the .NET tab look for "DocumentFormat.OpenXML". Click this reference and click OK. Again, Click Project > Add Reference and under the .NET tab look for "WindowsBase". Select this reference and click OK.

4. In your code, add the following namespaces:

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

5. Under your "program" class, find "Static void Main (string[] args)". After the closing terminator add the following method:

public static void CreateSpreadsheetWorkbook(string filepath)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
           
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
            sheets.Append(sheet);

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }

6. In your Main method, add the following line of code:

 CreateSpreadsheetWorkbook("C:\\Test\\Test.xlsx");

The entire method will look like this:

static void Main(string[] args)
        {
            CreateSpreadsheetWorkbook("C:\\Test\\Test.xlsx");
        }

What we are doing here is calling the  CreateSpreadsheetWorkbook method and telling it to create our basic spreadsheet in a directory on the C:\ drive called Test. We are also assigning a name to the workbook of "Text.XLSX" (if we didn't assign a name you would see an "Access Denied" error when the code is run"). Also, you will want to make sure the directory exists or else you will get a "DirectoryNotFoundException" during runtime.

At this point you should save the project and run it. If you have a directory C:\Test you should see a workbook called "Text.XLSX" appear in that folder. This is a very basic spreadsheet. It contains no data and only a single sheet called "mySheet". This is done by the two lines of code in the CreateSpreadsheetWorkbook method.

 

Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);

If you want to know more about customizing the workbook, check out the following reference: 

Generating Excel 2010 Workbooks by using the Open XML SDK 2.0 -  https://msdn.microsoft.com/en-us/library/office/hh180830(v=office.14).aspx

Comments

  • Anonymous
    June 01, 2013
    Thanks you so much... THis is really good to understand the basic... Thanks alot. :)

  • Anonymous
    November 07, 2013
    Thanks for nice explaination - got few clarifications to be made.

  1. Unable to compile code as  it not been able to fine using "DocumentFormat.OpenXml.Spreadsheet"; I tried in 3.5 and 4.0 aswell. Not sure whats missing.
  2. Is this 64 bit compatible?  I havent tried yet, but curious to know. RE
  • Anonymous
    March 18, 2014
    How to add colors to the cell based on cell values using Open xml.(my data is in data table)
    One column can have more than three to four colors.
  • Anonymous
    April 12, 2014
    Thanks it worked for XML SDK 2.5 too.. :)
  • Anonymous
    November 08, 2014
    Thak you, it helped.