• Creating an Excel Workbook with the Open XML SDK 2.0

    7
     
    March 2nd, 2009stuartwhiteford.NET, C#

    Introduction

    I dislike Excel. A lot. It’s not that it isn’t a useful tool, it’s just that every time it crosses my path someone has tried to make it do something it really doesn’t want to do. Let’s face it, it’s not an RDBMS by any stretch of the imagination and it’s a long way from being a fully functional reporting engine. However, cross my path it does, and it’s likely to do so for the foreseeable future, so I might as well try and play nice with it.

    As a follow up to Creating a Word Document with the Open XML SDK 2.0 I though I’d see how easy (or otherwise) it was to create a Workbook using similar tactics.

    Creating the Console Application

    I’ve added a second Console Application (imaginatively called WorkbookBuilder) to the Document Builder solution created last time round, and this time imported the following namespaces: -

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

    Firstly, add the following BuildWorkbook method to your new Program class.

    private static void BuildWorkbook(string fileName)
    {
        try
        {
            using (SpreadsheetDocument s = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = s.AddWorkbookPart();
                WorksheetPart worksheetPart = workbookPart.AddNewPart();
                string relId = workbookPart.GetIdOfPart(worksheetPart);
                Workbook workbook = new Workbook();
                FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
                Worksheet worksheet = new Worksheet();
                SheetData sheetData = new SheetData();
                DateTime date = new DateTime(2009, 1, 1);
                int salesLastYear = 25185;
                int salesThisYear = 25348;
                for (UInt32 i = 1; i <= 52; i++)
                {
                    Row contentRow = CreateContentRow(i, date, salesLastYear, salesThisYear);
                    sheetData.AppendChild(contentRow);
                    date = date.AddDays(7);
                    salesLastYear += (int)(salesLastYear * 0.031);
                    salesThisYear += (int)(salesThisYear * 0.027);
                }
                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
                Sheets sheets = new Sheets();
                Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
                sheets.Append(sheet);
                workbook.Append(fileVersion);
                workbook.Append(sheets);
                s.WorkbookPart.Workbook = workbook;
                s.WorkbookPart.Workbook.Save();
                s.Close();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            Console.ReadLine();
        }
    }

    Second, add the CreateContentRow to the mix: -

    private static Row CreateContentRow(UInt32 index, DateTime date, int salesLastYear, int salesThisYear)
    {
        Row r = new Row { RowIndex = index };
        Cell cell1 = CreateTextCell("A", index, date.ToString());
        Cell cell2 = CreateNumberCell("B", index, salesLastYear);
        Cell cell3 = CreateNumberCell("C", index, salesThisYear);
        r.Append(cell1);
        r.Append(cell2);
        r.Append(cell3);
        return r;
    }

    Next, add the CreateTextCell and CreateNumberCell methods: -

    private static Cell CreateTextCell(string header, UInt32 index, string text)
    {
        Cell c = new Cell { DataType = CellValues.InlineString, CellReference = header + index };
        InlineString istring = new InlineString();
        Text t = new Text { Text = text };
        istring.Append(t);
        c.Append(istring);
        return c;
    }
     
    private static Cell CreateNumberCell(string header, UInt32 index, int number)
    {
        Cell c = new Cell { CellReference = header + index };
        CellValue v = new CellValue { Text = number.ToString() };
        c.Append(v);
        return c;
    }

    Finally, add the call to the BuildWorkbook method from the Main method: -

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

    Hit F5 to run the application as before.

    I’m sure you’ll agree that this is hardly the most involved of Workbooks but I have to admit I’m nonetheless impressed with the speed of execution of these applications. If you’ve ever had to use the Office Object Model you’ll know that just instantiating the Application Classes can be quite time consuming.

    Score one for Excel? No, I don’t think so, this isn’t technically Excel!

    References

    Tags: , , ,
 

7 responses to “Creating an Excel Workbook with the Open XML SDK 2.0” RSS icon

  • Can you please give an example on how to add an image to header/footer to an existing excel using openxml sdk I searched but couldnt find?

  • stuartwhiteford

    Hi David,

    I’ve been looking into this recently, but haven’t found the right combination of code to include an image in an Excel header. I’ll post the solution if I figure it out though.

    Stuart.

  • Hi Stuart

    Could you give an example of how to add unique sheetdata to new sheets in a workbook. Using your example I can create multiple sheets but the sheetdata is displayed on every sheet, I can not target a sheet with unique data.

    Thanks in advance
    Andrew

  • stuartwhiteford

    Hi Andrew,

    If you modify the code from the original article as follows, you should be able to get unique data per sheet.

    Between the following two lines…

    sheets.Append(sheet);
    workbook.Append(fileVersion);

    …insert the following…

    WorksheetPart worksheetPart2 = workbookPart.AddNewPart();
    string relId2 = workbookPart.GetIdOfPart(worksheetPart2);
    Worksheet worksheet2 = new Worksheet();
    SheetData sheetData2 = new SheetData();
    DateTime date2 = new DateTime(2007, 1, 1);
    int salesLastYear2 = 67558;
    int salesThisYear2 = 87854;
    for (UInt32 i = 1; i <= 104; i++)
    {
        Row contentRow2 = CreateContentRow(i, date2, salesLastYear2, salesThisYear2);
        sheetData2.AppendChild(contentRow2);
        date2 = date2.AddDays(7);
        salesLastYear2 += (int)(salesLastYear2 * 0.076);
        salesThisYear2 += (int)(salesThisYear2 * 0.017);
    }
    worksheet2.Append(sheetData2);
    worksheet2.Save(worksheetPart2);
    Sheet sheet2 = new Sheet { Name = "Sheet2", SheetId = 2, Id = relId2 };
    sheets.Append(sheet2);

    Stuart.

  • Hi Stuart,
    I have a requirement of showing images in cell column or as cell comments.
    Can you guide me how can I achieve this?
    Thanks,
    Vara

  • This code works fine, it creates an Excel 2007 .xlsx file which Excel will happily open…

    …but the ‘Microsoft.ACE.OLEDB.12.0′ driver won’t.

    So, in SQL Server, I can create a linked server, pointing at this Excel file, but it throws an error if I try to see which tables are in it, or query any of them.

    Is there something missing ?

    The following SQL should work, but doesn’t.

    SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;Database=C:\Projects\Test.xlsx;HDR=NO;IMEX=1′, ‘SELECT * FROM [Sheet1$]‘)

    Interestingly, if I go into Excel, save the .xlsx as an .xls file, then re-save it back as a (differently named) .xlsx file, then this CAN be opened using the OLEDB method. Odd..

  • Can you please give an example on how to add text (like date and page number) to the header/footer to an existing excel using openxml sdk I searched but couldnt find?


Leave a reply