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