Retrieve the values of cells in a spreadsheet document

  • OpenXML SDK
  • Spire.XLS
  • Download Sample Code

class Program
    {
        static void Main(string[] args)
        {
            const string fileName = @"..\..\Documents\Sheets13.xlsx";
            // Retrieve the value in cell A1.
            string value = GetCellValue(fileName, "Sheet1", "A1");
            Console.WriteLine(value);
            // Retrieve the date value in cell A2.
            value = GetCellValue(fileName, "Sheet1", "A2");
            Console.WriteLine(DateTime.FromOADate(double.Parse(value)).ToShortDateString());
            Console.ReadLine();
        }
        // Retrieve the value of a cell, given a file name, sheet name, 
        // and address name.
        public static string GetCellValue(string fileName,
            string sheetName,
            string addressName)
        {
            string value = null;

            // 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;

                // 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().
                  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));

                // Use its Worksheet property to get a reference to the cell 
                // whose address matches the address you supplied.
                Cell theCell = wsPart.Worksheet.Descendants().
                  Where(c => c.CellReference == addressName).FirstOrDefault();

                // If the cell does not exist, return an empty string.
                if (theCell != null)
                {
                    value = theCell.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 (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case CellValues.SharedString:

                                // For shared strings, look up the value in the
                                // shared strings table.
                                var stringTable =
                                    wbPart.GetPartsOfType()
                                    .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;
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            const string fileName = @"..\..\Documents\Sheets13.xlsx";
            // Retrieve the value in cell A1.
            string value = GetCellValue(fileName, "Sheet1", "A1");
            Console.WriteLine(value);
            value = GetCellValue(fileName, "Sheet1", "A2");
            Console.WriteLine(DateTime.FromOADate(double.Parse(value)).ToShortDateString());
            Console.ReadKey();
        }
        public static string GetCellValue(string fileName, string sheetName, string addressName)
        {
            //Initialize a new Workboook object
            Workbook workbook = new Workbook();

            //Load the document
            workbook.LoadFromFile(fileName);

            //Get the worksheet by sheetName
            Worksheet sheet = workbook.Worksheets[sheetName];

            //Return the value from the cell
            string value = sheet.Range[addressName].Value.ToString();

            return value;
        }
    }