Open XML SDK Code Snippets

  • Article

In my previous post, I announced the release of the Open XML SDK August 2009 CTP. Today, I want to announce the release of the Open XML SDK code snippets. This package of code snippets provides over fifty reusable code samples, in both C# and VB.NET, which accomplish many common tasks involving Excel, PowerPoint, or Word documents. Looking back at the architecture diagram for the Open XML SDK 2.0, these code snippets are part of the high level helper functions:

Architecture diagram

Erika Ehrli provided a quick summary on all the code snippets in the following post.

Using the Open XML SDK Code Snippets

Let's walk through a quick example of using the Open XML SDK code snippets. In this example, we are given a spreadsheet with a table of data and are asked to read and change a particular cell value. Here is a screenshot of the spreadsheet:

Spreadsheet example

Let's say we are asked to read the value of C4 and then change the value from "Austin" to "Houston".

Here is how you would accomplish this scenario using the Open XML SDK code snippets and the Open XML SDK 2.0:

  1. Create a solution in Visual Studio 2008
  2. Add references to the Open XML SDK 2.0 (DocumentFormat.OpenXml.dll) and WindowsBase.dll
  3. Enable the code snippets for your solution by following these steps
  4. To read a cell value, add a new method to your solution based on the Open XML SDK code snippets. In particular, add the "Excel: Get cell value given row and column" code snippet, which retrieves a cell value given its row and column numbers, or a row number and column name
  5. Use the following code to read and display the value for C4:

    string c4Value = XLGetCellValueRowCol("output.xlsx", "Sheet1", "C", 4); Console.WriteLine("The value for C4 is: " + c4Value);
  6. To change a cell value, add a new method to your solution based on the "Excel: Insert string into cell" code snippet, which given a document name, a worksheet name, a cell name, and a value, inserts text into the specified cell
  7. Use the following code to change the value of C4 to "Houston"

XLInsertStringIntoCell("output.xlsx", "Sheet1", "C4", "Houston");

At the end of step #7 we end up with the following Excel spreadsheet:

Spreadsheet end result

Pretty easy with the code snippets! If you are interested in the full solution you can find it here.

Zeyad Rajabi