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

Example 91 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell 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 92 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell 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 93 with HSSFCell

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

the class TestIntercept method testFromFile.

/**
     *  Example from
     *  http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524
     */
public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("intercept.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("Example 1");
    HSSFCell a8 = example1.getRow(7).getCell(0);
    assertEquals("INTERCEPT(A2:A6,B2:B6)", a8.getCellFormula());
    fe.evaluate(a8);
    assertEquals(0.048387097, a8.getNumericCellValue(), 0.000000001);
}

Example 94 with HSSFCell

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

the class ExcelToHtmlConverter method processRow.

/**
     * @return maximum 1-base index of column that were rendered, zero if none
     */
protected int processRow(CellRangeAddress[][] mergedRanges, HSSFRow row, Element tableRowElement) {
    final HSSFSheet sheet = row.getSheet();
    final short maxColIx = row.getLastCellNum();
    if (maxColIx <= 0)
        return 0;
    final List<Element> emptyCells = new ArrayList<Element>(maxColIx);
    if (isOutputRowNumbers()) {
        Element tableRowNumberCellElement = htmlDocumentFacade.createTableHeaderCell();
        processRowNumber(row, tableRowNumberCellElement);
        emptyCells.add(tableRowNumberCellElement);
    }
    int maxRenderedColumn = 0;
    for (int colIx = 0; colIx < maxColIx; colIx++) {
        if (!isOutputHiddenColumns() && sheet.isColumnHidden(colIx))
            continue;
        CellRangeAddress range = ExcelToHtmlUtils.getMergedRange(mergedRanges, row.getRowNum(), colIx);
        if (range != null && (range.getFirstColumn() != colIx || range.getFirstRow() != row.getRowNum()))
            continue;
        HSSFCell cell = row.getCell(colIx);
        int divWidthPx = 0;
        if (isUseDivsToSpan()) {
            divWidthPx = getColumnWidth(sheet, colIx);
            boolean hasBreaks = false;
            for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++) {
                if (!isOutputHiddenColumns() && sheet.isColumnHidden(nextColumnIndex))
                    continue;
                if (row.getCell(nextColumnIndex) != null && !isTextEmpty(row.getCell(nextColumnIndex))) {
                    hasBreaks = true;
                    break;
                }
                divWidthPx += getColumnWidth(sheet, nextColumnIndex);
            }
            if (!hasBreaks)
                divWidthPx = Integer.MAX_VALUE;
        }
        Element tableCellElement = htmlDocumentFacade.createTableCell();
        if (range != null) {
            if (range.getFirstColumn() != range.getLastColumn())
                tableCellElement.setAttribute("colspan", String.valueOf(range.getLastColumn() - range.getFirstColumn() + 1));
            if (range.getFirstRow() != range.getLastRow())
                tableCellElement.setAttribute("rowspan", String.valueOf(range.getLastRow() - range.getFirstRow() + 1));
        }
        boolean emptyCell;
        if (cell != null) {
            emptyCell = processCell(cell, tableCellElement, getColumnWidth(sheet, colIx), divWidthPx, row.getHeight() / 20f);
        } else {
            emptyCell = true;
        }
        if (emptyCell) {
            emptyCells.add(tableCellElement);
        } else {
            for (Element emptyCellElement : emptyCells) {
                tableRowElement.appendChild(emptyCellElement);
            }
            emptyCells.clear();
            tableRowElement.appendChild(tableCellElement);
            maxRenderedColumn = colIx;
        }
    }
    return maxRenderedColumn + 1;
}

Example 95 with HSSFCell

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

the class TestSharedValueManager method testRecalculateFormulas47747.

/**
	 * Tests fix for a bug in the way shared formula cells are associated with shared formula
	 * records.  Prior to this fix, POI would attempt to use the upper left corner of the
	 * shared formula range as the locator cell.  The correct cell to use is the 'first cell'
	 * in the shared formula group which is not always the top left cell.  This is possible
	 * because shared formula groups may be sparse and may overlap.<br/>
	 *
	 * Two existing sample files (15228.xls and ex45046-21984.xls) had similar issues.
	 * These were not explored fully, but seem to be fixed now.
	 */
public void testRecalculateFormulas47747() {
    /*
		 * ex47747-sharedFormula.xls is a heavily cut-down version of the spreadsheet from
		 * the attachment (id=24176) in Bugzilla 47747.  This was done to make the sample
		 * file smaller, which hopefully allows the special data encoding condition to be
		 * seen more easily.  Care must be taken when modifying this file since the
		 * special conditions are easily destroyed (which would make this test useless).
		 * It seems that removing the worksheet protection has made this more so - if the
		 * current file is re-saved in Excel(2007) the bug condition disappears.
		 *
		 *
		 * Using BiffViewer, one can see that there are two shared formula groups representing
		 * the essentially same formula over ~20 cells.  The shared group ranges overlap and
		 * are A12:Q20 and A20:Q27.  The locator cell ('first cell') for the second group is
		 * Q20 which is not the top left cell of the enclosing range.  It is this specific
		 * condition which caused the bug to occur
		 */
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("ex47747-sharedFormula.xls");
    // pick out a cell from within the second shared formula group
    HSSFCell cell = wb.getSheetAt(0).getRow(23).getCell(0);
    String formulaText;
    try {
        formulaText = cell.getCellFormula();
    // succeeds if the formula record has been associated
    // with the second shared formula group
    } catch (RuntimeException e) {
        // with the first shared formula group
        if ("Shared Formula Conversion: Coding Error".equals(e.getMessage())) {
            throw new AssertionFailedError("Identified bug 47747");
        }
        throw e;
    }
    assertEquals("$AF24*A$7", formulaText);
}