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;
}
// Copy all cells in the specified range to a new location
public static void CopyCellRange(SpreadsheetDocument document, WorksheetPart worksheet, int startRow, int startColumn, int endRow, int endColumn,
int toRow, int toColumn)
{
int rowOffset = toRow - startRow;
int columnOffset = toColumn - startColumn;
XDocument worksheetXDocument = worksheet.GetXDocument();
for (int row = startRow; row <= endRow; row++)
for (int column = startColumn; column <= endColumn; column++)
{
XElement oldCell = GetCell(worksheetXDocument, column, row);
if (oldCell != null)
{
XElement newCell = new XElement(oldCell);
newCell.SetAttributeValue(NoNamespace.r, GetColumnId(column + columnOffset) + (row + rowOffset).ToString());
XElement formula = newCell.Element(S.f);
if (formula != null)
{
ParseFormula parser = new ParseFormula(formula.Value);
formula.SetValue(parser.ReplaceRelativeCell(rowOffset, columnOffset));
}
SetCell(worksheetXDocument, newCell);
}
}
worksheet.PutXDocument();
ForceCalculateOnLoad(document);
}
// Gets the value of the specified cell
// Returned object can be double/Double, int/Int32, bool/Boolean or string/String types
public static object GetCellValue(SpreadsheetDocument document, WorksheetPart worksheet, int column, int row)
{
XDocument worksheetXDocument = worksheet.GetXDocument();
XElement cellValue = GetCell(worksheetXDocument, column, row);
if (cellValue != null)
{
if (cellValue.Attribute(NoNamespace.t) == null)
{
string value = cellValue.Element(S.v).Value;
if (value.Contains("."))
return Convert.ToDouble(value);
return Convert.ToInt32(value);
}
switch (cellValue.Attribute(NoNamespace.t).Value)
{
case "b":
return (cellValue.Element(S.v).Value == "1");
case "s":
return GetSharedString(document, System.Convert.ToInt32(cellValue.Element(S.v).Value));
case "inlineStr":
return cellValue.Element(S._is).Element(S.t).Value;
}
}
return null;
}
// Sets the value for the specified cell
// The "value" must be double/Double, int/Int32, bool/Boolean or string/String type
public static void SetCellValue(SpreadsheetDocument document, WorksheetPart worksheet, int row, int column, object value)
{
XDocument worksheetXDocument = worksheet.GetXDocument();
string cellReference = GetColumnId(column) + row.ToString();
XElement newCell = null;
if (value is int || value is double)
newCell = new XElement(S.c, new XAttribute(NoNamespace.r, cellReference), new XElement(S.v, value.ToString()));
else if (value is bool)
newCell = new XElement(S.c, new XAttribute(NoNamespace.r, cellReference), new XAttribute(NoNamespace.t, "b"), new XElement(S.v, (bool)value ? "1" : "0"));
else if (value is string)
{
newCell = new XElement(S.c, new XAttribute(NoNamespace.r, cellReference), new XAttribute(NoNamespace.t, "inlineStr"),
new XElement(S._is, new XElement(S.t, value.ToString())));
}
if (newCell == null)
throw new ArgumentException("Invalid cell type.");
SetCell(worksheetXDocument, newCell);
}
// Creates a new worksheet with the specified name and contents from a memory spreadsheet
public static void SetSheetContents(SpreadsheetDocument document, WorksheetPart worksheet, MemorySpreadsheet contents)
{
XDocument worksheetXDocument = worksheet.GetXDocument();
worksheetXDocument.Root.Element(S.sheetData).ReplaceWith(contents.GetElements());
worksheet.PutXDocument();
}
