/** * 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.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0); IRow r = null; ICell 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 void TestOnARealFile() { HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("DateFormats.xls"); Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); InternalWorkbook wb = workbook.Workbook; IRow row; ICell cell; Npoi.Core.SS.UserModel.ICellStyle style; double aug_10_2007 = 39304.0; // Should have dates in 2nd column // All of them are the 10th of August // 2 US dates, 3 UK dates row = sheet.GetRow(0); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.AreEqual("d-mmm-yy", style.GetDataFormatString()); Assert.IsTrue(DateUtil.IsInternalDateFormat(style.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(1); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(2); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsTrue(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(3); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); row = sheet.GetRow(4); cell = row.GetCell(1); style = cell.CellStyle; Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001); Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat)); Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString())); Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); }
public void Test44410() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls"); Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb); // =index(C:C,2,1) -> 2 IRow rowIDX = sheet.GetRow(3); // =sum(C:C) -> 6 IRow rowSUM = sheet.GetRow(4); // =sum(C:D) -> 66 IRow rowSUM2D = sheet.GetRow(5); // Test the sum ICell cellSUM = rowSUM.GetCell(0); FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord; Ptg[] ops = frec.FormulaRecord.ParsedExpression; Assert.AreEqual(2, ops.Length); Assert.AreEqual(typeof(AreaPtg), ops[0].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType()); // Actually stored as C1 to C65536 // (last row is -1 === 65535) AreaPtg ptg = (AreaPtg)ops[0]; Assert.AreEqual(2, ptg.FirstColumn); Assert.AreEqual(2, ptg.LastColumn); Assert.AreEqual(0, ptg.FirstRow); Assert.AreEqual(65535, ptg.LastRow); Assert.AreEqual("C:C", ptg.ToFormulaString()); // Will show as C:C, but won't know how many // rows it covers as we don't have the sheet // to hand when turning the Ptgs into a string Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula); // But the evaluator knows the sheet, so it // can do it properly Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0); // Test the index // Again, the formula string will be right but // lacking row count, Evaluated will be right ICell cellIDX = rowIDX.GetCell(0); Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula); Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0); // Across two colums ICell cellSUM2D = rowSUM2D.GetCell(0); Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula); Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0); }
public void TestStyleNames() { HSSFWorkbook wb = OpenSample("WithExtendedStyles.xls"); Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0); ICell c1 = s.GetRow(0).GetCell(0); ICell c2 = s.GetRow(1).GetCell(0); ICell c3 = s.GetRow(2).GetCell(0); HSSFCellStyle cs1 = (HSSFCellStyle)c1.CellStyle; HSSFCellStyle cs2 = (HSSFCellStyle)c2.CellStyle; HSSFCellStyle cs3 = (HSSFCellStyle)c3.CellStyle; Assert.IsNotNull(cs1); Assert.IsNotNull(cs2); Assert.IsNotNull(cs3); // Check we got the styles we'd expect Assert.AreEqual(10, cs1.GetFont(wb).FontHeightInPoints); Assert.AreEqual(9, cs2.GetFont(wb).FontHeightInPoints); Assert.AreEqual(12, cs3.GetFont(wb).FontHeightInPoints); Assert.AreEqual(15, cs1.Index); Assert.AreEqual(23, cs2.Index); Assert.AreEqual(24, cs3.Index); Assert.IsNull(cs1.ParentStyle); Assert.IsNotNull(cs2.ParentStyle); Assert.IsNotNull(cs3.ParentStyle); Assert.AreEqual(21, cs2.ParentStyle.Index); Assert.AreEqual(22, cs3.ParentStyle.Index); // Now Check we can get style records for // the parent ones Assert.IsNull(wb.Workbook.GetStyleRecord(15)); Assert.IsNull(wb.Workbook.GetStyleRecord(23)); Assert.IsNull(wb.Workbook.GetStyleRecord(24)); Assert.IsNotNull(wb.Workbook.GetStyleRecord(21)); Assert.IsNotNull(wb.Workbook.GetStyleRecord(22)); // Now Check the style names Assert.AreEqual(null, cs1.UserStyleName); Assert.AreEqual(null, cs2.UserStyleName); Assert.AreEqual(null, cs3.UserStyleName); Assert.AreEqual("style1", cs2.ParentStyle.UserStyleName); Assert.AreEqual("style2", cs3.ParentStyle.UserStyleName); // now apply a named style to a new cell ICell c4 = s.GetRow(0).CreateCell(1); c4.CellStyle = (cs2); Assert.AreEqual("style1", ((HSSFCellStyle)c4.CellStyle).ParentStyle.UserStyleName); }
public void TestReadEmployeeSimple() { HSSFWorkbook workbook = OpenSample("Employee.xls"); Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); Assert.AreEqual(EMPLOYEE_INFORMATION, sheet.GetRow(1).GetCell(1).RichStringCellValue.String); Assert.AreEqual(LAST_NAME_KEY, sheet.GetRow(3).GetCell(2).RichStringCellValue.String); Assert.AreEqual(FIRST_NAME_KEY, sheet.GetRow(4).GetCell(2).RichStringCellValue.String); Assert.AreEqual(SSN_KEY, sheet.GetRow(5).GetCell(2).RichStringCellValue.String); }
public void Test44297() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44297.xls"); IRow row; ICell cell; Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb); row = sheet.GetRow(0); cell = row.GetCell(0); Assert.AreEqual("31+46", cell.CellFormula); Assert.AreEqual(77, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(1); cell = row.GetCell(0); Assert.AreEqual("30+53", cell.CellFormula); Assert.AreEqual(83, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(2); cell = row.GetCell(0); Assert.AreEqual("SUM(A1:A2)", cell.CellFormula); Assert.AreEqual(160, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(4); cell = row.GetCell(0); Assert.AreEqual("32767+32768", cell.CellFormula); Assert.AreEqual(65535, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(7); cell = row.GetCell(0); Assert.AreEqual("32744+42333", cell.CellFormula); Assert.AreEqual(75077, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(8); cell = row.GetCell(0); Assert.AreEqual("327680/32768", cell.CellFormula); Assert.AreEqual(10, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(9); cell = row.GetCell(0); Assert.AreEqual("32767+32769", cell.CellFormula); Assert.AreEqual(65536, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(10); cell = row.GetCell(0); Assert.AreEqual("35000+36000", cell.CellFormula); Assert.AreEqual(71000, eva.Evaluate(cell).NumberValue, 0); row = sheet.GetRow(11); cell = row.GetCell(0); Assert.AreEqual("-1000000-3000000", cell.CellFormula); Assert.AreEqual(-4000000, eva.Evaluate(cell).NumberValue, 0); }
public void TestModifySimple() { HSSFWorkbook workbook = OpenSample("Simple.xls"); Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); ICell cell = sheet.GetRow(0).GetCell(0); cell.SetCellValue(new HSSFRichTextString(REPLACED)); workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); sheet = workbook.GetSheetAt(0); cell = sheet.GetRow(0).GetCell(0); Assert.AreEqual(REPLACED, cell.RichStringCellValue.String); }
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); }
/** * 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); }
/** * 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 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 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 TestSquareMacro() { HSSFWorkbook w = OpenSample("SquareMacro.xls"); Npoi.Core.SS.UserModel.ISheet s0 = w.GetSheetAt(0); IRow[] r = { s0.GetRow(0), s0.GetRow(1) }; ICell a1 = r[0].GetCell(0); Assert.AreEqual("square(1)", a1.CellFormula); Assert.AreEqual(1d, a1.NumericCellValue, 1e-9); ICell a2 = r[1].GetCell(0); Assert.AreEqual("square(2)", a2.CellFormula); Assert.AreEqual(4d, a2.NumericCellValue, 1e-9); ICell b1 = r[0].GetCell(1); Assert.AreEqual("IF(TRUE,square(1))", b1.CellFormula); Assert.AreEqual(1d, b1.NumericCellValue, 1e-9); ICell b2 = r[1].GetCell(1); Assert.AreEqual("IF(TRUE,square(2))", b2.CellFormula); Assert.AreEqual(4d, b2.NumericCellValue, 1e-9); ICell c1 = r[0].GetCell(2); Assert.AreEqual("square(square(1))", c1.CellFormula); Assert.AreEqual(1d, c1.NumericCellValue, 1e-9); ICell c2 = r[1].GetCell(2); Assert.AreEqual("square(square(2))", c2.CellFormula); Assert.AreEqual(16d, c2.NumericCellValue, 1e-9); ICell d1 = r[0].GetCell(3); Assert.AreEqual("square(one())", d1.CellFormula); Assert.AreEqual(1d, d1.NumericCellValue, 1e-9); ICell d2 = r[1].GetCell(3); Assert.AreEqual("square(two())", d2.CellFormula); Assert.AreEqual(4d, d2.NumericCellValue, 1e-9); }
private static void ConfirmCell(Npoi.Core.SS.UserModel.ISheet sheet, int rowIx, int colIx, double expectedValue, String expectedFormula) { ICell cell = sheet.GetRow(rowIx).GetCell(colIx); Assert.AreEqual(expectedValue, cell.NumericCellValue, 0.0); Assert.AreEqual(expectedFormula, cell.CellFormula); }
private static void ConfirmResolveCellRef(HSSFWorkbook wb, CellReference cref) { Npoi.Core.SS.UserModel.ISheet s = wb.GetSheet(cref.SheetName); IRow r = s.GetRow(cref.Row); ICell c = r.GetCell((int)cref.Col); Assert.IsNotNull(c); }
private static DateTime ReadCell(HSSFWorkbook workbook, int rowIdx, int colIdx) { Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); IRow row = sheet.GetRow(rowIdx); ICell cell = row.GetCell(colIdx); return(cell.DateCellValue); }
public void TestShiftRows() { // Read initial file in HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls"); Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0); // Shift the second row down 1 and Write to temp file s.ShiftRows(1, 1, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); // Read from temp file and Check the number of cells in each // row (in original file each row was unique) s = wb.GetSheetAt(0); Assert.AreEqual(1, s.GetRow(0).PhysicalNumberOfCells); ConfirmEmptyRow(s, 1); Assert.AreEqual(2, s.GetRow(2).PhysicalNumberOfCells); Assert.AreEqual(4, s.GetRow(3).PhysicalNumberOfCells); Assert.AreEqual(5, s.GetRow(4).PhysicalNumberOfCells); // Shift rows 1-3 down 3 in the current one. This Tests when // 1 row is blank. Write to a another temp file s.ShiftRows(0, 2, 3); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); // Read and ensure things are where they should be s = wb.GetSheetAt(0); ConfirmEmptyRow(s, 0); ConfirmEmptyRow(s, 1); ConfirmEmptyRow(s, 2); Assert.AreEqual(1, s.GetRow(3).PhysicalNumberOfCells); ConfirmEmptyRow(s, 4); Assert.AreEqual(2, s.GetRow(5).PhysicalNumberOfCells); // Read the first file again wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls"); s = wb.GetSheetAt(0); // Shift rows 3 and 4 up and Write to temp file s.ShiftRows(2, 3, -2); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); s = wb.GetSheetAt(0); Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 3); Assert.AreEqual(s.GetRow(1).PhysicalNumberOfCells, 4); ConfirmEmptyRow(s, 2); ConfirmEmptyRow(s, 3); Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5); }
public void TestShiftWithFormulas() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ForShifting.xls"); Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheet("Sheet1"); Assert.AreEqual(20, sheet.LastRowNum); ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)"); ConfirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)"); ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)"); ConfirmCell(sheet, 6, 1, 271, "200+B1"); ConfirmCell(sheet, 7, 1, 272, "200+B2"); ConfirmCell(sheet, 8, 1, 273, "200+B3"); ConfirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced // ----------- // Row index 1 -> 11 (row "2" -> row "12") sheet.ShiftRows(1, 1, 10); // Now Check what sheet looks like after move // no changes on row "1" ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)"); // row "2" is now empty Assert.AreEqual(0, sheet.GetRow(1).PhysicalNumberOfCells); // Row "2" moved to row "12", and the formula has been updated. // note however that the cached formula result (2) has not been updated. (POI differs from Excel here) ConfirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)"); // no changes on row "3" ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)"); ConfirmCell(sheet, 14, 0, 0.0, "#REF!"); // Formulas on rows that weren't shifted: ConfirmCell(sheet, 6, 1, 271, "200+B1"); ConfirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved ConfirmCell(sheet, 8, 1, 273, "200+B3"); // Check formulas on other sheets Npoi.Core.SS.UserModel.ISheet sheet2 = wb.GetSheet("Sheet2"); ConfirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1"); ConfirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12"); ConfirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3"); ConfirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!"); // Note - named ranges formulas have not been updated }
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); }
public void TestReadSimple() { HSSFWorkbook workbook = OpenSample("Simple.xls"); Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); ICell cell = sheet.GetRow(0).GetCell(0); Assert.AreEqual(REPLACE_ME, cell.RichStringCellValue.String); }
public void TestDateWindowingRead() { DateTime date = new DateTime(2000, 1, 1); // first Check a file with 1900 Date Windowing HSSFWorkbook workbook = OpenSample("1900DateWindowing.xls"); Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); Assert.AreEqual(date, sheet.GetRow(0).GetCell(0).DateCellValue, "Date from file using 1900 Date Windowing"); // now Check a file with 1904 Date Windowing workbook = OpenSample("1904DateWindowing.xls"); sheet = workbook.GetSheetAt(0); Assert.AreEqual(date, sheet.GetRow(0).GetCell(0).DateCellValue, "Date from file using 1904 Date Windowing"); }
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 TestCloneSheet() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("HyperlinksOnManySheets.xls"); ICell cell; IHyperlink link; Npoi.Core.SS.UserModel.ISheet sheet = wb.CloneSheet(0); cell = sheet.GetRow(4).GetCell(0); link = cell.Hyperlink; Assert.IsNotNull(link); Assert.AreEqual("http://poi.apache.org/", link.Address); cell = sheet.GetRow(8).GetCell(0); link = cell.Hyperlink; Assert.IsNotNull(link); Assert.AreEqual("http://poi.apache.org/hssf/", link.Address); }
public void TestWriteModifySheetSimple() { 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")); } } for (int rownum = 0; rownum < 25; rownum++) { r = s.GetRow(rownum); s.RemoveRow(r); } for (int rownum = 75; rownum < 100; rownum++) { r = s.GetRow(rownum); s.RemoveRow(r); } wb.Write(out1); out1.Close(); sanityChecker.CheckHSSFWorkbook(wb); Assert.AreEqual(74, s.LastRowNum, "LAST ROW == 74"); Assert.AreEqual(25, s.FirstRowNum, "FIRST ROW == 25"); }
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 TestReadSheetWithRK() { HSSFWorkbook h = OpenSample("rk.xls"); Npoi.Core.SS.UserModel.ISheet s = h.GetSheetAt(0); ICell c = s.GetRow(0).GetCell(0); Npoi.Core.SS.UserModel.CellType a = c.CellType; Assert.AreEqual(a, Npoi.Core.SS.UserModel.CellType.Numeric); }
/// <summary> /// Gets the row height in points. /// </summary> /// <param name="sheet">The sheet.</param> /// <param name="rowNum">The row num.</param> /// <returns></returns> private float GetRowHeightInPoints(Npoi.Core.SS.UserModel.ISheet sheet, int rowNum) { Npoi.Core.SS.UserModel.IRow row = sheet.GetRow(rowNum); if (row == null) { return(sheet.DefaultRowHeightInPoints); } else { return(row.HeightInPoints); } }
public void TestReadSimpleWithDataFormat() { HSSFWorkbook workbook = OpenSample("SimpleWithDataFormat.xls"); Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); IDataFormat format = workbook.CreateDataFormat(); ICell cell = sheet.GetRow(0).GetCell(0); Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10); Assert.AreEqual(format.GetFormat(cell.CellStyle.DataFormat), "0.0"); }
public void TestEvaluateSimple() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestNames.xls"); Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); ICell cell = sheet.GetRow(8).GetCell(0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); Npoi.Core.SS.UserModel.CellValue cv = fe.Evaluate(cell); Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, cv.CellType); Assert.AreEqual(3.72, cv.NumberValue, 0.0); }
private static void SetCell(HSSFWorkbook workbook, int rowIdx, int colIdx, DateTime date) { Npoi.Core.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); IRow row = sheet.GetRow(rowIdx); ICell cell = row.GetCell(colIdx); if (cell == null) { cell = row.CreateCell(colIdx); } cell.SetCellValue(date); }