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

Example 21 with HSSFSheet

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

the class TestFormulaParser method testRangeOperator.

@Test
public void testRangeOperator() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFCell cell = sheet.createRow(0).createCell(0);
    wb.setSheetName(0, "Sheet1");
    // explicit range ':' operator
    cell.setCellFormula("Sheet1!B$4:Sheet1!$C1");
    assertEquals("Sheet1!B$4:Sheet1!$C1", cell.getCellFormula());
    // plain area ref
    cell.setCellFormula("Sheet1!B$4:$C1");
    // note - area ref is normalised
    assertEquals("Sheet1!B1:$C$4", cell.getCellFormula());
    // different syntax for plain area ref
    cell.setCellFormula("Sheet1!$C1...B$4");
    assertEquals("Sheet1!B1:$C$4", cell.getCellFormula());
    // with funny sheet name
    wb.setSheetName(0, "A1...A2");
    cell.setCellFormula("A1...A2!B1");
    assertEquals("A1...A2!B1", cell.getCellFormula());
    wb.close();
}

Example 22 with HSSFSheet

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

the class TestFormulaParser method testNamesWithUnderscore.

/** bug 49725, defined names with underscore */
@Test
public void testNamesWithUnderscore() throws IOException {
    //or new XSSFWorkbook();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("NamesWithUnderscore");
    HSSFName nm;
    nm = wb.createName();
    nm.setNameName("DA6_LEO_WBS_Number");
    nm.setRefersToFormula("33");
    nm = wb.createName();
    nm.setNameName("DA6_LEO_WBS_Name");
    nm.setRefersToFormula("33");
    nm = wb.createName();
    nm.setNameName("A1_");
    nm.setRefersToFormula("22");
    nm = wb.createName();
    nm.setNameName("_A1");
    nm.setRefersToFormula("11");
    nm = wb.createName();
    nm.setNameName("A_1");
    nm.setRefersToFormula("44");
    nm = wb.createName();
    nm.setNameName("A_1_");
    nm.setRefersToFormula("44");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("DA6_LEO_WBS_Number*2");
    assertEquals("DA6_LEO_WBS_Number*2", cell.getCellFormula());
    cell.setCellFormula("(A1_*_A1+A_1)/A_1_");
    assertEquals("(A1_*_A1+A_1)/A_1_", cell.getCellFormula());
    cell.setCellFormula("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))");
    assertEquals("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))", cell.getCellFormula());
    wb.close();
}

Example 23 with HSSFSheet

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

the class TestFormulaParser method testSetFormulaWithRowBeyond32768_Bug44539.

@Test
public void testSetFormulaWithRowBeyond32768_Bug44539() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("SUM(A32769:A32770)");
    if ("SUM(A-32767:A-32766)".equals(cell.getCellFormula())) {
        fail("Identified bug 44539");
    }
    assertEquals("SUM(A32769:A32770)", cell.getCellFormula());
    wb.close();
}

Example 24 with HSSFSheet

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

the class TestFormulaParserEval method testEvaluateFormulaWithRowBeyond32768_Bug44539.

public void testEvaluateFormulaWithRowBeyond32768_Bug44539() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("SUM(A32769:A32770)");
    // put some values in the cells to make the evaluation more interesting
    sheet.createRow(32768).createCell(0).setCellValue(31);
    sheet.createRow(32769).createCell(0).setCellValue(11);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result;
    try {
        result = fe.evaluate(cell);
    } catch (FormulaParseException e) {
        if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) {
            throw new AssertionFailedError("Identifed bug 44539");
        }
        throw e;
    }
    assertEquals(CellType.NUMERIC, result.getCellTypeEnum());
    assertEquals(42.0, result.getNumberValue(), 0.0);
}

Example 25 with HSSFSheet

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

the class TestFormulaParser method testExponentialInSheet.

@Test
public void testExponentialInSheet() 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;
    cell.setCellFormula("1.3E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1.3E+21/3", formula);
    cell.setCellFormula("-1.3E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1.3E+21/3", formula);
    cell.setCellFormula("1322E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1.322E+24/3", formula);
    cell.setCellFormula("-1322E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1.322E+24/3", formula);
    cell.setCellFormula("1.3E1/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "13/3", formula);
    cell.setCellFormula("-1.3E1/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-13/3", formula);
    cell.setCellFormula("1.3E-4/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "0.00013/3", formula);
    cell.setCellFormula("-1.3E-4/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-0.00013/3", formula);
    cell.setCellFormula("13E-15/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "0.000000000000013/3", formula);
    cell.setCellFormula("-13E-15/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-0.000000000000013/3", formula);
    cell.setCellFormula("1.3E3/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1300/3", formula);
    cell.setCellFormula("-1.3E3/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1300/3", formula);
    cell.setCellFormula("1300000000000000/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1300000000000000/3", formula);
    cell.setCellFormula("-1300000000000000/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1300000000000000/3", formula);
    cell.setCellFormula("-10E-1/3.1E2*4E3/3E4");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1/310*4000/30000", formula);
    wb.close();
}