public void TestMultipleNamedWrite()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("TestSheet1");
String sheetName = wb.GetSheetName(0);
Assert.AreEqual("TestSheet1", sheetName);
//Creating new Named Range
NPOI.SS.UserModel.Name newNamedRange = wb.CreateName();
newNamedRange.NameName = ("RangeTest");
newNamedRange.RefersToFormula = (sheetName + "!$D$4:$E$8");
//Creating another new Named Range
NPOI.SS.UserModel.Name newNamedRange2 = wb.CreateName();
newNamedRange2.NameName = ("AnotherTest");
newNamedRange2.RefersToFormula = (sheetName + "!$F$1:$G$6");
NPOI.SS.UserModel.Name namedRange1 = wb.GetNameAt(0);
String referece = namedRange1.RefersToFormula;
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
NPOI.SS.UserModel.Name nm = wb.GetNameAt(wb.GetNameIndex("RangeTest"));
Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.IsTrue((wb.GetSheetName(0) + "!$D$4:$E$8").Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula);
nm = wb.GetNameAt(wb.GetNameIndex("AnotherTest"));
Assert.IsTrue("AnotherTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.IsTrue(newNamedRange2.RefersToFormula.Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula);
}
public void TestDiscontinousReference()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls");
HSSFWorkbook wb = new HSSFWorkbook(is1);
NPOI.HSSF.Model.Workbook workbook = wb.Workbook;
HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual(1, wb.NumberOfNames);
String sheetName = "Tabelle1";
String rawRefA = "$C$10:$C$14";
String rawRefB = "$C$16:$C$18";
String refA = sheetName + "!" + rawRefA;
String refB = sheetName + "!" + rawRefB;
String ref1 = refA + "," + refB;
// Check the low level record
NameRecord nr = workbook.GetNameRecord(0);
Assert.IsNotNull(nr);
Assert.AreEqual("test", nr.NameText);
Ptg[] def = nr.NameDefinition;
Assert.AreEqual(4, def.Length);
MemFuncPtg ptgA = (MemFuncPtg)def[0];
Area3DPtg ptgB = (Area3DPtg)def[1];
Area3DPtg ptgC = (Area3DPtg)def[2];
UnionPtg ptgD = (UnionPtg)def[3];
Assert.AreEqual("", ptgA.ToFormulaString());
Assert.AreEqual(refA, ptgB.ToFormulaString(eb));
Assert.AreEqual(refB, ptgC.ToFormulaString(eb));
Assert.AreEqual(",", ptgD.ToFormulaString());
Assert.AreEqual(ref1, NPOI.HSSF.Model.HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition));
// Check the high level definition
int idx = wb.GetNameIndex("test");
Assert.AreEqual(0, idx);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(idx);
// Should have 2 references
Assert.AreEqual(ref1, aNamedCell.RefersToFormula);
// Check the parsing of the reference into cells
Assert.IsFalse(AreaReference.IsContiguous(aNamedCell.RefersToFormula));
AreaReference[] arefs = AreaReference.GenerateContiguous(aNamedCell.RefersToFormula);
Assert.AreEqual(2, arefs.Length);
Assert.AreEqual(refA, arefs[0].FormatAsString());
Assert.AreEqual(refB, arefs[1].FormatAsString());
for (int i = 0; i < arefs.Length; i++)
{
AreaReference ar = arefs[i];
ConfirmResolveCellRef(wb, ar.FirstCell);
ConfirmResolveCellRef(wb, ar.LastCell);
}
}
public void TestNamedRange()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");
//Creating new Named Range
IName newNamedRange = wb.CreateName();
//Getting Sheet Name for the reference
String sheetName = wb.GetSheetName(0);
//Setting its name
newNamedRange.NameName = "RangeTest";
//Setting its reference
newNamedRange.RefersToFormula = sheetName + "!$D$4:$E$8";
//Getting NAmed Range
IName namedRange1 = wb.GetNameAt(0);
//Getting it sheet name
sheetName = namedRange1.SheetName;
// sanity check
SanityChecker c = new SanityChecker();
c.CheckHSSFWorkbook(wb);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
IName nm = wb.GetNameAt(wb.GetNameIndex("RangeTest"));
Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.AreEqual(wb.GetSheetName(0) + "!$D$4:$E$8", nm.RefersToFormula);
}
public void TestNamedCell_2()
{
// setup for this Testcase
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sname);
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cvalue));
// Create named range for a single cell using cellreference
NPOI.SS.UserModel.Name namedCell = wb.CreateName();
namedCell.NameName = (cname);
String reference = sname + "!A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cname);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
CellReference cref = new CellReference(aNamedCell.RefersToFormula);
Assert.IsNotNull(cref);
NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName);
Row r = sheet.GetRow(cref.Row);
Cell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cvalue, "Contents of cell retrieved by its named reference");
}
public void TestNames()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
wb.GetNameAt(0);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("no defined names"), e.Message);
}
HSSFName name = (HSSFName)wb.CreateName();
Assert.IsNotNull(name);
Assert.IsNull(wb.GetName("somename"));
name.NameName = ("myname");
Assert.IsNotNull(wb.GetName("myname"));
Assert.AreEqual(0, wb.GetNameIndex(name));
Assert.AreEqual(0, wb.GetNameIndex("myname"));
try
{
wb.GetNameAt(5);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message);
}
try
{
wb.GetNameAt(-3);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message);
}
}
public void TestMultiNamedRange()
{
// Create a new workbook
HSSFWorkbook wb = new HSSFWorkbook();
// Create a worksheet 'sheet1' in the new workbook
wb.CreateSheet();
wb.SetSheetName(0, "sheet1");
// Create another worksheet 'sheet2' in the new workbook
wb.CreateSheet();
wb.SetSheetName(1, "sheet2");
// Create a new named range for worksheet 'sheet1'
NPOI.SS.UserModel.Name namedRange1 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet1'
namedRange1.NameName = ("RangeTest1");
// Set the reference for the named range for worksheet 'sheet1'
namedRange1.RefersToFormula = ("sheet1" + "!$A$1:$L$41");
// Create a new named range for worksheet 'sheet2'
NPOI.SS.UserModel.Name namedRange2 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet2'
namedRange2.NameName = ("RangeTest2");
// Set the reference for the named range for worksheet 'sheet2'
namedRange2.RefersToFormula = ("sheet2" + "!$A$1:$O$21");
// Write the workbook to a file
// Read the Excel file and verify its content
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
NPOI.SS.UserModel.Name nm1 = wb.GetNameAt(wb.GetNameIndex("RangeTest1"));
Assert.IsTrue("RangeTest1".Equals(nm1.NameName), "Name is " + nm1.NameName);
Assert.IsTrue((wb.GetSheetName(0) + "!$A$1:$L$41").Equals(nm1.RefersToFormula), "Reference is " + nm1.RefersToFormula);
NPOI.SS.UserModel.Name nm2 = wb.GetNameAt(wb.GetNameIndex("RangeTest2"));
Assert.IsTrue("RangeTest2".Equals(nm2.NameName), "Name is " + nm2.NameName);
Assert.IsTrue((wb.GetSheetName(1) + "!$A$1:$O$21").Equals(nm2.RefersToFormula), "Reference is " + nm2.RefersToFormula);
}
private NPOI.SS.UserModel.ICell GetCell(string rangeName)
{
var range = Book.GetNameAt(Book.GetNameIndex(ExcelNameEscape(rangeName)));
var areaRef = new AreaReference(range.RefersToFormula);
CellReference[] crefs = areaRef.GetAllReferencedCells();
CellReference cref = crefs[0];
return(Book.GetSheet(cref.SheetName)
.GetRow(cref.Row)
.GetCell(cref.Col));
}
public void TestNamedRead()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("namedinput.xls");
//Get index of the named range with the name = "NamedRangeName" , which was defined in input.xls as A1:D10
int NamedRangeIndex = wb.GetNameIndex("NamedRangeName");
//Getting NAmed Range
IName namedRange1 = wb.GetNameAt(NamedRangeIndex);
String sheetName = wb.GetSheetName(0);
//Getting its reference
String reference = namedRange1.RefersToFormula;
Assert.AreEqual(sheetName + "!$A$1:$D$10", reference);
IName namedRange2 = wb.GetNameAt(1);
Assert.AreEqual(sheetName + "!$D$17:$G$27", namedRange2.RefersToFormula);
Assert.AreEqual("SecondNamedRange", namedRange2.NameName);
}
public void TestNamedCell_1()
{
// setup for this Testcase
String sheetName = "Test Named Cell";
String cellName = "A name for a named cell";
String cellValue = "TEST Value";
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sheetName);
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cellValue));
// Create named range for a single cell using areareference
NPOI.SS.UserModel.Name namedCell = wb.CreateName();
namedCell.NameName = (cellName);
String reference = "'" + sheetName + "'" + "!A1:A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cellName);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
AreaReference aref = new AreaReference(aNamedCell.RefersToFormula);
Assert.IsTrue(aref.IsSingleCell, "Should be exactly 1 cell in the named cell :'" + cellName + "'");
CellReference cref = aref.FirstCell;
Assert.IsNotNull(cref);
NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName);
Assert.IsNotNull(s);
Row r = sheet.GetRow(cref.Row);
Cell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference");
}
