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