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