예제 #1
0
        public static XElement RetrieveTable(SpreadsheetDocument sDoc, string tableName)
        {
            var table = sDoc.Table(tableName);

            if (table == null)
            {
                throw new ArgumentException("Table not found", "tableName");
            }

            var styleXDoc = sDoc.WorkbookPart.WorkbookStylesPart.GetXDocument();
            var r = table.Ref;
            int leftColumn, topRow, rightColumn, bottomRow;

            XlsxTables.ParseRange(r, out leftColumn, out topRow, out rightColumn, out 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 =>
            {
                int rowRef;
                if (!int.TryParse(tr.Row.RowId, out 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);
                    }

                    XElement cellProps = GetCellProps_InTable(sDoc, 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;
                        }
                        XElement 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(
                                (string)"General",
                                tc.Value,
                                out color);
                        }
                        XElement 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);
            }));

            XElement 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);
        }
예제 #2
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", "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.
            SharedStringTablePart sharedStringTable = sDoc.WorkbookPart.SharedStringTablePart;

            FixUpCellsThatHaveNoRAtt(shXDoc);

            // assemble the transform
            var sheetData = shXDoc
                            .Root
                            .Elements(S.sheetData)
                            .Elements(S.row)
                            .Select(row =>
            {
                // filter
                string ra = (string)row.Attribute("r");
                if (ra == null)
                {
                    return(null);
                }
                int rowNbr;
                if (!int.TryParse(ra, out 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")
                    {
                        int sharedStringIndex;
                        string 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 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)
                    {
                        XElement cellProps = GetCellProps_NotInTable(sDoc, styleXDoc, cell);
                        string value       = sharedString;
                        string displayValue;
                        string color = null;
                        if (cellProps != null)
                        {
                            displayValue = SmlCellFormatter.FormatCell(
                                (string)cellProps.Attribute("formatCode"),
                                value,
                                out color);
                        }
                        else
                        {
                            displayValue = value;
                        }
                        XElement 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");
                        XElement value = new XElement("Value", cell.Value);
                        if (type != null && type == "inlineStr")
                        {
                            type = "s";
                        }
                        XAttribute typeAttr = null;
                        if (type != null)
                        {
                            typeAttr = new XAttribute("Type", type);
                        }

                        XElement cellProps = GetCellProps_NotInTable(sDoc, 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(
                                (string)"General",
                                cell.Value,
                                out color);
                        }
                        XElement 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);
                    }
                });
                XElement dataRow = new XElement("Row",
                                                row.Attribute("r") != null ? new XAttribute("RowNumber", (int)row.Attribute("r")) : null,
                                                cells);
                return(dataRow);
            });

            var      dataProps = GetDataProps(shXDoc);
            XElement data      = new XElement("Data",
                                              dataProps,
                                              sheetData);

            return(data);
        }