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



Last edited Jan 8, 2014 at 10:42 AM by asposemarketplace, version 1