Get excel cell value with Row and Column Position through open xml sdk linq query

Anyone know how to get an excel cell value wit开发者_高级运维h open XML SDK 2.0, knowing the row and column position e.g. (A2), through Linq?

So excel stores cell values either directly or if it's a string, in a common data structure known as a SharedString table.

In the sample below we first use linq to get the cell at a particular address and then pass that to another function to get the cell's value.

Also note that you would have to add null checks at every stage to determine if the Row/Column exists

using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace TestOpenXmlSDK
{
    class Program
    {
        static void Main(string[] args)
        {
            string pathSource = @"D:\sample.xlsx";
            using (FileStream fsSource = new FileStream(pathSource, FileMode.Open, FileAccess.Read))
            {
                byte[] bytes = new byte[fsSource.Length];
                fsSource.Read(bytes, 0, (int)fsSource.Length);
                using (MemoryStream mem = new MemoryStream())
                {
                    mem.Write(bytes, 0, (int)bytes.Length);
                    using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(mem, true))
                    {
                        var wbPart = excelDocument.WorkbookPart;
                        var wsPart = wbPart.WorksheetParts.First();
                        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();

                        var cellValue = GetCellValue(GetCell(sheetData, "B2"), wbPart);
                    }
                }
            }
        }

        public static Cell GetCell(SheetData sheetData, string cellAddress)
        {
            uint rowIndex = uint.Parse(Regex.Match(cellAddress, @"[0-9]+").Value);
            return sheetData.Descendants<Row>().FirstOrDefault(p => p.RowIndex == rowIndex).Descendants<Cell>().FirstOrDefault(p => p.CellReference == cellAddress);
        }

        public static string GetCellValue(Cell cell, WorkbookPart wbPart)
        {
            string value = cell.InnerText;
            if (cell.DataType != null)
            {
                switch (cell.DataType.Value)
                {
                    case CellValues.SharedString:
                        var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        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;
        }
    }
}

Simply copy paste this function to your program, and then call the Function by the example below.

(It will instantly bring your CellValue without opening Excel or anything in <1 sec)

// Example GetCellValue(@"c:\test.xlsx", "Sheet1", "A1");
// 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<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));

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

        // If the cell does not exist, return an empty string.
        if (theCell.InnerText.Length > 0)
        {
            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<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;
}

Reference: https://learn.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet