Examples with HSSFRow org.apache.poi.hssf.usermodel.HSSFRow used on opensource projects

Example 76 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class TestIrr method testEvaluateInSheet.

public void testEvaluateInSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue(-4000d);
    row.createCell(1).setCellValue(1200d);
    row.createCell(2).setCellValue(1410d);
    row.createCell(3).setCellValue(1875d);
    row.createCell(4).setCellValue(1050d);
    HSSFCell cell = row.createCell(5);
    cell.setCellFormula("IRR(A1:E1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.clearAllCachedResultValues();
    fe.evaluateFormulaCellEnum(cell);
    double res = cell.getNumericCellValue();
    assertEquals(0.143d, Math.round(res * 1000d) / 1000d);
}

Example 77 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class TestIrr method testIrrFromSpreadsheet.

public void testIrrFromSpreadsheet() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("IrrNpvTestCaseData.xls");
    HSSFSheet sheet = wb.getSheet("IRR-NPV");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    StringBuffer failures = new StringBuffer();
    int failureCount = 0;
    // FormulaEvaluator as of r1041407 throws "Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg)"
    for (int rownum = 9; rownum <= 15; rownum++) {
        HSSFRow row = sheet.getRow(rownum);
        HSSFCell cellA = row.getCell(0);
        try {
            CellValue cv = fe.evaluate(cellA);
            assertFormulaResult(cv, cellA);
        } catch (Throwable e) {
            if (failures.length() > 0)
                failures.append('\n');
            failures.append("Row[" + (cellA.getRowIndex() + 1) + "]: " + cellA.getCellFormula() + " ");
            failures.append(e.getMessage());
            failureCount++;
        }
        //IRR-NPV relationship: NPV(IRR(values), values) = 0
        HSSFCell cellC = row.getCell(2);
        try {
            CellValue cv = fe.evaluate(cellC);
            // should agree within 0.01%
            assertEquals(0, cv.getNumberValue(), 0.0001);
        } catch (Throwable e) {
            if (failures.length() > 0)
                failures.append('\n');
            failures.append("Row[" + (cellC.getRowIndex() + 1) + "]: " + cellC.getCellFormula() + " ");
            failures.append(e.getMessage());
            failureCount++;
        }
    }
    if (failures.length() > 0) {
        throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures);
    }
}

Example 78 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class TestWorkbookEvaluator method testMissingArg.

/**
     * This test makes sure that any {@link MissingArgEval} that propagates to
     * the result of a function gets translated to {@link BlankEval}.
     */
@Test
public void testMissingArg() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("1+IF(1,,)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv = null;
    try {
        cv = fe.evaluate(cell);
    } catch (RuntimeException e) {
        fail("Missing arg result not being handled correctly.");
    }
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    // adding blank to 1.0 gives 1.0
    assertEquals(1.0, cv.getNumberValue(), 0.0);
    // check with string operand
    cell.setCellFormula("\"abc\"&IF(1,,)");
    fe.notifySetFormula(cell);
    cv = fe.evaluate(cell);
    assertEquals(CellType.STRING, cv.getCellTypeEnum());
    // adding blank to "abc" gives "abc"
    assertEquals("abc", cv.getStringValue());
    // check CHOOSE()
    cell.setCellFormula("\"abc\"&CHOOSE(2,5,,9)");
    fe.notifySetFormula(cell);
    cv = fe.evaluate(cell);
    assertEquals(CellType.STRING, cv.getCellTypeEnum());
    // adding blank to "abc" gives "abc"
    assertEquals("abc", cv.getStringValue());
}

Example 79 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class NumberComparingSpreadsheetGenerator method writeHeaderRow.

static void writeHeaderRow(HSSFWorkbook wb, HSSFSheet sheet) {
    sheet.setColumnWidth(0, 6000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 3600);
    sheet.setColumnWidth(3, 3600);
    sheet.setColumnWidth(4, 2400);
    sheet.setColumnWidth(5, 2400);
    sheet.setColumnWidth(6, 2400);
    sheet.setColumnWidth(7, 2400);
    sheet.setColumnWidth(8, 2400);
    HSSFRow row = sheet.createRow(0);
    HSSFCellStyle style = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    writeHeaderCell(row, 0, "Raw Long Bits A", style);
    writeHeaderCell(row, 1, "Raw Long Bits B", style);
    writeHeaderCell(row, 2, "Value A", style);
    writeHeaderCell(row, 3, "Value B", style);
    writeHeaderCell(row, 4, "Exp Cmp", style);
    writeHeaderCell(row, 5, "LT", style);
    writeHeaderCell(row, 6, "EQ", style);
    writeHeaderCell(row, 7, "GT", style);
    writeHeaderCell(row, 8, "Check", style);
}

Example 80 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class NumberRenderingSpreadsheetGenerator method writeHeaderRow.

static void writeHeaderRow(HSSFWorkbook wb, HSSFSheet sheet) {
    sheet.setColumnWidth(0, 3000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 6000);
    sheet.setColumnWidth(3, 6000);
    sheet.setColumnWidth(4, 6000);
    sheet.setColumnWidth(5, 1600);
    sheet.setColumnWidth(6, 20000);
    HSSFRow row = sheet.createRow(0);
    HSSFCellStyle style = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    writeHeaderCell(row, 0, "Value", style);
    writeHeaderCell(row, 1, "Raw Long Bits", style);
    writeHeaderCell(row, 2, "JDK Double Rendering", style);
    writeHeaderCell(row, 3, "Actual Rendering", style);
    writeHeaderCell(row, 4, "Expected Rendering", style);
    writeHeaderCell(row, 5, "Match", style);
    writeHeaderCell(row, 6, "Java Metadata", style);
}