EasyExcelUtils导出指定字段-不推荐速度没有poi快

dorisPotter

已于 2022-12-27 11:58:01 修改

阅读量1k

点赞数

分类专栏：工具类文章标签： java 前端开发语言

于 2022-08-12 17:35:20 首次发布

本文链接：https://blog.csdn.net/weixin_45860338/article/details/126308116

版权

工具类专栏收录该内容

1 篇文章 0 订阅

订阅专栏

EasyExcelUtils导出指定字段

实体类
具体实现

产品要求导出改成可以前端选择自定义导出的列作为导出Excel的表头

实体类

@Data
@ApiModel(value = "订单表查询参数")
public class OpExportDTO implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 保函申请id
     */
    @ApiModelProperty(value = "保函申请id")
    private String guaranteeNo;

    /**
     * 订单编号
     */
    @ApiModelProperty(value = "订单编号")
    private String orderNo;

    /**
     * 保函编号
     */
    private String guaranteeCode;
    /**
     * 提交时间
     *
     * @return
     */
    @ApiModelProperty(value = "提交时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", locale = "zh", timezone = "GMT+8")
    private Date createTime;
    
    /**
     * 选择导出的字段
     */
    @ApiModelProperty(value = "选择导出的字段")
    private List<String> fieldList;
}

具体实现


/**
 * @author: doris
 * @date :2022/8/5 15:40
 * 导出列的自适应宽度
 */
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public CustomCellWriteHandler() {
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData)cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch(type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

/**
 * 操作excel工具类
 * @author: doris
 * @date :2022/4/25 17:00
 */
public class EasyExcelUtils {
    /**
     * 根据模型写入excel
     */
    public static <T> void writeExcelWithModel(OutputStream outputStream, Class<T> clazz, Map<Integer,String[]> dropDownMap) throws IOException {
        EasyExcel.write(outputStream, clazz).registerWriteHandler(new TitleHandler(dropDownMap)).sheet("模板").doWrite(ListUtil.empty());
    }

    /**
     * 自定义列名生成excel
     * @param excelPath excel文件路径.后缀
     * @param sheetName excel sheet名称
     * @param bodyList excel 主数据
     * @param headList excel 头标题
     */
    public static void writeExcelWithListColumnData(String excelPath, String sheetName, List<List<String>> bodyList, List<List<String>> headList) {
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = defaultStyles();
        CustomCellWriteHandler handler = new CustomCellWriteHandler();
        //指定单元格样式
        //用来记录需要为第`key`行中的第`value.get(i)`列设置样式
        HashMap<Integer, List<Integer>> map = new HashMap<>();
//        CellColorSheetWriteHandler writeHandler = new CellColorSheetWriteHandler(map, IndexedColors.RED.getIndex());
        EasyExcel.write(excelPath).excelType(ExcelTypeEnum.XLSX).sheet().sheetName(sheetName)
                .head(headList).automaticMergeHead(true)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(new AutoColumnWidthStrategy())
                .registerWriteHandler(handler)
                .doWrite(bodyList);
    }

    // 处理样式
    public static HorizontalCellStyleStrategy defaultStyles() {
        //TODO 默认样式
        //表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置淡蓝色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 14);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式策略策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置垂直居中为居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置左右对齐为靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 设置单元格上下左右边框为细边框
        /*contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);*/
        //创建字体对象
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 初始化表格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
}

/**
 * @params fieldList 要导入的列 比如["guaranteeCode","orderNo"]
*/
 public File exportGuaranteeInfoExcel(List<String> fieldList) {
        // excel 表数据
        List<List<String>> bodyList = new ArrayList<>();
        // excel 表头
        List<List<String>> headList = new ArrayList<>();
        // 获取所有可以导入的列
        Map<String, String> exportFieldList = this.getExportFieldList();
        List<OpExportDTO> data = new ArrayList<>();// 从数据库查询或者自己插入
        // 非空判断
        if (!CollectionUtils.isEmpty(fieldList)) {
            for (String head : fieldList) {
                // 添加表头
                List<String> headClist = new ArrayList<>();
                // 根据key获取中文表头
                headClist.add(exportFieldList.get(head));
                headList.add(headClist);
            }
            // 处理要导入的字段的数据
            if (!CollectionUtils.isEmpty(fieldList)) {
                for (OpExportDTO opExportDTO : data) {
                    bodyList.addAll(this.dataList(fieldList, opExportDTO));
                }
            }
            File file = new File("D:/导出"+LocalDateTime.now()+".xlsx");
            file.createNewFile();
            EasyExcelUtils.writeExcelWithListColumnData(file.getAbsolutePath(), "sheet", bodyList, headList);
            return file;
        }
        return null;
    }

    /**
     * 获取所有字段表头
     *
     * @return
     */
    public Map<String, String> getExportFieldList() {
        Map<String, String> map = new HashMap<>();
        map.put("guaranteeCode", "保函编号");
        map.put("orderNo", "订单编号");
        map.put("createTime", "提交时间");
        map.put("guaranteeRange", "保函类型");
        map.put("oState", "订单状态");
        map.put("gState", "申请单状态");
        return map;
    }
    
    /**
     * 要导出的字段数据
     */
    @SneakyThrows
    public static <T> List<List<T>> dataList(List<String> propList, OpExportDTO obj) {
        List<List<T>> list = new ArrayList<>();
        List<T> data = new ArrayList<>();
        //先根据反射获取实体类的class对象
        Class objClass = obj.getClass();
        //设置实体类属性的集合
        Field[] fields = ReflectUtil.getFields(objClass);
        for (String prop : propList) {
            //循环实体类对象集合
            for (Field field : fields) {
                field.setAccessible(true);
                //判断实体类属性跟特定字段集合名是否一样
                if (field.getName().equals(prop)) {
                    T object = (T) field.get(obj);
                    // 如果是日期类型转换为yyyy-MM-dd
                    if (field.getType() == Date.class) {
                        String dateStr = DateUtils.format((Date) object, DateUtils.PATTERN_DATE_10);
                        data.add((T) dateStr);
                    }else{
                        data.add(object);
                    }
                }
            }
        }
        list.add(data);
        return list;
    }