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(); }