Get excel cell value using row and column position through open xml sdk linq query

Question

Does anyone know how to get the value of an excel cell with an open XML SDK 2.0, knowing the position of the row and column, for example. (A2), via Linq?

Answer 1

In this way, excel stores the cell values ​​directly or if it is a row, in a common data structure known as the SharedString table.

In the example below, we first use linq to get the cell at a specific address, and then pass it to another function to get the cell value.

Also note that you will need to add null checks at each step to determine if a 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; } } } 

Answer 2

Just copy and paste this function into your program, and then call the Function as in the example below.

(It will instantly display your CellValue without opening Excel or anything in & lt; 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; } 

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