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