Reading and writing to Excel 2007 or Excel 2010 from C# - Part IV: Putting it together

Published on 19 August 2009 • Estimated reading time: 3 minutes

[Note: See the series index for a list of all parts in this series.]

In part III, we looked at the interesting part of Excel—Shared Strings—which is just a central store for unique values that the actual spreadsheet cells can map to. Now, how do we take that data and combine it with the sheet to get the values?

What makes up a sheet?

First, let's look at what a sheet looks like in the package:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2008/2/ac">
    <dimension ref="A1:A4" />
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="A5" sqref="A5" />
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25" />
    <sheetData>
        <row r="1" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A1" t="s">
                <v>0</v>
            </c>
        </row>
        <row r="2" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A2" t="s">
                <v>1</v>
            </c>
        </row>
        <row r="3" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A3" t="s">
                <v>2</v>
            </c>
        </row>
        <row r="4" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A4" t="s">
                <v>3</v>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
</worksheet>

Well, there’s a lot to understand in the XML, but for now we care about the <row> (which represents the rows in our spreadsheet) and within that, the cells, which the first one looks like this:

<c r="A1" t="s">
    <v>0</v>
</c>

First, that t="s" attribute is very important—it tells us the value is stored in the shared strings. Then, the index to the shared string is in the <v> node; in this example, it is index 0. It is also important to note that the r attribute for both rows and cells contains the position in the sheet.

As an aside, what would this look like if we didn’t use shared strings?

<c r="A1">
    <v>Some</v>
</c>

Now, the <v> node contains the actual value, and we no longer have the t attribute on the <c> node.

The foundation code for parsing the data

Now that we understand the structure—and we have this Dictionary<int, string> that contains the shared strings—we can combine them. But first, we need a class to store the data in, then we need to locate the correct worksheet, and a way to parse the column and row info—once we have that, we can parse the data.

Before we read the data, we need a simple class to hold the information:

public class Cell
{
    public Cell(string column, int row, string data)
    {
        this.Column = column;
        this.Row = row;
        this.Data = data;
    }

    public override string ToString()
    {
        return string.Format("{0}:{1} - {2}", Column, Row, Data);
    }

    public string Column { get; set; }
    public int Row { get; set; }
    public string Data { get; set; }
}

How do we find the right worksheet? In the same way as we did to get the shared strings...