使用EasyPOI实现导入导出

EasyPOI动态表头导入导出,

1. 实体的’主要负责人’ 和’次要负责人’的列名名称需要根据配置动态替换
2. 指定字段(“工地分组”) 根据条件判断是否需要导出
3. 实现下拉列表
4. 给表头加填充颜色
5. 因为在导出的时候替换了相应列的名称, 所以在导入的时候需要动态的去匹配列名名称(重写importService)
6. 导入的时候过滤空行

package com.mok.dms.domain.export;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.ToString;

import java.util.Date;

/**
 * 
 *
 * @author Elvis
 * @version 1.0, 2021/5/14
 */
@Data
@ToString
public class ProjectImportModel {
    @Excel(name = "工地名称(必填)", width = 25, orderNum = "0")
    private String name;
    @Excel(name = "工地分组(必填)", width = 25)
    private String groupName;
    @Excel(name = "主要负责人(必填)", width = 50)
    private String supervisorName;
    @Excel(name = "次要负责人(必填)", width = 50)
    private String managerName;
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date planStartDate;
    @Excel(name = "周末施工", replace = {"是_true", "否_false"}, addressList = true)
    private Boolean weekendWork;
    @Excel(name = "错误信息", width = 30)
    private String errorMsg;
}

/**
 * '主要负责人' 和'次要负责人'的表头名称需要根据配置动态替换
 *  指定字段("工地分组") 根据条件判断是否需要导出
 * 
 */
private List<ExcelExportEntity> buildExcelExportList() {
    List<ExcelExportEntity> excelExportEntityList = new ArrayList<>();
    Class<ProjectImportModel> projectExportVoClass = ProjectImportModel.class;
    Field[] declaredFields = projectExportVoClass.getDeclaredFields();
    boolean enable = true;	// 根据业务逻辑判断是否需要导出当前字段
    String mainInCharge = "新标题1";// 根据业务逻辑取出对应表头
    String secondaryInCharge = "新标题2";// 根据业务逻辑取出对应表头
    for (Field declaredField : declaredFields) {
        declaredField.setAccessible(true);
        Excel annotation = declaredField.getAnnotation(Excel.class);
        if (annotation != null) {
            String name = annotation.name();
            int width = (int) annotation.width();
            if ("工地分组(必填)".equals(name) && !enable) {
                // 如果不需要导出字段 则忽略
                sColDiff++;
                continue;
            }

            if ("主要负责人(必填)".equals(name)) {
                ExcelExportEntity excelExportEntity = new ExcelExportEntity(mainInCharge, declaredField.getName(), width);
                excelExportEntityList.add(excelExportEntity);
                continue;
            }
            if ("次要负责人(必填)".equals(name)) {
                ExcelExportEntity excelExportEntity = new ExcelExportEntity(secondaryInCharge, declaredField.getName(), width);
                excelExportEntityList.add(excelExportEntity);
                // 导出下拉列表方式1
              	// excelExportEntity.setAddressList(true);
                // 需要一个String数组,字符串格式("李监理_1111")
                // String[] param = new String[]{"李监理_1111"}; 可以参数传入此数组
                // excelExportEntity.setReplace(param);

                continue;
            }

            ExcelExportEntity excelExportEntity = new ExcelExportEntity(name, declaredField.getName(), width);
            String format = annotation.format();
            if (StrUtil.isNotBlank(format)) {
                excelExportEntity.setFormat(format);
            }
            if ("周末施工".equals(name)) {
                excelExportEntity.setAddressList(true);
                excelExportEntity.setReplace(new String[]{"是_true", "否_false"});
            }
            excelExportEntityList.add(excelExportEntity);
        }        
    }
    
   return excelExportEntityList;
}

  /**
  *
  *
  */
private void doExport(List<ExcelExportEntity> excelExportEntityList, HttpServletResponse response, List<ProjectImportModel> resultList) {
       try (Workbook workbook = ExcelExportUtil.exportExcel(param, excelExportEntityList, orderExportExcels);
             ServletOutputStream outputStream = response.getOutputStream()) {
            int sheetNum = 1;
         	addCellStyle(workbook, 0, 0, 1, 0, 2, HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
            addCellStyle(workbook, 0, 0, 1, 3, 5, HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());
		   // selectList1 = supervisorSets.toArray(new String[supervisorSets.size()])
           	// selectList2 = managerSets.toArray(new String[managerSets.size()])
           	//    String mainInCharge = "新标题1";// 根据业务逻辑取出对应表头
   		    //	 String secondaryInCharge = "新标题2";// 根据业务逻辑取出对应表头
            // 因为上面组装ExcelExportEntity时候替换了表头名称,所以这里需要取替换后的表头名称
            handleTemplateSelectListWithArray(workbook, sheet, selectList1, mainInCharge + "(必填)");
            handleTemplateSelectListWithArray(workbook, sheet, selectList2, secondaryInCharge + "(必填)");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(DateUtil.now(), "GBK") + ".xls");
            workbook.write(outputStream);
        }
   }

public class ExcelUtils {

    /**
     * 使用常量给导出的模板设置下拉列表
     * 原理就是创建隐藏sheet,并且把下拉列表放入隐藏sheet,然后用公式关联
     * @param workBook
     * @param sheetNum
     * @param selectArray
     * @param selectListKey
     */
    public static void handleTemplateSelectListWithArray(Workbook workBook, int sheetNum,  String[] selectArray, String selectListKey) {
        Sheet sheet = workbook.getSheetAt(sheetNum);
        // 取表头行
        Row row = sheet.getRow(0);
        Iterator<Cell> iterator = row.cellIterator();
        //处理下拉列表
        int col = -1;
        // 循环表头行的列名称, 找到 列名称等于传入selectListKey, 记录下列的位置索引col
        while (iterator.hasNext()) {
            Cell cell = iterator.next();
            String cellValue = cell.getStringCellValue();
            if (selectListKey.equals(cellValue)) {
                col = cell.getColumnIndex();
                break;
            }
        }
        if (col > -1) {
            DataValidation dataValidation = ExcelUtils.getDataValidation(workBook, selectArray, 1, Integer.MAX_VALUE, col, col);
            sheet.addValidationData(dataValidation);
        }
    }

    /**
     * 创建一个单独的sheet, 存放下拉框, 解决下拉框太多的问题。
     * @param workBook
     * @param textList 下拉列表
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     * @return
     */
    public static DataValidation getDataValidation(Workbook workBook, String[] textList, int firstRow, int lastRow, int firstCol, int lastCol) {
        // 创建隐藏sheet 用于存储下拉列表
        Sheet hidden = workBook.createSheet("hidden" + firstCol);
        // 把下拉列表的内容存到新创建的隐藏列的第一列
        for (int i = 0; i < textList.length; i++) {
            String name = textList[i];
            Row row = hidden.createRow(i);
            Cell cell = row.createCell(0);
            cell.setCellValue(name);
        }
        
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationHelper hiddenDataValidationHelper = workBook.getSheet("hidden" + firstCol).getDataValidationHelper();
        // 创建公式与需要下拉的列关联
        DataValidationConstraint formulaListConstraint = hiddenDataValidationHelper.createFormulaListConstraint("hidden" + firstCol + "!$A$1:$A$" + textList.length);
        DataValidation dataValidation = hiddenDataValidationHelper.createValidation(formulaListConstraint, regions);
        // 隐藏该sheet
        int sheetIndex = workBook.getSheetIndex(hidden);
        workBook.setSheetHidden(sheetIndex, true);

        return dataValidation;
    }

    /**
     * 给指定的行列添加填充色
     * @param workbook
     * @param sheetNum 
     * @param startRow 开始行
     * @param endRow   结束行(不含)
     * @param startCell
     * @param endCell    不含
     * @param colorIndex org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined
     */
    public static void addCellStyle(Workbook workbook, int sheetNum, int startRow, int endRow, int startCell, int endCell, short colorIndex) {
        Sheet sheet = workbook.getSheetAt(sheetNum);
        for (int i = startRow; i < endRow; i++) {
            Row row = sheet.getRow(i);
            addCellStyle(row, getCellStyle(workbook, colorIndex), startCell, endCell);
        }
    }

    private static void addCellStyle(Row row, CellStyle cellStyle, int start, int end) {
        for (int i = start; i < end; i++) {
            Cell cell1 = row.getCell(i);
            cell1.setCellStyle(cellStyle);
        }
    }

    private static CellStyle getCellStyle(Workbook workbook, short colorIndex) {
        CellStyle cs = workbook.createCellStyle();
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs.setFillForegroundColor(colorIndex);
        return cs;
    }
}

/*
*
* 导入的时候动态匹配修改后列名称
*
*/
public class MyImportService extends ExcelImportService {
 	private final String secondaryInCharge;
    private final String mainInCharge;
    private final String[] styleSelectList;

    public MyImportService(String mainInCharge,String secondaryInCharge, String[] styleSelectList) {
        String secondaryInCharge = secondaryInCharge;
        String mainInCharge = mainInCharge;
        this.styleSelectList = styleSelectList;
    }

    @Override
    public void addEntityToMap(String targetId, Field field, ExcelImportEntity excelEntity, Class<?> pojoClass, List<Method> getMethods, Map<String, ExcelImportEntity> temp, ExcelEntity excelEntityAnn) throws Exception {
        Excel excel = field.getAnnotation(Excel.class);
        excelEntity = new ExcelImportEntity();
        excelEntity.setType(excel.type());
        excelEntity.setSaveUrl(excel.savePath());
        excelEntity.setSaveType(excel.imageType());
        excelEntity.setReplace(excel.replace());
        excelEntity.setDatabaseFormat(excel.databaseFormat());
        excelEntity.setSuffix(excel.suffix());
        excelEntity.setImportField(Boolean.valueOf(PoiPublicUtil.getValueByTargetId(excel.isImportField(), targetId, "false")));
        excelEntity.setFixedIndex(excel.fixedIndex());
        String name = excel.name();
        // 实体原来的列名
        if ("主要负责人(必填)".equals(name)) {
            name = mainInCharge + "(必填)";
        }
        if ("次要负责人(必填)".equals(name)) {
            name = secondaryInCharge + "(必填)";
        }
       // if ("装修风格".equals(name)) {
       //     excelEntity.setAddressList(true);
       //     excelEntity.setReplace(styleSelectList);
       // }
        excelEntity.setName(PoiPublicUtil.getValueByTargetId(name, targetId, null));
        if (StringUtils.isNoneEmpty(excel.groupName())) {
            excelEntity.setName(excel.groupName() + "_" + excelEntity.getName());
        }
        if (excelEntityAnn != null && excelEntityAnn.show()) {
            excelEntity.setName(excelEntityAnn.name() + "_" + excelEntity.getName());
        }
        if (i18nHandler != null) {
            excelEntity.setName(i18nHandler.getLocaleName(excelEntity.getName()));
        }
        excelEntity.setMethod(PoiReflectorUtil.fromCache(pojoClass).getSetMethod(field.getName()));
        if (StringUtils.isNotEmpty(excel.importFormat())) {
            excelEntity.setFormat(excel.importFormat());
        } else {
            excelEntity.setFormat(excel.format());
        }
        excelEntity.setDict(excel.dict());
        excelEntity.setEnumImportMethod(excel.enumImportMethod());
        if (getMethods != null) {
            List<Method> newMethods = new ArrayList<Method>();
            newMethods.addAll(getMethods);
            newMethods.add(excelEntity.getMethod());
            excelEntity.setMethods(newMethods);
        }
        if (excelEntity.getFixedIndex() != -1) {
            temp.put("FIXED_" + excelEntity.getFixedIndex(), excelEntity);
        } else {
            temp.put(excelEntity.getName(), excelEntity);
        }
    }
}

/**
* 导入数据
*
*/
public List<ProjectImportModel> doImport(@RequestPart(value = "file") MultipartFile file) throws Exception {
    try (InputStream inputStream = file.getInputStream()) {
     	ImportParams params = buildImportParams();
      List<ProjectImportModel> result = new MyImportService(mainInCharge, secondaryInCharge, selectList).importExcelByIs(inputStream, ProjectImportModel.class, params, false).getList();
        return result;
    }
}
/**
*
* 构建验证的handler,过滤空行,防止产生空对象,也就是对象所有的属性都为null
*
*/
private ImportParams buildImportPramas() {
    ImportParams params = new ImportParams();
    params.setVerifyHandler(new IExcelVerifyHandler() {
        @Override
        public ExcelVerifyHandlerResult verifyHandler(Object obj) {
            ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
            if (ObjectUtil.isNotNull(obj)) {
                // 判断对象属性是否全部为空
                boolean b = checkFieldAllNull(obj);
                result.setSuccess(!b);
            }
            return result;
        }

        private boolean checkFieldAllNull(Object obj) {
            // 得到类对象
            Class clazz = obj.getClass();
            Field[] fs = clazz.getDeclaredFields();
            boolean flag = true;
            for (Field f : fs) {
                f.setAccessible(true);
                if (!f.isAnnotationPresent(Excel.class)) {
                    continue;
                }
                // 得到此属性的值
                Object val = null;
                try {
                    val = f.get(obj);
                } catch (IllegalAccessException e) {
                    throw new RuntimeException(e);
                }
                if (Objects.nonNull(val)) {
                    flag = false;
                    break;
                }
            }
            return flag;
        }
    });
}