public void Test46535() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("46535.xlsx"); CalculationChain chain = wb.GetCalculationChain(); //the bean holding the reference to the formula to be deleted CT_CalcCell c = chain.GetCTCalcChain().GetCArray(0); int cnt = chain.GetCTCalcChain().c.Count; Assert.AreEqual(10, c.i); Assert.AreEqual("E1", c.r); ISheet sheet = wb.GetSheet("Test"); ICell cell = sheet.GetRow(0).GetCell(4); Assert.AreEqual(CellType.FORMULA, cell.CellType); cell.SetCellFormula(null); //the count of items is less by one c = chain.GetCTCalcChain().GetCArray(0); int cnt2 = chain.GetCTCalcChain().c.Count; Assert.AreEqual(cnt - 1, cnt2); //the first item in the calculation chain is the former second one Assert.AreEqual(10, c.i); Assert.AreEqual("C1", c.r); Assert.AreEqual(CellType.STRING, cell.CellType); cell.SetCellValue("ABC"); Assert.AreEqual(CellType.STRING, cell.CellType); }
public void RemoveAllFormulas() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("TwoFunctions.xlsx"); CalculationChain chain = wb.GetCalculationChain(); //the bean holding the reference to the formula to be deleted CT_CalcCell c = chain.GetCTCalcChain().GetCArray(0); int cnt = chain.GetCTCalcChain().c.Count; Assert.AreEqual(1, c.i); Assert.AreEqual("A5", c.r); Assert.AreEqual(2, cnt); ISheet sheet = wb.GetSheet("Sheet1"); ICell cell = sheet.GetRow(4).GetCell(0); Assert.AreEqual(CellType.Formula, cell.CellType); cell.SetCellFormula(null); //the count of items is less by one c = chain.GetCTCalcChain().GetCArray(0); int cnt2 = chain.GetCTCalcChain().c.Count; Assert.AreEqual(cnt - 1, cnt2); //the first item in the calculation chain is the former second one Assert.AreEqual(1, c.i); Assert.AreEqual("A4", c.r); Assert.AreEqual(1, cnt2); //remove final formula from spread sheet ICell cell2 = sheet.GetRow(3).GetCell(0); Assert.AreEqual(CellType.Formula, cell2.CellType); cell2.SetCellFormula(null); //the count of items within the chain should be 0 int cnt3 = chain.GetCTCalcChain().c.Count; Assert.AreEqual(0, cnt3); }
internal override void OnDocumentRead() { try { XmlDocument xmldoc = ConvertStreamToXml(GetPackagePart().GetInputStream()); doc = WorkbookDocument.Parse(xmldoc, NamespaceManager); this.workbook = doc.Workbook; Dictionary<String, XSSFSheet> shIdMap = new Dictionary<String, XSSFSheet>(); Dictionary<String, ExternalLinksTable> elIdMap = new Dictionary<String, ExternalLinksTable>(); foreach (POIXMLDocumentPart p in GetRelations()) { if (p is SharedStringsTable) sharedStringSource = (SharedStringsTable)p; else if (p is StylesTable) stylesSource = (StylesTable)p; else if (p is ThemesTable) theme = (ThemesTable)p; else if (p is CalculationChain) calcChain = (CalculationChain)p; else if (p is MapInfo) mapInfo = (MapInfo)p; else if (p is XSSFSheet) { shIdMap.Add(p.GetPackageRelationship().Id,(XSSFSheet)p); } else if (p is ExternalLinksTable) { elIdMap.Add(p.GetPackageRelationship().Id, (ExternalLinksTable)p); } } bool packageReadOnly = (Package.GetPackageAccess() == PackageAccess.READ); if (stylesSource == null) { // Create Styles if it is missing if (packageReadOnly) { stylesSource = new StylesTable(); } else { stylesSource = (StylesTable)CreateRelationship(XSSFRelation.STYLES, XSSFFactory.GetInstance()); } } stylesSource.SetTheme(theme); if (sharedStringSource == null) { //Create SST if it is missing if (packageReadOnly) { sharedStringSource = new SharedStringsTable(); } else { sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance()); } } // Load individual sheets. The order of sheets is defined by the order // of CTSheet elements in the workbook sheets = new List<XSSFSheet>(shIdMap.Count); foreach (CT_Sheet ctSheet in this.workbook.sheets.sheet) { XSSFSheet sh = null; if(shIdMap.ContainsKey(ctSheet.id)) sh = shIdMap[ctSheet.id]; if (sh == null) { logger.Log(POILogger.WARN, "Sheet with name " + ctSheet.name + " and r:id " + ctSheet.id + " was defined, but didn't exist in package, skipping"); continue; } sh.sheet = ctSheet; sh.OnDocumentRead(); sheets.Add(sh); } // Load the external links tables. Their order is defined by the order // of CTExternalReference elements in the workbook externalLinks = new List<ExternalLinksTable>(elIdMap.Count); if (this.workbook.IsSetExternalReferences()) { foreach (CT_ExternalReference er in this.workbook.externalReferences.externalReference) { ExternalLinksTable el = null; if(elIdMap.ContainsKey(er.id)) el = elIdMap[(er.id)]; if (el == null) { logger.Log(POILogger.WARN, "ExternalLinksTable with r:id " + er.id + " was defined, but didn't exist in package, skipping"); continue; } externalLinks.Add(el); } } // Process the named ranges ReprocessNamedRanges(); } catch (XmlException e) { throw new POIXMLException(e); } }
private void SaveCalculationChain() { if (calcChain != null) { int count = calcChain.GetCTCalcChain().SizeOfCArray(); if (count == 0) { RemoveRelation(calcChain); calcChain = null; } } }
/** * Gracefully remove references to the sheet being deleted * * @param index the 0-based index of the sheet to delete */ private void OnSheetDelete(int index) { //delete the CT_Sheet reference from workbook.xml workbook.sheets.RemoveSheet(index); //calculation chain is auxiliary, remove it as it may contain orphan references to deleted cells if (calcChain != null) { RemoveRelation(calcChain); calcChain = null; } List<XSSFName> toRemoveNamedRanges=new List<XSSFName>(); //adjust indices of names ranges foreach (XSSFName nm in namedRanges) { CT_DefinedName ct = nm.GetCTName(); if (!ct.IsSetLocalSheetId()) continue; if (ct.localSheetId == index) { toRemoveNamedRanges.Add(nm); } else if (ct.localSheetId > index) { // Bump down by one, so still points at the same sheet ct.localSheetId = (ct.localSheetId - 1); ct.localSheetIdSpecified = true; } } foreach (XSSFName nm in toRemoveNamedRanges) { namedRanges.Remove(nm); } }
internal override void OnDocumentRead() { try { XmlDocument xmldoc = ConvertStreamToXml(GetPackagePart().GetInputStream()); doc = WorkbookDocument.Parse(xmldoc, NamespaceManager); this.workbook = doc.Workbook; Dictionary<String, XSSFSheet> shIdMap = new Dictionary<String, XSSFSheet>(); foreach (POIXMLDocumentPart p in GetRelations()) { if (p is SharedStringsTable) sharedStringSource = (SharedStringsTable)p; else if (p is StylesTable) stylesSource = (StylesTable)p; else if (p is ThemesTable) theme = (ThemesTable)p; else if (p is CalculationChain) calcChain = (CalculationChain)p; else if (p is MapInfo) mapInfo = (MapInfo)p; else if (p is XSSFSheet) { shIdMap.Add(p.GetPackageRelationship().Id,(XSSFSheet)p); } } if (null != stylesSource) { stylesSource.SetTheme(theme); } if (sharedStringSource == null) { //Create SST if it is missing sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance()); } // Load individual sheets. The order of sheets is defined by the order of CT_Sheet elements in the workbook sheets = new List<XSSFSheet>(shIdMap.Count); foreach (CT_Sheet ctSheet in this.workbook.sheets.sheet) { XSSFSheet sh = shIdMap[ctSheet.id]; if (sh == null) { logger.Log(POILogger.WARN, "Sheet with name " + ctSheet.name + " and r:id " + ctSheet.id + " was defined, but didn't exist in package, skipping"); continue; } sh.sheet = ctSheet; sh.OnDocumentRead(); sheets.Add(sh); } // Process the named ranges namedRanges = new List<XSSFName>(); if (workbook.IsSetDefinedNames()) { foreach (CT_DefinedName ctName in workbook.definedNames.definedName) { namedRanges.Add(new XSSFName(ctName, this)); } } } catch (XmlException e) { throw new POIXMLException(e); } }