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); }
public IEnumerable <TableRow> TableRows() { string refStart = Ref.Split(':').First(); int rowStart = Int32.Parse(XlsxTables.SplitAddress(refStart)[1]); string refEnd = Ref.Split(':').ElementAt(1); int rowEnd = Int32.Parse(XlsxTables.SplitAddress(refEnd)[1]); int headerRowsCount = HeaderRowCount == null ? 0 : (int)HeaderRowCount; int totalRowsCount = TotalsRowCount == null ? 0 : (int)TotalsRowCount; return(Parent .Rows() .Skip(headerRowsCount) .SkipLast(totalRowsCount) .Where(r => { int rowId = Int32.Parse(r.RowId); return rowId >= rowStart && rowId <= rowEnd; } ) .Select(r => new TableRow(this) { Row = r })); }