Springboot整合EasyExcel

作者: 通通小小通 | 来源:发表于2021-05-31 15:45 被阅读0次

Springboot整合EasyExcel
Java一行代码读写Excel
springboot项目整合阿里开源框架EasyExcel，附带
37. 从零开始学springboot-集成EasyExcel读
springboot整合easyexcel实现Excel导入导出
SpringBoot整合EasyExcel进行报表导入导出
springboot整合springsecurity从Hello
springboot整合springsecurity从Hello
springboot整合springsecurity从Hello
SpringBoot + Shiro 整合 JWT

引入依赖

<!--EasyExcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.4</version>
</dependency>
<!--POI-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

案例:我一般都是CV拿一套官网的示例，然后在示例得基础上修修改改定制自己得需求。

如果是基本的单表导出或者有对应的实体可以参照官网案例直接使用注解，本次记录得是在没有实体得情况下进行导出操作
基础案例

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.*;

public class ExportExcelByPoiUtil {

    public static void main(String[] args) {
        // 生成Excel路径

        String fileName = "/Users/cc/Downloads/测试12.xlsx";

        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        //headWriteCellStyle.setFillForegroundColor(IndexedColors.BLACK1.getIndex());
        //WriteFont headWriteFont = new WriteFont();
        //headWriteFont.setFontHeightInPoints((short)20);
        //headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 垂直对齐方式
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);


        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);


        EasyExcel.write(fileName)
                .head(head())
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
                //设置合并单元格策略
                //.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
                .sheet("模板")
                .doWrite(dataList());

    }
    
    // 模拟请求头
    private static List<List<String>> head() {

        List<List<String>> list = new ArrayList<>();

        List<String> head0 = new ArrayList<>();
        //head0.add("测试导出");

        head0.add("姓名");

        List<String> head1 = new ArrayList<>();
        head1.add("年龄");

        List<String> head2 = new ArrayList<>();

        head2.add("生日");

        list.add(head0);

        list.add(head1);

        list.add(head2);

        return list;
    }
    
    // 模拟数据
    private static List<List<Object>> dataList() {
        List<List<Object>> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {

            List<Object> data = new ArrayList<>();
            Map<String, Object> model = new HashMap<>();
            data.add("张三");

            data.add(25);

            data.add(new Date());

            list.add(data);
        }

        return list;

    }
}

运行以上main方法会生成如下Excel

1.png
通过上面示例就大概可以理解生成Excel的主要步骤。

照葫芦画瓢实现自己的需求

1. 组装需要导出得数据，按需组装我得数据格式挺复杂。如果数据结构很清晰不需要写那么多循环

    /**
     * 数据组装
     *
     * @param keyList        需要导出得字段名称
     * @param jsonObjectList 初始数据
     * @return 结果集
     */
    @SuppressWarnings("unchecked")
    public List<List<Object>> exportExcelData(List<String> keyList, List<JSONObject> jsonObjectList) {

        List<List<Object>> exportExcelData = new ArrayList<>();
        // 数据组装
        for (JSONObject jsonObject : jsonObjectList) {
            List<Map<String, Object>> checkDataList = (List<Map<String, Object>>) jsonObject.get("CheckData");
            // 拿到存放车厢信息和检测信息CheckData
            for (Map<String, Object> map : checkDataList) {
                // 拿到检测数值
                List<Map<String, Object>> checkSizeList = (List<Map<String, Object>>) map.get("CheckSize");
                for (Map<String, Object> stringObjectMap : checkSizeList) {
                    List<Object> data = new ArrayList<>();
                    // 需要导出得字段
                    for (String str : keyList) {
                        // 检测数据
                        data.add(stringObjectMap.get(str) == null ? "" : stringObjectMap.get(str).toString());
                    }
                    exportExcelData.add(data);
                }
            }
        }
        return exportExcelData;

    }

1. 标题头合并标题头

    /**
     * 导出Excel表头数据处理
     *
     * @param headName 表头合并名称（如数据统计,会自动合并该列得标题头）
     * @param heads    表头集合
     * @return 表头组装数据
     */
    public List<List<String>> head(String headName, List<String> heads) {
        List<List<String>> list = new ArrayList<>();
        for (String head : heads) {
            List<String> headList = new ArrayList<>();
            headList.add(headName);
            headList.add(head);
            list.add(headList);
        }

        return list;
    }

接口

 @PostMapping("/exportHistoryData")
    @ApiOperation("历史检测数据导出")
    @SuppressWarnings("unchecked")
    public void historyData(SearchModel searchModel, HttpServletResponse response, HttpServletRequest request) throws Exception {

        List<String> detectItemList = searchModel.getDetectItemList();
        // 选中字段英文名称
        LinkedList<String> keyList = new LinkedList<>();
        // 选中字段中文名称
        LinkedList<String> lableList = new LinkedList<>();

        detectItemList.forEach(item -> {
            keyList.add(item.substring(0, item.indexOf(":")));

            int rule = item.lastIndexOf(":");
            String Suffix = item.substring((rule + 1), item.length());

            lableList.add(Suffix);
        });

        // 根据日期筛选
        if (searchModel.getStartTime() != null && searchModel.getEndTime() != null) {
            query.addCriteria(Criteria.where("CheckTime").gte(date2String(searchModel.getStartTime()))
                    .lte(date2String(searchModel.getEndTime())));
        }

        // 根据编组查询
        if (StringUtils.isNotBlank(searchModel.getGroupInfoName())) {
            query.addCriteria(Criteria.where("TrnsetName").is(searchModel.getGroupInfoName()));
        }

        // 检测线
        if (StringUtils.isNotBlank(searchModel.getDeviceInfoName())) {
            query.addCriteria(Criteria.where("device").is(searchModel.getDeviceInfoName()));
        }

        // 车厢编号
        if (StringUtils.isNotBlank(searchModel.getCarUID())) {
            query.addCriteria(Criteria.where("CarUID").is(searchModel.getCarUID()));
        }

        // 拿到数据
        List<JSONObject> jsonObjectList = mongodbUtils.getByCollectionName(query, "wheelset");

        // 必要数据填充
        List<String> basisLable = Arrays.asList("编组名称", "车厢编号");
        lableList.add("检测时间");

        List<String> basisKey = Arrays.asList("TrnsetName", "CarUID");
        keyList.add("CheckTime");


        List<String> lable = ListUtils.union(basisLable, lableList);
        List<String> key = ListUtils.union(basisKey, keyList);
        // Excel标题头
        List<List<String>> headList = head("轮对设备历史数据统计", lable);
        // 组装需要导出得数据
        List<List<Object>> lists = exportExcelData(key, jsonObjectList);

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // URLEncoder.encode防止中文乱码
        String fileName = URLEncoder.encode("轮对设备历史数据统计", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");


        // 合并单元格
        // 获取最后一行检测时间得下标
        int checkTimeIndex = key.size() - 1;
        //需要合并的列
        int[] mergeColumeIndex = {0, 1, checkTimeIndex};
        // 从那一列开始合并
        int mergeRowIndex = 0;

        EasyExcel.write(response.getOutputStream())
                .head(headList)
                .registerWriteHandler(getHorizontalCellStyleStrategy())
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
                //设置合并单元格策略
                .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
                .sheet("模板")
                .doWrite(lists);


        System.err.println("导出成功");
    }

导出还需要合并单元格，这里EasyExcel提供了CellWriteHandler接口,按照接口实现自己得需求.以下代码可以通用。但是在数据量特别大得时候


import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * EasyExcel合并策略
 */
public class ExcelFillCellMergeStrategy implements CellWriteHandler {


    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }


    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            for (int columnIndex : mergeColumnIndex) {
                if (curColIndex == columnIndex) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder Excel对象
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        // 如果相等合并
        Boolean bool = cell.getRow().getCell(0).toString().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).toString());
        if (dataBool && bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

然后在导出接口的时候设置一下需要合并得列，这里在贴一下主要代码

        //需要合并的列
        int[] mergeColumeIndex = {0, 1};
        // 从那一列开始合并
        int mergeRowIndex = 0;

        EasyExcel.write(response.getOutputStream())
                //设置合并单元格策略
                .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))

导出结果如下，虽然需求实现但是在导出7000+行数据得时候效率会变得特别慢，待优化得一个地方

2.png

Springboot整合EasyExcel
引入依赖案例:我一般都是CV拿一套官网的示例，然后在示例得基础上修修改改定制自己得需求。如果是基本的单表导出或...
Java一行代码读写Excel
SpringBoot整合EasyExcel Java解析、生成Excel比较有名的框架有Apache poi、jx...
springboot项目整合阿里开源框架EasyExcel，附带
本文旨在分享springboot项目整合阿里开源框架EasyExcel，附带整理好的Excel操作工具类前言：该...
37. 从零开始学springboot-集成EasyExcel读
前言小编最近使用springboot集成使用EasyExcel时发现了一个坑 EasyExcel EasyExc...
springboot整合easyexcel实现Excel导入导出
easyexcel：快速、简单避免OOM的java处理Excel工具 Java解析、生成Excel比较有名的框架有...
SpringBoot整合EasyExcel进行报表导入导出
引入为什么使用EasyExcel? Java解析、生成Excel比较有名的框架有Apache poi、jxl。但...
springboot整合springsecurity从Hello
springboot整合springsecurity系列文章：springboot整合springsecurity...
springboot整合springsecurity从Hello
springboot整合springsecurity系列文章：springboot整合springsecurity...
springboot整合springsecurity从Hello
springboot整合springsecurity系列文章：springboot整合springsecurity...
SpringBoot + Shiro 整合 JWT
SpringBoot + Shiro 整合 JWTspringboot(十四)：springboot整合shiro...