public static XElement GetTableInfoForSheet(SpreadsheetDocument spreadsheetDocument, WorksheetPart sheetPart, string sheetName,
     MetricsGetterSettings settings)
 {
     var xd = sheetPart.GetXDocument();
     XElement sheetInformation = new XElement(H.Sheet,
             new XAttribute(H.Name, sheetName),
             xd.Root.Elements(S.tableParts).Elements(S.tablePart).Select(tp =>
             {
                 string rId = (string)tp.Attribute(R.id);
                 TableDefinitionPart tablePart = (TableDefinitionPart)sheetPart.GetPartById(rId);
                 var txd = tablePart.GetXDocument();
                 var tableName = (string)txd.Root.Attribute("displayName");
                 XElement tableCellData = null;
                 if (settings.IncludeXlsxTableCellData)
                 {
                     var xlsxTable = spreadsheetDocument.Table(tableName);
                     tableCellData = new XElement(H.TableData,
                         xlsxTable.TableRows()
                             .Select(row =>
                             {
                                 var rowElement = new XElement(H.Row,
                                     xlsxTable.TableColumns().Select(col =>
                                     {
                                         var cellElement = new XElement(H.Cell,
                                             new XAttribute(H.Name, col.Name),
                                             new XAttribute(H.Val, (string)row[col.Name]));
                                         return cellElement;
                                     }));
                                 return rowElement;
                             }));
                 }
                 var table = new XElement(H.Table,
                     new XAttribute(H.Name, (string)txd.Root.Attribute("name")),
                     new XAttribute(H.DisplayName, tableName),
                     new XElement(H.Columns,
                         txd.Root.Element(S.tableColumns).Elements(S.tableColumn)
                         .Select(tc => new XElement(H.Column,
                             new XAttribute(H.Name, (string)tc.Attribute("name"))))),
                             tableCellData
                     );
                 return table;
             })
         );
     if (!sheetInformation.HasElements)
         return null;
     return sheetInformation;
 }
Пример #2
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);
        }