public void TestSetNameName() { // Test that renaming named ranges doesn't break our new named range map XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet("First Sheet"); // Two named ranges called "name1", one scoped to sheet1 and one globally XSSFName nameSheet1 = wb.CreateName() as XSSFName; nameSheet1.NameName = "name1"; nameSheet1.RefersToFormula = "'First Sheet'!$A$1"; nameSheet1.SheetIndex = 0; XSSFName nameGlobal = wb.CreateName() as XSSFName; nameGlobal.NameName = "name1"; nameGlobal.RefersToFormula = "'First Sheet'!$B$1"; // Rename sheet-scoped name to "name2", check everything is updated properly // and that the other name is unaffected nameSheet1.NameName = "name2"; Assert.AreEqual(1, wb.GetNames("name1").Count); Assert.AreEqual(1, wb.GetNames("name2").Count); Assert.AreEqual(nameGlobal, wb.GetName("name1")); Assert.AreEqual(nameSheet1, wb.GetName("name2")); // Rename the other name to "name" and check everything again nameGlobal.NameName = "name2"; Assert.AreEqual(0, wb.GetNames("name1").Count); Assert.AreEqual(2, wb.GetNames("name2").Count); Assert.IsTrue(wb.GetNames("name2").Contains(nameGlobal)); Assert.IsTrue(wb.GetNames("name2").Contains(nameSheet1)); wb.Close(); }
public void Bug47737() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("47737.xlsx"); Assert.AreEqual(2, wb.NumberOfNames); Assert.IsNotNull(wb.GetCalculationChain()); XSSFName nm0 = (XSSFName)wb.GetNameAt(0); Assert.IsTrue(nm0.GetCTName().IsSetLocalSheetId()); Assert.AreEqual(0u, nm0.GetCTName().localSheetId); XSSFName nm1 = (XSSFName)wb.GetNameAt(1); Assert.IsTrue(nm1.GetCTName().IsSetLocalSheetId()); Assert.AreEqual(1u, nm1.GetCTName().localSheetId); wb.RemoveSheetAt(0); Assert.AreEqual(1, wb.NumberOfNames); XSSFName nm2 = (XSSFName)wb.GetNameAt(0); Assert.IsTrue(nm2.GetCTName().IsSetLocalSheetId()); Assert.AreEqual(0u, nm2.GetCTName().localSheetId); //calculation chain is Removed as well Assert.IsNull(wb.GetCalculationChain()); wb.Close(); }
public void TestRemoveSheet() { // Test removing a sheet maintains the named ranges correctly XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet("Sheet1"); wb.CreateSheet("Sheet2"); XSSFName sheet1Name = wb.CreateName() as XSSFName; sheet1Name.NameName = "name1"; sheet1Name.SheetIndex = 0; sheet1Name.RefersToFormula = "Sheet1!$A$1"; XSSFName sheet2Name = wb.CreateName() as XSSFName; sheet2Name.NameName = "name1"; sheet2Name.SheetIndex = 1; sheet2Name.RefersToFormula = "Sheet2!$A$1"; Assert.IsTrue(wb.GetAllNames().Contains(sheet1Name)); Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name)); Assert.AreEqual(2, wb.GetNames("name1").Count); Assert.AreEqual(sheet1Name, wb.GetNames("name1")[0]); Assert.AreEqual(sheet2Name, wb.GetNames("name1")[1]); // Remove sheet1, we should only have sheet2Name now wb.RemoveSheetAt(0); Assert.IsFalse(wb.GetAllNames().Contains(sheet1Name)); Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name)); Assert.AreEqual(1, wb.GetNames("name1").Count); Assert.AreEqual(sheet2Name, wb.GetNames("name1")[0]); // Check by index as well for sanity Assert.AreEqual(1, wb.NumberOfNames); Assert.AreEqual(0, wb.GetNameIndex("name1")); Assert.AreEqual(sheet2Name, wb.GetNameAt(0)); wb.Close(); }
public override void SetUp() { wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; IRow row1 = sheet.CreateRow(0); // Create a cell and Put a value in it. ICell cell = row1.CreateCell(0); cell.SetCellValue("Names"); ICell cell2 = row1.CreateCell(1); cell2.SetCellValue("#"); ICell cell7 = row1.CreateCell(2); cell7.SetCellValue("Data"); ICell cell10 = row1.CreateCell(3); cell10.SetCellValue("Value"); IRow row2 = sheet.CreateRow(1); ICell cell3 = row2.CreateCell(0); cell3.SetCellValue("Jan"); ICell cell4 = row2.CreateCell(1); cell4.SetCellValue(10); ICell cell8 = row2.CreateCell(2); cell8.SetCellValue("Apa"); ICell cell11 = row1.CreateCell(3); cell11.SetCellValue(11.11); IRow row3 = sheet.CreateRow(2); ICell cell5 = row3.CreateCell(0); cell5.SetCellValue("Ben"); ICell cell6 = row3.CreateCell(1); cell6.SetCellValue(9); ICell cell9 = row3.CreateCell(2); cell9.SetCellValue("Bepa"); ICell cell12 = row1.CreateCell(3); cell12.SetCellValue(12.12); XSSFName namedRange = sheet.Workbook.CreateName() as XSSFName; namedRange.RefersToFormula = (/*setter*/ sheet.SheetName + "!" + "A1:C2"); pivotTable = sheet.CreatePivotTable(namedRange, new CellReference("H5")); XSSFSheet offsetSheet = wb.CreateSheet() as XSSFSheet; IRow tableRow_1 = offsetSheet.CreateRow(1); offsetOuterCell = tableRow_1.CreateCell(1); offsetOuterCell.SetCellValue(-1); ICell tableCell_1_1 = tableRow_1.CreateCell(2); tableCell_1_1.SetCellValue("Row #"); ICell tableCell_1_2 = tableRow_1.CreateCell(3); tableCell_1_2.SetCellValue("Exponent"); ICell tableCell_1_3 = tableRow_1.CreateCell(4); tableCell_1_3.SetCellValue("10^Exponent"); IRow tableRow_2 = offsetSheet.CreateRow(2); ICell tableCell_2_1 = tableRow_2.CreateCell(2); tableCell_2_1.SetCellValue(0); ICell tableCell_2_2 = tableRow_2.CreateCell(3); tableCell_2_2.SetCellValue(0); ICell tableCell_2_3 = tableRow_2.CreateCell(4); tableCell_2_3.SetCellValue(1); IRow tableRow_3 = offsetSheet.CreateRow(3); ICell tableCell_3_1 = tableRow_3.CreateCell(2); tableCell_3_1.SetCellValue(1); ICell tableCell_3_2 = tableRow_3.CreateCell(3); tableCell_3_2.SetCellValue(1); ICell tableCell_3_3 = tableRow_3.CreateCell(4); tableCell_3_3.SetCellValue(10); IRow tableRow_4 = offsetSheet.CreateRow(4); ICell tableCell_4_1 = tableRow_4.CreateCell(2); tableCell_4_1.SetCellValue(2); ICell tableCell_4_2 = tableRow_4.CreateCell(3); tableCell_4_2.SetCellValue(2); ICell tableCell_4_3 = tableRow_4.CreateCell(4); tableCell_4_3.SetCellValue(100); namedRange = sheet.Workbook.CreateName() as XSSFName; namedRange.RefersToFormula = (/*setter*/ "C2:E4"); namedRange.SheetIndex = (/*setter*/ sheet.Workbook.GetSheetIndex(sheet)); offsetPivotTable = offsetSheet.CreatePivotTable(namedRange, new CellReference("C6")); }