コード例 #1
0
        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(""));
                }
            }
        }
コード例 #2
0
        // 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;
                }
            }
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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
            }));
        }
コード例 #5
0
        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);
        }