Wiki
Clone wikiAspose for OpenXML / Get a column heading
OpenXML Excel
using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.Text.RegularExpressions; static void Main(string[] args) { string docName = "Get a column heading.xlsx"; string worksheetName = "Sheet1"; string cellName = "B2"; string s1 = GetColumnHeading(docName, worksheetName, cellName); } // Given a document name, a worksheet name, and a cell name, gets the column of the cell and returns // the content of the first cell in that column. public static string GetColumnHeading(string docName, string worksheetName, string cellName) { // Open the document as read-only. using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, false)) { IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName); if (sheets.Count() == 0) { // The specified worksheet does not exist. return null; } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); // Get the column name for the specified cell. string columnName = GetColumnName(cellName); // Get the cells in the specified column and order them by row. IEnumerable<Cell> cells = worksheetPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnName(c.CellReference.Value), columnName, true) == 0) .OrderBy(r => GetRowIndex(r.CellReference)); if (cells.Count() == 0) { // The specified column does not exist. return null; } // Get the first cell in the column. Cell headCell = cells.First(); // If the content of the first cell is stored as a shared string, get the text of the first cell // from the SharedStringTablePart and return it. Otherwise, return the string value of the cell. if (headCell.DataType != null && headCell.DataType.Value == CellValues.SharedString) { SharedStringTablePart shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First(); SharedStringItem[] items = shareStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray(); return items[int.Parse(headCell.CellValue.Text)].InnerText; } else { return headCell.CellValue.Text; } } } // Given a cell name, parses the specified cell to get the column name. private static string GetColumnName(string cellName) { // Create a regular expression to match the column name portion of the cell name. Regex regex = new Regex("[A-Za-z]+"); Match match = regex.Match(cellName); return match.Value; } // Given a cell name, parses the specified cell to get the row index. private static uint GetRowIndex(string cellName) { // Create a regular expression to match the row index portion the cell name. Regex regex = new Regex(@"\d+"); Match match = regex.Match(cellName); return uint.Parse(match.Value); }
Aspose.Cells
using Aspose.Cells static void Main(string[] args) { string docName = "Get a column heading.xlsx"; string worksheetName = "Sheet1"; string cellName = "B2"; string s1 = GetColumnHeading(docName, worksheetName, cellName); } private static string GetColumnHeading(string docName, string worksheetName, string cellName) { //Instantiating a Workbook object Workbook workbook = new Workbook(docName); //Obtaining the reference of the worksheet by passing its Name Worksheet worksheet = workbook.Worksheets[worksheetName]; //Removing value from cell Cell Cell = worksheet.Cells[cellName]; //Get First row of the column string ColumnHeadingName = CellsHelper.CellIndexToName(0, Cell.Column); //return value of heading cell return worksheet.Cells[ColumnHeadingName].Value.ToString(); }
Download
Updated