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);
}
