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

Example 86 with HSSFRow

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

the class ExcelExtractor method getText.

@Override
public String getText() {
    StringBuffer text = new StringBuffer();
    // We don't care about the difference between
    //  null (missing) and blank cells
    _wb.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
    // Process each sheet in turn
    for (int i = 0; i < _wb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = _wb.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        if (_includeSheetNames) {
            String name = _wb.getSheetName(i);
            if (name != null) {
                text.append(name);
                text.append("\n");
            }
        }
        // Header text, if there is any
        if (_includeHeadersFooters) {
            text.append(_extractHeaderFooter(sheet.getHeader()));
        }
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();
        for (int j = firstRow; j <= lastRow; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            // Check each cell in turn
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();
            if (_includeBlankCells) {
                firstCell = 0;
            }
            for (int k = firstCell; k < lastCell; k++) {
                HSSFCell cell = row.getCell(k);
                boolean outputContents = true;
                if (cell == null) {
                    // Only output if requested
                    outputContents = _includeBlankCells;
                } else {
                    switch(cell.getCellTypeEnum()) {
                        case STRING:
                            text.append(cell.getRichStringCellValue().getString());
                            break;
                        case NUMERIC:
                            text.append(_formatter.formatCellValue(cell));
                            break;
                        case BOOLEAN:
                            text.append(cell.getBooleanCellValue());
                            break;
                        case ERROR:
                            text.append(ErrorEval.getText(cell.getErrorCellValue()));
                            break;
                        case FORMULA:
                            if (!_shouldEvaluateFormulas) {
                                text.append(cell.getCellFormula());
                            } else {
                                switch(cell.getCachedFormulaResultTypeEnum()) {
                                    case STRING:
                                        HSSFRichTextString str = cell.getRichStringCellValue();
                                        if (str != null && str.length() > 0) {
                                            text.append(str);
                                        }
                                        break;
                                    case NUMERIC:
                                        HSSFCellStyle style = cell.getCellStyle();
                                        double nVal = cell.getNumericCellValue();
                                        short df = style.getDataFormat();
                                        String dfs = style.getDataFormatString();
                                        text.append(_formatter.formatRawCellContents(nVal, df, dfs));
                                        break;
                                    case BOOLEAN:
                                        text.append(cell.getBooleanCellValue());
                                        break;
                                    case ERROR:
                                        text.append(ErrorEval.getText(cell.getErrorCellValue()));
                                        break;
                                    default:
                                        throw new IllegalStateException("Unexpected cell cached formula result type: " + cell.getCachedFormulaResultTypeEnum());
                                }
                            }
                            break;
                        default:
                            throw new RuntimeException("Unexpected cell type (" + cell.getCellTypeEnum() + ")");
                    }
                    // Output the comment, if requested and exists
                    HSSFComment comment = cell.getCellComment();
                    if (_includeCellComments && comment != null) {
                        // Replace any newlines with spaces, otherwise it
                        //  breaks the output
                        String commentText = comment.getString().getString().replace('\n', ' ');
                        text.append(" Comment by " + comment.getAuthor() + ": " + commentText);
                    }
                }
                // Output a tab if we're not on the last cell
                if (outputContents && k < (lastCell - 1)) {
                    text.append("\t");
                }
            }
            // Finish off the row
            text.append("\n");
        }
        // Finally Footer text, if there is any
        if (_includeHeadersFooters) {
            text.append(_extractHeaderFooter(sheet.getFooter()));
        }
    }
    return text.toString();
}

Example 87 with HSSFRow

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

the class TestMirr method testMirrFromSpreadsheet.

public void testMirrFromSpreadsheet() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("mirrTest.xls");
    HSSFSheet sheet = wb.getSheet("Mirr");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    StringBuilder failures = new StringBuilder();
    int failureCount = 0;
    int[] resultRows = { 9, 19, 29, 45 };
    for (int rowNum : resultRows) {
        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[").append(cellA.getRowIndex() + 1).append("]: ").append(cellA.getCellFormula()).append(" ");
            failures.append(e.getMessage());
            failureCount++;
        }
    }
    HSSFRow row = sheet.getRow(37);
    HSSFCell cellA = row.getCell(0);
    CellValue cv = fe.evaluate(cellA);
    assertEquals(ErrorEval.DIV_ZERO.getErrorCode(), cv.getErrorValue());
    if (failures.length() > 0) {
        throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures);
    }
}

Example 88 with HSSFRow

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

the class TestAreaReference method confirmResolveCellRef.

private static void confirmResolveCellRef(HSSFWorkbook wb, CellReference cref) {
    HSSFSheet s = wb.getSheet(cref.getSheetName());
    HSSFRow r = s.getRow(cref.getRow());
    HSSFCell c = r.getCell((int) cref.getCol());
    assertNotNull(c);
}

Example 89 with HSSFRow

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

the class TestFormulaParser method testNumbers.

@Test
public void testNumbers() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Cash_Flow");
    HSSFSheet sheet = wb.createSheet("Test");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    String formula;
    // starts from decimal point
    cell.setCellFormula(".1");
    formula = cell.getCellFormula();
    assertEquals("0.1", formula);
    cell.setCellFormula("+.1");
    formula = cell.getCellFormula();
    assertEquals("0.1", formula);
    cell.setCellFormula("-.1");
    formula = cell.getCellFormula();
    assertEquals("-0.1", formula);
    // has exponent
    cell.setCellFormula("10E1");
    formula = cell.getCellFormula();
    assertEquals("100", formula);
    cell.setCellFormula("10E+1");
    formula = cell.getCellFormula();
    assertEquals("100", formula);
    cell.setCellFormula("10E-1");
    formula = cell.getCellFormula();
    assertEquals("1", formula);
    wb.close();
}

Example 90 with HSSFRow

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

the class TestFormulaParser method testParseStringLiterals_bug28754.

@Test
public void testParseStringLiterals_bug28754() throws IOException {
    StringPtg sp;
    try {
        sp = (StringPtg) parseSingleToken("\"test\"\"ing\"", StringPtg.class);
    } catch (RuntimeException e) {
        if (e.getMessage().startsWith("Cannot Parse")) {
            fail("Identified bug 28754a");
        }
        throw e;
    }
    assertEquals("test\"ing", sp.getValue());
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet();
        wb.setSheetName(0, "Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellFormula("right(\"test\"\"ing\", 3)");
        String actualCellFormula = cell.getCellFormula();
        if ("RIGHT(\"test\"ing\",3)".equals(actualCellFormula)) {
            fail("Identified bug 28754b");
        }
        assertEquals("RIGHT(\"test\"\"ing\",3)", actualCellFormula);
    } finally {
        wb.close();
    }
}