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

Example 96 with HSSFCell

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

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

the class TestWorkbookEvaluator method testEvaluateMultipleWorkbooks.

@Test
public void testEvaluateMultipleWorkbooks() {
    HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
    HSSFWorkbook wbB = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaB.xls");
    HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
    HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);
    // Hook up the workbook evaluators to enable evaluation of formulas across books
    String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls" };
    HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
    HSSFCell cell;
    HSSFSheet aSheet1 = wbA.getSheetAt(0);
    HSSFSheet bSheet1 = wbB.getSheetAt(0);
    // Simple case - single link from wbA to wbB
    confirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
    cell = aSheet1.getRow(0).getCell(0);
    confirmEvaluation(35, evaluatorA, cell);
    // more complex case - back link into wbA
    // [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
    confirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
    // [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
    confirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");
    cell = aSheet1.getRow(1).getCell(0);
    confirmEvaluation(264, evaluatorA, cell);
    // change [wbB]BSheet1!B3 (from 50 to 60)
    HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
    cellB3.setCellValue(60);
    evaluatorB.notifyUpdateCell(cellB3);
    confirmEvaluation(274, evaluatorA, cell);
    // change [wbA]ASheet1!A3 (from 100 to 80)
    HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
    cellA3.setCellValue(80);
    evaluatorA.notifyUpdateCell(cellA3);
    confirmEvaluation(234, evaluatorA, cell);
    // change [wbA]AnotherSheet!A1 (from 2 to 3)
    HSSFCell cellA1 = wbA.getSheetAt(1).getRow(0).getCell(0);
    cellA1.setCellValue(3);
    evaluatorA.notifyUpdateCell(cellA1);
    confirmEvaluation(235, evaluatorA, cell);
}

Example 98 with HSSFCell

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

the class NumberRenderingSpreadsheetGenerator method writeHeaderCell.

private static void writeHeaderCell(HSSFRow row, int i, String text, HSSFCellStyle style) {
    HSSFCell cell = row.createCell(i);
    cell.setCellValue(new HSSFRichTextString(text));
    cell.setCellStyle(style);
}

Example 99 with HSSFCell

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

the class Alignment method createCell.

/**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb        the workbook
     * @param row       the row to create the cell in
     * @param column    the column number to create the cell in
     * @param align     the alignment for the cell.
     */
private static void createCell(HSSFWorkbook wb, HSSFRow row, int column, HorizontalAlignment align) {
    HSSFCell cell = row.createCell(column);
    cell.setCellValue("Align It");
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(align);
    cell.setCellStyle(cellStyle);
}

Example 100 with HSSFCell

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

the class Borders method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet("new sheet");
        // Create a row and put some cells in it. Rows are 0 based.
        HSSFRow row = sheet.createRow(1);
        // Create a cell and put a value in it.
        HSSFCell cell = row.createCell(1);
        cell.setCellValue(4);
        // Style the cell with borders all around.
        HSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColorPredefined.GREEN.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColorPredefined.BLUE.getIndex());
        style.setBorderTop(BorderStyle.MEDIUM_DASHED);
        style.setTopBorderColor(HSSFColorPredefined.ORANGE.getIndex());
        cell.setCellStyle(style);
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    } finally {
        wb.close();
    }
}