public static XElement GetTableInfoForSheet(SpreadsheetDocument spreadsheetDocument, WorksheetPart sheetPart, string sheetName,
MetricsGetterSettings settings)
{
var xd = sheetPart.GetXDocument();
XElement sheetInformation = new XElement(H.Sheet,
new XAttribute(H.Name, sheetName),
xd.Root.Elements(S.tableParts).Elements(S.tablePart).Select(tp =>
{
string rId = (string)tp.Attribute(R.id);
TableDefinitionPart tablePart = (TableDefinitionPart)sheetPart.GetPartById(rId);
var txd = tablePart.GetXDocument();
var tableName = (string)txd.Root.Attribute("displayName");
XElement tableCellData = null;
if (settings.IncludeXlsxTableCellData)
{
var xlsxTable = spreadsheetDocument.Table(tableName);
tableCellData = new XElement(H.TableData,
xlsxTable.TableRows()
.Select(row =>
{
var rowElement = new XElement(H.Row,
xlsxTable.TableColumns().Select(col =>
{
var cellElement = new XElement(H.Cell,
new XAttribute(H.Name, col.Name),
new XAttribute(H.Val, (string)row[col.Name]));
return cellElement;
}));
return rowElement;
}));
}
var table = new XElement(H.Table,
new XAttribute(H.Name, (string)txd.Root.Attribute("name")),
new XAttribute(H.DisplayName, tableName),
new XElement(H.Columns,
txd.Root.Element(S.tableColumns).Elements(S.tableColumn)
.Select(tc => new XElement(H.Column,
new XAttribute(H.Name, (string)tc.Attribute("name"))))),
tableCellData
);
return table;
})
);
if (!sheetInformation.HasElements)
return null;
return sheetInformation;
}
public static XElement RetrieveTable(SpreadsheetDocument sDoc, string tableName)
{
var table = sDoc.Table(tableName);
if (table == null)
{
throw new ArgumentException("Table not found", "tableName");
}
var styleXDoc = sDoc.WorkbookPart.WorkbookStylesPart.GetXDocument();
var r = table.Ref;
int leftColumn, topRow, rightColumn, bottomRow;
XlsxTables.ParseRange(r, out leftColumn, out topRow, out rightColumn, out 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 =>
{
int rowRef;
if (!int.TryParse(tr.Row.RowId, out 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);
}
XElement cellProps = GetCellProps_InTable(sDoc, 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;
}
XElement 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(
(string)"General",
tc.Value,
out color);
}
XElement 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);
}));
XElement 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);
}