public TableCell this[string columnName] { get { var tc = Parent .TableColumns() .FirstOrDefault(x => x.Name.ToLower() == columnName.ToLower()); if (tc == null) { throw new Exception("Invalid column name: " + columnName); } var refs = Parent.Ref.Split(':'); var startRefs = XlsxTables.SplitAddress(refs[0]); var columnAddress = XlsxTables.IndexToColumnAddress(XlsxTables.ColumnAddressToIndex(startRefs[0]) + tc.ColumnIndex); var cell = Row.Cells().FirstOrDefault(c => c.ColumnAddress == columnAddress); if (cell != null) { if (cell.Type == "s") { return(new TableCell(cell.SharedString)); } else { return(new TableCell(cell.Value)); } } else { return(new TableCell("")); } } }
// Sometimes encounter cells that have no r attribute, so infer it if possible. // These are invalid spreadsheets, but attempt to get the data anyway. private static void FixUpCellsThatHaveNoRAtt(XDocument shXDoc) { // if there are any rows that have all cells with no r attribute, then fix them up var invalidRows = shXDoc .Descendants(S.row) .Where(r => !r.Elements(S.c).Any(c => c.Attribute("r") != null)) .ToList(); foreach (var row in invalidRows) { var rowNumberStr = (string)row.Attribute("r"); var colNumber = 0; foreach (var cell in row.Elements(S.c)) { var newCellRef = XlsxTables.IndexToColumnAddress(colNumber) + rowNumberStr; cell.Add(new XAttribute("r", newCellRef)); } } // repeat iteratively until no further fixes can be made while (true) { var invalidCells = shXDoc .Descendants(S.c) .Where(c => c.Attribute("r") == null) .ToList(); var didFixup = false; foreach (var cell in invalidCells) { var followingCell = cell.ElementsAfterSelf(S.c).FirstOrDefault(); if (followingCell != null) { var followingR = (string)followingCell.Attribute("r"); if (followingR != null) { var spl = XlsxTables.SplitAddress(followingR); var colIdxFollowing = XlsxTables.ColumnAddressToIndex(spl[0]); var newRef = XlsxTables.IndexToColumnAddress(colIdxFollowing - 1) + spl[1]; cell.Add(new XAttribute("r", newRef)); didFixup = true; } else { didFixup = FixUpBasedOnPrecedingCell(didFixup, cell); } } else { didFixup = FixUpBasedOnPrecedingCell(didFixup, cell); } } if (!didFixup) { break; } } }
private static bool FixUpBasedOnPrecedingCell(bool didFixup, XElement cell) { var precedingCell = GetPrevousElement(cell); if (precedingCell != null) { var precedingR = (string)precedingCell.Attribute("r"); if (precedingR != null) { var spl = XlsxTables.SplitAddress(precedingR); var colIdxFollowing = XlsxTables.ColumnAddressToIndex(spl[0]); var newRef = XlsxTables.IndexToColumnAddress(colIdxFollowing + 1) + spl[1]; cell.Add(new XAttribute("r", newRef)); didFixup = true; } } return(didFixup); }