public void RemoveName(XSSFName name)
{
if (!this.namedRanges.Remove(name))
{
throw new ArgumentException("Name was not found: " + (object)name);
}
}
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 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 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 IName CreateName()
{
XSSFName xssfName = new XSSFName(new CT_DefinedName()
{
name = ""
}, this);
this.namedRanges.Add(xssfName);
return((IName)xssfName);
}
/// <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));
}
/**
* Compares this name to the specified object.
* The result is <code>true</code> if the argument is XSSFName and the
* underlying CTDefinedName bean Equals to the CTDefinedName representing this name
*
* @param o the object to compare this <code>XSSFName</code> against.
* @return <code>true</code> if the <code>XSSFName </code>are Equal;
* <code>false</code> otherwise.
*/
public override bool Equals(Object o)
{
if (o == this)
{
return(true);
}
if (!(o is XSSFName))
{
return(false);
}
XSSFName cf = (XSSFName)o;
return(_ctName.name == cf.GetCTName().name&& _ctName.localSheetId == cf.GetCTName().localSheetId);
}
public override bool Equals(object o)
{
if (o == this)
{
return(true);
}
if (!(o is XSSFName))
{
return(false);
}
XSSFName xssfName = (XSSFName)o;
if (this._ctName.name == xssfName.GetCTName().name)
{
return((int)this._ctName.localSheetId == (int)xssfName.GetCTName().localSheetId);
}
return(false);
}
public void SetPrintArea(int sheetIndex, string reference)
{
XSSFName xssfName = this.GetBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex) ?? this.CreateBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
string[] strArray = XSSFWorkbook.COMMA_PATTERN.Split(reference);
StringBuilder out1 = new StringBuilder(32);
for (int index = 0; index < strArray.Length; ++index)
{
if (index > 0)
{
out1.Append(",");
}
SheetNameFormatter.AppendFormat(out1, this.GetSheetName(sheetIndex));
out1.Append("!");
out1.Append(strArray[index]);
}
xssfName.RefersToFormula = out1.ToString();
}
internal XSSFName CreateBuiltInName(string builtInName, int sheetNumber)
{
this.ValidateSheetIndex(sheetNumber);
CT_DefinedName name = (this.workbook.definedNames == null ? this.workbook.AddNewDefinedNames() : this.workbook.definedNames).AddNewDefinedName();
name.name = builtInName;
name.localSheetId = (uint)sheetNumber;
name.localSheetIdSpecified = true;
XSSFName xssfName = new XSSFName(name, this);
foreach (object namedRange in this.namedRanges)
{
if (namedRange.Equals((object)xssfName))
{
throw new POIXMLException("Builtin (" + builtInName + ") already exists for sheet (" + (object)sheetNumber + ")");
}
}
this.namedRanges.Add(xssfName);
return(xssfName);
}
public String ResolveNameXText(NameXPtg n)
{
int idx = n.NameIndex;
String name = null;
// First, try to find it as a User Defined Function
IndexedUDFFinder udfFinder = (IndexedUDFFinder)GetUDFFinder();
name = udfFinder.GetFunctionName(idx);
if (name != null)
{
return(name);
}
// Otherwise, try it as a named range
XSSFName xname = _uBook.GetNameAt(idx) as XSSFName;
if (xname != null)
{
name = xname.NameName;
}
return(name);
}
public Name(IName name, int index, IFormulaParsingWorkbook fpBook)
{
_nameRecord = (XSSFName)name;
_index = index;
_fpBook = fpBook;
}
public IName CreateName()
{
CT_DefinedName ctName = new CT_DefinedName();
ctName.name = ("");
XSSFName name = new XSSFName(ctName, this);
namedRanges.Add(name);
return name;
}
/**
* Generates a NameRecord to represent a built-in region
*
* @return a new NameRecord
* @throws ArgumentException if sheetNumber is invalid
* @throws POIXMLException if such a name already exists in the workbook
*/
internal XSSFName CreateBuiltInName(String builtInName, int sheetNumber)
{
ValidateSheetIndex(sheetNumber);
CT_DefinedNames names = workbook.definedNames == null ? workbook.AddNewDefinedNames() : workbook.definedNames;
CT_DefinedName nameRecord = names.AddNewDefinedName();
nameRecord.name = (builtInName);
nameRecord.localSheetId = (uint)sheetNumber;
nameRecord.localSheetIdSpecified = true;
XSSFName name = new XSSFName(nameRecord, this);
foreach (XSSFName nr in namedRanges)
{
if (nr.Equals(name))
throw new POIXMLException("Builtin (" + builtInName
+ ") already exists for sheet (" + sheetNumber + ")");
}
namedRanges.Add(name);
return name;
}
/**
* As {@link #removeName(String)} is not necessarily unique
* (name + sheet index is unique), this method is more accurate.
*
* @param name the name to remove.
*/
public void RemoveName(XSSFName name)
{
if (!namedRanges.Remove(name))
{
throw new ArgumentException("Name was not found: " + name);
}
}
public Name(IName name, int index, IFormulaParsingWorkbook fpBook)
{
_nameRecord = (XSSFName)name;
_index = index;
_fpBook = fpBook;
}
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"));
}
