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);
}
public static XElement RetrieveSheet(SpreadsheetDocument sDoc, string sheetName)
{
var wbXdoc = sDoc.WorkbookPart.GetXDocument();
var sheet = wbXdoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.FirstOrDefault(s => (string)s.Attribute("name") == sheetName);
if (sheet == null)
{
throw new ArgumentException("Invalid sheet name passed to RetrieveSheet", nameof(sheetName));
}
var range = "A1:XFD1048576";
XlsxTables.ParseRange(range, out var leftColumn, out var topRow, out var rightColumn, out var bottomRow);
return(RetrieveRange(sDoc, sheetName, leftColumn, topRow, rightColumn, bottomRow));
}
public List <Cell> Cells()
{
XNamespace s = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
var doc = (SpreadsheetDocument)Parent.OpenXmlPackage;
var sharedStringTable = doc.WorkbookPart.SharedStringTablePart;
var cells = 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()
{
var refStart = Ref.Split(':').First();
var rowStart = int.Parse(XlsxTables.SplitAddress(refStart)[1]);
var refEnd = Ref.Split(':').ElementAt(1);
var rowEnd = int.Parse(XlsxTables.SplitAddress(refEnd)[1]);
var headerRowsCount = HeaderRowCount == null ? 0 : (int)HeaderRowCount;
var totalRowsCount = TotalsRowCount == null ? 0 : (int)TotalsRowCount;
return(Parent
.Rows()
.Skip(headerRowsCount)
.PtSkipLast(totalRowsCount)
.Where(r =>
{
var rowId = int.Parse(r.RowId);
return rowId >= rowStart && rowId <= rowEnd;
}
)
.Select(r => new TableRow(this)
{
Row = r
}));
}
public static XElement RetrieveRange(SpreadsheetDocument sDoc, string sheetName, int leftColumn, int topRow, int rightColumn, int bottomRow)
{
var wbXdoc = sDoc.WorkbookPart.GetXDocument();
var sheet = wbXdoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.FirstOrDefault(s => (string)s.Attribute("name") == sheetName);
if (sheet == null)
{
throw new ArgumentException("Invalid sheet name passed to RetrieveRange", nameof(sheetName));
}
var rId = (string)sheet.Attribute(R.id);
if (rId == null)
{
throw new FileFormatException("Invalid spreadsheet");
}
var sheetPart = sDoc.WorkbookPart.GetPartById(rId);
if (sheetPart == null)
{
throw new FileFormatException("Invalid spreadsheet");
}
var shXDoc = sheetPart.GetXDocument();
if (sDoc.WorkbookPart.WorkbookStylesPart == null)
{
throw new FileFormatException("Invalid spreadsheet. No WorkbookStylesPart.");
}
var styleXDoc = sDoc.WorkbookPart.WorkbookStylesPart.GetXDocument();
// if there is no shared string table, sharedStringTable will be null
// it will only be used if there is a cell type == "s", in which case, referencing this
// part would indicate an invalid spreadsheet.
var sharedStringTable = sDoc.WorkbookPart.SharedStringTablePart;
FixUpCellsThatHaveNoRAtt(shXDoc);
// assemble the transform
var sheetData = shXDoc
.Root
.Elements(S.sheetData)
.Elements(S.row)
.Select(row =>
{
// filter
var ra = (string)row.Attribute("r");
if (ra == null)
{
return(null);
}
if (!int.TryParse(ra, out var rowNbr))
{
return(null);
}
if (rowNbr < topRow)
{
return(null);
}
if (rowNbr > bottomRow)
{
return(null);
}
var cells = row
.Elements(S.c)
.Select(cell =>
{
var cellAddress = (string)cell.Attribute("r");
if (cellAddress == null)
{
throw new FileFormatException("Invalid spreadsheet - cell does not have r attribute.");
}
var splitCellAddress = XlsxTables.SplitAddress(cellAddress);
var columnAddress = splitCellAddress[0];
var columnIndex = XlsxTables.ColumnAddressToIndex(columnAddress);
// filter
if (columnIndex < leftColumn || columnIndex > rightColumn)
{
return(null);
}
var cellType = (string)cell.Attribute("t");
string?sharedString = null;
if (cellType == "s")
{
var sharedStringBeforeParsing = (string)cell.Element(S.v);
if (sharedStringBeforeParsing == null)
{
sharedStringBeforeParsing = (string)cell.Elements(S._is).Elements(S.t).FirstOrDefault();
}
if (sharedStringBeforeParsing == null)
{
throw new FileFormatException("Invalid document");
}
if (!int.TryParse(sharedStringBeforeParsing, out var sharedStringIndex))
{
throw new FileFormatException("Invalid document");
}
XElement?sharedStringElement = null;
if (sharedStringTable == null)
{
throw new FileFormatException("Invalid spreadsheet. Shared string, but no Shared String Part.");
}
sharedStringElement =
sharedStringTable
.GetXDocument()
.Root
.Elements(S.si)
.Skip(sharedStringIndex)
.FirstOrDefault();
if (sharedStringElement == null)
{
throw new FileFormatException("Invalid spreadsheet. Shared string reference not valid.");
}
sharedString =
sharedStringElement
.Descendants(S.t)
.StringConcatenate(e => (string)e);
}
if (sharedString != null)
{
var cellProps = GetCellProps_NotInTable(styleXDoc, cell);
var value = sharedString;
string displayValue;
string?color = null;
if (cellProps != null)
{
displayValue = SmlCellFormatter.FormatCell(
(string)cellProps.Attribute("formatCode"),
value,
out color);
}
else
{
displayValue = value;
}
var newCell1 = new XElement("Cell",
new XAttribute("Ref", (string)cell.Attribute("r")),
new XAttribute("ColumnId", columnAddress),
new XAttribute("ColumnNumber", columnIndex),
cell.Attribute("f") != null ? new XAttribute("Formula", (string)cell.Attribute("f")) : null,
cell.Attribute("s") != null ? new XAttribute("Style", (string)cell.Attribute("s")) : null,
cell.Attribute("t") != null ? new XAttribute("Type", (string)cell.Attribute("t")) : null,
cellProps,
new XElement("Value", value),
new XElement("DisplayValue", displayValue),
color != null ? new XElement("DisplayColor", color) : null
);
return(newCell1);
}
else
{
var type = (string)cell.Attribute("t");
var value = new XElement("Value", cell.Value);
if (type != null && type == "inlineStr")
{
type = "s";
}
XAttribute?typeAttr = null;
if (type != null)
{
typeAttr = new XAttribute("Type", type);
}
var cellProps = GetCellProps_NotInTable(styleXDoc, cell);
string displayValue;
string?color = null;
if (cellProps != null)
{
displayValue = SmlCellFormatter.FormatCell(
(string)cellProps.Attribute("formatCode"),
cell.Value,
out color);
}
else
{
displayValue = displayValue = SmlCellFormatter.FormatCell(
"General",
cell.Value,
out color);
}
var newCell2 = new XElement("Cell",
new XAttribute("Ref", (string)cell.Attribute("r")),
new XAttribute("ColumnId", columnAddress),
new XAttribute("ColumnNumber", columnIndex),
typeAttr,
cell.Attribute("f") != null ? new XAttribute("Formula", (string)cell.Attribute("f")) : null,
cell.Attribute("s") != null ? new XAttribute("Style", (string)cell.Attribute("s")) : null,
cellProps,
value,
new XElement("DisplayValue", displayValue),
color != null ? new XElement("DisplayColor", color) : null);
return(newCell2);
}
});
var dataRow = new XElement("Row",
row.Attribute("r") != null ? new XAttribute("RowNumber", (int)row.Attribute("r")) : null,
cells);
return(dataRow);
});
var dataProps = GetDataProps(shXDoc);
var data = new XElement("Data",
dataProps,
sheetData);
return(data);
}
private static XElement?GetCellProps_InTable(XDocument styleXDoc, Table table, Cell tc)
{
var style = tc.Style;
if (style == null)
{
return(null);
}
var colIdStr = tc.ColumnAddress;
var colNbr = XlsxTables.ColumnAddressToIndex(colIdStr);
var column = table.TableColumns().FirstOrDefault(z => z.ColumnNumber == colNbr);
if (column == null)
{
throw new FileFormatException("Invalid spreadsheet");
}
var cellProps = new XElement("CellProps");
var d = column.DataDxfId;
if (d != null)
{
var dataDxf = styleXDoc
.Root
.Elements(S.dxfs)
.Elements(S.dxf)
.Skip((int)d)
.FirstOrDefault();
if (dataDxf == null)
{
throw new FileFormatException("Invalid spreadsheet");
}
var numFmt = dataDxf.Element(S.numFmt);
if (numFmt != null)
{
var numFmtId = (int?)numFmt.Attribute("numFmtId");
if (numFmtId != null)
{
cellProps.Add(new XAttribute("numFmtId", numFmtId));
}
var formatCode = (string)numFmt.Attribute("formatCode");
if (formatCode != null)
{
cellProps.Add(new XAttribute("formatCode", formatCode));
}
}
}
var xf = styleXDoc.Root
.Elements(S.cellXfs)
.Elements(S.xf)
.Skip((int)style)
.FirstOrDefault();
if (xf == null)
{
throw new FileFormatException("Invalid spreadsheet");
}
// if xf has different numFmtId, then replace the ones from the table definition
var numFmtId2 = (int?)xf.Attribute("numFmtId");
if (numFmtId2 != null)
{
AddNumFmtIdAndFormatCode(styleXDoc, cellProps, numFmtId2);
}
var masterXfId = (int?)xf.Attribute("xfId");
if (masterXfId != null)
{
var masterXf = styleXDoc
.Root
.Elements(S.cellStyleXfs)
.Elements(S.xf)
.Skip((int)masterXfId)
.FirstOrDefault();
if (masterXf != null)
{
AddFormattingToCellProps(styleXDoc, cellProps, masterXf);
}
}
AddFormattingToCellProps(styleXDoc, cellProps, xf);
AugmentAndCleanUpProps(cellProps);
if (!cellProps.HasElements && !cellProps.HasAttributes)
{
return(null);
}
return(cellProps);
}
public static XElement RetrieveRange(SpreadsheetDocument sDoc, string sheetName, string range)
{
XlsxTables.ParseRange(range, out var leftColumn, out var topRow, out var rightColumn, out var bottomRow);
return(RetrieveRange(sDoc, sheetName, leftColumn, topRow, rightColumn, bottomRow));
}
public static XElement RetrieveTable(SpreadsheetDocument sDoc, string tableName)
{
var table = sDoc.Table(tableName);
if (table == null)
{
throw new ArgumentException("Table not found", nameof(tableName));
}
var styleXDoc = sDoc.WorkbookPart.WorkbookStylesPart.GetXDocument();
var r = table.Ref;
XlsxTables.ParseRange(r, out var leftColumn, out var topRow, out var rightColumn, out var bottomRow);
var shXDoc = table.Parent.GetXDocument();
FixUpCellsThatHaveNoRAtt(shXDoc);
// assemble the transform
var columns = new XElement("Columns",
table.TableColumns().Select(tc =>
{
var colXElement = new XElement("Column",
tc.Name != null ? new XAttribute("Name", tc.Name) : null,
tc.UniqueName != null ? new XAttribute("UniqueName", tc.UniqueName) : null,
new XAttribute("ColumnIndex", tc.ColumnIndex),
new XAttribute("Id", tc.Id),
tc.DataDxfId != null ? new XAttribute("DataDxfId", tc.DataDxfId) : null,
tc.QueryTableFieldId != null ? new XAttribute("QueryTableFieldId", tc.QueryTableFieldId) : null);
return(colXElement);
}));
var dataProps = GetDataProps(shXDoc);
var data = new XElement("Data",
dataProps,
table.TableRows().Select(tr =>
{
if (!int.TryParse(tr.Row.RowId, out var rowRef))
{
throw new FileFormatException("Invalid spreadsheet");
}
// filter
if (rowRef < topRow || rowRef > bottomRow)
{
return(null);
}
var cellData = tr.Row.Cells().Select(tc =>
{
// filter
var columnIndex = tc.ColumnIndex;
if (columnIndex < leftColumn || columnIndex > rightColumn)
{
return(null);
}
var cellProps = GetCellProps_InTable(styleXDoc, table, tc);
if (tc.SharedString != null)
{
string displayValue;
string?color = null;
if (cellProps != null)
{
displayValue = SmlCellFormatter.FormatCell(
(string)cellProps.Attribute("formatCode"),
tc.SharedString,
out color);
}
else
{
displayValue = tc.SharedString;
}
var newCell1 = new XElement("Cell",
tc.CellElement != null ? new XAttribute("Ref", (string)tc.CellElement.Attribute("r")) : null,
tc.ColumnAddress != null ? new XAttribute("ColumnId", tc.ColumnAddress) : null,
new XAttribute("ColumnNumber", tc.ColumnIndex),
tc.Type != null ? new XAttribute("Type", "s") : null,
tc.Formula != null ? new XAttribute("Formula", tc.Formula) : null,
tc.Style != null ? new XAttribute("Style", tc.Style) : null,
cellProps,
new XElement("Value", tc.SharedString),
new XElement("DisplayValue", displayValue),
color != null ? new XElement("DisplayColor", color) : null);
return(newCell1);
}
else
{
XAttribute?type = null;
if (tc.Type != null)
{
if (tc.Type == "inlineStr")
{
type = new XAttribute("Type", "s");
}
else
{
type = new XAttribute("Type", tc.Type);
}
}
string displayValue;
string?color = null;
if (cellProps != null)
{
displayValue = SmlCellFormatter.FormatCell(
(string)cellProps.Attribute("formatCode"),
tc.Value,
out color);
}
else
{
displayValue = SmlCellFormatter.FormatCell(
"General",
tc.Value,
out color);
}
var newCell = new XElement("Cell",
tc.CellElement != null ? new XAttribute("Ref", (string)tc.CellElement.Attribute("r")) : null,
tc.ColumnAddress != null ? new XAttribute("ColumnId", tc.ColumnAddress) : null,
new XAttribute("ColumnNumber", tc.ColumnIndex),
type,
tc.Formula != null ? new XAttribute("Formula", tc.Formula) : null,
tc.Style != null ? new XAttribute("Style", tc.Style) : null,
cellProps,
new XElement("Value", tc.Value),
new XElement("DisplayValue", displayValue),
color != null ? new XElement("DisplayColor", color) : null);
return(newCell);
}
});
var rowProps = GetRowProps(tr.Row.RowElement);
var newRow = new XElement("Row",
rowProps,
new XAttribute("RowNumber", tr.Row.RowId),
cellData);
return(newRow);
}));
var tableProps = GetTableProps(table);
var tableXml = new XElement("Table",
tableProps,
table.TableName != null ? new XAttribute("TableName", table.TableName) : null,
table.DisplayName != null ? new XAttribute("DisplayName", table.DisplayName) : null,
table.Ref != null ? new XAttribute("Ref", table.Ref) : null,
table.HeaderRowCount != null ? new XAttribute("HeaderRowCount", table.HeaderRowCount) : null,
table.TotalsRowCount != null ? new XAttribute("TotalsRowCount", table.TotalsRowCount) : null,
columns,
data);
return(tableXml);
}
