public void TestIndexFormula() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("Sheet1"); short colB = 1; sheet.CreateRow(0).CreateCell(colB).SetCellValue(1); sheet.CreateRow(1).CreateCell(colB).SetCellValue(2); sheet.CreateRow(2).CreateCell(colB).SetCellValue(3); Row row4 = sheet.CreateRow(3); Cell testCell = row4.CreateCell((short)0); // This formula should evaluate to the contents of B2, testCell.CellFormula = ("INDEX(A1:B4,2,2)"); // However the range A1:B4 also includes the current cell A4. If the other parameters // were 4 and 1, this would represent a circular reference. Since POI 'fully' evaluates // arguments before invoking operators, POI must handle such potential cycles gracefully. NPOI.SS.UserModel.CellValue cellValue = EvaluateWithCycles(wb, testCell); Assert.IsTrue(cellValue.CellType == NPOI.SS.UserModel.CellType.NUMERIC); Assert.AreEqual(2, cellValue.NumberValue, 0); }
/** * Make sure that there is no cross-talk between rows especially with getFirstCellNum and getLastCellNum * This Test was Added in response to bug report 44987. */ public void TestBoundsInMultipleRows() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(); Row rowA = sheet.CreateRow(0); rowA.CreateCell(10); rowA.CreateCell(5); Assert.AreEqual(5, rowA.FirstCellNum); Assert.AreEqual(11, rowA.LastCellNum); Row rowB = sheet.CreateRow(1); rowB.CreateCell(15); rowB.CreateCell(30); Assert.AreEqual(15, rowB.FirstCellNum); Assert.AreEqual(31, rowB.LastCellNum); Assert.AreEqual(5, rowA.FirstCellNum); Assert.AreEqual(11, rowA.LastCellNum); rowA.CreateCell(50); Assert.AreEqual(51, rowA.LastCellNum); Assert.AreEqual(31, rowB.LastCellNum); }
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); Row row = sheet.CreateRow(0); Cell 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); NPOI.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.SS.UserModel.CellType.NUMERIC, result.CellType); Assert.AreEqual(42.0, result.NumberValue, 0.0); }
public void TestRowHeight() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(); Row row1 = sheet.CreateRow(0); Assert.AreEqual(0xFF, row1.Height); Assert.AreEqual(sheet.DefaultRowHeight, row1.Height); Row row2 = sheet.CreateRow(1); row2.Height = ((short)400); Assert.AreEqual(400, row2.Height); workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); sheet = workbook.GetSheetAt(0); row1 = sheet.GetRow(0); Assert.AreEqual(0xFF, row1.Height); Assert.AreEqual(sheet.DefaultRowHeight, row1.Height); row2 = sheet.GetRow(1); Assert.AreEqual(400, row2.Height); }
public void TestSheetFunctions() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet("A"); Row r = null; Cell 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 TestDateFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet("TestSheet1"); Row r = null; Cell c = null; r = s.CreateRow(0); c = r.CreateCell(0); NPOI.SS.UserModel.CellStyle 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.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); }
private static void floatTest(String operator1) { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); Row r = null; Cell 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); }
/** * 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.SS.UserModel.Sheet s = wb.CreateSheet(); Row r = null; Cell 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 TestRowBounds() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(); //Test low row bound sheet.CreateRow(0); //Test low row bound exception try { sheet.CreateRow(-1); Assert.Fail("IndexOutOfBoundsException should have been thrown"); } catch (ArgumentException e) { // expected during successful Test Assert.AreEqual("Invalid row number (-1) outside allowable range (0..65535)", e.Message); } //Test high row bound sheet.CreateRow(65535); //Test high row bound exception try { sheet.CreateRow(65536); Assert.Fail("IndexOutOfBoundsException should have been thrown"); } catch (ArgumentException e) { // expected during successful Test Assert.AreEqual("Invalid row number (65536) outside allowable range (0..65535)", e.Message); } }
public void TestDataStyle() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle(); Row 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.SS.UserModel.Sheet s = b.CreateSheet(); s.CreateRow(0).CreateCell(0).SetCellValue("TEST1"); s.CreateRow(3).CreateCell(0).SetCellValue("TEST2"); s.ShiftRows(0, 4, 1); }
/// <summary> /// 把DataTable 转为Excel 内容 /// </summary> /// <param name="dt"></param> /// <param name="startRow"></param> /// <param name="endRow"></param> /// <param name="book"></param> /// <param name="sheetName"></param> private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, HSSFWorkbook book, string sheetName) { //头部样式 CellStyle headstyle = book.CreateCellStyle(); headstyle.Alignment = HorizontalAlignment.CENTER; headstyle.VerticalAlignment = VerticalAlignment.CENTER; Font headfont = book.CreateFont(); headfont.Boldweight = 700; headstyle.SetFont(headfont); headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND; headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; headstyle.BorderBottom = CellBorderType.THIN; headstyle.BorderLeft = CellBorderType.THIN; headstyle.BorderRight = CellBorderType.THIN; headstyle.BorderTop = CellBorderType.THIN; NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName); NPOI.SS.UserModel.Row header = sheet.CreateRow(0); header.Height = 20 * 20; //表格内容样 CellStyle dataStyle = book.CreateCellStyle(); dataStyle.BorderBottom = CellBorderType.THIN; dataStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; dataStyle.BorderLeft = CellBorderType.THIN; dataStyle.BorderRight = CellBorderType.THIN; dataStyle.BorderTop = CellBorderType.THIN; for (int i = 0; i < dt.Columns.Count; i++) { NPOI.SS.UserModel.Cell cell = header.CreateCell(i); cell.CellStyle = headstyle; string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName; cell.SetCellValue(val); } int rowIndex = 1; for (int i = startRow; i <= endRow; i++) { DataRow dtRow = dt.Rows[i]; NPOI.SS.UserModel.Row excelRow = sheet.CreateRow(rowIndex++); for (int j = 0; j < dtRow.ItemArray.Length; j++) { excelRow.CreateCell(j).CellStyle = dataStyle; excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString()); } } }
public void TestMoveCell() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(); Row row = sheet.CreateRow(0); Row rowB = sheet.CreateRow(1); Cell cellA2 = rowB.CreateCell(0); Assert.AreEqual(0, rowB.FirstCellNum); Assert.AreEqual(0, rowB.FirstCellNum); Assert.AreEqual(-1, row.LastCellNum); Assert.AreEqual(-1, row.FirstCellNum); Cell cellB2 = row.CreateCell(1); Cell cellB3 = row.CreateCell(2); Cell 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 e) { // 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 e) { // 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 Test27405() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("input"); // input row 0 Row row = sheet.CreateRow((short)0); Cell cell = row.CreateCell((short)0); cell = row.CreateCell((short)1); cell.SetCellValue(1); // B1 // input row 1 row = sheet.CreateRow((short)1); cell = row.CreateCell((short)1); cell.SetCellValue(999); // B2 int rno = 4; row = sheet.CreateRow(rno); cell = row.CreateCell((short)1); // B5 cell.CellFormula = ("isnumber(b1)"); cell = row.CreateCell((short)3); // D5 cell.CellFormula = ("IF(ISNUMBER(b1),b1,b2)"); //if (false) //{ // Set true to check excel file manually // // bug report mentions 'Editing the formula in excel "fixes" the problem.' // try // { // FileStream fileOut = new FileStream("27405output.xls",FileMode.Open); // wb.Write(fileOut); // fileOut.Close(); // } // catch (IOException) // { // throw; // } //} // use POI's evaluator as an extra sanity check HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); //fe.SetCurrentRow(row); NPOI.SS.UserModel.CellValue cv; cv = fe.Evaluate(cell); Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, cv.CellType); Assert.AreEqual(1.0, cv.NumberValue, 0.0); cv = fe.Evaluate(row.GetCell(1)); Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, cv.CellType); Assert.AreEqual(true, cv.BooleanValue); }
public void CreateDVTypeRow(String strTypeDescription) { NPOI.SS.UserModel.Sheet sheet = _currentSheet; Row row = sheet.CreateRow(sheet.PhysicalNumberOfRows); row = sheet.CreateRow(sheet.PhysicalNumberOfRows); sheet.AddMergedRegion(new CellRangeAddress(sheet.PhysicalNumberOfRows - 1, sheet.PhysicalNumberOfRows - 1, 0, 5)); Cell cell = row.CreateCell(0); SetCellValue(cell, strTypeDescription); cell.CellStyle = (_style_3); row = sheet.CreateRow(sheet.PhysicalNumberOfRows); }
public void Test44636() { // Open the existing file, tweak one value and // re-calculate HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44636.xls"); NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0); Row 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 TestWriteSheetFont() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); Row r = null; Cell c = null; Font fnt = wb.CreateFont(); NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle(); fnt.Color = (NPOI.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"); }
/** * Writes a function then Tests to see if its correct * */ public void RefAreaArrayFunctionTest(String function) { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); Row r = null; Cell 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 TestCloneSheetMultipleTimes() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); cell.SetCellValue(new HSSFRichTextString("Clone_Test")); //Clone the sheet multiple times workbook.CloneSheet(0); workbook.CloneSheet(0); Assert.IsNotNull(workbook.GetSheet("Test Clone")); Assert.IsNotNull(workbook.GetSheet("Test Clone (2)")); Assert.AreEqual("Test Clone (3)", workbook.GetSheetName(2)); Assert.IsNotNull(workbook.GetSheet("Test Clone (3)")); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.CreateSheet("abc ( 123)"); workbook.CloneSheet(0); Assert.AreEqual("abc (124)", workbook.GetSheetName(1)); }
public void TestCloneSheet() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); Cell cell2 = row.CreateCell(1); cell.SetCellValue(new HSSFRichTextString("Clone_Test")); cell2.CellFormula = ("sin(1)"); NPOI.SS.UserModel.Sheet ClonedSheet = workbook.CloneSheet(0); Row ClonedRow = ClonedSheet.GetRow(0); //Check for a good Clone Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test"); //Check that the cells are not somehow linked cell.SetCellValue(new HSSFRichTextString("Difference Check")); cell2.CellFormula = ("cos(2)"); if ("Difference Check".Equals(ClonedRow.GetCell(0).RichStringCellValue.String)) { Assert.Fail("string cell not properly Cloned"); } if ("COS(2)".Equals(ClonedRow.GetCell(1).CellFormula)) { Assert.Fail("formula cell not properly Cloned"); } Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test"); Assert.AreEqual(ClonedRow.GetCell(1).CellFormula, "SIN(1)"); }
public void Test3DArea() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); wb.CreateSheet(); wb.SetSheetName(1, "Sheet2"); Row row = sheet1.CreateRow(0); Cell cell = row.CreateCell((short)0); cell.CellFormula = ("isblank(Sheet2!A1:A1)"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb); //fe.SetCurrentRow(row); NPOI.SS.UserModel.CellValue result = fe.Evaluate(cell); Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType); Assert.AreEqual(true, result.BooleanValue); cell.CellFormula = ("isblank(D7:D7)"); result = fe.Evaluate(cell); Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType); Assert.AreEqual(true, result.BooleanValue); }
public void TestWriteSheetSimple() { string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls"); FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate); HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); Row r = null; Cell 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 TestManyRows() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(); Row row; Cell 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.SS.UserModel.Sheet 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 TestWriteModifySheetMerged() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); for (int rownum = 0; rownum < 100; rownum++) { Row r = s.CreateRow(rownum); for (int cellnum = 0; cellnum < 50; cellnum += 2) { Cell 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 TestAbsRefs() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); Row r; Cell 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 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 TestDataStyle() { string filepath = TempFile.GetTempFilePath("TestWriteSheetStyleDate", ".xls"); FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate); HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle(); Row row = s.CreateRow(0); // with Date: Cell 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 TestHashEquals() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = wb.CreateSheet(); NPOI.SS.UserModel.CellStyle cs1 = wb.CreateCellStyle(); NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle(); Row row = s.CreateRow(0); Cell cell1 = row.CreateCell(1); Cell 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 TestReMoveCell() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(); HSSFRow row = (HSSFRow)sheet.CreateRow(0); Assert.AreEqual(-1, row.LastCellNum); Assert.AreEqual(-1, row.FirstCellNum); row.CreateCell(1); Assert.AreEqual(2, row.LastCellNum); Assert.AreEqual(1, row.FirstCellNum); row.CreateCell(3); Assert.AreEqual(4, row.LastCellNum); Assert.AreEqual(1, row.FirstCellNum); row.RemoveCell(row.GetCell(3)); Assert.AreEqual(2, row.LastCellNum); Assert.AreEqual(1, row.FirstCellNum); row.RemoveCell(row.GetCell(1)); Assert.AreEqual(-1, row.LastCellNum); Assert.AreEqual(-1, row.FirstCellNum); // all cells on this row have been Removed // so Check the row record actually Writes it out as 0's byte[] data = new byte[100]; row.RowRecord.Serialize(0, data); Assert.AreEqual(0, data[6]); Assert.AreEqual(0, data[8]); workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); sheet = workbook.GetSheetAt(0); Assert.AreEqual(-1, sheet.GetRow(0).LastCellNum); Assert.AreEqual(-1, sheet.GetRow(0).FirstCellNum); }
public void TestFullColumnRefs() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("Sheet1"); Row row = sheet.CreateRow(0); Cell cell0 = row.CreateCell(0); cell0.CellFormula = ("sum(D:D)"); Cell cell1 = row.CreateCell(1); cell1.CellFormula = ("sum(D:E)"); // some values in column D setValue(sheet, 1, 3, 5.0); setValue(sheet, 2, 3, 6.0); setValue(sheet, 5, 3, 7.0); setValue(sheet, 50, 3, 8.0); // some values in column E setValue(sheet, 1, 4, 9.0); setValue(sheet, 2, 4, 10.0); setValue(sheet, 30000, 4, 11.0); // some other values setValue(sheet, 1, 2, 100.0); setValue(sheet, 2, 5, 100.0); setValue(sheet, 3, 6, 100.0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); Assert.AreEqual(26.0, fe.Evaluate(cell0).NumberValue, 0.0); Assert.AreEqual(56.0, fe.Evaluate(cell1).NumberValue, 0.0); }