Hello Vikas,
Here are some suggestions:
"Delete specified sheets if the worksheets contains formulaes"
When you add a formula to the worksheet(s), an instance of the part type 'Calculation Chain Part' (calcChain.xml) is added to the package. It contains an ordered set of references to all cells in all worksheets in the workbook whose value is calculated from any formula.
So if you want to delete a worksheet containing formulas, you need to delete the entries from calcChain.xml as well.
Here is the sample code to do it: Please note that in this code the sheetName and the id (doc1.SelectNodes("//ss:c[@i='1']", nsManager) is hardcoded. You can get this value easily using node variable and querying for the attributes.
using (SpreadsheetDocument xlDoc = SpreadsheetDocument.Open(filePath, true))
{
XmlDocument doc = new XmlDocument();
doc.Load(xlDoc.WorkbookPart.GetStream());
XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
string searchString = string.Format("//d:sheet[@name='{0}']", "Sheet1");
XmlNode node = doc.SelectSingleNode(searchString, nsManager);
if (node != null)
{
XmlAttribute relationAttribute = node.Attributes["r:id"];
if (relationAttribute != null)
{
string relId = relationAttribute.Value;
CalculationChainPart w = xlDoc.WorkbookPart.CalculationChainPart;
XmlDocument doc1 = new XmlDocument();
doc1.Load(w.GetStream());
XmlNamespaceManager nsManager1 = new XmlNamespaceManager(doc1.NameTable);
nsManager1.AddNamespace("ss", doc1.DocumentElement.NamespaceURI);
XmlNodeList ss1= doc1.SelectNodes("//ss:c[@i='1']", nsManager1);
foreach (XmlNode w1 in ss1)
{
w1.ParentNode.RemoveChild(w1);
}
MessageBox.Show(doc1.InnerXml);
xlDoc.WorkbookPart.DeletePart(relId);
node.ParentNode.RemoveChild(node);
doc.Save(xlDoc.WorkbookPart.GetStream(FileMode.Create));
doc1.Save(w.GetStream(FileMode.Create));
}
}
}