public void TestBug47737()
{
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());
}
public IEvaluationName GetName(String name, int sheetIndex)
{
for (int i = 0; i < _uBook.NumberOfNames; i++)
{
IName nm = _uBook.GetNameAt(i);
String nameText = nm.NameName;
if (name.Equals(nameText, StringComparison.InvariantCultureIgnoreCase) && nm.SheetIndex == sheetIndex)
{
return(new Name(_uBook.GetNameAt(i), i, this));
}
}
return(sheetIndex == -1 ? null : GetName(name, -1));
}
/// <summary>
/// Return EvaluationName wrapper around the matching XSSFName (named range)
/// </summary>
/// <param name="name">case-aware but case-insensitive named range in workbook</param>
/// <param name="sheetIndex">index of sheet if named range scope is limited to one sheet
/// if named range scope is global to the workbook, sheetIndex is -1.</param>
/// <returns>If name is a named range in the workbook, returns
/// EvaluationName corresponding to that named range
/// Returns null if there is no named range with the same name and scope in the workbook
/// </returns>
public IEvaluationName GetName(String name, int sheetIndex)
{
for (int i = 0; i < _uBook.NumberOfNames; i++)
{
XSSFName nm = _uBook.GetNameAt(i) as XSSFName;
String nameText = nm.NameName;
int nameSheetindex = nm.SheetIndex;
if (name.Equals(nameText, StringComparison.CurrentCultureIgnoreCase) &&
(nameSheetindex == -1 || nameSheetindex == sheetIndex))
{
return(new Name(nm, i, this));
}
}
return(sheetIndex == -1 ? null : GetName(name, -1));
}
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 void TestRepeatingRowsAndColums()
{
// First Test that Setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("First Sheet");
wb.SetRepeatingRowsAndColumns(0, -1, -1, -1, -1);
// Set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
//sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
IName nr1 = wb.GetNameAt(0);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName);
Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula);
//remove the columns part
wb.SetRepeatingRowsAndColumns(0, -1, -1, 0, 3);
Assert.AreEqual("'First Sheet'!$1:$4", nr1.RefersToFormula);
//revert
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
//remove the rows part
wb.SetRepeatingRowsAndColumns(0, 0, 0, -1, -1);
Assert.AreEqual("'First Sheet'!$A:$A", nr1.RefersToFormula);
//revert
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
// Save and re-open
IWorkbook nwb = XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName);
Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula);
// check that Setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
nwb.CreateSheet("SecondSheet");
nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);
Assert.AreEqual(2, nwb.NumberOfNames);
IName nr2 = nwb.GetNameAt(1);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr2.NameName);
Assert.AreEqual("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.RefersToFormula);
nwb.SetRepeatingRowsAndColumns(1, -1, -1, -1, -1);
}
public void TestRepeatingRowsAndColums()
{
// First Test that Setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("First Sheet");
wb.SetRepeatingRowsAndColumns(0, -1, -1, -1, -1);
// Set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
//sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
IName nr1 = wb.GetNameAt(0);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName);
Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula);
//remove the columns part
wb.SetRepeatingRowsAndColumns(0, -1, -1, 0, 3);
Assert.AreEqual("'First Sheet'!$1:$4", nr1.RefersToFormula);
//revert
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
//remove the rows part
wb.SetRepeatingRowsAndColumns(0, 0, 0, -1, -1);
Assert.AreEqual("'First Sheet'!$A:$A", nr1.RefersToFormula);
//revert
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
// Save and re-open
IWorkbook nwb = XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName);
Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula);
// check that Setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
nwb.CreateSheet("SecondSheet");
nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);
Assert.AreEqual(2, nwb.NumberOfNames);
IName nr2 = nwb.GetNameAt(1);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr2.NameName);
Assert.AreEqual("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.RefersToFormula);
nwb.SetRepeatingRowsAndColumns(1, -1, -1, -1, -1);
}
