OpenXml - how to loop through all cells in Excel - Including empty cells which are ignored by OpenXML

Watch this example on YouTube:



You can copy and paste code below to see how to loop through Excel doc, all explanations in video above.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;


namespace WebApplication1
{
    public partial class OpenXML___Looping_Through_Empty_Cells : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        private string GetColumnLetter(int columnNumber)
        {
            string LetterName = string.Empty;
            if (columnNumber < 25)
            {
                LetterName = ((char)(columnNumber + 66)).ToString();
            }
            else if (columnNumber < 51)
            {
                LetterName = "A" + ((char)(columnNumber + 40)).ToString();
            }
            return LetterName;
        }
        private string GetCellValue(WorkbookPart workBookPart, string sheetName, string addressName)
        {
            string value = null;
            Sheet mySheet = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
            if (mySheet == null)
            {
                throw new ArgumentException("cannot find the following sheet: " + mySheet);
            }
            WorksheetPart wsPart = (WorksheetPart)(workBookPart.GetPartById(mySheet.Id));
            Cell myCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
            if (myCell != null)
            {
                value = myCell.InnerText;
                if (myCell.DataType != null)
                {
                    switch (myCell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            var stringTable = workBookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                            }
                            if (stringTable == null)
                            {
                                value = "";
                            }
                            break;
                        case CellValues.Boolean:
                            switch (value)
                            {
                                case "0":
                                    value = "FALSE";
                                    break;
                                case "1":
                                    value = "TRUE";
                                    break;
                            }
                            break;
                    }
                }
            }
            return value;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open("C:\\Temp\\ExcelTest\\testexcel.xlsx", false))
            {
                string cellValue;
                int columnNumber = -1;
                int rowNumber = 0;

                WorkbookPart workBookPart = spreadSheetDocument.WorkbookPart;
                Sheet workSheet = workBookPart.Workbook.Descendants<Sheet>().Where(s =>
                    s.Name.Value.Equals("Sheet1")).FirstOrDefault();
                WorksheetPart workSheetPart = (WorksheetPart)(workBookPart.GetPartById(workSheet.Id));
                SheetData sheetData = workSheetPart.Worksheet.Elements<SheetData>().First();
                foreach (Row r in sheetData.Elements<Row>())
                {
                    rowNumber++;
                    columnNumber = -1;
                    foreach (Cell c in r.Elements<Cell>())
                    {
                        string letter;
                        letter = GetColumnLetter(columnNumber);

                        while (c.CellReference.ToString().Substring(0, letter.Length) != letter)
                        {
                            columnNumber++;
                            letter = GetColumnLetter(columnNumber);
                        }

                        cellValue = GetCellValue(workBookPart, "Sheet1", c.CellReference);
                        switch (letter)
                        {
                            case "A":
                                ListBox1.Items.Add(new ListItem("A" + rowNumber.ToString() + ": " + cellValue));
                                break;
                            case "B":
                                ListBox1.Items.Add(new ListItem("B" + rowNumber.ToString() + ": " + cellValue));
                                break;
                            case "C":
                                ListBox1.Items.Add(new ListItem("C" + rowNumber.ToString() + ": " + cellValue));
                                break;
                            default:
                                break;
                        }
                        columnNumber++;
                    }
                }
            }
        }
    }
}