private static void floatTest(string operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = ("" + float.MinValue + operator1 + float.MinValue);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y = (short)(y + 2))
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + "." + y + operator1 + y + "." + x);
}
}
if (s.LastRowNum < short.MaxValue)
{
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + float.MaxValue + operator1 + float.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
floatVerify(operator1, wb);
}
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
// put some values in the cells to make the evaluation more interesting
sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);
//HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
Npoi.Core.SS.UserModel.CellValue result;
try
{
result = fe.Evaluate(cell);
}
catch (Exception e)
{
if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
{
Assert.Fail("Identifed bug 44539");
}
throw;
}
Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, result.CellType);
Assert.AreEqual(42.0, result.NumberValue, 0.0);
}
/**
* All multi-binomial operator Tests use this to Create a worksheet with a
* huge set of x operator y formulas. Next we call BinomialVerify and verify
* that they are all how we expect.
*/
private static void BinomialOperator(string operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = (1 + operator1 + 1);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + operator1 + y);
}
}
//make sure we do the maximum value of the Int operator
if (s.LastRowNum < short.MaxValue)
{
r = s.GetRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + short.MaxValue + operator1 + short.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
BinomialVerify(operator1, wb);
}
public void TestDateFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet("TestSheet1");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
Npoi.Core.SS.UserModel.ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"));
c.SetCellValue(new DateTime());
c.CellStyle = (cellStyle);
// Assert.AreEqual("Checking hour = " + hour, date.GetTime().GetTime(),
// Npoi.Core.SS.UserModel.DateUtil.GetJavaDate(excelDate).GetTime());
for (int k = 1; k < 100; k++)
{
r = s.CreateRow(k);
c = r.CreateCell(0);
c.CellFormula = ("A" + (k) + "+1");
c.CellStyle = cellStyle;
}
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
public void TestSheetFunctions()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet("A");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1);
c = r.CreateCell(1); c.SetCellValue(2);
s = wb.CreateSheet("B");
r = s.CreateRow(0);
c = r.CreateCell(0); c.CellFormula = ("AVERAGE(A!A1:B1)");
c = r.CreateCell(1); c.CellFormula = ("A!A1+A!B1");
c = r.CreateCell(2); c.CellFormula = ("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheet("B");
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("AVERAGE(A!A1:B1)").Equals(c.CellFormula), "expected: AVERAGE(A!A1:B1) got: " + c.CellFormula);
c = r.GetCell(1);
Assert.IsTrue(("A!A1+A!B1").Equals(c.CellFormula), "expected: A!A1+A!B1 got: " + c.CellFormula);
}
public void TestDataStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
Npoi.Core.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
IRow row = s.CreateRow((short)0);
// with Date:
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
// with Calendar:
row = s.CreateRow((short)1);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(1, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
public void TestShiftRow0()
{
HSSFWorkbook b = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = b.CreateSheet();
s.CreateRow(0).CreateCell(0).SetCellValue("TEST1");
s.CreateRow(3).CreateCell(0).SetCellValue("TEST2");
s.ShiftRows(0, 4, 1);
}
public void TestMoveCell()
{
HSSFWorkbook workbook = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet = workbook.CreateSheet();
IRow row = sheet.CreateRow(0);
IRow rowB = sheet.CreateRow(1);
ICell cellA2 = rowB.CreateCell(0);
Assert.AreEqual(0, rowB.FirstCellNum);
Assert.AreEqual(0, rowB.FirstCellNum);
Assert.AreEqual(-1, row.LastCellNum);
Assert.AreEqual(-1, row.FirstCellNum);
ICell cellB2 = row.CreateCell(1);
ICell cellB3 = row.CreateCell(2);
ICell cellB4 = row.CreateCell(3);
Assert.AreEqual(1, row.FirstCellNum);
Assert.AreEqual(4, row.LastCellNum);
// Try to move to somewhere else that's used
try
{
row.MoveCell(cellB2, (short)3);
Assert.Fail("ArgumentException should have been thrown");
}
catch (ArgumentException)
{
// expected during successful Test
}
// Try to move one off a different row
try
{
row.MoveCell(cellA2, (short)3);
Assert.Fail("ArgumentException should have been thrown");
}
catch (ArgumentException)
{
// expected during successful Test
}
// Move somewhere spare
Assert.IsNotNull(row.GetCell(1));
row.MoveCell(cellB2, (short)5);
Assert.IsNull(row.GetCell(1));
Assert.IsNotNull(row.GetCell(5));
Assert.AreEqual(5, cellB2.ColumnIndex);
Assert.AreEqual(2, row.FirstCellNum);
Assert.AreEqual(6, row.LastCellNum);
}
public void Test44636()
{
// Open the existing file, tweak one value and
// re-calculate
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44636.xls");
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(0);
row.GetCell(0).SetCellValue(4.2);
row.GetCell(2).SetCellValue(25);
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual(4.2 * 25, row.GetCell(3).NumericCellValue, 0.0001);
if (OUTPUT_TEST_FILES)
{
// Save
FileStream existing = File.Open(tmpDirName + "44636-existing.xls", FileMode.Open);
existing.Seek(0, SeekOrigin.End);
wb.Write(existing);
existing.Close();
Console.Error.WriteLine("Existing file for bug #44636 written to " + existing.ToString());
}
// Now, do a new file from scratch
wb = new HSSFWorkbook();
sheet = wb.CreateSheet();
row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(1.2);
row.CreateCell(1).SetCellValue(4.2);
row = sheet.CreateRow(1);
row.CreateCell(0).CellFormula = ("SUM(A1:B1)");
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual(5.4, row.GetCell(0).NumericCellValue, 0.0001);
if (OUTPUT_TEST_FILES)
{
// Save
FileStream scratch = File.Open(tmpDirName + "44636-scratch.xls", FileMode.Open);
scratch.Seek(0, SeekOrigin.End);
wb.Write(scratch);
scratch.Close();
Console.Error.WriteLine("New file for bug #44636 written to " + scratch.ToString());
}
}
public void TestWriteModifySheetMerged()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
for (int rownum = 0; rownum < 100; rownum++)
{
IRow r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
ICell c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
s.AddMergedRegion(new CellRangeAddress(0, 10, 0, 10));
s.AddMergedRegion(new CellRangeAddress(30, 40, 5, 15));
sanityChecker.CheckHSSFWorkbook(wb);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
CellRangeAddress r1 = s.GetMergedRegion(0);
CellRangeAddress r2 = s.GetMergedRegion(1);
ConfirmRegion(new CellRangeAddress(0, 10, 0, 10), r1);
ConfirmRegion(new CellRangeAddress(30, 40, 5, 15), r2);
}
public void TestUmlatReadWrite()
{
HSSFWorkbook wb = new HSSFWorkbook();
//Create a unicode sheet name (euro symbol)
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet("Test");
IRow r = s.CreateRow(0);
ICell c = r.CreateCell(1);
c.SetCellValue(new HSSFRichTextString("\u00e4"));
//Confirm that the sring will be compressed
Assert.AreEqual(((HSSFRichTextString)c.RichStringCellValue).UnicodeString.OptionFlags, 0);
string path = Npoi.Core.Util.TempFile.GetTempFilePath("umlat", "Test.xls");
FileStream tempFile = File.Create(path);
wb.Write(tempFile);
tempFile.Close();
wb = null;
FileStream in1 = File.Open(path, FileMode.Open);
wb = new HSSFWorkbook(in1);
in1.Close();
//Test the sheetname
s = wb.GetSheet("Test");
Assert.IsNotNull(s);
c = r.GetCell(1);
Assert.AreEqual(c.RichStringCellValue.String, "\u00e4");
}
public void TestHashEquals()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
Npoi.Core.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
Npoi.Core.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
IRow row = s.CreateRow(0);
ICell cell1 = row.CreateCell(1);
ICell cell2 = row.CreateCell(2);
cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cs2.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));
cell1.CellStyle = (cs1);
cell1.SetCellValue(DateTime.Now);
cell2.CellStyle = (cs2);
cell2.SetCellValue(DateTime.Now);
Assert.AreEqual(cs1.GetHashCode(), cs1.GetHashCode());
Assert.AreEqual(cs2.GetHashCode(), cs2.GetHashCode());
Assert.IsTrue(cs1.Equals(cs1));
Assert.IsTrue(cs2.Equals(cs2));
// Change cs1, hash will alter
int hash1 = cs1.GetHashCode();
cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));
Assert.IsFalse(hash1 == cs1.GetHashCode());
}
public void TestDataStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyleDate",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
Npoi.Core.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
IRow row = s.CreateRow(0);
// with Date:
ICell cell = row.CreateCell(1);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
// with Calendar:
cell = row.CreateCell(2);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(0, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
public void TestManyRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet = workbook.CreateSheet();
IRow row;
ICell cell;
int i, j;
for (i = 0, j = 32771; j > 0; i++, j--)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(i);
}
sanityChecker.CheckHSSFWorkbook(workbook);
Assert.AreEqual(32770, sheet.LastRowNum, "LAST ROW == 32770");
cell = sheet.GetRow(32770).GetCell(0);
double lastVal = cell.NumericCellValue;
HSSFWorkbook wb = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
row = s.GetRow(32770);
cell = row.GetCell(0);
Assert.AreEqual(lastVal, cell.NumericCellValue, 0, "Value from last row == 32770");
Assert.AreEqual(32770, s.LastRowNum, "LAST ROW == 32770");
}
public void TestWriteSheetSimple()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
for (int rownum = 0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
wb.Write(out1);
out1.Close();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
//ICell c = null;
IFont fnt = wb.CreateFont();
Npoi.Core.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color = (Npoi.Core.HSSF.Util.HSSFColor.Red.Index);
fnt.Boldweight = (short)FontBoldWeight.Bold;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle = (cs);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
public void TestAbsRefs()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r;
ICell c;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("A3+A2");
c = r.CreateCell(1);
c.CellFormula = ("$A3+$A2");
c = r.CreateCell(2);
c.CellFormula = ("A$3+A$2");
c = r.CreateCell(3);
c.CellFormula = ("$A$3+$A$2");
c = r.CreateCell(4);
c.CellFormula = ("SUM($A$3,$A$2)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("A3+A2").Equals(c.CellFormula), "A3+A2");
c = r.GetCell(1);
Assert.IsTrue(("$A3+$A2").Equals(c.CellFormula), "$A3+$A2");
c = r.GetCell(2);
Assert.IsTrue(("A$3+A$2").Equals(c.CellFormula), "A$3+A$2");
c = r.GetCell(3);
Assert.IsTrue(("$A$3+$A$2").Equals(c.CellFormula), "$A$3+$A$2");
c = r.GetCell(4);
Assert.IsTrue(("SUM($A$3,$A$2)").Equals(c.CellFormula), "SUM($A$3,$A$2)");
}
public void TestComplexSheetRefs()
{
HSSFWorkbook sb = new HSSFWorkbook();
try
{
Npoi.Core.SS.UserModel.ISheet s1 = sb.CreateSheet("Sheet a.1");
Npoi.Core.SS.UserModel.ISheet s2 = sb.CreateSheet("Sheet.A");
s2.CreateRow(1).CreateCell(2).CellFormula = ("'Sheet a.1'!A1");
s1.CreateRow(1).CreateCell(2).CellFormula = ("'Sheet.A'!A1");
string tmpfile = TempFile.GetTempFilePath("TestComplexSheetRefs", ".xls");
FileStream fs = new FileStream(tmpfile, FileMode.Create);
try
{
sb.Write(fs);
}
finally
{
fs.Close();
}
}
finally
{
sb.Close();
}
}
/**
* Writes a function then Tests to see if its correct
*
*/
public void RefAreaArrayFunctionTest(string function)
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = (function + "(A2:A4,B2:B4)");
c = r.CreateCell(1);
c.CellFormula = (function + "($A$2:$A4,B$2:B4)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(
(function + "(A2:A4,B2:B4)").Equals(c.CellFormula), "function =" + function + "(A2:A4,B2:B4)"
);
c = r.GetCell(1);
Assert.IsTrue((function + "($A$2:$A4,B$2:B4)").Equals(c.CellFormula),
"function =" + function + "($A$2:$A4,B$2:B4)"
);
}
public void TestCreateDocumentLink()
{
HSSFWorkbook wb = new HSSFWorkbook();
//link to a place in this workbook
IHyperlink link;
ICell cell;
Npoi.Core.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//create a target sheet and cell
Npoi.Core.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
//cell A1 has a link to 'Target Sheet-1'!A1
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = (link);
//cell B1 has a link to cell A1 on the same sheet
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Hyperlinks'!A1");
cell.Hyperlink = (link);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
Assert.AreEqual("'Target Sheet'!A1", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Hyperlinks'!A1", link.TextMark);
Assert.AreEqual("'Hyperlinks'!A1", link.Address);
}
private static void setValue(Npoi.Core.SS.UserModel.ISheet sheet, int rowIndex, int colIndex, double value)
{
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
row.CreateCell(colIndex).SetCellValue(value);
}
public void TestSlowEvaluate45376()
{
/*
* Note - to observe behaviour without caching, disable the call to
* updateValue() from FormulaCellCacheEntry.updateFormulaResult().
*/
// Firstly set up a sequence of formula cells where each depends on the previous multiple
// times. Without caching, each subsequent cell take about 4 times longer to Evaluate.
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
for (int i = 1; i < 10; i++)
{
ICell cell = row.CreateCell(i);
char prevCol = (char)('A' + i - 1);
string prevCell = prevCol + "1";
// this formula is inspired by the offending formula of the attachment for bug 45376
string formula = "IF(DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1)<=$D$3," +
"DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1),NA())";
cell.CellFormula = (formula);
}
row.CreateCell(0).SetCellValue(new DateTime(2000, 1, 1, 0, 0, 0));
// Choose cell A9, so that the Assert.Failing Test case doesn't take too long to execute.
ICell cell1 = row.GetCell(8);
EvalListener evalListener = new EvalListener();
WorkbookEvaluator evaluator = WorkbookEvaluatorTestHelper.CreateEvaluator(wb, evalListener);
ValueEval ve = evaluator.Evaluate(HSSFEvaluationTestHelper.WrapCell(cell1));
int evalCount = evalListener.GetCountCacheMisses();
if (evalCount > 10)
{
// Without caching, evaluating cell 'A9' takes 21845 evaluations which consumes
// much time (~3 sec on Core 2 Duo 2.2GHz)
Console.Error.WriteLine("Cell A9 took " + evalCount + " intermediate evaluations");
throw new AssertionException("Identifed bug 45376 - Formula evaluator should cache values");
}
// With caching, the evaluationCount is 8 which is a big improvement
// Note - these expected values may change if the WorkbookEvaluator is
// ever optimised to short circuit 'if' functions.
Assert.AreEqual(8, evalCount);
// The cache hits would be 24 if fully evaluating all arguments of the
// "IF()" functions (Each of the 8 formulas has 4 refs to formula cells
// which result in 1 cache miss and 3 cache hits). However with the
// short-circuit-if optimisation, 2 of the cell refs get skipped
// reducing this metric 8.
Assert.AreEqual(8, evalListener.GetCountCacheHits());
// confirm the evaluation result too
Assert.AreEqual(ErrorEval.NA, ve);
}
public void TestSumIf()
{
string function = "SUMIF(A1:A5,\">4000\",B1:B5)";
HSSFWorkbook wb = OpenSample("sumifformula.xls");
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
IRow r = s.GetRow(0);
ICell c = r.GetCell(2);
Assert.AreEqual(function, c.CellFormula);
wb = new HSSFWorkbook();
s = wb.CreateSheet();
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1000);
c = r.CreateCell(1); c.SetCellValue(1);
r = s.CreateRow(1);
c = r.CreateCell(0); c.SetCellValue(2000);
c = r.CreateCell(1); c.SetCellValue(2);
r = s.CreateRow(2);
c = r.CreateCell(0); c.SetCellValue(3000);
c = r.CreateCell(1); c.SetCellValue(3);
r = s.CreateRow(3);
c = r.CreateCell(0); c.SetCellValue(4000);
c = r.CreateCell(1); c.SetCellValue(4);
r = s.CreateRow(4);
c = r.CreateCell(0); c.SetCellValue(5000);
c = r.CreateCell(1); c.SetCellValue(5);
r = s.GetRow(0);
c = r.CreateCell(2); c.CellFormula = (function);
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
public void TestWriteSheetStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyle",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
Npoi.Core.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
Npoi.Core.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (BorderStyle.Thin);
cs.BorderLeft = (BorderStyle.Thin);
cs.BorderRight = (BorderStyle.Thin);
cs.BorderTop = (BorderStyle.Thin);
cs.FillForegroundColor = ( short )0xA;
cs.FillPattern = FillPattern.SolidForeground;
fnt.Color = ( short )0xf;
fnt.IsItalic = (true);
cs2.FillForegroundColor = ( short )0x0;
cs2.FillPattern = FillPattern.SolidForeground;
cs2.SetFont(fnt);
for (short rownum = ( short )0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double )rownum / 1000)
+ (( double )cellnum / 10000)));
c.CellStyle = (cs);
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs2);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public void TestRVAoperands()
{
string tmpfile = TempFile.GetTempFilePath("TestFormulaRVA", ".xls");
FileStream out1 = new FileStream(tmpfile, FileMode.Create);
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("A3+A2");
c = r.CreateCell(1);
c.CellFormula = ("AVERAGE(A3,A2)");
c = r.CreateCell(2);
c.CellFormula = ("ROW(A3)");
c = r.CreateCell(3);
c.CellFormula = ("AVERAGE(A2:A3)");
c = r.CreateCell(4);
c.CellFormula = ("POWER(A2,A3)");
c = r.CreateCell(5);
c.CellFormula = ("SIN(A2)");
c = r.CreateCell(6);
c.CellFormula = ("SUM(A2:A3)");
c = r.CreateCell(7);
c.CellFormula = ("SUM(A2,A3)");
r = s.CreateRow(1); c = r.CreateCell(0); c.SetCellValue(2.0);
r = s.CreateRow(2); c = r.CreateCell(0); c.SetCellValue(3.0);
wb.Write(out1);
out1.Close();
Assert.IsTrue(File.Exists(tmpfile), "file exists");
}
private static void AddNewSheetWithCellsA1toD4(HSSFWorkbook book, int sheet)
{
Npoi.Core.SS.UserModel.ISheet sht = book.CreateSheet("s" + sheet);
for (int r = 0; r < 4; r++)
{
IRow row = sht.CreateRow(r);
for (int c = 0; c < 4; c++)
{
ICell cel = row.CreateCell(c);
cel.SetCellValue(sheet * 100 + r * 10 + c);
}
}
}
public void TestShiftRowBreaks()
{
HSSFWorkbook b = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = b.CreateSheet();
IRow row = s.CreateRow(4);
row.CreateCell(0).SetCellValue("Test");
s.SetRowBreak(4);
s.ShiftRows(4, 4, 2);
Assert.IsTrue(s.IsRowBroken(6), "Row number 6 should have a pagebreak");
}
public void TestLogicalFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet("A");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(1); c.CellFormula = ("IF(A1<A2,B1,B2)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(1);
Assert.AreEqual("IF(A1<A2,B1,B2)", c.CellFormula, "Formula in cell 1 ");
}
public void TestWriteSheetFont()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
Npoi.Core.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color = (Npoi.Core.HSSF.Util.HSSFColor.Red.Index);
fnt.Boldweight = (short)FontBoldWeight.Bold;
cs.SetFont(fnt);
for (short rownum = ( short )0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double )rownum / 1000)
+ (( double )cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public void TestWriteDataFormat()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IDataFormat format = wb.CreateDataFormat();
Npoi.Core.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
short df = format.GetFormat("0.0");
cs.DataFormat = (df);
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellStyle = (cs);
c.SetCellValue(1.25);
wb.Write(out1);
out1.Close();
FileStream stream = new FileStream(filepath, FileMode.OpenOrCreate);
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell =
sheet.GetRow(0).GetCell(0);
format = workbook.CreateDataFormat();
Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10);
Assert.AreEqual(format.GetFormat(df), "0.0");
Assert.AreEqual(format, workbook.CreateDataFormat());
stream.Close();
}
