Examples with CellRangeAddressList org.apache.poi.ss.util.CellRangeAddressList used on opensource projects

Example 11 with CellRangeAddressList

use of org.apache.poi.ss.util.CellRangeAddressList in project poi by apache.

the class TestDataValidation method testGetDataValidationsDate.

@Test
public void testGetDataValidationsDate() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    List<HSSFDataValidation> list = sheet.getDataValidations();
    assertEquals(0, list.size());
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = dataValidationHelper.createDateConstraint(OperatorType.EQUAL, "2014/10/25", null, null);
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
    sheet.addValidationData(validation);
    // <-- works
    list = sheet.getDataValidations();
    assertEquals(1, list.size());
    HSSFDataValidation dv = list.get(0);
    DVConstraint c = dv.getConstraint();
    assertEquals(ValidationType.DATE, c.getValidationType());
    assertEquals(OperatorType.EQUAL, c.getOperator());
    assertEquals(null, c.getFormula1());
    assertEquals(null, c.getFormula2());
    assertEquals(DateUtil.getExcelDate(DateUtil.parseYYYYMMDDDate("2014/10/25")), c.getValue1(), 0);
    assertEquals(null, c.getValue2());
    wb.close();
}

Example 12 with CellRangeAddressList

use of org.apache.poi.ss.util.CellRangeAddressList in project poi by apache.

the class TestHSSFSheet method dvProtectionOrder_bug47363b.

/**
     * There should be no problem with adding data validations after sheet protection
     */
@Test
public void dvProtectionOrder_bug47363b() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sheet1");
    sheet.protectSheet("secret");
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    DataValidationConstraint dvc = dataValidationHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, "10", "100");
    CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
    DataValidation dv = dataValidationHelper.createValidation(dvc, numericCellAddressList);
    try {
        sheet.addValidationData(dv);
    } catch (IllegalStateException e) {
        String expMsg = "Unexpected (org.apache.poi.hssf.record.PasswordRecord) while looking for DV Table insert pos";
        if (expMsg.equals(e.getMessage())) {
            fail("Identified bug 47363b");
        }
        workbook.close();
        throw e;
    }
    RecordCollector rc;
    rc = new RecordCollector();
    sheet.getSheet().visitContainedRecords(rc, 0);
    int nRecsWithProtection = rc.getRecords().length;
    sheet.protectSheet(null);
    rc = new RecordCollector();
    sheet.getSheet().visitContainedRecords(rc, 0);
    int nRecsWithoutProtection = rc.getRecords().length;
    assertEquals(4, nRecsWithProtection - nRecsWithoutProtection);
    workbook.close();
}

Example 13 with CellRangeAddressList

use of org.apache.poi.ss.util.CellRangeAddressList in project poi by apache.

the class BaseTestBugzillaIssues method test60370.

@Test
public void test60370() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    final Sheet sheet = wb.createSheet();
    DataValidation dataValidation;
    CellRangeAddressList headerCell = new CellRangeAddressList(0, 1, 0, 1);
    DataValidationConstraint constraint = sheet.getDataValidationHelper().createCustomConstraint("A1<>\"\"");
    dataValidation = sheet.getDataValidationHelper().createValidation(constraint, headerCell);
    checkFailures(dataValidation, TEST_SPECIAL_TITLE, TEST_SPECIAL, false);
    dataValidation.setShowErrorBox(true);
    dataValidation.setShowPromptBox(true);
    sheet.addValidationData(dataValidation);
    // write out and read back in to trigger some more validation
    final Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb);
    final Sheet sheetBack = wbBack.getSheetAt(0);
    final List<? extends DataValidation> dataValidations = sheetBack.getDataValidations();
    assertEquals(1, dataValidations.size());
    /*String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx";
        OutputStream str = new FileOutputStream("/tmp/60370" + ext);
        try {
            wb.write(str);
        } finally {
            str.close();
        }*/
    wb.close();
}

Example 14 with CellRangeAddressList

use of org.apache.poi.ss.util.CellRangeAddressList in project poi by apache.

the class XSSFSheet method getDataValidations.

@Override
public List<XSSFDataValidation> getDataValidations() {
    List<XSSFDataValidation> xssfValidations = new ArrayList<XSSFDataValidation>();
    CTDataValidations dataValidations = this.worksheet.getDataValidations();
    if (dataValidations != null && dataValidations.getCount() > 0) {
        for (CTDataValidation ctDataValidation : dataValidations.getDataValidationArray()) {
            CellRangeAddressList addressList = new CellRangeAddressList();
            @SuppressWarnings("unchecked") List<String> sqref = ctDataValidation.getSqref();
            for (String stRef : sqref) {
                String[] regions = stRef.split(" ");
                for (String region : regions) {
                    String[] parts = region.split(":");
                    CellReference begin = new CellReference(parts[0]);
                    CellReference end = parts.length > 1 ? new CellReference(parts[1]) : begin;
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.getRow(), end.getRow(), begin.getCol(), end.getCol());
                    addressList.addCellRangeAddress(cellRangeAddress);
                }
            }
            XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
            xssfValidations.add(xssfDataValidation);
        }
    }
    return xssfValidations;
}

Example 15 with CellRangeAddressList

use of org.apache.poi.ss.util.CellRangeAddressList in project swift by luastar.

the class ExcelUtils method writeXlsxSheet.

/**
 * 将数据写入sheet
 *
 * @param workbook
 * @param sheetConfig
 */
private static void writeXlsxSheet(XSSFWorkbook workbook, ExportSheet sheetConfig) throws Exception {
    if (workbook == null || sheetConfig == null || CollectionUtils.isEmpty(sheetConfig.getColumnList())) {
        throw new IllegalArgumentException("excel导出参数错误！");
    }
    CreationHelper createHelper = workbook.getCreationHelper();
    DataFormat dataFormat = createHelper.createDataFormat();
    XSSFSheet sheet = workbook.createSheet(ObjUtils.ifNull(sheetConfig.getName(), "sheet1"));
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    // 设置标题
    int columnNum = sheetConfig.getColumnList().size();
    List<ExportColumn> columnList = sheetConfig.getColumnList();
    XSSFRow rowTitle = sheet.createRow(0);
    for (int i = 0; i < columnNum; i++) {
        ExportColumn column = columnList.get(i);
        String title = ObjUtils.ifNull(column.getTitle(), "");
        XSSFCell cell = rowTitle.createCell(i);
        cell.setCellStyle(ObjUtils.ifNull(column.getTitleStyle(), sheetConfig.getTitleStyle()));
        cell.setCellValue(createHelper.createRichTextString(title));
        ExcelUtils.setColumnWidthTitle(column, sheet, i, title);
        sheet.setColumnHidden(i, column.isHidden());
    }
    if (CollectionUtils.isEmpty(sheetConfig.getDataList())) {
        logger.info("sheet {} 数据为空", sheet.getSheetName());
        return;
    }
    // 设置内容
    int rowNum = sheetConfig.getDataList().size();
    for (int i = 0; i < rowNum; i++) {
        XSSFRow row = sheet.createRow(i + 1);
        logger.info("写入第{}/{}条数据", row.getRowNum(), rowNum);
        Object data = sheetConfig.getDataList().get(i);
        for (int j = 0; j < columnNum; j++) {
            ExportColumn column = columnList.get(j);
            XSSFCell xssfCell = row.createCell(j);
            xssfCell.setCellStyle(ObjUtils.ifNull(column.getRowStyle(), sheetConfig.getRowStyle()));
            // 设置下拉框
            if (i == 0 && column.getType() == ExcelDataType.EnumValue && ArrayUtils.isNotEmpty(column.getValueArray())) {
                XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(column.getValueArray());
                CellRangeAddressList addressList = new CellRangeAddressList(1, rowNum + 1, j, j);
                XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
                sheet.addValidationData(validation);
            }
            // 获取列值
            Object valueObj;
            if (data instanceof Map) {
                valueObj = ((Map) data).get(column.getProp());
            } else {
                valueObj = PropertyUtils.getProperty(data, column.getProp());
            }
            if (valueObj == null) {
                continue;
            }
            // 设置宽度
            ExcelUtils.setColumnWidthRow(column, sheet, j, ObjUtils.toString(valueObj));
            // 设置不同类型的值
            if (column.getType() == ExcelDataType.EnumValue && valueObj instanceof IExcelEnum) {
                String value = ((IExcelEnum) valueObj).getValue();
                xssfCell.setCellValue(ObjUtils.ifNull(value, ""));
            } else if (column.getType() == ExcelDataType.IntegerValue || column.getType() == ExcelDataType.LongValue) {
                BigDecimal value = ObjUtils.toBigDecimal(valueObj, BigDecimal.ZERO).setScale(0);
                if (value.toString().length() <= 12) {
                    XSSFCellStyle cellStyle = (XSSFCellStyle) xssfCell.getCellStyle().clone();
                    cellStyle.setDataFormat(dataFormat.getFormat("#0"));
                    xssfCell.setCellStyle(cellStyle);
                    xssfCell.setCellValue(value.longValue());
                } else {
                    xssfCell.setCellValue(value.toString());
                }
            } else if (column.getType() == ExcelDataType.BigDecimalValue) {
                BigDecimal value = ObjUtils.toBigDecimal(valueObj, BigDecimal.ZERO).setScale(column.getScale(), BigDecimal.ROUND_HALF_UP);
                String format = "#,##0.00";
                if (column.getScale() > 0 && column.getScale() <= 8) {
                    format = "#,##0." + StringUtils.repeat("0", column.getScale());
                }
                XSSFCellStyle cellStyle = (XSSFCellStyle) xssfCell.getCellStyle().clone();
                cellStyle.setDataFormat(dataFormat.getFormat(format));
                xssfCell.setCellStyle(cellStyle);
                xssfCell.setCellValue(value.doubleValue());
            } else if (column.getType() == ExcelDataType.DateValue) {
                xssfCell.setCellValue(DateUtils.format((Date) (valueObj)));
            } else {
                xssfCell.setCellValue(ObjUtils.toString(valueObj, ""));
            }
        }
    }
}