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