/** * 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 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 TestStripFields() { String simple = "I am a Test header"; String withPage = "I am a&P Test header"; String withLots = "I&A am&N a&P Test&T header&U"; String withFont = "I&22 am a&\"Arial,bold\" Test header"; String withOtherAnds = "I am a&P Test header&&"; String withOtherAnds2 = "I am a&P Test header&a&b"; Assert.AreEqual(simple, HSSFHeader.StripFields(simple)); Assert.AreEqual(simple, HSSFHeader.StripFields(withPage)); Assert.AreEqual(simple, HSSFHeader.StripFields(withLots)); Assert.AreEqual(simple, HSSFHeader.StripFields(withFont)); Assert.AreEqual(simple + "&&", HSSFHeader.StripFields(withOtherAnds)); Assert.AreEqual(simple + "&a&b", HSSFHeader.StripFields(withOtherAnds2)); // Now Test the default Strip flag HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("EmbeddedChartHeaderTest.xls"); NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0); Header head = s.Header; Assert.AreEqual("Top Left", head.Left); Assert.AreEqual("Top Center", head.Center); Assert.AreEqual("Top Right", head.Right); head.Left = ("Top &P&F&D Left"); Assert.AreEqual("Top &P&F&D Left", head.Left); Assert.AreEqual("Top Left", NPOI.HSSF.UserModel.HeaderFooter.StripFields(head.Left)); // Now even more complex head.Center = ("HEADER TEXT &P&N&D&T&Z&F&F&A&G&X END"); Assert.AreEqual("HEADER TEXT END", NPOI.HSSF.UserModel.HeaderFooter.StripFields(head.Center)); }
public void Test27349() { // 27349-vlookupAcrossSheets.xls is1 bugzilla/attachment.cgi?id=10622 Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vlookupAcrossSheets.xls"); HSSFWorkbook wb; try { // original bug may have thrown exception here, or output warning to // stderr wb = new HSSFWorkbook(is1); } catch (IOException) { throw; } NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0); Row row = sheet.GetRow(1); Cell cell = row.GetCell(0); // this definitely would have failed due to 27349 Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell.CellFormula); // We might as well evaluate the formula HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); //fe.SetCurrentRow(row); NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell); Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, cv.CellType); Assert.AreEqual(3.0, cv.NumberValue, 0.0); }
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 TestPrintSetupLandscapeNew() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheetL = workbook.CreateSheet("LandscapeS"); NPOI.SS.UserModel.Sheet sheetP = workbook.CreateSheet("LandscapeP"); // Check two aspects of the print setup Assert.IsFalse(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(0, sheetL.PrintSetup.Copies); Assert.AreEqual(0, sheetP.PrintSetup.Copies); // Change one on each sheetL.PrintSetup.Landscape = (true); sheetP.PrintSetup.Copies = ((short)3); // Check taken Assert.IsTrue(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(0, sheetL.PrintSetup.Copies); Assert.AreEqual(3, sheetP.PrintSetup.Copies); // Save and re-load, and Check still there MemoryStream baos = new MemoryStream(); workbook.Write(baos); workbook = new HSSFWorkbook(new MemoryStream(baos.ToArray())); Assert.IsTrue(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(0, sheetL.PrintSetup.Copies); Assert.AreEqual(3, sheetP.PrintSetup.Copies); }
private static void ConfirmRow(NPOI.SS.UserModel.Sheet sheet, int rowIx, double valA, double valB, double valC, String formulaA, String formulaB, String formulaC) { ConfirmCell(sheet, rowIx, 4, valA, formulaA); ConfirmCell(sheet, rowIx, 5, valB, formulaB); ConfirmCell(sheet, rowIx, 6, valC, formulaC); }
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 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 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 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); }
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 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 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)"); }
/** * 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 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 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 TestPageBreakFiles() { HSSFWorkbook wb = OpenSample("SimpleWithPageBreaks.xls"); NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0); Assert.IsNotNull(sheet); Assert.AreEqual(1, sheet.RowBreaks.Length, "1 row page break"); Assert.AreEqual(1, sheet.ColumnBreaks.Length, "1 column page break"); Assert.IsTrue(sheet.IsRowBroken(22), "No row page break"); Assert.IsTrue(sheet.IsColumnBroken((short)4), "No column page break"); sheet.SetRowBreak(10); sheet.SetColumnBreak((short)13); Assert.AreEqual(2, sheet.RowBreaks.Length, "row breaks number"); Assert.AreEqual(2, sheet.ColumnBreaks.Length, "column breaks number"); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0); Assert.IsTrue(sheet.IsRowBroken(22), "No row page break"); Assert.IsTrue(sheet.IsColumnBroken((short)4), "No column page break"); Assert.AreEqual(2, sheet.RowBreaks.Length, "row breaks number"); Assert.AreEqual(2, sheet.ColumnBreaks.Length, "column breaks number"); }
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 TestClassCast_bug44861() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls"); // Check direct HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); // And via calls int numSheets = wb.NumberOfSheets; for (int i = 0; i < numSheets; i++) { NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(i); HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext();) { Row r = (Row)rows.Current; for (IEnumerator cells = r.GetCellEnumerator(); cells.MoveNext();) { Cell c = (Cell)cells.Current; eval.EvaluateFormulaCell(c); } } } }
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 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"); }
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 Test44916() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(); // 1. Create drawing patriarch Drawing patr = sheet.CreateDrawingPatriarch(); // 2. Try to re-get the patriarch Drawing existingPatr; try { existingPatr = sheet.DrawingPatriarch; } catch (NullReferenceException e) { throw new AssertFailedException("Identified bug 44916"); } // 3. Use patriarch HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2); anchor.AnchorType = (3); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); patr.CreatePicture(anchor, idx1); // 4. Try to re-use patriarch later existingPatr = sheet.DrawingPatriarch; Assert.IsNotNull(existingPatr); }
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 TestReadComments() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithComments.xls"); NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0); Cell cell; Row row; Comment comment; for (int rownum = 0; rownum < 3; rownum++) { row = sheet.GetRow(rownum); cell = row.GetCell(0); comment = cell.CellComment; Assert.IsNull(comment, "Cells in the first column are not commented"); Assert.IsNull(sheet.GetCellComment(rownum, 0)); } for (int rownum = 0; rownum < 3; rownum++) { row = sheet.GetRow(rownum); cell = row.GetCell(1); comment = cell.CellComment; Assert.IsNotNull(comment, "Cells in the second column have comments"); Assert.IsNotNull(sheet.GetCellComment(rownum, 1), "Cells in the second column have comments"); Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_COMMENT, ((HSSFSimpleShape)comment).ShapeType); Assert.AreEqual("Yegor Kozlov", comment.Author); Assert.IsFalse(comment.String.String == string.Empty, "cells in the second column have not empyy notes"); Assert.AreEqual(rownum, comment.Row); Assert.AreEqual(cell.ColumnIndex, comment.Column); } }
/** * Opens the sheet we wrote out by BinomialOperator and makes sure the formulas * all Match what we expect (x operator y) */ private static void BinomialVerify(String operator1, HSSFWorkbook wb) { NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0); Row r = null; Cell c = null; //get our minimum values r = s.GetRow(0); c = r.GetCell(1); Assert.IsTrue(("1" + operator1 + "1").Equals(c.CellFormula), "minval Formula is as expected 1" + operator1 + "1 != " + c.CellFormula); for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2)) { r = s.GetRow(x); for (int y = 1; y < 256 && y > 0; y++) { c = r.GetCell(y); Assert.IsTrue(("" + x + operator1 + y).Equals(c.CellFormula), "loop Formula is as expected " + x + operator1 + y + "!=" + c.CellFormula ); } } //Test our maximum values r = s.GetRow(0); c = r.GetCell(0); Assert.IsTrue( ("" + short.MaxValue + operator1 + short.MaxValue).Equals(c.CellFormula), "maxval Formula is as expected" ); }
public MySheet() { _wb = new HSSFWorkbook(); _evalListener = new EvalListener(_wb); _evaluator = WorkbookEvaluatorTestHelper.CreateEvaluator(_wb, _evalListener); _sheet = _wb.CreateSheet("Sheet1"); }
public void TestDeleteComments() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithComments.xls"); NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0); // Zap from rows 1 and 3 Assert.IsNotNull(sheet.GetRow(0).GetCell(1).CellComment); Assert.IsNotNull(sheet.GetRow(1).GetCell(1).CellComment); Assert.IsNotNull(sheet.GetRow(2).GetCell(1).CellComment); sheet.GetRow(0).GetCell(1).RemoveCellComment(); sheet.GetRow(2).GetCell(1).CellComment = (null); // Check gone so far Assert.IsNull(sheet.GetRow(0).GetCell(1).CellComment); Assert.IsNotNull(sheet.GetRow(1).GetCell(1).CellComment); Assert.IsNull(sheet.GetRow(2).GetCell(1).CellComment); // Save and re-load MemoryStream out1 = new MemoryStream(); wb.Write(out1); out1.Close(); wb = new HSSFWorkbook(new MemoryStream(out1.ToArray())); // Check Assert.IsNull(sheet.GetRow(0).GetCell(1).CellComment); Assert.IsNotNull(sheet.GetRow(1).GetCell(1).CellComment); Assert.IsNull(sheet.GetRow(2).GetCell(1).CellComment); // FileOutputStream fout = new FileOutputStream("/tmp/c.xls"); // wb.Write(fout); // fout.Close(); }
public HSSFFormulaEvaluator(NPOI.SS.UserModel.Sheet sheet, NPOI.SS.UserModel.Workbook workbook) : this(workbook) { this.sheet = sheet; this.workbook = workbook; }