Open XML SDK Code Snippets

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:

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:

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:

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

Zeyad Rajabi

Comments

• Anonymous
  September 22, 2009
  Hey, nice post. But I have a question, is there a way to remove the style using the SDk when coping and pasting from word to a web page's text area? This is a big headache for me recently. Thx

• Anonymous
  September 22, 2009
  David - Copy/paste from Office applications use html as the clipboard format. It is possible to write some kind of addin that sanitizes the html clipboard such that style information is removed. That being said, the SDK will not help you accomplish this scenario since the clipboard format is html rather than Open XML.

• Anonymous
  September 23, 2009
  Hi There, I need to change FONT on generated PowerPoint slide and by default it's using 18pt. How can I change the font on slide when adding repeating rows in a table? Thanks,

• Anonymous
  September 23, 2009
  JK - There are a couple of ways you can change the font size for tables:

• Direct formatting - Add a sz attribute to a:rPr
• Change the table style - The style for tables specifies the size to be 18pt. Change it to whatever you want. Zeyad Rajabi (MS)

• Anonymous
  September 25, 2009
  Can you pleas provide a code snippet or example to insert a row in to existing rows, after or before a certain row.. Thanks in advance...TR

• Anonymous
  September 25, 2009
  TR - Are you asking about inserting a row into a spreadsheet, word document, or presentation? Zeyad Rajabi(MS)

• Anonymous
  September 28, 2009
  Hello Zeyad: I'm looking to add a row into a spreadsheet.

• Anonymous
  September 28, 2009
  Hello Zeyad:  I'm trying to use  following code,the new row is getting added,but cell formats did not apply, could you direct me on this..  Thanks for your help. Row existingrow = worksheetPart.Worksheet.Descendants<Row>()            .Where(r => rowIndex == r.RowIndex).First(); foreach (Row row in worksheetPart.Worksheet.Descendants<Row>().               Where(r => r.RowIndex.Value > rowIndex))            {                uint newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);                foreach (Cell cell in row.Elements<Cell>())                {                    // Update the references for reserved cells.                    string cellReference = cell.CellReference.Value;                    cell.CellReference = new StringValue(cellReference.Replace(                      row.RowIndex.Value.ToString(), newRowIndex.ToString()));                }                // Update the row index.                row.RowIndex = new UInt32Value(newRowIndex);            } worksheetPart.Worksheet.Save();            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();            var element = new Row(               new Cell() { CellReference = "A34", StyleIndex = (UInt32Value)33U },               new Cell() { CellReference = "B34", StyleIndex = (UInt32Value)17U },               new Cell() { CellReference = "C34", StyleIndex = (UInt32Value)23U }           ) { RowIndex = (UInt32Value)34U, Spans = new ListValue<StringValue>() { InnerText = "1:55" } };     sheetData.InsertAfter(element, existingrow);