public void TestRepeatingRowsAndColumsNames()
{
// First Test that Setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("FirstSheet");
// set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1);
sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
IName nr1 = wb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
if (false)
{
#if !HIDE_UNREACHABLE_CODE
// TODO - full column references not rendering properly, absolute markers not present either
Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula);
#endif
}
else
{
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
}
// Save and re-open
HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
// check that Setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
sheet = (HSSFSheet)nwb.CreateSheet("SecondSheet");
nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);
Assert.AreEqual(2, nwb.NumberOfNames);
IName nr2 = nwb.GetNameAt(1);
Assert.AreEqual("Print_Titles", nr2.NameName);
Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula);
//if (false) {
// // In case you fancy Checking in excel, to ensure it
// // won't complain about the file now
// File tempFile = File.CreateTempFile("POI-45126-", ".xls");
// FileOutputStream fout = new FileOutputStream(tempFile);
// nwb.Write(fout);
// fout.close();
// Console.WriteLine("check out " + tempFile.GetAbsolutePath());
//}
}
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 TestSheetLevelFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
IRow row;
ISheet sh1 = wb.CreateSheet("Sheet1");
IName nm1 = wb.CreateName();
nm1.NameName = ("sales_1");
nm1.SheetIndex = (0);
nm1.RefersToFormula = ("Sheet1!$A$1");
row = sh1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*2");
ISheet sh2 = wb.CreateSheet("Sheet2");
IName nm2 = wb.CreateName();
nm2.NameName = ("sales_1");
nm2.SheetIndex = (1);
nm2.RefersToFormula = ("Sheet2!$A$1");
row = sh2.CreateRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*3");
//check that NamePtg refers to the correct NameRecord
Ptg[] ptgs1 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 0);
NamePtg nPtg1 = (NamePtg)ptgs1[0];
Assert.AreSame(nm1, wb.GetNameAt(nPtg1.Index));
Ptg[] ptgs2 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 1);
NamePtg nPtg2 = (NamePtg)ptgs2[0];
Assert.AreSame(nm2, wb.GetNameAt(nPtg2.Index));
//check that the formula evaluator returns the correct result
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(3.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(6.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0);
Assert.AreEqual(5.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(15.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.0);
}
public void TestDiscontinousReference()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls");
HSSFWorkbook wb = new HSSFWorkbook(is1);
InternalWorkbook 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.IName 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);
}
}