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