public TableCell this[string columnName] { get { TableColumn tc = Parent .TableColumns() .Where(x => x.Name.ToLower() == columnName.ToLower()) .FirstOrDefault(); if (tc == null) { throw new Exception("Invalid column name: " + columnName); } string[] refs = Parent.Ref.Split(':'); string[] startRefs = XlsxTables.SplitAddress(refs[0]); string columnAddress = XlsxTables.IndexToColumnAddress(XlsxTables.ColumnAddressToIndex(startRefs[0]) + tc.ColumnIndex); Cell cell = Row.Cells().Where(c => c.ColumnAddress == columnAddress).FirstOrDefault(); if (cell != null) { if (cell.Type == "s") { return(new TableCell(cell.SharedString)); } else { return(new TableCell(cell.Value)); } } else { return(new TableCell("")); } } }
public static void ParseRange(string theRef, out int leftColumn, out int topRow, out int rightColumn, out int bottomRow) { // C5:E7 var spl = theRef.Split(':'); string refStart = spl.First(); var refStartSplit = XlsxTables.SplitAddress(refStart); leftColumn = XlsxTables.ColumnAddressToIndex(refStartSplit[0]); topRow = Int32.Parse(refStartSplit[1]); string refEnd = spl.ElementAt(1); var refEndSplit = XlsxTables.SplitAddress(refEnd); rightColumn = XlsxTables.ColumnAddressToIndex(refEndSplit[0]); bottomRow = Int32.Parse(refEndSplit[1]); }
public List <Cell> Cells() { XNamespace s = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"; SpreadsheetDocument doc = (SpreadsheetDocument)Parent.OpenXmlPackage; SharedStringTablePart sharedStringTable = doc.WorkbookPart.SharedStringTablePart; IEnumerable <XElement> cells = this.RowElement.Elements(S.c); var r = cells .Select(cell => { var cellType = (string)cell.Attribute("t"); var sharedString = cellType == "s" ? sharedStringTable .GetXDocument() .Root .Elements(s + "si") .Skip((int)cell.Element(s + "v")) .First() .Descendants(s + "t") .StringConcatenate(e => (string)e) : null; var column = (string)cell.Attribute("r"); var columnAddress = column.Split('0', '1', '2', '3', '4', '5', '6', '7', '8', '9').First(); var columnIndex = XlsxTables.ColumnAddressToIndex(columnAddress); var newCell = new Cell(this) { CellElement = cell, Row = (string)RowElement.Attribute("r"), Column = column, ColumnAddress = columnAddress, ColumnIndex = columnIndex, Type = cellType, Formula = (string)cell.Element(S.f), Style = (int?)cell.Attribute("s"), Value = (string)cell.Element(S.v), SharedString = sharedString }; return(newCell); }); var ra = r.ToList(); return(ra); }