public TableCell this[string columnName] { get { var tc = Parent .TableColumns() .FirstOrDefault(x => x.Name.ToLower() == columnName.ToLower()); if (tc == null) { throw new Exception("Invalid column name: " + columnName); } var refs = Parent.Ref.Split(':'); var startRefs = XlsxTables.SplitAddress(refs[0]); var columnAddress = XlsxTables.IndexToColumnAddress(XlsxTables.ColumnAddressToIndex(startRefs[0]) + tc.ColumnIndex); var cell = Row.Cells().FirstOrDefault(c => c.ColumnAddress == columnAddress); if (cell != null) { if (cell.Type == "s") { return(new TableCell(cell.SharedString)); } else { return(new TableCell(cell.Value)); } } else { return(new TableCell("")); } } }
// Sometimes encounter cells that have no r attribute, so infer it if possible. // These are invalid spreadsheets, but attempt to get the data anyway. private static void FixUpCellsThatHaveNoRAtt(XDocument shXDoc) { // if there are any rows that have all cells with no r attribute, then fix them up var invalidRows = shXDoc .Descendants(S.row) .Where(r => !r.Elements(S.c).Any(c => c.Attribute("r") != null)) .ToList(); foreach (var row in invalidRows) { var rowNumberStr = (string)row.Attribute("r"); var colNumber = 0; foreach (var cell in row.Elements(S.c)) { var newCellRef = XlsxTables.IndexToColumnAddress(colNumber) + rowNumberStr; cell.Add(new XAttribute("r", newCellRef)); } } // repeat iteratively until no further fixes can be made while (true) { var invalidCells = shXDoc .Descendants(S.c) .Where(c => c.Attribute("r") == null) .ToList(); var didFixup = false; foreach (var cell in invalidCells) { var followingCell = cell.ElementsAfterSelf(S.c).FirstOrDefault(); if (followingCell != null) { var followingR = (string)followingCell.Attribute("r"); if (followingR != null) { var spl = XlsxTables.SplitAddress(followingR); var colIdxFollowing = XlsxTables.ColumnAddressToIndex(spl[0]); var newRef = XlsxTables.IndexToColumnAddress(colIdxFollowing - 1) + spl[1]; cell.Add(new XAttribute("r", newRef)); didFixup = true; } else { didFixup = FixUpBasedOnPrecedingCell(didFixup, cell); } } else { didFixup = FixUpBasedOnPrecedingCell(didFixup, cell); } } if (!didFixup) { break; } } }
private static bool FixUpBasedOnPrecedingCell(bool didFixup, XElement cell) { var precedingCell = GetPrevousElement(cell); if (precedingCell != null) { var precedingR = (string)precedingCell.Attribute("r"); if (precedingR != null) { var spl = XlsxTables.SplitAddress(precedingR); var colIdxFollowing = XlsxTables.ColumnAddressToIndex(spl[0]); var newRef = XlsxTables.IndexToColumnAddress(colIdxFollowing + 1) + spl[1]; cell.Add(new XAttribute("r", newRef)); didFixup = true; } } return(didFixup); }
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 List <Cell> Cells() { XNamespace s = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"; var doc = (SpreadsheetDocument)Parent.OpenXmlPackage; var sharedStringTable = doc.WorkbookPart.SharedStringTablePart; var cells = RowElement.Elements(S.c); var r = cells .Select(cell => { var cellType = (string)cell.Attribute("t"); var sharedString = cellType == "s" ? sharedStringTable .GetXDocument() .Root .Elements(s + "si") .Skip((int)cell.Element(s + "v")) .First() .Descendants(s + "t") .StringConcatenate(e => (string)e) : null; var column = (string)cell.Attribute("r"); var columnAddress = column.Split('0', '1', '2', '3', '4', '5', '6', '7', '8', '9').First(); var columnIndex = XlsxTables.ColumnAddressToIndex(columnAddress); var newCell = new Cell(this) { CellElement = cell, Row = (string)RowElement.Attribute("r"), Column = column, ColumnAddress = columnAddress, ColumnIndex = columnIndex, Type = cellType, Formula = (string)cell.Element(S.f), Style = (int?)cell.Attribute("s"), Value = (string)cell.Element(S.v), SharedString = sharedString }; return(newCell); }); var ra = r.ToList(); return(ra); }
public IEnumerable <TableRow> TableRows() { var refStart = Ref.Split(':').First(); var rowStart = int.Parse(XlsxTables.SplitAddress(refStart)[1]); var refEnd = Ref.Split(':').ElementAt(1); var rowEnd = int.Parse(XlsxTables.SplitAddress(refEnd)[1]); var headerRowsCount = HeaderRowCount == null ? 0 : (int)HeaderRowCount; var totalRowsCount = TotalsRowCount == null ? 0 : (int)TotalsRowCount; return(Parent .Rows() .Skip(headerRowsCount) .PtSkipLast(totalRowsCount) .Where(r => { var rowId = int.Parse(r.RowId); return rowId >= rowStart && rowId <= rowEnd; } ) .Select(r => new TableRow(this) { Row = r })); }
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); }
private static XElement?GetCellProps_InTable(XDocument styleXDoc, Table table, Cell tc) { var style = tc.Style; if (style == null) { return(null); } var colIdStr = tc.ColumnAddress; var colNbr = XlsxTables.ColumnAddressToIndex(colIdStr); var column = table.TableColumns().FirstOrDefault(z => z.ColumnNumber == colNbr); if (column == null) { throw new FileFormatException("Invalid spreadsheet"); } var cellProps = new XElement("CellProps"); var d = column.DataDxfId; if (d != null) { var dataDxf = styleXDoc .Root .Elements(S.dxfs) .Elements(S.dxf) .Skip((int)d) .FirstOrDefault(); if (dataDxf == null) { throw new FileFormatException("Invalid spreadsheet"); } var numFmt = dataDxf.Element(S.numFmt); if (numFmt != null) { var numFmtId = (int?)numFmt.Attribute("numFmtId"); if (numFmtId != null) { cellProps.Add(new XAttribute("numFmtId", numFmtId)); } var formatCode = (string)numFmt.Attribute("formatCode"); if (formatCode != null) { cellProps.Add(new XAttribute("formatCode", formatCode)); } } } var xf = styleXDoc.Root .Elements(S.cellXfs) .Elements(S.xf) .Skip((int)style) .FirstOrDefault(); if (xf == null) { throw new FileFormatException("Invalid spreadsheet"); } // if xf has different numFmtId, then replace the ones from the table definition var numFmtId2 = (int?)xf.Attribute("numFmtId"); if (numFmtId2 != null) { AddNumFmtIdAndFormatCode(styleXDoc, cellProps, numFmtId2); } var masterXfId = (int?)xf.Attribute("xfId"); if (masterXfId != null) { var masterXf = styleXDoc .Root .Elements(S.cellStyleXfs) .Elements(S.xf) .Skip((int)masterXfId) .FirstOrDefault(); if (masterXf != null) { AddFormattingToCellProps(styleXDoc, cellProps, masterXf); } } AddFormattingToCellProps(styleXDoc, cellProps, xf); AugmentAndCleanUpProps(cellProps); if (!cellProps.HasElements && !cellProps.HasAttributes) { return(null); } return(cellProps); }
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); }