C# WorkbookPart.DeletePart方法代码示例

        private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, SaveContext context)
        {
            var thisRelId = context.RelIdGenerator.GetNext(RelType.Workbook);
            if (workbookPart.CalculationChainPart == null)
                workbookPart.AddNewPart<CalculationChainPart>(thisRelId);

            if (workbookPart.CalculationChainPart.CalculationChain == null)
                workbookPart.CalculationChainPart.CalculationChain = new CalculationChain();

            var calculationChain = workbookPart.CalculationChainPart.CalculationChain;
            calculationChain.RemoveAllChildren<CalculationCell>();

            foreach (var worksheet in WorksheetsInternal)
            {
                var cellsWithoutFormulas = new HashSet<String>();
                foreach (var c in worksheet.Internals.CellsCollection.GetCells())
                {
                    if (XLHelper.IsNullOrWhiteSpace(c.FormulaA1))
                        cellsWithoutFormulas.Add(c.Address.ToStringRelative());
                    else
                    {
                        if (c.FormulaA1.StartsWith("{"))
                        {
                            var cc = new CalculationCell
                            {
                                CellReference = c.Address.ToString(),
                                SheetId = worksheet.SheetId
                            };

                            if (c.FormulaReference.FirstAddress.Equals(c.Address))
                            {
                                cc.Array = true;
                                calculationChain.AppendChild(cc);
                                calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), InChildChain = true});
                            }
                            else
                            {
                                calculationChain.AppendChild(cc);
                            }
                        }
                        else
                        {
                            calculationChain.AppendChild(new CalculationCell
                            {
                                CellReference = c.Address.ToString(),
                                SheetId = worksheet.SheetId
                            });
                        }
                    }
                }

                //var cCellsToRemove = new List<CalculationCell>();
                var m = from cc in calculationChain.Elements<CalculationCell>()
                    where !(cc.SheetId != null || cc.InChildChain != null)
                          && calculationChain.Elements<CalculationCell>()
                              .Where(c1 => c1.SheetId != null)
                              .Select(c1 => c1.CellReference.Value)
                              .Contains(cc.CellReference.Value)
                          || cellsWithoutFormulas.Contains(cc.CellReference.Value)
                    select cc;
                //m.ToList().ForEach(cc => cCellsToRemove.Add(cc));
                m.ToList().ForEach(cc => calculationChain.RemoveChild(cc));
            }

            if (!calculationChain.Any())
                workbookPart.DeletePart(workbookPart.CalculationChainPart);
        }

        /// <summary>
        ///     Deletes a sheet given the workbook the sheet is in and the sheetname;
        /// </summary>
        /// <param name="wbPart">Workbook the sheet is part of</param>
        /// <param name="sheetToDelete">The name of the sheet to be deleted</param>
        private void deleteAWorkSheet(WorkbookPart wbPart, string sheetToDelete)
        {
            string Sheetid = "";

            // Get the pivot Table Parts
            IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts =
                wbPart.PivotTableCacheDefinitionParts;
            var pvtTableCacheDefinationPart =
                new Dictionary<PivotTableCacheDefinitionPart, string>();
            foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts) {
                PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                //Check if this CacheSource is linked to SheetToDelete
                IEnumerable<CacheSource> 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>()
                      .FirstOrDefault(s => s.Name == sheetToDelete);
            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.
            var worksheetPart =
                (WorksheetPart) (wbPart.GetPartById(theSheet.Id));
            theSheet.Remove();

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

            //Get the DefinedNames
            DefinedNames definedNames =
                wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
            if (definedNames != null) {
                var 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();
                }
            }

            // Save the workbook.
            wbPart.Workbook.Save();
        }

        // http://blogs.msdn.com/b/vsod/archive/2010/02/05/how-to-delete-a-worksheet-from-excel-using-open-xml-sdk-2-0.aspx
        private void DeleteSheetAndDependencies(WorkbookPart wbPart, string sheetId)
        {
            //Get the SheetToDelete from workbook.xml
            Sheet worksheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Id == sheetId).FirstOrDefault();
            if (worksheet == null)
            { }

            string sheetName = worksheet.Name;
            // 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 == sheetName);
                if (pvtCahce.Count() > 0)
                {
                    pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                }
            }
            foreach (var Item in pvtTableCacheDefinationPart)
            {
                wbPart.DeletePart(Item.Key);
            }

            // Remove the sheet reference from the workbook.
            WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(sheetId));
            worksheet.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(worksheet.Name + "!"))
                        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);
                }
            }
        }

        public static void DeleteWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            //string sheetid = "";

            // Get the pivot Table Parts
            IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = workbookPart.PivotTableCacheDefinitionParts;
            var pvtTableCacheDefinationPart =
                (from pivotTableCacheDefinitionPart in pvtTableCacheParts
                 let pvtCacheDef = pivotTableCacheDefinitionPart.PivotCacheDefinition
                 let pvtCache = pvtCacheDef.Descendants<CacheSource>().
                 Where(s => s.WorksheetSource.Sheet == sheetName)
                 where pvtCache.Count() > 0 select pivotTableCacheDefinitionPart).
                 ToDictionary(pivotTableCacheDefinitionPart => pivotTableCacheDefinitionPart, pivotTableCacheDefinitionPart => pivotTableCacheDefinitionPart.ToString());

            foreach (var item in pvtTableCacheDefinationPart)
            {
                workbookPart.DeletePart(item.Key);
            }

            //Get the SheetToDelete from workbook.xml
            Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
            if (sheet == null)
            {
                // The specified sheet doesn't exist.
                return;
            }

            //Store the SheetID for the reference
            var sheetid = sheet.SheetId;

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

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

            //Get the DefinedNames
            var definedNames = workbookPart.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(sheetName + "!"))
                        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 = workbookPart.CalculationChainPart;
            if (calChainPart != null)
            {
                var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => (uint)c.SheetId.Value == sheetid);
                foreach (CalculationCell item in calChainEntries)
                {
                    item.Remove();
                }

                if (calChainPart.CalculationChain.Count() == 0)
                {
                    workbookPart.DeletePart(calChainPart);
                }
            }
        }