Read the contents of a spreadsheet with C# and OpenXml.


If you’ve found your way here you’re probably looking for an example of reading data out of a sheet or multiple sheets inside of an Excel spreadsheet.  In this example I’m going to use the NuGet Package “DocumentFormat.OpenXml” (version 2.8.1) loop through all of the data in an Excel spreadsheet.

To briefly explain, this snippit will loop through each sheet in a spreadsheet. On each sheet it will loop through all of the rows and all of the columns. This should give you the basic structure you would need to iterate over all of the contained data for whatever purpose you see fit. The examples I had found available as of the posting of this were for older versions of the OpenXml library and failed at one point or another.

C#

using (var excel = SpreadsheetDocument.Open(@"C:\Temp\template.xlsx", false))
{

    var sheets = excel.WorkbookPart.WorksheetParts;

    // Loop through each of the sheets in the spreadsheet
    foreach (var wp in sheets)
    {
        Worksheet worksheet = wp.Worksheet;

        // Loop through each of the rows in the current sheet
        var rows = worksheet.GetFirstChild<SheetData>().Elements<Row>();
        foreach (var row in rows)
        {
            // Loop through each of the cells in the current row.
            var cells = row.Elements<Cell>();
            foreach (var cell in cells)
            {
                // Here is where you would do something with the values of the spreadsheet.
                string str = cell.CellValue.Text;
            }
        }
    }

    excel.Close();
}

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.