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

Example 41 with HSSFCell

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

the class TestIndirect method testBasic.

@Test
public void testBasic() throws Exception {
    HSSFWorkbook wbA = createWBA();
    HSSFCell c = wbA.getSheetAt(0).createRow(5).createCell(2);
    HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);
    // non-error cases
    confirm(feA, c, "INDIRECT(\"C2\")", 23);
    confirm(feA, c, "INDIRECT(\"C2\", TRUE)", 23);
    confirm(feA, c, "INDIRECT(\"$C2\")", 23);
    confirm(feA, c, "INDIRECT(\"C$2\")", 23);
    // area ref
    confirm(feA, c, "SUM(INDIRECT(\"Sheet2!B1:C3\"))", 351);
    // spaces in area ref
    confirm(feA, c, "SUM(INDIRECT(\"Sheet2! B1 : C3 \"))", 351);
    // special chars in sheet name
    confirm(feA, c, "SUM(INDIRECT(\"'John''s sales'!A1:C1\"))", 93);
    // redundant sheet name quotes
    confirm(feA, c, "INDIRECT(\"'Sheet1'!B3\")", 32);
    // case-insensitive sheet name
    confirm(feA, c, "INDIRECT(\"sHeet1!B3\")", 32);
    // spaces around cell ref
    confirm(feA, c, "INDIRECT(\" D3 \")", 34);
    // spaces around cell ref
    confirm(feA, c, "INDIRECT(\"Sheet1! D3 \")", 34);
    // explicit arg1. only TRUE supported so far
    confirm(feA, c, "INDIRECT(\"A1\", TRUE)", 11);
    // de-reference area ref (note formula is in C4)
    confirm(feA, c, "INDIRECT(\"A1:G1\")", 13);
    // indirect defined name
    confirm(feA, c, "SUM(INDIRECT(A4))", 50);
    // indirect defined name pointinh to other sheet
    confirm(feA, c, "SUM(INDIRECT(B4))", 351);
    // simple error propagation:
    // arg0 is evaluated to text first
    confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
    confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
    confirm(feA, c, "INDIRECT(#NAME?, \"x\")", ErrorEval.NAME_INVALID);
    confirm(feA, c, "INDIRECT(#NUM!, #N/A)", ErrorEval.NUM_ERROR);
    // arg1 is evaluated to boolean before arg0 is decoded
    confirm(feA, c, "INDIRECT(\"garbage\", #N/A)", ErrorEval.NA);
    // empty string is not valid boolean
    confirm(feA, c, "INDIRECT(\"garbage\", \"\")", ErrorEval.VALUE_INVALID);
    // must be "TRUE" or "FALSE"
    confirm(feA, c, "INDIRECT(\"garbage\", \"flase\")", ErrorEval.VALUE_INVALID);
    // spaces around sheet name (with or without quotes makes no difference)
    confirm(feA, c, "INDIRECT(\"'Sheet1 '!D3\")", ErrorEval.REF_INVALID);
    confirm(feA, c, "INDIRECT(\" Sheet1!D3\")", ErrorEval.REF_INVALID);
    confirm(feA, c, "INDIRECT(\"'Sheet1' !D3\")", ErrorEval.REF_INVALID);
    // bad quote escaping
    confirm(feA, c, "SUM(INDIRECT(\"'John's sales'!A1:C1\"))", ErrorEval.REF_INVALID);
    // unknown external workbook
    confirm(feA, c, "INDIRECT(\"[Book1]Sheet1!A1\")", ErrorEval.REF_INVALID);
    // unknown sheet
    confirm(feA, c, "INDIRECT(\"Sheet3!A1\")", ErrorEval.REF_INVALID);
    //        if (false) { // TODO - support evaluation of defined names
    //            confirm(feA, c, "INDIRECT(\"Sheet1!IW1\")", ErrorEval.REF_INVALID); // bad column
    //            confirm(feA, c, "INDIRECT(\"Sheet1!A65537\")", ErrorEval.REF_INVALID); // bad row
    //        }
    // space in cell ref
    confirm(feA, c, "INDIRECT(\"Sheet1!A 1\")", ErrorEval.REF_INVALID);
    wbA.close();
}

Example 42 with HSSFCell

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

the class TestFind method testFind.

@Test
public void testFind() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    confirmResult(fe, cell, "find(\"h\", \"haystack\")", 1);
    confirmResult(fe, cell, "find(\"a\", \"haystack\",2)", 2);
    confirmResult(fe, cell, "find(\"a\", \"haystack\",3)", 6);
    // number args converted to text
    confirmResult(fe, cell, "find(7, 32768)", 3);
    confirmResult(fe, cell, "find(\"34\", 1341235233412, 3)", 10);
    confirmResult(fe, cell, "find(5, 87654)", 4);
    // Errors
    confirmError(fe, cell, "find(\"n\", \"haystack\")", FormulaError.VALUE);
    confirmError(fe, cell, "find(\"k\", \"haystack\",9)", FormulaError.VALUE);
    confirmError(fe, cell, "find(\"k\", \"haystack\",#REF!)", FormulaError.REF);
    confirmError(fe, cell, "find(\"k\", \"haystack\",0)", FormulaError.VALUE);
    confirmError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", FormulaError.DIV0);
    confirmError(fe, cell, "find(2, #N/A, #REF!)", FormulaError.NA);
    wb.close();
}

Example 43 with HSSFCell

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

the class TestIPMT method testFromFile.

/**
     *  from http://office.microsoft.com/en-001/excel-help/ipmt-HP005209145.aspx
     */
public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("finance.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("IPMT");
    HSSFCell ex1cell1 = example1.getRow(6).getCell(0);
    fe.evaluate(ex1cell1);
    assertEquals(-22.41, ex1cell1.getNumericCellValue(), 0.1);
    HSSFCell ex1cell2 = example1.getRow(7).getCell(0);
    fe.evaluate(ex1cell2);
    assertEquals(-292.45, ex1cell2.getNumericCellValue(), 0.1);
}

Example 44 with HSSFCell

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

the class TestIsBlank method test3DArea.

public void test3DArea() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    wb.createSheet();
    wb.setSheetName(1, "Sheet2");
    HSSFRow row = sheet1.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("isblank(Sheet2!A1:A1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result = fe.evaluate(cell);
    assertEquals(CellType.BOOLEAN, result.getCellTypeEnum());
    assertEquals(true, result.getBooleanValue());
    cell.setCellFormula("isblank(D7:D7)");
    result = fe.evaluate(cell);
    assertEquals(CellType.BOOLEAN, result.getCellTypeEnum());
    assertEquals(true, result.getBooleanValue());
}

Example 45 with HSSFCell

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

the class NumberComparingSpreadsheetGenerator 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);
}