StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So excel stores cell values either directly or if it's a string, in a common data structure known as a SharedString table.</p> <p>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. </p> <p>Also note that you would have to add null checks at every stage to determine if the Row/Column exists</p> <pre><code>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; } } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload