You had me at "Hello World!"

Retrieving Information From Excel 2007 Files

Ever needed a sample code to programmatically extract data from Excel 2007 table?

Given an excel table like this (in book1.xlsx):

Here’s a function to retrieve the contents of the table:

/// <summary>

/// Retrieves an excel table from a given sheet in an xlsx file

/// </summary>

/// <param name="fileName">xlsx file name</param>

/// <param name="sheetName">Sheet name (e.g. "Sheet1"</param>

/// <returns>table[rows][cols] containing all the table information for the given sheet in the given file

/// Cells with no information are returned as null. The table starts at the first element containing data in the sheet.</returns>

public List<List<string>> XLGetTable(string fileName, string sheetName);

And a sample usage of the above function:

List<List<string>> table = XLGetTable(@"book1.xlsx", "Sheet1");

int rows, cols;

if (table.Count == 0) {

    rows = 0;

    cols = 0;

}

else {

    rows = table.Count;

    cols = table[0].Count;

}

Debug.WriteLine(String.Format("rows = {0}, cols = {1}", rows, cols));

for (int row = 0; row < rows; row++) {

    for (int col = 0; col < cols; col++) {

        Debug.Write(String.Format("{0} ({1}, {2})\t\t", table[row][col], row, col));

    }

    Debug.WriteLine(String.Empty);

}

Output for the above sample in the VS Debug Window:

rows = 4, cols = 3

Name (0, 0)       ID (0, 1)         Color (0, 2)           

Ball (1, 0)       1 (1, 1)          White (1, 2)           

Phone (2, 0)      2 (2, 1)          Green (2, 2)           

Cup (3, 0)        3 (3, 1)          Yellow (3, 2)    

See the attached ExcelHelper.cs for the full code of the above sample.

The sample is based on 2007 Office System Sample: Open XML File Format Code Snippets for Visual Studio 2005.

The Office 2007 files are using the Open XML file format. They are zip files with embedded data.

For more information check out the Ecma Office Open XML Formats architecture guide.

If you look inside an xlsx file (rename it to Zip and then open it) or uncomment the debugging code in the sample; you will see that the Excel data sheet is partially represented with XML similar to this one:

<?

You probably notice that there are no strings like “Ball”, “Phone” or “Cup” in the above XML file. That is because the strings are stored in a separate file to avoid redundancy. The sheet XML contains references to the actual strings.

Xlsx files can be read using functions from the System.IO.Packaging namespace. The attached code opens the xlsx file, retrieves the “main document part” - worksheet, looks for the given sheet name, and extracts the XML to construct the table information.

Published Sunday, February 18, 2007 5:43 PM by nikola