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

Example 26 with CellRangeAddressList

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

the class ExcelUtils method writeXlsSheet.

/**
 * 将数据写入sheet
 *
 * @param workbook
 * @param sheetConfig
 * @throws Exception
 */
private static void writeXlsSheet(HSSFWorkbook 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();
    HSSFSheet sheet = workbook.createSheet(ObjUtils.ifNull(sheetConfig.getName(), "sheet1"));
    // 设置标题
    int columnNum = sheetConfig.getColumnList().size();
    List<ExportColumn> columnList = sheetConfig.getColumnList();
    HSSFRow rowTitle = sheet.createRow(0);
    for (int i = 0; i < columnNum; i++) {
        ExportColumn column = columnList.get(i);
        String title = ObjUtils.ifNull(column.getTitle(), "");
        HSSFCell cell = rowTitle.createCell(i);
        cell.setCellValue(createHelper.createRichTextString(title));
        cell.setCellStyle(ObjUtils.ifNull(column.getTitleStyle(), sheetConfig.getTitleStyle()));
        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++) {
        HSSFRow 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);
            HSSFCell hssfCell = row.createCell(j);
            hssfCell.setCellStyle(ObjUtils.ifNull(column.getRowStyle(), sheetConfig.getRowStyle()));
            if (i == 0 && column.getType() == ExcelDataType.EnumValue && ArrayUtils.isNotEmpty(column.getValueArray())) {
                CellRangeAddressList addressList = new CellRangeAddressList(1, rowNum + 1, j, j);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(column.getValueArray());
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
                sheet.addValidationData(dataValidation);
            }
            // 获取列值
            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();
                hssfCell.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) {
                    HSSFCellStyle cellStyle = hssfCell.getCellStyle();
                    cellStyle.setDataFormat(dataFormat.getFormat("#0"));
                    hssfCell.setCellStyle(cellStyle);
                    hssfCell.setCellValue(value.longValue());
                } else {
                    hssfCell.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());
                }
                HSSFCellStyle cellStyle = hssfCell.getCellStyle();
                cellStyle.setDataFormat(dataFormat.getFormat(format));
                hssfCell.setCellStyle(cellStyle);
                hssfCell.setCellValue(value.doubleValue());
            } else if (column.getType() == ExcelDataType.DateValue) {
                hssfCell.setCellValue(DateUtils.format((Date) (valueObj)));
            } else {
                hssfCell.setCellValue(ObjUtils.toString(valueObj, ""));
            }
        }
    }
}

Example 27 with CellRangeAddressList

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

the class ExcelWriteTest method addSelectTest2.

@Test
@Ignore
public void addSelectTest2() {
    ExcelWriter writer = ExcelUtil.getWriter("d:/test/select.xls");
    writer.writeCellValue(0, 0, "请选择科目");
    int firstRow = 0;
    int lastRow = 0;
    int firstCol = 0;
    int lastCol = 0;
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    writer.addSelect(addressList, "1001", "1002", "1003");
    List<?> rows = new ArrayList<>();
    writer.write(rows, true);
    writer.close();
}