コード例 #1
0
        public static void WriteDataInExcelFile(ResultTable results, Stream stream)
        {
            if (results == null)
            {
                throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString());
            }

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
            {
                document.PackageProperties.Creator        = "";
                document.PackageProperties.LastModifiedBy = "";

                WorkbookPart workbookPart = document.WorkbookPart;

                WorksheetPart worksheetPart = document.GetWorksheetPartByName(ExcelMessage.Data.NiceToString());

                CellBuilder cb = PlainExcelGenerator.CellBuilder;

                SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().SingleEx();

                List <ColumnData> columnEquivalences = GetColumnsEquivalences(document, sheetData, results);

                UInt32Value headerStyleIndex = worksheetPart.Worksheet.FindCell("A1").StyleIndex;

                //Clear sheetData from the template sample data
                sheetData.InnerXml = "";

                sheetData.Append(new Sequence <Row>()
                {
                    (from columnData in columnEquivalences
                     select cb.Cell(columnData.Column.Column.DisplayName, headerStyleIndex)).ToRow(),

                    from r in results.Rows
                    select(from columnData in columnEquivalences
                           select cb.Cell(r[columnData.Column], cb.GetTemplateCell(columnData.Column.Column.Type), columnData.StyleIndex)).ToRow()
                }.Cast <OpenXmlElement>());

                var pivotTableParts = workbookPart.PivotTableCacheDefinitionParts
                                      .Where(ptpart => ptpart.PivotCacheDefinition.Descendants <WorksheetSource>()
                                             .Any(wss => wss.Sheet.Value == ExcelMessage.Data.NiceToString()));

                foreach (PivotTableCacheDefinitionPart ptpart in pivotTableParts)
                {
                    PivotCacheDefinition pcd = ptpart.PivotCacheDefinition;
                    WorksheetSource      wss = pcd.Descendants <WorksheetSource>().FirstEx();
                    wss.Reference.Value = "A1:" + GetExcelColumn(columnEquivalences.Count(ce => !ce.IsNew) - 1) + (results.Rows.Count() + 1).ToString();

                    pcd.RefreshOnLoad = true;
                    pcd.SaveData      = false;
                    pcd.Save();
                }

                workbookPart.Workbook.Save();
                document.Close();
            }
        }
コード例 #2
0
    /// <summary>
    /// Deletes the A work sheet.
    /// </summary>
    /// <param name="fileName">Name of the file.</param>
    /// <param name="sheetToDelete">The sheet to delete.</param>
    public void ClearWorkSheetData(string fileName, string sheetToClear)
    {
        string Sheetid = "";

        //Open the workbook
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
        {
            WorkbookPart wbPart = document.WorkbookPart;

            // Get the pivot Table Parts
            IEnumerable <PivotTableCacheDefinitionPart>        pvtTableCacheParts          = wbPart.PivotTableCacheDefinitionParts;
            Dictionary <PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary <PivotTableCacheDefinitionPart, string>();
            foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
            {
                PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                //Check if this CacheSource is linked to SheetToDelete
                var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToClear);
                if (pvtCahce.Count() > 0)
                {
                    pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                }
            }
            foreach (var Item in pvtTableCacheDefinationPart)
            {
                wbPart.DeletePart(Item.Key);
            }
            //Get the SheetToDelete from workbook.xml
            Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetToClear).FirstOrDefault();
            if (theSheet == null)
            {
                // The specified sheet doesn't exist.
            }
            //Store the SheetID for the reference
            Sheetid = theSheet.SheetId;

            // Remove the sheet reference from the workbook.
            WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

            Worksheet workSheet = worksheetPart.Worksheet;
            SheetData sheetData = workSheet.GetFirstChild <SheetData>();

            for (int childIndex = 1; childIndex < sheetData.ChildElements.Count; childIndex++)
            {
                sheetData.RemoveChild(sheetData.ChildElements[childIndex]);
            }

            IEnumerable <Row> rows = sheetData.Descendants <Row>();

            List <Row> rowsList = rows.ToList();

            //rowsList.RemoveRange(1, rowsList.Count - 1);

            // Save the workbook.
            wbPart.Workbook.Save();
        }
    }
コード例 #3
0
        // Generates content of pivotTableCacheDefinitionPart1.
        private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1)
        {
            PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition() { Id = "rId1", RefreshedBy = "Author", RefreshedDate = 40905.65532824074D, CreatedVersion = 4, RefreshedVersion = 4, MinRefreshableVersion = 3, RecordCount = (UInt32Value)3U };

            CacheSource cacheSource1 = new CacheSource() { Type = SourceValues.Worksheet };
            WorksheetSource worksheetSource1 = new WorksheetSource() { Reference = "A1:B4", Sheet = "Sheet2" };

            cacheSource1.Append(worksheetSource1);

            CacheFields cacheFields1 = new CacheFields() { Count = (UInt32Value)2U };

            CacheField cacheField1 = new CacheField() { Name = "id", NumberFormatId = (UInt32Value)0U };

            SharedItems sharedItems1 = new SharedItems() { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 1D, MaxValue = 3D, Count = (UInt32Value)3U };
            NumberItem numberItem1 = new NumberItem() { Val = 1D };
            NumberItem numberItem2 = new NumberItem() { Val = 2D };
            NumberItem numberItem3 = new NumberItem() { Val = 3D };

            sharedItems1.Append(numberItem1);
            sharedItems1.Append(numberItem2);
            sharedItems1.Append(numberItem3);

            cacheField1.Append(sharedItems1);

            CacheField cacheField2 = new CacheField() { Name = "score", NumberFormatId = (UInt32Value)0U };
            SharedItems sharedItems2 = new SharedItems() { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 100D, MaxValue = 132D };

            cacheField2.Append(sharedItems2);

            cacheFields1.Append(cacheField1);
            cacheFields1.Append(cacheField2);

            PivotCacheDefinitionExtensionList pivotCacheDefinitionExtensionList1 = new PivotCacheDefinitionExtensionList();

            pivotCacheDefinition1.Append(cacheSource1);
            pivotCacheDefinition1.Append(cacheFields1);
            pivotCacheDefinition1.Append(pivotCacheDefinitionExtensionList1);

            pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1;
        }
コード例 #4
0
        // Generates content of pivotTableCacheDefinitionPart
        private static void GeneratePivotTableCacheDefinitionPartContent(
            PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt)
        {
            var source = pt.SourceRange;

            var pivotCacheDefinition = new PivotCacheDefinition
            {
                Id = "rId1",
                SaveData = pt.SaveSourceData,
                RefreshOnLoad = true //pt.RefreshDataOnOpen
            };
            if (pt.ItemsToRetainPerField == XLItemsToRetain.None)
                pivotCacheDefinition.MissingItemsLimit = 0U;
            else if (pt.ItemsToRetainPerField == XLItemsToRetain.Max)
                pivotCacheDefinition.MissingItemsLimit = XLHelper.MaxRowNumber;

            pivotCacheDefinition.AddNamespaceDeclaration("r",
                "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            var cacheSource = new CacheSource {Type = SourceValues.Worksheet};
            cacheSource.AppendChild(new WorksheetSource {Name = source.ToString()});

            var cacheFields = new CacheFields();

            foreach (var c in source.Columns())
            {
                var columnNumber = c.ColumnNumber();
                var columnName = c.FirstCell().Value.ToString();
                var xlpf = pt.Fields.Add(columnName);

                var field =
                    pt.RowLabels.Union(pt.ColumnLabels).Union(pt.ReportFilters).FirstOrDefault(f => f.SourceName == columnName);
                if (field != null)
                {
                    xlpf.CustomName = field.CustomName;
                    xlpf.Subtotals.AddRange(field.Subtotals);
                }

                var sharedItems = new SharedItems();

                var onlyNumbers =
                    !source.Cells().Any(
                        cell =>
                            cell.Address.ColumnNumber == columnNumber &&
                            cell.Address.RowNumber > source.FirstRow().RowNumber() && cell.DataType != XLCellValues.Number);
                if (onlyNumbers)
                {
                    sharedItems = new SharedItems
                    {ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true};
                }
                else
                {
                    foreach (var cellValue in source.Cells().Where(cell =>
                        cell.Address.ColumnNumber == columnNumber &&
                        cell.Address.RowNumber >
                        source.FirstRow().RowNumber()).Select(
                            cell => cell.Value.ToString())
                        .Where(cellValue => !xlpf.SharedStrings.Contains(cellValue)))
                    {
                        xlpf.SharedStrings.Add(cellValue);
                    }

                    foreach (var li in xlpf.SharedStrings)
                    {
                        sharedItems.AppendChild(new StringItem {Val = li});
                    }
                }

                var cacheField = new CacheField {Name = xlpf.SourceName};
                cacheField.AppendChild(sharedItems);
                cacheFields.AppendChild(cacheField);
            }

            pivotCacheDefinition.AppendChild(cacheSource);
            pivotCacheDefinition.AppendChild(cacheFields);

            pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition;

            var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart<PivotTableCacheRecordsPart>("rId1");

            var pivotCacheRecords = new PivotCacheRecords();
            pivotCacheRecords.AddNamespaceDeclaration("r",
                "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            pivotTableCacheRecordsPart.PivotCacheRecords = pivotCacheRecords;
        }
コード例 #5
0
        internal PivotCacheDefinition ToPivotCacheDefinition()
        {
            PivotCacheDefinition pcd = new PivotCacheDefinition();
            if (this.Id != null && this.Id.Length > 0) pcd.Id = this.Id;
            if (this.Invalid != false) pcd.Invalid = this.Invalid;
            if (this.SaveData != true) pcd.SaveData = this.SaveData;
            if (this.RefreshOnLoad != false) pcd.RefreshOnLoad = this.RefreshOnLoad;
            if (this.OptimizeMemory != false) pcd.OptimizeMemory = this.OptimizeMemory;
            if (this.EnableRefresh != true) pcd.EnableRefresh = this.EnableRefresh;
            if (this.RefreshedBy != null && this.RefreshedBy.Length > 0) pcd.RefreshedBy = this.RefreshedBy;
            if (this.RefreshedDate != null) pcd.RefreshedDate = this.RefreshedDate.Value;
            if (this.BackgroundQuery != false) pcd.BackgroundQuery = this.BackgroundQuery;
            if (this.MissingItemsLimit != null) pcd.MissingItemsLimit = this.MissingItemsLimit.Value;
            if (this.CreatedVersion != 0) pcd.CreatedVersion = this.CreatedVersion;
            if (this.RefreshedVersion != 0) pcd.RefreshedVersion = this.RefreshedVersion;
            if (this.MinRefreshableVersion != 0) pcd.MinRefreshableVersion = this.MinRefreshableVersion;
            if (this.RecordCount != null) pcd.RecordCount = this.RecordCount.Value;
            if (this.UpgradeOnRefresh != false) pcd.UpgradeOnRefresh = this.UpgradeOnRefresh;
            if (this.IsTupleCache != false) pcd.IsTupleCache = this.IsTupleCache;
            if (this.SupportSubquery != false) pcd.SupportSubquery = this.SupportSubquery;
            if (this.SupportAdvancedDrill != false) pcd.SupportAdvancedDrill = this.SupportAdvancedDrill;

            pcd.CacheSource = this.CacheSource.ToCacheSource();

            pcd.CacheFields = new CacheFields() { Count = (uint)this.CacheFields.Count };
            foreach (SLCacheField cf in this.CacheFields)
            {
                pcd.CacheFields.Append(cf.ToCacheField());
            }

            if (this.CacheHierarchies.Count > 0)
            {
                pcd.CacheHierarchies = new CacheHierarchies() { Count = (uint)this.CacheHierarchies.Count };
                foreach (SLCacheHierarchy ch in this.CacheHierarchies)
                {
                    pcd.CacheHierarchies.Append(ch.ToCacheHierarchy());
                }
            }

            if (this.Kpis.Count > 0)
            {
                pcd.Kpis = new Kpis() { Count = (uint)this.Kpis.Count };
                foreach (SLKpi k in this.Kpis)
                {
                    pcd.Kpis.Append(k.ToKpi());
                }
            }

            if (this.HasTupleCache) pcd.TupleCache = this.TupleCache.ToTupleCache();

            if (this.CalculatedItems.Count > 0)
            {
                pcd.CalculatedItems = new CalculatedItems() { Count = (uint)this.CalculatedItems.Count };
                foreach (SLCalculatedItem ci in this.CalculatedItems)
                {
                    pcd.CalculatedItems.Append(ci.ToCalculatedItem());
                }
            }

            if (this.CalculatedMembers.Count > 0)
            {
                pcd.CalculatedMembers = new CalculatedMembers() { Count = (uint)this.CalculatedMembers.Count };
                foreach (SLCalculatedMember cm in this.CalculatedMembers)
                {
                    pcd.CalculatedMembers.Append(cm.ToCalculatedMember());
                }
            }

            if (this.Dimensions.Count > 0)
            {
                pcd.Dimensions = new Dimensions() { Count = (uint)this.Dimensions.Count };
                foreach (SLDimension d in this.Dimensions)
                {
                    pcd.Dimensions.Append(d.ToDimension());
                }
            }

            if (this.MeasureGroups.Count > 0)
            {
                pcd.MeasureGroups = new MeasureGroups() { Count = (uint)this.MeasureGroups.Count };
                foreach (SLMeasureGroup mg in this.MeasureGroups)
                {
                    pcd.MeasureGroups.Append(mg.ToMeasureGroup());
                }
            }

            if (this.Maps.Count > 0)
            {
                pcd.Maps = new Maps() { Count = (uint)this.Maps.Count };
                foreach (SLMeasureDimensionMap mdm in this.Maps)
                {
                    pcd.Maps.Append(mdm.ToMeasureDimensionMap());
                }
            }

            return pcd;
        }
コード例 #6
0
        internal void FromPivotCacheDefinition(PivotCacheDefinition pcd)
        {
            this.SetAllNull();

            if (pcd.Id != null) this.Id = pcd.Id.Value;
            if (pcd.Invalid != null) this.Invalid = pcd.Invalid.Value;
            if (pcd.SaveData != null) this.SaveData = pcd.SaveData.Value;
            if (pcd.RefreshOnLoad != null) this.RefreshOnLoad = pcd.RefreshOnLoad.Value;
            if (pcd.OptimizeMemory != null) this.OptimizeMemory = pcd.OptimizeMemory.Value;
            if (pcd.EnableRefresh != null) this.EnableRefresh = pcd.EnableRefresh.Value;
            if (pcd.RefreshedBy != null) this.RefreshedBy = pcd.RefreshedBy.Value;
            if (pcd.RefreshedDate != null) this.RefreshedDate = pcd.RefreshedDate.Value;
            if (pcd.BackgroundQuery != null) this.BackgroundQuery = pcd.BackgroundQuery.Value;
            if (pcd.MissingItemsLimit != null) this.MissingItemsLimit = pcd.MissingItemsLimit.Value;
            if (pcd.CreatedVersion != null) this.CreatedVersion = pcd.CreatedVersion.Value;
            if (pcd.RefreshedVersion != null) this.RefreshedVersion = pcd.RefreshedVersion.Value;
            if (pcd.MinRefreshableVersion != null) this.MinRefreshableVersion = pcd.MinRefreshableVersion.Value;
            if (pcd.RecordCount != null) this.RecordCount = pcd.RecordCount.Value;
            if (pcd.UpgradeOnRefresh != null) this.UpgradeOnRefresh = pcd.UpgradeOnRefresh.Value;
            if (pcd.IsTupleCache != null) this.IsTupleCache = pcd.IsTupleCache.Value;
            if (pcd.SupportSubquery != null) this.SupportSubquery = pcd.SupportSubquery.Value;
            if (pcd.SupportAdvancedDrill != null) this.SupportAdvancedDrill = pcd.SupportAdvancedDrill.Value;

            if (pcd.CacheSource != null) this.CacheSource.FromCacheSource(pcd.CacheSource);

            // doing one by one because it's bloody hindering awkward complicated.

            if (pcd.CacheFields != null)
            {
                SLCacheField cf;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CacheFields))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CacheField))
                        {
                            cf = new SLCacheField();
                            cf.FromCacheField((CacheField)oxr.LoadCurrentElement());
                            this.CacheFields.Add(cf);
                        }
                    }
                }
            }

            if (pcd.CacheHierarchies != null)
            {
                SLCacheHierarchy ch;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CacheHierarchies))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CacheHierarchy))
                        {
                            ch = new SLCacheHierarchy();
                            ch.FromCacheHierarchy((CacheHierarchy)oxr.LoadCurrentElement());
                            this.CacheHierarchies.Add(ch);
                        }
                    }
                }
            }

            if (pcd.Kpis != null)
            {
                SLKpi k;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.Kpis))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(Kpi))
                        {
                            k = new SLKpi();
                            k.FromKpi((Kpi)oxr.LoadCurrentElement());
                            this.Kpis.Add(k);
                        }
                    }
                }
            }

            if (pcd.TupleCache != null)
            {
                this.TupleCache.FromTupleCache(pcd.TupleCache);
                this.HasTupleCache = true;
            }

            if (pcd.CalculatedItems != null)
            {
                SLCalculatedItem ci;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CalculatedItems))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CalculatedItem))
                        {
                            ci = new SLCalculatedItem();
                            ci.FromCalculatedItem((CalculatedItem)oxr.LoadCurrentElement());
                            this.CalculatedItems.Add(ci);
                        }
                    }
                }
            }

            if (pcd.CalculatedMembers != null)
            {
                SLCalculatedMember cm;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.CalculatedMembers))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CalculatedMember))
                        {
                            cm = new SLCalculatedMember();
                            cm.FromCalculatedMember((CalculatedMember)oxr.LoadCurrentElement());
                            this.CalculatedMembers.Add(cm);
                        }
                    }
                }
            }

            if (pcd.Dimensions != null)
            {
                SLDimension d;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.Dimensions))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(Dimension))
                        {
                            d = new SLDimension();
                            d.FromDimension((Dimension)oxr.LoadCurrentElement());
                            this.Dimensions.Add(d);
                        }
                    }
                }
            }

            if (pcd.MeasureGroups != null)
            {
                SLMeasureGroup mg;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.MeasureGroups))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(MeasureGroup))
                        {
                            mg = new SLMeasureGroup();
                            mg.FromMeasureGroup((MeasureGroup)oxr.LoadCurrentElement());
                            this.MeasureGroups.Add(mg);
                        }
                    }
                }
            }

            if (pcd.Maps != null)
            {
                SLMeasureDimensionMap mdm;
                using (OpenXmlReader oxr = OpenXmlReader.Create(pcd.Maps))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(MeasureDimensionMap))
                        {
                            mdm = new SLMeasureDimensionMap();
                            mdm.FromMeasureDimensionMap((MeasureDimensionMap)oxr.LoadCurrentElement());
                            this.Maps.Add(mdm);
                        }
                    }
                }
            }
        }
コード例 #7
0
ファイル: TimetableCreator.cs プロジェクト: dKluev/Site
        private static void DeleteAWorkSheet(SpreadsheetDocument document, string sheetToDelete)
        {
            WorkbookPart wbPart = document.WorkbookPart;

            // Get the pivot Table Parts
            IEnumerable <PivotTableCacheDefinitionPart>        pvtTableCacheParts          = wbPart.PivotTableCacheDefinitionParts;
            Dictionary <PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart =
                new Dictionary <PivotTableCacheDefinitionPart, string>();

            foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
            {
                PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                //Check if this CacheSource is linked to SheetToDelete
                var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
                if (pvtCahce.Count() > 0)
                {
                    pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                }
            }
            foreach (var Item in pvtTableCacheDefinationPart)
            {
                wbPart.DeletePart(Item.Key);
            }
            //Get the SheetToDelete from workbook.xml
            Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();

            if (theSheet == null)
            {
                // The specified sheet doesn't exist.
            }
            //Store the SheetID for the reference
            var Sheetid = theSheet.SheetId;

            // Remove the sheet reference from the workbook.
            WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

            theSheet.Remove();

            // Delete the worksheet part.
            wbPart.DeletePart(worksheetPart);

            //Get the DefinedNames
            var definedNames = wbPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();

            if (definedNames != null)
            {
                foreach (DefinedName Item in definedNames)
                {
                    // This condition checks to delete only those names which are part of Sheet in question
                    if (Item.Text.Contains(sheetToDelete + "!"))
                    {
                        Item.Remove();
                    }
                }
            }
            // Get the CalculationChainPart
            //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
            //workbook whose value is calculated from any formula

            CalculationChainPart calChainPart;

            calChainPart = wbPart.CalculationChainPart;
            if (calChainPart != null)
            {
                var calChainEntries =
                    calChainPart.CalculationChain.Descendants <CalculationCell>().Where(c => c.SheetId.ToString() == Sheetid);
                foreach (CalculationCell Item in calChainEntries)
                {
                    Item.Remove();
                }
                if (calChainPart.CalculationChain.Count() == 0)
                {
                    wbPart.DeletePart(calChainPart);
                }
            }
        }
コード例 #8
0
        /// <summary>
        /// Deletes worksheet and its dependencies.
        /// </summary>
        /// <param name="fileName">Workbook to delete sheet from.</param>
        /// <param name="sheetToDelete">Worksheetto delete.</param>
        /// <remarks>Code taken and modified from <a href="https://blogs.msdn.microsoft.com/vsod/2010/02/05/how-to-delete-a-worksheet-from-excel-using-open-xml-sdk-2-0/">Ankush Bhatia</a>.</remarks>
        private void DeleteWorkSheet(string fileName, string sheetToDelete)
        {
            string Sheetid = string.Empty;

            //Open the workbook
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;

                // Get the pivot Table Parts
                IEnumerable <PivotTableCacheDefinitionPart>        pvtTableCacheParts          = wbPart.PivotTableCacheDefinitionParts;
                Dictionary <PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary <PivotTableCacheDefinitionPart, string>();
                foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
                {
                    PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                    //Check if this CacheSource is linked to SheetToDelete
                    var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
                    if (pvtCahce.Count() > 0)
                    {
                        pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                    }
                }
                foreach (var Item in pvtTableCacheDefinationPart)
                {
                    wbPart.DeletePart(Item.Key);
                }
                //Get the SheetToDelete from workbook.xml
                Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
                if (theSheet == null)
                {
                    return;
                }
                //Store the SheetID for the reference
                Sheetid = theSheet.SheetId;

                // Remove the sheet reference from the workbook.
                WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
                theSheet.Remove();

                // Delete the worksheet part.
                wbPart.DeletePart(worksheetPart);

                //Get the DefinedNames
                var definedNames = wbPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();
                if (definedNames != null)
                {
                    List <DefinedName> defNamesToDelete = new List <DefinedName>();

                    foreach (DefinedName Item in definedNames)
                    {
                        // This condition checks to delete only those names which are part of Sheet in question
                        if (Item.Text.Contains(sheetToDelete + "!"))
                        {
                            defNamesToDelete.Add(Item);
                        }
                    }

                    foreach (DefinedName Item in defNamesToDelete)
                    {
                        Item.Remove();
                    }
                }
                // Get the CalculationChainPart
                //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
                //workbook whose value is calculated from any formula

                CalculationChainPart calChainPart;
                calChainPart = wbPart.CalculationChainPart;
                if (calChainPart != null)
                {
                    var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().Where(c => c.SheetId == Sheetid);
                    List <CalculationCell> calcsToDelete = new List <CalculationCell>();
                    foreach (CalculationCell Item in calChainEntries)
                    {
                        calcsToDelete.Add(Item);
                    }

                    foreach (CalculationCell Item in calcsToDelete)
                    {
                        Item.Remove();
                    }

                    if (calChainPart.CalculationChain.Count() == 0)
                    {
                        wbPart.DeletePart(calChainPart);
                    }
                }
                // force recalculation if any
                var calcProps = document.WorkbookPart.Workbook.Elements <CalculationProperties>().FirstOrDefault();
                if (calcProps != null)
                {
                    document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation  = true;
                    document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
                }
            } // using autosaves
        }
コード例 #9
0
        internal PivotCacheDefinition ToPivotCacheDefinition()
        {
            var pcd = new PivotCacheDefinition();

            if ((Id != null) && (Id.Length > 0))
            {
                pcd.Id = Id;
            }
            if (Invalid)
            {
                pcd.Invalid = Invalid;
            }
            if (SaveData != true)
            {
                pcd.SaveData = SaveData;
            }
            if (RefreshOnLoad)
            {
                pcd.RefreshOnLoad = RefreshOnLoad;
            }
            if (OptimizeMemory)
            {
                pcd.OptimizeMemory = OptimizeMemory;
            }
            if (EnableRefresh != true)
            {
                pcd.EnableRefresh = EnableRefresh;
            }
            if ((RefreshedBy != null) && (RefreshedBy.Length > 0))
            {
                pcd.RefreshedBy = RefreshedBy;
            }
            if (RefreshedDate != null)
            {
                pcd.RefreshedDate = RefreshedDate.Value;
            }
            if (BackgroundQuery)
            {
                pcd.BackgroundQuery = BackgroundQuery;
            }
            if (MissingItemsLimit != null)
            {
                pcd.MissingItemsLimit = MissingItemsLimit.Value;
            }
            if (CreatedVersion != 0)
            {
                pcd.CreatedVersion = CreatedVersion;
            }
            if (RefreshedVersion != 0)
            {
                pcd.RefreshedVersion = RefreshedVersion;
            }
            if (MinRefreshableVersion != 0)
            {
                pcd.MinRefreshableVersion = MinRefreshableVersion;
            }
            if (RecordCount != null)
            {
                pcd.RecordCount = RecordCount.Value;
            }
            if (UpgradeOnRefresh)
            {
                pcd.UpgradeOnRefresh = UpgradeOnRefresh;
            }
            if (IsTupleCache)
            {
                pcd.IsTupleCache = IsTupleCache;
            }
            if (SupportSubquery)
            {
                pcd.SupportSubquery = SupportSubquery;
            }
            if (SupportAdvancedDrill)
            {
                pcd.SupportAdvancedDrill = SupportAdvancedDrill;
            }

            pcd.CacheSource = CacheSource.ToCacheSource();

            pcd.CacheFields = new CacheFields {
                Count = (uint)CacheFields.Count
            };
            foreach (var cf in CacheFields)
            {
                pcd.CacheFields.Append(cf.ToCacheField());
            }

            if (CacheHierarchies.Count > 0)
            {
                pcd.CacheHierarchies = new CacheHierarchies {
                    Count = (uint)CacheHierarchies.Count
                };
                foreach (var ch in CacheHierarchies)
                {
                    pcd.CacheHierarchies.Append(ch.ToCacheHierarchy());
                }
            }

            if (Kpis.Count > 0)
            {
                pcd.Kpis = new Kpis {
                    Count = (uint)Kpis.Count
                };
                foreach (var k in Kpis)
                {
                    pcd.Kpis.Append(k.ToKpi());
                }
            }

            if (HasTupleCache)
            {
                pcd.TupleCache = TupleCache.ToTupleCache();
            }

            if (CalculatedItems.Count > 0)
            {
                pcd.CalculatedItems = new CalculatedItems {
                    Count = (uint)CalculatedItems.Count
                };
                foreach (var ci in CalculatedItems)
                {
                    pcd.CalculatedItems.Append(ci.ToCalculatedItem());
                }
            }

            if (CalculatedMembers.Count > 0)
            {
                pcd.CalculatedMembers = new CalculatedMembers {
                    Count = (uint)CalculatedMembers.Count
                };
                foreach (var cm in CalculatedMembers)
                {
                    pcd.CalculatedMembers.Append(cm.ToCalculatedMember());
                }
            }

            if (Dimensions.Count > 0)
            {
                pcd.Dimensions = new Dimensions {
                    Count = (uint)Dimensions.Count
                };
                foreach (var d in Dimensions)
                {
                    pcd.Dimensions.Append(d.ToDimension());
                }
            }

            if (MeasureGroups.Count > 0)
            {
                pcd.MeasureGroups = new MeasureGroups {
                    Count = (uint)MeasureGroups.Count
                };
                foreach (var mg in MeasureGroups)
                {
                    pcd.MeasureGroups.Append(mg.ToMeasureGroup());
                }
            }

            if (Maps.Count > 0)
            {
                pcd.Maps = new Maps {
                    Count = (uint)Maps.Count
                };
                foreach (var mdm in Maps)
                {
                    pcd.Maps.Append(mdm.ToMeasureDimensionMap());
                }
            }

            return(pcd);
        }
コード例 #10
0
        internal void FromPivotCacheDefinition(PivotCacheDefinition pcd)
        {
            SetAllNull();

            if (pcd.Id != null)
            {
                Id = pcd.Id.Value;
            }
            if (pcd.Invalid != null)
            {
                Invalid = pcd.Invalid.Value;
            }
            if (pcd.SaveData != null)
            {
                SaveData = pcd.SaveData.Value;
            }
            if (pcd.RefreshOnLoad != null)
            {
                RefreshOnLoad = pcd.RefreshOnLoad.Value;
            }
            if (pcd.OptimizeMemory != null)
            {
                OptimizeMemory = pcd.OptimizeMemory.Value;
            }
            if (pcd.EnableRefresh != null)
            {
                EnableRefresh = pcd.EnableRefresh.Value;
            }
            if (pcd.RefreshedBy != null)
            {
                RefreshedBy = pcd.RefreshedBy.Value;
            }
            if (pcd.RefreshedDate != null)
            {
                RefreshedDate = pcd.RefreshedDate.Value;
            }
            if (pcd.BackgroundQuery != null)
            {
                BackgroundQuery = pcd.BackgroundQuery.Value;
            }
            if (pcd.MissingItemsLimit != null)
            {
                MissingItemsLimit = pcd.MissingItemsLimit.Value;
            }
            if (pcd.CreatedVersion != null)
            {
                CreatedVersion = pcd.CreatedVersion.Value;
            }
            if (pcd.RefreshedVersion != null)
            {
                RefreshedVersion = pcd.RefreshedVersion.Value;
            }
            if (pcd.MinRefreshableVersion != null)
            {
                MinRefreshableVersion = pcd.MinRefreshableVersion.Value;
            }
            if (pcd.RecordCount != null)
            {
                RecordCount = pcd.RecordCount.Value;
            }
            if (pcd.UpgradeOnRefresh != null)
            {
                UpgradeOnRefresh = pcd.UpgradeOnRefresh.Value;
            }
            if (pcd.IsTupleCache != null)
            {
                IsTupleCache = pcd.IsTupleCache.Value;
            }
            if (pcd.SupportSubquery != null)
            {
                SupportSubquery = pcd.SupportSubquery.Value;
            }
            if (pcd.SupportAdvancedDrill != null)
            {
                SupportAdvancedDrill = pcd.SupportAdvancedDrill.Value;
            }

            if (pcd.CacheSource != null)
            {
                CacheSource.FromCacheSource(pcd.CacheSource);
            }

            // doing one by one because it's bloody hindering awkward complicated.

            if (pcd.CacheFields != null)
            {
                SLCacheField cf;
                using (var oxr = OpenXmlReader.Create(pcd.CacheFields))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CacheField))
                        {
                            cf = new SLCacheField();
                            cf.FromCacheField((CacheField)oxr.LoadCurrentElement());
                            CacheFields.Add(cf);
                        }
                    }
                }
            }

            if (pcd.CacheHierarchies != null)
            {
                SLCacheHierarchy ch;
                using (var oxr = OpenXmlReader.Create(pcd.CacheHierarchies))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CacheHierarchy))
                        {
                            ch = new SLCacheHierarchy();
                            ch.FromCacheHierarchy((CacheHierarchy)oxr.LoadCurrentElement());
                            CacheHierarchies.Add(ch);
                        }
                    }
                }
            }

            if (pcd.Kpis != null)
            {
                SLKpi k;
                using (var oxr = OpenXmlReader.Create(pcd.Kpis))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(Kpi))
                        {
                            k = new SLKpi();
                            k.FromKpi((Kpi)oxr.LoadCurrentElement());
                            Kpis.Add(k);
                        }
                    }
                }
            }

            if (pcd.TupleCache != null)
            {
                TupleCache.FromTupleCache(pcd.TupleCache);
                HasTupleCache = true;
            }

            if (pcd.CalculatedItems != null)
            {
                SLCalculatedItem ci;
                using (var oxr = OpenXmlReader.Create(pcd.CalculatedItems))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CalculatedItem))
                        {
                            ci = new SLCalculatedItem();
                            ci.FromCalculatedItem((CalculatedItem)oxr.LoadCurrentElement());
                            CalculatedItems.Add(ci);
                        }
                    }
                }
            }

            if (pcd.CalculatedMembers != null)
            {
                SLCalculatedMember cm;
                using (var oxr = OpenXmlReader.Create(pcd.CalculatedMembers))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CalculatedMember))
                        {
                            cm = new SLCalculatedMember();
                            cm.FromCalculatedMember((CalculatedMember)oxr.LoadCurrentElement());
                            CalculatedMembers.Add(cm);
                        }
                    }
                }
            }

            if (pcd.Dimensions != null)
            {
                SLDimension d;
                using (var oxr = OpenXmlReader.Create(pcd.Dimensions))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(Dimension))
                        {
                            d = new SLDimension();
                            d.FromDimension((Dimension)oxr.LoadCurrentElement());
                            Dimensions.Add(d);
                        }
                    }
                }
            }

            if (pcd.MeasureGroups != null)
            {
                SLMeasureGroup mg;
                using (var oxr = OpenXmlReader.Create(pcd.MeasureGroups))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(MeasureGroup))
                        {
                            mg = new SLMeasureGroup();
                            mg.FromMeasureGroup((MeasureGroup)oxr.LoadCurrentElement());
                            MeasureGroups.Add(mg);
                        }
                    }
                }
            }

            if (pcd.Maps != null)
            {
                SLMeasureDimensionMap mdm;
                using (var oxr = OpenXmlReader.Create(pcd.Maps))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(MeasureDimensionMap))
                        {
                            mdm = new SLMeasureDimensionMap();
                            mdm.FromMeasureDimensionMap((MeasureDimensionMap)oxr.LoadCurrentElement());
                            Maps.Add(mdm);
                        }
                    }
                }
            }
        }
コード例 #11
0
        internal PivotCacheDefinition ToPivotCacheDefinition()
        {
            PivotCacheDefinition pcd = new PivotCacheDefinition();

            if (this.Id != null && this.Id.Length > 0)
            {
                pcd.Id = this.Id;
            }
            if (this.Invalid != false)
            {
                pcd.Invalid = this.Invalid;
            }
            if (this.SaveData != true)
            {
                pcd.SaveData = this.SaveData;
            }
            if (this.RefreshOnLoad != false)
            {
                pcd.RefreshOnLoad = this.RefreshOnLoad;
            }
            if (this.OptimizeMemory != false)
            {
                pcd.OptimizeMemory = this.OptimizeMemory;
            }
            if (this.EnableRefresh != true)
            {
                pcd.EnableRefresh = this.EnableRefresh;
            }
            if (this.RefreshedBy != null && this.RefreshedBy.Length > 0)
            {
                pcd.RefreshedBy = this.RefreshedBy;
            }
            if (this.RefreshedDate != null)
            {
                pcd.RefreshedDate = this.RefreshedDate.Value;
            }
            if (this.BackgroundQuery != false)
            {
                pcd.BackgroundQuery = this.BackgroundQuery;
            }
            if (this.MissingItemsLimit != null)
            {
                pcd.MissingItemsLimit = this.MissingItemsLimit.Value;
            }
            if (this.CreatedVersion != 0)
            {
                pcd.CreatedVersion = this.CreatedVersion;
            }
            if (this.RefreshedVersion != 0)
            {
                pcd.RefreshedVersion = this.RefreshedVersion;
            }
            if (this.MinRefreshableVersion != 0)
            {
                pcd.MinRefreshableVersion = this.MinRefreshableVersion;
            }
            if (this.RecordCount != null)
            {
                pcd.RecordCount = this.RecordCount.Value;
            }
            if (this.UpgradeOnRefresh != false)
            {
                pcd.UpgradeOnRefresh = this.UpgradeOnRefresh;
            }
            if (this.IsTupleCache != false)
            {
                pcd.IsTupleCache = this.IsTupleCache;
            }
            if (this.SupportSubquery != false)
            {
                pcd.SupportSubquery = this.SupportSubquery;
            }
            if (this.SupportAdvancedDrill != false)
            {
                pcd.SupportAdvancedDrill = this.SupportAdvancedDrill;
            }

            pcd.CacheSource = this.CacheSource.ToCacheSource();

            pcd.CacheFields = new CacheFields()
            {
                Count = (uint)this.CacheFields.Count
            };
            foreach (SLCacheField cf in this.CacheFields)
            {
                pcd.CacheFields.Append(cf.ToCacheField());
            }

            if (this.CacheHierarchies.Count > 0)
            {
                pcd.CacheHierarchies = new CacheHierarchies()
                {
                    Count = (uint)this.CacheHierarchies.Count
                };
                foreach (SLCacheHierarchy ch in this.CacheHierarchies)
                {
                    pcd.CacheHierarchies.Append(ch.ToCacheHierarchy());
                }
            }

            if (this.Kpis.Count > 0)
            {
                pcd.Kpis = new Kpis()
                {
                    Count = (uint)this.Kpis.Count
                };
                foreach (SLKpi k in this.Kpis)
                {
                    pcd.Kpis.Append(k.ToKpi());
                }
            }

            if (this.HasTupleCache)
            {
                pcd.TupleCache = this.TupleCache.ToTupleCache();
            }

            if (this.CalculatedItems.Count > 0)
            {
                pcd.CalculatedItems = new CalculatedItems()
                {
                    Count = (uint)this.CalculatedItems.Count
                };
                foreach (SLCalculatedItem ci in this.CalculatedItems)
                {
                    pcd.CalculatedItems.Append(ci.ToCalculatedItem());
                }
            }

            if (this.CalculatedMembers.Count > 0)
            {
                pcd.CalculatedMembers = new CalculatedMembers()
                {
                    Count = (uint)this.CalculatedMembers.Count
                };
                foreach (SLCalculatedMember cm in this.CalculatedMembers)
                {
                    pcd.CalculatedMembers.Append(cm.ToCalculatedMember());
                }
            }

            if (this.Dimensions.Count > 0)
            {
                pcd.Dimensions = new Dimensions()
                {
                    Count = (uint)this.Dimensions.Count
                };
                foreach (SLDimension d in this.Dimensions)
                {
                    pcd.Dimensions.Append(d.ToDimension());
                }
            }

            if (this.MeasureGroups.Count > 0)
            {
                pcd.MeasureGroups = new MeasureGroups()
                {
                    Count = (uint)this.MeasureGroups.Count
                };
                foreach (SLMeasureGroup mg in this.MeasureGroups)
                {
                    pcd.MeasureGroups.Append(mg.ToMeasureGroup());
                }
            }

            if (this.Maps.Count > 0)
            {
                pcd.Maps = new Maps()
                {
                    Count = (uint)this.Maps.Count
                };
                foreach (SLMeasureDimensionMap mdm in this.Maps)
                {
                    pcd.Maps.Append(mdm.ToMeasureDimensionMap());
                }
            }

            return(pcd);
        }
コード例 #12
0
        protected void Page_Load(object sender, EventArgs e)
        {
            //load url arguments in variables
            string INSTANCE_NAME = @Request.QueryString["instance"];
            string CATALOG_NAME  = Request.QueryString["catalog"];
            string CUBE_NAME     = Request.QueryString["cube"];

            //Create Excel file name
            string ConnectionName = INSTANCE_NAME.Replace("\\", "_") + "_" + CATALOG_NAME + "_" + CUBE_NAME;

            Response.Write(ConnectionName);


            //Create Workbook
            string filename = Server.MapPath(@"tmp/" + ConnectionName + ".xlsx");
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();

            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();

            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
            };

            sheets.Append(sheet);

            //Add a connectionPart to the workbookpart
            ConnectionsPart connectionsPart1 = workbookpart.AddNewPart <ConnectionsPart>();
            Connections     connections1     = new Connections();
            Connection      connection1      = new Connection()
            {
                Id = (UInt32Value)1U, KeepAlive = true, Name = ConnectionName, Type = (UInt32Value)5U, RefreshedVersion = 5, Background = true
            };
            DatabaseProperties databaseProperties1 = new DatabaseProperties()
            {
                Connection = "Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + CATALOG_NAME + ";Data Source=" + @INSTANCE_NAME + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error", Command = CUBE_NAME, CommandType = (UInt32Value)1U
            };
            OlapProperties olapProperties1 = new OlapProperties()
            {
                SendLocale = true, RowDrillCount = (UInt32Value)1000U
            };

            connection1.Append(databaseProperties1);
            connection1.Append(olapProperties1);
            connections1.Append(connection1);
            connectionsPart1.Connections = connections1;

            //Add a PivottableCache part
            PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = workbookpart.AddNewPart <PivotTableCacheDefinitionPart>();
            PivotCacheDefinition          pivotCacheDefinition1          = new PivotCacheDefinition()
            {
                SaveData = false, BackgroundQuery = true, SupportSubquery = true, SupportAdvancedDrill = true
            };

            pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1;

            workbookpart.Workbook.Save();
            // Close the document.
            spreadsheetDocument.Close();

            Response.Clear();
            Response.AddHeader("content-disposition", "attachment; filename=" + @"D:\MyBI\SSAS\SSAS2012_MyBI\tmp\test.xlsx");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.WriteFile(Server.MapPath(@"tmp/" + ConnectionName + ".xlsx"));
            Response.End();
        }