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); } } }