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());
}
public IEvaluationName GetName(String name, int sheetIndex)
{
for (int i = 0; i < _uBook.NumberOfNames; i++)
{
IName nm = _uBook.GetNameAt(i);
String nameText = nm.NameName;
int nameSheetindex = nm.SheetIndex;
if (name.Equals(nameText, StringComparison.CurrentCultureIgnoreCase) &&
(nameSheetindex == -1 || nameSheetindex == sheetIndex))
{
return(new Name(_uBook.GetNameAt(i), i, this));
}
}
return(sheetIndex == -1 ? null : GetName(name, -1));
}
/**
* Update sheet name in all formulas and named ranges.
* Called from {@link XSSFWorkbook#setSheetName(int, String)}
* <p/>
* <p>
* The idea is to parse every formula and render it back to string
* with the updated sheet name. This is done by parsing into Ptgs,
* looking for ones with sheet references in them, and changing those
* </p>
*
* @param sheetIndex the 0-based index of the sheet being changed
* @param oldName the old sheet name
* @param newName the new sheet name
*/
public void UpdateSheetName(int sheetIndex, string oldName, string newName)
{
// update named ranges
for (int i = 0; i < _wb.NumberOfNames; i++)
{
IName nm = _wb.GetNameAt(i);
if (nm.SheetIndex == -1 || nm.SheetIndex == sheetIndex)
{
UpdateName(nm, oldName, newName);
}
}
// update formulas
foreach (ISheet sh in _wb)
{
foreach (IRow row in sh)
{
foreach (ICell cell in row)
{
if (cell.CellType == CellType.Formula)
{
UpdateFormula((XSSFCell)cell, oldName, newName);
}
}
}
}
}
/**
* Update sheet name in all formulas and named ranges.
* Called from {@link XSSFWorkbook#SetSheetName(int, String)}
* <p/>
* <p>
* The idea is to parse every formula and render it back to string
* with the updated sheet name. The IFormulaParsingWorkbook passed to the formula Parser
* is constructed from the old workbook (sheet name is not yet updated) and
* the FormulaRenderingWorkbook passed to FormulaRenderer#toFormulaString is a custom implementation that
* returns the new sheet name.
* </p>
*
* @param sheetIndex the 0-based index of the sheet being Changed
* @param name the new sheet name
*/
public void UpdateSheetName(int sheetIndex, String name)
{
/**
* An instance of FormulaRenderingWorkbook that returns
*/
IFormulaRenderingWorkbook frwb = new XSSFFormulaRenderingWorkbook(_fpwb, sheetIndex, name);
// update named ranges
for (int i = 0; i < _wb.NumberOfNames; i++)
{
IName nm = _wb.GetNameAt(i);
if (nm.SheetIndex == -1 || nm.SheetIndex == sheetIndex)
{
UpdateName(nm, frwb);
}
}
// update formulas
foreach (ISheet sh in _wb)
{
foreach (IRow row in sh)
{
foreach (ICell cell in row)
{
if (cell.CellType == CellType.Formula)
{
UpdateFormula((XSSFCell)cell, frwb);
}
}
}
}
}
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();
}
private List <IName> GetNamesFromWorkbook(XSSFWorkbook workbook)
{
List <IName> names = new List <IName>();
int numberOfNames = workbook.NumberOfNames;
for (int i = 0; i < numberOfNames; i++)
{
names.Add(workbook.GetNameAt(i));
}
return(names);
}
public IName GetNameAt(int nameIndex)
{
return(XssfWorkbook.GetNameAt(nameIndex));
}
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();
XSSFSheet sheet1 = (XSSFSheet)wb.CreateSheet("First Sheet");
sheet1.RepeatingRows = (null);
sheet1.RepeatingColumns = (null);
// Set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
sheet1.RepeatingRows = (CellRangeAddress.ValueOf("1:4"));
sheet1.RepeatingColumns = (CellRangeAddress.ValueOf("A:A"));
//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
sheet1.RepeatingColumns = (null);
Assert.AreEqual("'First Sheet'!$1:$4", nr1.RefersToFormula);
//revert
sheet1.RepeatingColumns = (CellRangeAddress.ValueOf("A:A"));
//remove the rows part
sheet1.RepeatingRows = (null);
Assert.AreEqual("'First Sheet'!$A:$A", nr1.RefersToFormula);
//revert
sheet1.RepeatingRows = (CellRangeAddress.ValueOf("1:4"));
// 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
XSSFSheet sheet2 = (XSSFSheet)nwb.CreateSheet("SecondSheet");
sheet2.RepeatingRows = (CellRangeAddress.ValueOf("1:1"));
sheet2.RepeatingColumns = (CellRangeAddress.ValueOf("B:C"));
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);
sheet2.RepeatingRows = (null);
sheet2.RepeatingColumns = (null);
}
