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

Example 96 with HSSFWorkbook

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

the class TestMirr method testEvaluateInSheet.

public void testEvaluateInSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue(-7500d);
    row.createCell(1).setCellValue(3000d);
    row.createCell(2).setCellValue(5000d);
    row.createCell(3).setCellValue(1200d);
    row.createCell(4).setCellValue(4000d);
    row.createCell(5).setCellValue(0.05d);
    row.createCell(6).setCellValue(0.08d);
    HSSFCell cell = row.createCell(7);
    cell.setCellFormula("MIRR(A1:E1, F1, G1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.clearAllCachedResultValues();
    fe.evaluateFormulaCellEnum(cell);
    double res = cell.getNumericCellValue();
    assertEquals(0.18736225093, res, 0.00000001);
}

Example 97 with HSSFWorkbook

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

the class TestArea3DPtg method testToFormulaString.

/**
	 * confirms that sheet names get properly escaped
	 */
public void testToFormulaString() {
    Area3DPtg target = new Area3DPtg("A1:B1", (short) 0);
    String sheetName = "my sheet";
    HSSFWorkbook wb = createWorkbookWithSheet(sheetName);
    HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(wb);
    assertEquals("'my sheet'!A1:B1", target.toFormulaString(book));
    wb.setSheetName(0, "Sheet1");
    assertEquals("Sheet1!A1:B1", target.toFormulaString(book));
    wb.setSheetName(0, "C64");
    assertEquals("'C64'!A1:B1", target.toFormulaString(book));
}

Example 98 with HSSFWorkbook

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

the class TestErrPtg method testReading.

/**
     * Tests reading a file containing this ptg.
     */
public void testReading() {
    HSSFWorkbook workbook = loadWorkbook("ErrPtg.xls");
    HSSFCell cell = workbook.getSheetAt(0).getRow(3).getCell(0);
    assertEquals("Wrong cell value", 4.0, cell.getNumericCellValue(), 0.0);
    assertEquals("Wrong cell formula", "ERROR.TYPE(#REF!)", cell.getCellFormula());
}

Example 99 with HSSFWorkbook

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

the class BaseTestCell method testMaxTextLength.

/**
     *  The maximum length of cell contents (text) is 32,767 characters.
     * @throws IOException 
     */
@Test
public void testMaxTextLength() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    Cell cell = sheet.createRow(0).createCell(0);
    int maxlen = wb instanceof HSSFWorkbook ? SpreadsheetVersion.EXCEL97.getMaxTextLength() : SpreadsheetVersion.EXCEL2007.getMaxTextLength();
    assertEquals(32767, maxlen);
    StringBuffer b = new StringBuffer();
    // 32767 is okay
    for (int i = 0; i < maxlen; i++) {
        b.append("X");
    }
    cell.setCellValue(b.toString());
    b.append("X");
    // 32768 produces an invalid XLS file
    try {
        cell.setCellValue(b.toString());
        fail("Expected exception");
    } catch (IllegalArgumentException e) {
        assertEquals("The maximum length of cell contents (text) is 32,767 characters", e.getMessage());
    }
    wb.close();
}

Example 100 with HSSFWorkbook

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

the class BaseTestBugzillaIssues method bug58260.

@SuppressWarnings("deprecation")
@Test
public void bug58260() throws IOException {
    //Create workbook and worksheet
    Workbook wb = _testDataProvider.createWorkbook();
    //Sheet worksheet = wb.createSheet("sample");
    //Loop through and add all values from array list
    // use a fixed seed to always produce the same file which makes comparing stuff easier
    //Random rnd = new Random(4352345);
    int maxStyles = (wb instanceof HSSFWorkbook) ? 4009 : 64000;
    for (int i = 0; i < maxStyles; i++) {
        //Create new row
        //Row row = worksheet.createRow(i);
        //Create cell style
        CellStyle style = null;
        try {
            style = wb.createCellStyle();
        } catch (IllegalStateException e) {
            fail("Failed for row " + i);
        }
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        if ((wb instanceof HSSFWorkbook)) {
            // there are some predefined styles
            assertEquals(i + 21, style.getIndex());
        } else {
            // getIndex() returns short, which is not sufficient for > 32767
            // we should really change the API to be "int" for getIndex() but
            // that needs API changes
            assertEquals(i + 1, style.getIndex() & 0xffff);
        }
    //Create cell
    //Cell cell = row.createCell(0);
    //Set cell style
    //cell.setCellStyle(style);
    //Set cell value
    //cell.setCellValue("r" + rnd.nextInt());
    }
    // should fail if we try to add more now
    try {
        wb.createCellStyle();
        fail("Should fail after " + maxStyles + " styles, but did not fail");
    } catch (IllegalStateException e) {
    // expected here
    }
    /*//add column width for appearance sake
        worksheet.setColumnWidth(0, 5000);

        // Write the output to a file
        System.out.println("Writing...");
        OutputStream fileOut = new FileOutputStream("C:\\temp\\58260." + _testDataProvider.getStandardFileNameExtension());

        // the resulting file can be compressed nicely, so we need to disable the zip bomb detection here
        double before = ZipSecureFile.getMinInflateRatio();
        try {
            ZipSecureFile.setMinInflateRatio(0.00001);
            wb.write(fileOut);
        } finally {
            fileOut.close();
            ZipSecureFile.setMinInflateRatio(before);
        }*/
    wb.close();
}