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