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();
}
/**
* 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
int numberOfNames = _wb.NumberOfNames;
foreach (IName nm in _wb.GetAllNames())
{
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);
}
}
}
}
}
internal static void FillNamedAreas(DataSet dataSet, XSSFWorkbook workBook)
{
foreach (var areaName in workBook.GetAllNames())
{
if (areaName.IsDeleted)
{
continue;
}
if (dataSet.Tables.Contains(areaName.NameName))
{
var dataTable = dataSet.Tables[areaName.NameName];
FillNamedArea(dataTable, areaName, workBook);
}
}
}
/// <summary>
/// Returns all defined names
/// </summary>
/// <returns>Returns all defined names</returns>
public IList <IName> GetAllNames()
{
return(_wb.GetAllNames());
}