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

Posted by Blake on 5/11/2018
)

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