C# – Retrieve all cell values in each row and column with Open XML SDK 2.0

cexcelopenxml-sdkvisual studio 2010

I am using the Open XML SDK to open an Excel file to retrieve the cell values from all of the rows and columns that contain data within the worksheet. Here is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ReadingExcel
{
    class RetrieveListOfExcelValues
    {
        static void Main(string[] args)
        {    
            String fileName = @"C:\Users\Documents\TestReadingExcel.xlsx";
            // Comment one of the following lines to test the method separately.
            ReadExcelFileDOM(fileName);   // DOM
        }  //end Main

        static void ReadExcelFileDOM(string fileName)
        {    
            // Open the spreadsheet document for read-only access.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
            {    
                // Retrieve a reference to the workbook part.
                WorkbookPart wbPart = document.WorkbookPart;
                //Worksheet name that is the the workbook
                string sheetName = "Sheet1";
                // Find the sheet with the supplied name, and then use that
                // Sheet object to retrieve a reference to the first worksheet.
                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
                Where(s => s.Name == sheetName).FirstOrDefault();

                // Throw an exception if there is no sheet.
                if (theSheet == null)
                {    
                    throw new ArgumentException("sheetName");
                }

                // Retrieve a reference to the worksheet part.
                WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
                string parseCellValue;
                foreach (Row r in theSheet.Elements<Row>())
                {    
                    foreach (Cell c in r.Elements<Cell>())
                    {    
                        Cell theCell = wsPart.Worksheet.Descendants<Cell>().FirstOrDefault();
                        parseCellValue = GetCellValue(theCell, wbPart);

                        Console.Write(parseCellValue + " ");
                        //text = c.CellValue.Text;
                        //Console.Write(text + " ");
                    }    
                }

                Console.WriteLine();
                Console.ReadKey();
            }    
        }    //end ReadExcelFileDOMMethod

        public static string GetCellValue(Cell cell, WorkbookPart wbPart)
        {    
            string value = null;
            if (cell != null)
            {    
                value = cell.InnerText;

                // If the cell represents an integer number, you are done.
                // For dates, this code returns the serialized value that
                // represents the date. The code handles strings and
                // Booleans individually. For shared strings, the code
                // looks up the corresponding value in the shared string
                // table. For Booleans, the code converts the value into
                // the words TRUE or FALSE.
                if (cell.DataType != null)
                {    
                    switch (cell.DataType.Value)
                    {    
                        case CellValues.SharedString:
                            // For shared strings, look up the value in the
                            // shared strings table.
                            var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>()
                                .FirstOrDefault();

                            // If the shared string table is missing, something
                            // is wrong. Return the index that is in
                            // the cell. Otherwise, look up the correct text in
                            // the table.
                            if (stringTable != null)
                            {    
                                value = stringTable.SharedStringTable.ElementAt(
                                     int.Parse(value)).InnerText;
                            }

                            break;
                        case CellValues.Boolean:
                            switch (value)
                            {    
                                case "0":
                                    value =

                                    "FALSE";
                                    break;
                                default:
                                    value =

                                    "TRUE";
                                    break;
                            }    
                            break;
                    }    
                }    
            }    
            return value;
        }  
    }
}

The text does not display on the console and I cannot find out exactly what I am doing wrong in this code. This is my first attempt at using Open XML. I may be overlooking something or few lines of code might need to be moved around.

How I can get the all of the cell values from the rows and columns within the worksheet to display on the console?

Excel Format

A1        B1          C1

Fruit    Lot         Date

Banana      4          4/13/2014

Orange      6          5/01/2014

Apple       9          3/14/2014

Best Answer

value = cell.Cellvalue.InnerText;

Give this a try

Related Topic