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

Example 86 with HSSFWorkbook

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

the class TestCFRecordsAggregate method testCFRecordsAggregate.

public void testCFRecordsAggregate() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    List<Record> recs = new ArrayList<Record>();
    CFHeaderBase header = new CFHeaderRecord();
    CFRuleBase rule1 = CFRuleRecord.create(sheet, "7");
    CFRuleBase rule2 = CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "2", "5");
    CFRuleBase rule3 = CFRuleRecord.create(sheet, ComparisonOperator.GE, "100", null);
    header.setNumberOfConditionalFormats(3);
    CellRangeAddress[] cellRanges = { new CellRangeAddress(0, 1, 0, 0), new CellRangeAddress(0, 1, 2, 2) };
    header.setCellRanges(cellRanges);
    recs.add(header);
    recs.add(rule1);
    recs.add(rule2);
    recs.add(rule3);
    CFRecordsAggregate record = CFRecordsAggregate.createCFAggregate(new RecordStream(recs, 0));
    // Serialize
    byte[] serializedRecord = new byte[record.getRecordSize()];
    record.serialize(0, serializedRecord);
    InputStream in = new ByteArrayInputStream(serializedRecord);
    //Parse
    recs = RecordFactory.createRecords(in);
    // Verify
    assertNotNull(recs);
    assertEquals(4, recs.size());
    header = (CFHeaderRecord) recs.get(0);
    rule1 = (CFRuleRecord) recs.get(1);
    assertNotNull(rule1);
    rule2 = (CFRuleRecord) recs.get(2);
    assertNotNull(rule2);
    rule3 = (CFRuleRecord) recs.get(3);
    assertNotNull(rule3);
    cellRanges = header.getCellRanges();
    assertEquals(2, cellRanges.length);
    assertEquals(3, header.getNumberOfConditionalFormats());
    assertFalse(header.getNeedRecalculation());
    record = CFRecordsAggregate.createCFAggregate(new RecordStream(recs, 0));
    record = record.cloneCFAggregate();
    assertNotNull(record.getHeader());
    assertEquals(3, record.getNumberOfRules());
    header = record.getHeader();
    rule1 = record.getRule(0);
    assertNotNull(rule1);
    rule2 = record.getRule(1);
    assertNotNull(rule2);
    rule3 = record.getRule(2);
    assertNotNull(rule3);
    cellRanges = header.getCellRanges();
    assertEquals(2, cellRanges.length);
    assertEquals(3, header.getNumberOfConditionalFormats());
    assertFalse(header.getNeedRecalculation());
}

Example 87 with HSSFWorkbook

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

the class TestPLVRecord method testPLVRecord.

public void testPLVRecord() throws Exception {
    InputStream is = HSSFTestDataSamples.openSampleFileStream(XLS_FILENAME);
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1);
    DataValidationConstraint constraint = DVConstraint.createFormulaListConstraint(DV_DEFINITION);
    HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint);
    // This used to throw an error before
    try {
        workbook.getSheet(SHEET_NAME).addValidationData(dataValidation);
    } catch (IllegalStateException ex) {
        throw new AssertionFailedError("Identified bug 53972, PLV record breaks addDataValidation()");
    }
}

Example 88 with HSSFWorkbook

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

the class TestNameRecord method testFormulaGeneral.

public void testFormulaGeneral() throws IOException {
    // perhaps this testcase belongs on TestHSSFName
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFName name = wb.createName();
    wb.createSheet("Sheet1");
    name.setNameName("test");
    name.setRefersToFormula("Sheet1!A1+Sheet1!A2");
    assertEquals("Sheet1!A1+Sheet1!A2", name.getRefersToFormula());
    name.setRefersToFormula("5*6");
    assertEquals("5*6", name.getRefersToFormula());
    wb.close();
}

Example 89 with HSSFWorkbook

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

the class TestFeatRecord method testWithoutFeatRecord.

public void testWithoutFeatRecord() throws Exception {
    HSSFWorkbook hssf = HSSFTestDataSamples.openSampleWorkbook("46136-WithWarnings.xls");
    InternalWorkbook wb = HSSFTestHelper.getWorkbookForTest(hssf);
    assertEquals(1, hssf.getNumberOfSheets());
    int countFR = 0;
    int countFRH = 0;
    // Check on the workbook, but shouldn't be there!
    for (Record r : wb.getRecords()) {
        if (r instanceof FeatRecord) {
            countFR++;
        } else if (r.getSid() == FeatRecord.sid) {
            countFR++;
        }
        if (r instanceof FeatHdrRecord) {
            countFRH++;
        } else if (r.getSid() == FeatHdrRecord.sid) {
            countFRH++;
        }
    }
    assertEquals(0, countFR);
    assertEquals(0, countFRH);
    // Now check on the sheet
    HSSFSheet s = hssf.getSheetAt(0);
    InternalSheet sheet = HSSFTestHelper.getSheetForTest(s);
    for (RecordBase rb : sheet.getRecords()) {
        if (rb instanceof Record) {
            Record r = (Record) rb;
            if (r instanceof FeatRecord) {
                countFR++;
            } else if (r.getSid() == FeatRecord.sid) {
                countFR++;
            }
            if (r instanceof FeatHdrRecord) {
                countFRH++;
            } else if (r.getSid() == FeatHdrRecord.sid) {
                countFRH++;
            }
        }
    }
    assertEquals(0, countFR);
    assertEquals(0, countFRH);
}

Example 90 with HSSFWorkbook

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

the class TestSubtotal method testAvg.

@Test
public void testAvg() throws IOException {
    Workbook wb = new HSSFWorkbook();
    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sh = wb.createSheet();
    Cell a1 = sh.createRow(1).createCell(1);
    a1.setCellValue(1);
    Cell a2 = sh.createRow(2).createCell(1);
    a2.setCellValue(3);
    Cell a3 = sh.createRow(3).createCell(1);
    a3.setCellFormula("SUBTOTAL(1,B2:B3)");
    Cell a4 = sh.createRow(4).createCell(1);
    a4.setCellValue(1);
    Cell a5 = sh.createRow(5).createCell(1);
    a5.setCellValue(7);
    Cell a6 = sh.createRow(6).createCell(1);
    a6.setCellFormula("SUBTOTAL(1,B2:B6)*2 + 2");
    Cell a7 = sh.createRow(7).createCell(1);
    a7.setCellFormula("SUBTOTAL(1,B2:B7)");
    Cell a8 = sh.createRow(8).createCell(1);
    a8.setCellFormula("SUBTOTAL(1,B2,B3,B4,B5,B6,B7,B8)");
    fe.evaluateAll();
    assertEquals(2.0, a3.getNumericCellValue(), 0);
    assertEquals(8.0, a6.getNumericCellValue(), 0);
    assertEquals(3.0, a7.getNumericCellValue(), 0);
    assertEquals(3.0, a8.getNumericCellValue(), 0);
    wb.close();
}