Open XML Format SDK 2.0 Code Snippets for Visual Studio 2008 – 52 C#/VB Code Snippets to help ease your Open XML coding - Greg's Cool [Insert Clever Name] of the Day

Brian Jones: Office Extensibility - 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:

…

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:

Create a solution in Visual Studio 2008

Add references to the Open XML SDK 2.0 (DocumentFormat.OpenXml.dll) and WindowsBase.dll

Enable the code snippets for your solution by following these steps

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

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);

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

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:

…”

Microsoft Downloads - 2007 Office System Sample: Open XML Format SDK 2.0 Code Snippets for Visual Studio 2008

“Download this package to install Open XML Format SDK 2.0 code snippets for use with Visual Studio 2008.

File Name: Office2007OpenXML20Snippets.msi

Version: 0809

Date Published: 8/27/2009

Language: English

Download Size: 417 KB

…The snippets in this download use the Open XML SDK 2.0 to accomplish many tasks involving Microsoft Excel 2007, Microsoft PowerPoint 2007, and Microsoft Word 2007 documents. You can use the enclosed code snippets with the Microsoft Visual Studio® 2008 Code Snippet Manager. Each snippet provides unique functionality that you can reuse within an application. This download provides snippets written in Microsoft Visual Basic.NET® and Microsoft C#® development languages. …

…”

From the Readme.doc (yes .Doc and not .DocX… I find some irony in that… ;)

“…

Microsoft Office Excel Snippets

Excel: Add custom UI
Add custom ribbon markup to a specified workbook.

Excel: Delete comments by user
Delete comments from a workbook, given an author name. Pass an empty author name to delete all comments.

Excel: Delete row
Given a document name, a worksheet name, and a one-based row index, delete a row from the worksheet.

Excel: Delete worksheet
Delete the specified sheet from within the specified workbook.

Excel: Delete XL4 macro sheets
Given a document name delete all the XL4 macro sheets.

Excel: Export chart
Given a workbook and the name of a chart, export the chart to an XML file.

Excel: Get all sheets
Retrieve a List of all the sheets in a workbook.

Excel: Get cell for reading
Given a document name, a worksheet name, and a cell name, retrieve a reference to the cell for reading. Raise an exception of the cell doesn't exist.

Excel: Get cell for writing
Given a spreadsheet document, a sheet name and an address, return a reference to a cell ready to accept a value. Create the cell if necessary.

Excel: Get cell format
Given a document name, a worksheet name, and a cell name, return the CellFormat instance associated with the cell.

Excel: Get cell value
Given a document name, a worksheet name, and a cell name, get the value of the cell.

Excel: Get cell value given row and column
Rertrieve a cell value given its row and column numbers, or a row number and column name.

Excel: Get column header
Given a document name, a worksheet name, and a cell name, get the column of the cell and return the content of the first cell in that column.

Excel: Get defined names
Given a document name, return a dictionary of defined names.

Excel: Get hidden rows or columns
Given a document name, and a worksheet name, return a list of either hidden rows or columns.

Excel: Get hidden worksheets
Retrieve a list of all the hidden worksheets in a workbook.

Excel: Get style border
Retrieve information about a cell's border.

Excel: Get style border info
Get style border information.

Excel: Get style fill
Retrieve information about a cell's fill style.

Excel: Get style fill information
Retrieve specific font formatting information about a cell.

Excel: Get style font information
Retrieve specific font formatting information about a cell.

Excel: Insert Custom XML
Insert a custom XML part into a workbook.

Excel: Insert header or footer
Insert a header or footer into a workbook.

Excel: Insert number into cell
Given a file, a sheet, and a cell, insert a specified numeric value.

Excel: Insert string into cell
Given a document name, a worksheet name, a cell name, and a value, insert the text into the specified cell.

Excel: Insert string into cell
Insert a string into a specified cell.

Excel: Set recalc option
Given a file name, set the recalculation behavior of the workbook. Return the previous calc mode.

Excel: Worksheet part by name
Retrieve an entire worksheet part, given its name.

Microsoft Office PowerPoint Snippets

PowerPoint: Add comment
Add a comment to the first slide in a presentation.

PowerPoint: Delete all comments, by author
Delete all comments in a PowerPoint presentation for a specific author. Pass an empty string for the author name to delete all comments.

PowerPoint: Delete slide by title
Given a presentation and a slide title, delete the slide.

PowerPoint: Get List of Slide Titles
Given a presentation file, retrieve a generic list of strings containing the slide titles. Some slide titles might be empty strings.

PowerPoint: Get slide count
Given a file name, retrieve the number of slides in the presentation.

PowerPoint: Get slide index, by title
Find the zero-based index of a slide within a presentation, given its title.

PowerPoint: Reorder slides
Given a PPT deck, an original position, and a new position, attempt to place the slide in the original position into the new position within the deck.

PowerPoint: Replace image on slide
Given a presentation, a slide title, and an image file, replace the first image on the selected slide with the new image.

PowerPoint: Replace slide title
Given a presentation, a slide title, and a new slide title, find the slide, and modify its title.

Microsoft Office Word Snippets

Word: Accept all revisions
Given a document name and an author name, accept all revisions by the specified author. Pass an empty string for the author to accept all revisions.

Word: Add Table
Add a table, including text from an array, to the end of a document.

Word: Convert DOCM to DOCX
Convert a macro-enabled document to a standard document.

Word: Delete all comments
Given a document name and an author name, delete all comments by the specified author. Pass an empty string for the author to accept all revisions.

Word: Delete headers and footers
Delete headers and footers from a document.

Word: Delete hidden text
Delete hidden text from a document.

Word: Extract Styles
Extract the Styles part from a document, so you can insert it into another document.

Word: Get application property
Retrieve the value of an application property from a document.

Word: Get Content Control
Retrieve the markup for a specific content control.

Word: Get core property
Retrieve the value of a core property from a document.

Word: Get custom property
Retrieve a custom property for a document.

Word: Replace the styles part
Replace the entire styles part with a styles part extracted from another document.

Word: Retrieve comments
Retrieve all the comments from a document in an XDocument instance.

Word: Retrieve Table of Contents
Retrieve the table of contents markup, if it exists.

Word: Set application property
Given a document name, a property to set, and a value, update the document.

Word: Set core property
Set a core Word property (like Version, or LastModifiedBy).

Word: Set custom property
Given a document name, a property name/value, and the property type, add a custom property to a document.

Word: Set print orientation
Set the print orientation for each section in a document.

…”

Now that’s a good number of useful Open XML code snippets! Something for everyone…

The important point is that Office 2007 is does NOT have to be installed to use these. That’s the beauty of the OpenXML format (and ODF too, of course).