Springboot整合EasyExcel实现Excel导入导出

Spirit_NKlaus

已于 2024-04-29 11:11:28 修改

阅读量1.2k

点赞数 2

分类专栏： SpringBoot 文章标签： spring boot excel java

于 2020-12-23 20:46:00 首次发布

本文链接：https://blog.csdn.net/littlemangoyx/article/details/111519251

版权

SpringBoot 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

之前也有写过EasyExcel的文章，这次大致整合一下常用功能，并升级到了2.2.6的版本，模板实体无需再继承BaseRowModel，先构建Springboot项目再引入easyExcel相关的依赖，项目中其它的依赖就不贴出来了，主要是swagger和mybatisplus那些，之前的文章中也有写过

<!-- 引入easyExcel依赖 -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.2.6</version>
</dependency>

简单编写一个Excel的工具类EasyExcelUtil

/**
 * EasyExcel工具类
 */
public class EasyExcelUtil {

    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel 文件
     * @param rowModel 实体类映射
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, Object rowModel) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new RuntimeException("文件格式错误！");
        }
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(excel.getInputStream(),rowModel.getClass(),excelListener).doReadAll();
        return excelListener.getDatas();
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response HttpServletResponse
     * @param list 数据 list
     * @param fileName 导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object 映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<?> list, String fileName,
                                  String sheetName, Object object) {
        EasyExcel.write(getOutputStream(fileName, response),object.getClass())
                .excelType(ExcelTypeEnum.XLSX)
                .autoCloseStream(Boolean.TRUE)
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 导出 Excel 自动合并单元格
     * @param response HttpServletResponse
     * @param list 数据 list
     * @param fileName 导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object 映射实体类，Excel 模型
     * @param mergeColumnIndex 需要合并的列
     * @param mergeRowIndex 从哪一列开始合并
     */
    public static void writeMergeExcel(HttpServletResponse response, List<?> list, String fileName,
                                       String sheetName, Object object, int[] mergeColumnIndex, int mergeRowIndex) {
        EasyExcel.write(getOutputStream(fileName, response),object.getClass())
                .excelType(ExcelTypeEnum.XLSX)
                .autoCloseStream(Boolean.TRUE)
                .registerWriteHandler(new ExcelFillCellMergeHandler(mergeRowIndex,mergeColumnIndex))
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        //创建本地文件
        fileName = fileName + ".xls";

        try {
            fileName = new String(fileName.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);

            return response.getOutputStream();
        } catch (Exception e) {
            throw new RuntimeException("导出异常！");
        }
    }

}

单元格合并处理器ExcelFillCellMergeHandler

/**
 * 合并单元格处理类
 */
public class ExcelFillCellMergeHandler implements CellWriteHandler {

    //需要合并的列
    private int[] mergeColumnIndex;
    //从哪一列开始合并
    private int mergeRowIndex;

    public ExcelFillCellMergeHandler() {
    }

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

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

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

    }

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

    }

    @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 i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @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).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        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);
            }
        }
    }

}

读取Excel数据的监听器ExcelListener

/**
 * 解析监听器，
 * 每解析一行会回调invoke()方法。
 * 整个excel解析结束会执行doAfterAllAnalysed()方法
 */
public class ExcelListener extends AnalysisEventListener {

    private List<Object> datas = new ArrayList<>();

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    /**
     * 逐行解析
     * object : 当前行的数据
     */
    @Override
    public void invoke(Object object, AnalysisContext context) {
        //当前行
        // context.getCurrentRowNum()
        if (object != null) {
            datas.add(object);
        }
    }


    /**
     * 解析完所有数据后会调用该方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析结束销毁不用的资源
    }
}

准备一个实体ExcelSysUser

@ContentRowHeight(25)
@HeadRowHeight(15)
@ColumnWidth(25)
@HeadFontStyle(fontHeightInPoints=9)
@ContentStyle(horizontalAlignment= HorizontalAlignment.CENTER)
@Data
public class ExcelSysUser {

    public ExcelSysUser(){

    }

    public ExcelSysUser(String loginName, String userName, String userPwd){
        this.loginName = loginName;
        this.userName = userName;
        this.userPwd = userPwd;
    }

    @ExcelProperty(value = "登录名",index = 0)
    private String loginName;

    @ExcelProperty(value = "用户名",index = 1)
    private String userName;

    @ExcelProperty(value = "密码",index = 2)
    private String userPwd;

}

编写EasyExcelLoadsController来测试功能

@Api(tags = {"Excel导出管理"})
@RestController
@RequestMapping("/excel")
public class EasyExcelLoadsController {

    @Autowired
    private SysUserMapper sysUserMapper;

    /**
     * 用户信息导出Excel
     */
    @ApiOperation(value="用户信息导出")
    @RequestMapping(value = "/exportSysUser",method = RequestMethod.GET)
    public void exportSysUser(HttpServletResponse response){
        QueryWrapper<SysUser> queryWrapper = null;
        List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper);
        if (sysUsers != null && sysUsers.size() > 0){
            List<ExcelSysUser> excelSysUsers = sysUsers.stream().map(sysUser -> {
                ExcelSysUser excelSysUser = new ExcelSysUser();
                excelSysUser.setLoginName(sysUser.getLoginName());
                excelSysUser.setUserName(sysUser.getUserName());
                excelSysUser.setUserPwd(sysUser.getUserPwd());
                return excelSysUser;
            }).collect(Collectors.toList());
            EasyExcelUtil.writeExcel(response, excelSysUsers,"用户信息","用户信息", new ExcelSysUser());
        }

    }

    /**
     * 用户信息导出Excel(合并单元格)
     */
    @ApiOperation(value="用户信息导出(合并单元格)")
    @RequestMapping(value = "/exportMergeSysUser",method = RequestMethod.GET)
    public void exportMergeSysUser(HttpServletResponse response){
        List<ExcelSysUser> excelSysUsers = new ArrayList<>();
        ExcelSysUser excelSysUser1 = new ExcelSysUser("张三", "zhangsan002", "zsmm123");
        ExcelSysUser excelSysUser2 = new ExcelSysUser("张三", "zhangsan003", "zsmm456");
        ExcelSysUser excelSysUser3 = new ExcelSysUser("张三", "zhangsan001", "zsmm789");
        ExcelSysUser excelSysUser4 = new ExcelSysUser("李四", "zhangsan001", "zsmm123");
        ExcelSysUser excelSysUser5 = new ExcelSysUser("李四", "zhangsan001", "zsmm456");
        excelSysUsers.add(excelSysUser1);
        excelSysUsers.add(excelSysUser2);
        excelSysUsers.add(excelSysUser3);
        excelSysUsers.add(excelSysUser4);
        excelSysUsers.add(excelSysUser5);
        int[] mergeColumnIndex = {0,1};
        int mergeRowIndex = 0;
        EasyExcelUtil.writeMergeExcel(response, excelSysUsers,"用户信息","用户信息", new ExcelSysUser(), mergeColumnIndex, mergeRowIndex);

    }

    @ApiOperation(value="用户信息导入")
    @RequestMapping(value = "/importSysUser",method = RequestMethod.POST)
    public void importSysUser(MultipartFile excel){
        List<Object> dataList = null;
        try {
            dataList = EasyExcelUtil.readExcel(excel, new ExcelSysUser());
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (dataList!= null && dataList.size() > 0){
            dataList.stream().map(o -> {
                SysUser sysUser = new SysUser();
                sysUser.setLoginName(((ExcelSysUser) o).getLoginName());
                sysUser.setUserName(((ExcelSysUser) o).getUserName());
                sysUser.setUserPwd(((ExcelSysUser) o).getUserPwd());
                return sysUser;
            }).forEach(sysUser -> {
                sysUserMapper.insert(sysUser);
            });
        }

    }

}

打开swagger地址来测试调用，准备一个有两个sheet的Excel测试导入接口，导出这里演示一下合并单元格的结果

Spirit_NKlaus

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
Springboot整合EasyExcel实现Excel导入导出

引入easyExcel依赖<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version></dependency>EasyExcelUtil/** * EasyExcel工具类 * * @author: YuanXing on 2020/1.
复制链接

扫一扫