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

Example 56 with HSSFSheet

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

the class TestSharedValueManager method testPartiallyOverlappingRanges.

/**
	 * This bug happened when there were two or more shared formula ranges that overlapped.  POI
	 * would sometimes associate formulas in the overlapping region with the wrong shared formula
	 */
public void testPartiallyOverlappingRanges() {
    Record[] records;
    int attempt = 1;
    do {
        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(SAMPLE_FILE_NAME);
        HSSFSheet sheet = wb.getSheetAt(0);
        RecordInspector.getRecords(sheet, 0);
        assertEquals("1+1", sheet.getRow(2).getCell(0).getCellFormula());
        if ("1+1".equals(sheet.getRow(3).getCell(0).getCellFormula())) {
            throw new AssertionFailedError("Identified bug - wrong shared formula record chosen" + " (attempt " + attempt + ")");
        }
        assertEquals("2+2", sheet.getRow(3).getCell(0).getCellFormula());
        records = RecordInspector.getRecords(sheet, 0);
    } while (attempt++ < MAX_ATTEMPTS);
    int count = 0;
    for (Record record : records) {
        if (record instanceof SharedFormulaRecord) {
            count++;
        }
    }
    assertEquals(2, count);
}

Example 57 with HSSFSheet

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

the class TestXorEncryption method testUserFile.

@SuppressWarnings("static-access")
@Test
public void testUserFile() throws IOException {
    Biff8EncryptionKey.setCurrentUserPassword("abc");
    NPOIFSFileSystem fs = new NPOIFSFileSystem(samples.getSampleFile("xor-encryption-abc.xls"), true);
    HSSFWorkbook hwb = new HSSFWorkbook(fs.getRoot(), true);
    HSSFSheet sh = hwb.getSheetAt(0);
    assertEquals(1.0, sh.getRow(0).getCell(0).getNumericCellValue(), 0.0);
    assertEquals(2.0, sh.getRow(1).getCell(0).getNumericCellValue(), 0.0);
    assertEquals(3.0, sh.getRow(2).getCell(0).getNumericCellValue(), 0.0);
    hwb.close();
    fs.close();
}

Example 58 with HSSFSheet

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

the class TestEvaluationCache method testBlankCellChangedToValueCell_bug46053.

/**
	 * Make sure that when blank cells are changed to value/formula cells, any dependent formulas
	 * have their cached results cleared.
	 */
public void testBlankCellChangedToValueCell_bug46053() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    HSSFCell cellB1 = row.createCell(1);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    cellA1.setCellFormula("B1+2.2");
    cellB1.setCellValue(1.5);
    fe.notifyUpdateCell(cellA1);
    fe.notifyUpdateCell(cellB1);
    CellValue cv;
    cv = fe.evaluate(cellA1);
    assertEquals(3.7, cv.getNumberValue(), 0.0);
    cellB1.setCellType(CellType.BLANK);
    fe.notifyUpdateCell(cellB1);
    // B1 was used to evaluate A1
    cv = fe.evaluate(cellA1);
    assertEquals(2.2, cv.getNumberValue(), 0.0);
    // changing B1, so A1 cached result should be cleared
    cellB1.setCellValue(0.4);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    if (cv.getNumberValue() == 2.2) {
        // looks like left-over cached result from before change to B1
        throw new AssertionFailedError("Identified bug 46053");
    }
    assertEquals(2.6, cv.getNumberValue(), 0.0);
}

Example 59 with HSSFSheet

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

the class TestMissingArgEval method testCountFuncs.

public void testCountFuncs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    // 4 missing args, C5 is blank 
    cell.setCellFormula("COUNT(C5,,,,)");
    assertEquals(4.0, fe.evaluate(cell).getNumberValue(), 0.0);
    // 2 missing args, C5 is blank 
    cell.setCellFormula("COUNTA(C5,,)");
    fe.clearAllCachedResultValues();
    assertEquals(2.0, fe.evaluate(cell).getNumberValue(), 0.0);
}

Example 60 with HSSFSheet

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

the class TestMissingArgEval method testEvaluateMissingArgs.

public void testEvaluateMissingArgs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    cell.setCellFormula("if(true,)");
    fe.clearAllCachedResultValues();
    CellValue cv;
    try {
        cv = fe.evaluate(cell);
    } catch (EmptyStackException e) {
        throw new AssertionFailedError("Missing args evaluation not implemented (bug 43354");
    }
    // MissingArg -> BlankEval -> zero (as formula result)
    assertEquals(0.0, cv.getNumberValue(), 0.0);
    // MissingArg -> BlankEval -> empty string (in concatenation)
    cell.setCellFormula("\"abc\"&if(true,)");
    fe.clearAllCachedResultValues();
    assertEquals("abc", fe.evaluate(cell).getStringValue());
}