原 easyexcel（十二）：easyexcel工具类

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1320

背景：之前有个项目用easyexcel做了导入，导出的功能，最近有同事也在用easyexcel，刚好最近有时间，就想把一些常用的功能封装起来，方便调用，虽然easyexcel已经让代码很简洁了，但是对于初学者来使用还是有点费劲，加上官网的文档写的还不够详细和友好，那就让我来做一个活雷锋。

一：公用的代码。

1.1）引入pom

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.5</version>
</dependency>

1.2）实体类

package com.mszl.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class UserInfo {

    @ExcelProperty("姓名")  // 表头
    private String name;

    @ExcelProperty("手机号")
    private String phone;

    @ExcelProperty("年龄")
    private int age;

}

1.3）模拟查询数据库的list

public List<UserInfo> selectUserInfo(){
        List<UserInfo> userList=new ArrayList<>();
        UserInfo u1=new UserInfo();
        u1.setName("张三");
        u1.setAge(20);
        u1.setPhone("13211111111");

        UserInfo u2=new UserInfo();
        u2.setName("李四");
        u2.setAge(25);
        u2.setPhone("13255555555");

        userList.add(u1);
        userList.add(u2);
        return userList;
}

1.4）EasyExcelUtils工具类

package com.mszl.utils;

import cn.hutool.core.io.FileTypeUtil;
import cn.hutool.core.io.FileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.gaia.ksf.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * 功能：easyexcel 工具类
 * 作者：zxb
 * 官网：https://www.91mszl.com
 * easyexcel官网地址：
 *      https://yuque.com/easyexcel
 *      https://github.com/alibaba/easyexcel
 */
public class EasyExcelUtils {

    /**
     * 功能：上传文件
     * 参数：
     *      1. file 上传文件需要
     *      2. filePath 上传目标文件夹, 如：E:/data/uploadExcel
     * 作者：zxb
     */
    public static void uploadFile(MultipartFile file, String filePath) throws IOException {
        String fileName=file.getOriginalFilename();
        String fullPath=fileUpload(fileName, filePath, file); // 调用上传文件的方法
        // 判断上传的文件是否为excel
        File ff=FileUtil.file(fullPath);
        String fileType=FileTypeUtil.getType(ff);
        if(!fileType.equals(BusinessUtils.EXCEL_XLS) && !fileType.equals(BusinessUtils.EXCEL_XLSX) && !fileType.equals(BusinessUtils.EXCEL_XLS_CAPITAL) && !fileType.equals(BusinessUtils.EXCEL_XLSX_CAPITAL)){
            deleteUploadFile(fullPath); // 调用删除附件的方法[当上传的文件不是excel时, 把上传的附件进行删除]
            throw new BusinessException(BusinessUtils.CODE_201, BusinessUtils.ISEXCEL_FILE);
        }
    }

    /**
     * 功能：导出单个sheet（实体对象）
     * 参数：
     *      1. response
     *      2. dataList 要导出的list数据
     *      3. clazz 定义的实体对象和excel的列名对应
     *      4. fileName 文件名称, 如：123.xlsx
     *      5. sheetName sheet的名称
     * 备注：
     *      1. 文件下载（失败了会返回一个有部分数据的Excel）
     *      2. 这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     * 作者：zxb
     */
    public static <T> void singleSheet(HttpServletResponse response, List<T> dataList, Class<T> clazz, String fileName, String sheetName) throws IOException {
        OutputStream os=responseInfo(response, fileName); // 调用responseInfo方法
        EasyExcel.write(os, clazz).sheet(sheetName).doWrite(dataList);
    }

    /**
     * 功能：导出单个sheet（动态表头）
     * 参数：
     *      1. response
     *      2. dataList 要导出的list数据
     *      3. clazz 定义的实体对象和excel的列名对应
     *      4. fileName 文件名称, 如：123.xlsx
     *      5. sheetName sheet的名称
     * 备注：
     *      1. 文件下载（失败了会返回一个有部分数据的Excel）
     *      2. 这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     * 作者：zxb
     */
    public static <T> void dynamicHeadSingleSheet(HttpServletResponse response, List<T> dataList, List<List<String>> headList, String fileName, String sheetName) throws IOException {
        OutputStream os=responseInfo(response, fileName); // 调用responseInfo方法
        EasyExcel.write(os).head(headList).sheet(sheetName).doWrite(dataList);
    }

    /**
     * 功能：导出单个sheet（动态表头）（自定义列宽）
     * 参数：
     *      1. response
     *      2. dataList 要导出的list数据
     *      3. headList 表头list
     *      4. fileName 文件名称, 如：123.xlsx
     *      5. sheetName sheet的名称
     */
    public static <T> void dynamicHeadCustomizeColumnWidthSingleSheet(HttpServletResponse response, List<T> dataList, List<List<String>> headList, String fileName, String sheetName) throws IOException {
        OutputStream os=responseInfo(response, fileName); // 调用responseInfo方法
        EasyExcel.write(os).head(headList).sheet(sheetName).registerWriteHandler(new CustomizeColumnWidth()).doWrite(dataList);
    }

    /**
     * 功能：导出单个sheet（动态表头）（（自定义样式）
     * 参数：
     *      1. response
     *      2. dataList 要导出的list数据
     *      3. headList 表头list
     *      4. fileName 文件名称, 如：123.xlsx
     *      5. sheetName sheet的名称
     */
    public static <T> void dynamicHeadCustomizeStyleSingleSheet(HttpServletResponse response, List<T> dataList, List<List<String>> headList, String fileName, String sheetName) throws IOException {
        OutputStream os=responseInfo(response, fileName); // 调用responseInfo方法
        EasyExcel.write(os).head(headList).sheet(sheetName).registerWriteHandler(setStyleStrategy()).doWrite(dataList);
        // EasyExcel.write(os).head(headList).sheet(sheetName).registerWriteHandler(new CustomizeColumnWidth()).registerWriteHandler(setStyleStrategy()).doWrite(dataList); // 可以同时设置列宽和样式
    }

    /**
     * 功能：导出[多个sheet]
     * 参数：
     *      1. response
     *      2. dataList 要导出的list数据
     *      3. clazz 定义的实体对象和excel的列名对应
     *      4. fileName 文件名称, 如：123.xlsx
     *      5. sheetNo 多个个sheet
     *      6. sheetNameList sheet的名称list
     */
    public static <T> void multipleSheet(HttpServletResponse response, List<T> dataList, Class<T> clazz, String fileName, int sheetNo, List<String> sheetNameList) throws IOException {
        OutputStream os=responseInfo(response, fileName); // 调用responseInfo方法
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(os, clazz).build(); // 这里指定文件
            for (int i = 0; i < sheetNo; i++) { // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetNameList.get(i)).build(); // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                excelWriter.write(dataList, writeSheet); // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            }
        } finally {
            if (excelWriter != null) { // 千万别忘记finish 会帮忙关闭流
                excelWriter.finish();
            }
        }
    }

    /**
     * 功能：设置表格样式[对齐方式, 背景色, 字体, 换行, 边框等]
     */
    public static HorizontalCellStyleStrategy setStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 背景色, 设置为白色，也是默认颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 背景绿色
        //contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置 水平居中
        // contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

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

    /**
     * 功能：公用方法
     * 参数：fileName 文件名称, 如：123.xlsx
     */
    public static OutputStream responseInfo(HttpServletResponse response, String fileName) throws IOException {
        // 这里注意有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment; filename*=utf-8''" + fileName);
        OutputStream os=response.getOutputStream();
        return os;
    }

    /**
     * 功能：上传文件，并创建文件夹
     * 参数：
     *      1. fileName 文件名称, 如：123.xlsx
     *      2. excelUrl 上传的文件路径, 如：/opt/excel
     *      3. file 文件上传需要指定的file
     */
    public static String fileUpload(String fileName, String excelUrl, MultipartFile file) throws IOException {
        // 文件夹和文件名称
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
        Date dt=new Date();
        String dirName=sf.format(dt);
        int index = fileName.lastIndexOf(".");
        String fileSuffix = fileName.substring(index);
        int randomEight = (int)((Math.random()*9+1)*10000000);
        long currentTime = System.currentTimeMillis();
        String newFileNamePrefix = String.valueOf(currentTime) + String.valueOf(randomEight);
        String newFileName=newFileNamePrefix+fileSuffix;
        String filePath = excelUrl + File.separator + dirName;
        String fullPath = filePath + File.separator + newFileName;
        File fe = new File(fullPath);
        File uploadFile = new File(filePath);
        if (!uploadFile.exists()) {
            uploadFile.mkdirs();
        }
        file.transferTo(fe);
        return fullPath;
    }

    /**
     * 功能：删除附件
     * 参数：文件路径
     */
    public static void deleteUploadFile(String filePath){
        if (StringUtils.isNotBlank(filePath)) {
            String[] split=filePath.split(",");
            for (String str : split) {
                File fe=new File(str);
                if(fe.exists()){
                    boolean result=fe.delete();
                    if(result){
                        // 删除成功
                    }
                } else{
                    // 文件不存在
                }
            }
        } else{
            // 文件路径不能为空
        }
    }


}

二：导出单个sheet

2.1）导出单个sheet（有实体对象）即我们这里的UserInfo对象，默认列高和行高。

public void testExcel(HttpServletResponse response) throws IOException {
        String fileName=System.currentTimeMillis() + ".xlsx"; // excel文件名称
        String sheetName="用户信息"; // sheet名字
        List<UserInfo> dataList=this.selectUserInfo(); // 模拟数据库查询
        EasyExcelUtils.singleSheet(response, dataList, UserInfo.class, fileName, sheetName);
}

效果图：

2.2）导出单个sheet（有实体对象）即我们这里的UserInfo对象，自定义列宽和表头行高，内容行高。

代码如下：

@Data
@HeadRowHeight(40) // 表头的行高
@ContentRowHeight(30) // 内容的行高
public class UserInfo {

    @ColumnWidth(15) // 设置列宽
    @ExcelProperty("姓名")  // 表头
    private String name;

    @ColumnWidth(20) // 设置列宽
    @ExcelProperty("手机号")
    private String phone;

    @ColumnWidth(25) // 设置列宽
    @ExcelProperty("年龄")
    private int age;

}

效果图：

2.3）导出单个sheet（动态表头【无实体对象】），默认行高和列高。

public void testExcel(HttpServletResponse response) throws IOException {
        String fileName=System.currentTimeMillis() + ".xlsx"; // excel文件名称
        String sheetName="用户信息"; // sheet名字
        List<UserInfo> dataList=this.selectUserInfo(); // 模拟数据库查询

        // 动态表头
        List<List<String>> headList=new ArrayList<List<String>>();
        headList.add(Lists.newArrayList("姓名"));
        headList.add(Lists.newArrayList("手机号"));
        headList.add(Lists.newArrayList("年龄"));
        EasyExcelUtils.dynamicHeadSingleSheet(response, dataList, headList, fileName, sheetName);
}

注意：Lists.newArrayList() 是com.google.common.collect.Lists包下面的。

效果图：

2.4）导出单个sheet（动态表头【无实体对象】），自定义列宽。

public void testExcel(HttpServletResponse response) throws IOException {
        String fileName=System.currentTimeMillis() + ".xlsx"; // excel文件名称
        String sheetName="用户信息"; // sheet名字
        List<UserInfo> dataList=this.selectUserInfo(); // 模拟数据库查询

        // 动态表头
        List<List<String>> headList=new ArrayList<List<String>>();
        headList.add(Lists.newArrayList("姓名"));
        headList.add(Lists.newArrayList("手机号"));
        headList.add(Lists.newArrayList("年龄"));
        EasyExcelUtils.dynamicHeadCustomizeColumnWidthSingleSheet(response, dataList, headList, fileName, sheetName);
}

excel在写的时候多了一个registerWriteHandler方法，CustomizeColumnWidth()为自定义的类

EasyExcel.write(os).head(headList).sheet(sheetName).registerWriteHandler(new CustomizeColumnWidth()).doWrite(dataList);

CustomizeColumnWidth 自定义列宽的代码如下：

package com.mszl.utils;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.List;

/**
 * 功能：设置表头的列宽【自定义表头的方式】
 */
public class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {

    /**
     * 功能：自定义列宽
     */
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
        if (isHead && cell.getRowIndex() == 0) { // isHead=true表示为表头，如果表头只有1行这里设置为0
            int columnWidth = cell.getStringCellValue().getBytes().length;
            int cellIndex = cell.getColumnIndex();
            switch (cellIndex) { // 表头第1, 3列的宽度为10
                case 0:
                case 2:
                    columnWidth = 10;
                    break;
                case 1:
                    columnWidth = 20;
                    break;
                default:
                    break;
            }

            if (columnWidth > 255) {
                columnWidth = 255;
            }
            writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
        }
    }


}

效果图：

2.5）设置excel表格样式，如：对齐方式, 背景色, 字体, 换行, 边框等

public void testExcel(HttpServletResponse response) throws IOException {
        String fileName=System.currentTimeMillis() + ".xlsx"; // excel文件名称
        String sheetName="用户信息"; // sheet名字
        List<UserInfo> dataList=this.selectUserInfo(); // 模拟数据库查询

        // 动态表头
        List<List<String>> headList=new ArrayList<List<String>>();
        headList.add(Lists.newArrayList("姓名"));
        headList.add(Lists.newArrayList("手机号"));
        headList.add(Lists.newArrayList("年龄"));
        EasyExcelUtils.dynamicHeadCustomizeStyleSingleSheet(response, dataList, headList, fileName, sheetName);
}

excel在写的时候多了一个registerWriteHandler方法，setStyleStrategy()为自定义样式方法，具体的样式根据自己的需求来设置

EasyExcel.write(os).head(headList).sheet(sheetName).registerWriteHandler(setStyleStrategy()).doWrite(dataList);

效果图：

备注：你还可以同时设置样式和列宽，只需要连续写多个registerWriteHandler即可，代码如下：

EasyExcel.write(os).head(headList).sheet(sheetName).registerWriteHandler(new CustomizeColumnWidth()).registerWriteHandler(setStyleStrategy()).doWrite(dataList);

效果图：

三：导出多个sheet

public void testExcel(HttpServletResponse response) throws IOException {
        String fileName=System.currentTimeMillis() + ".xlsx"; // excel文件名称
        int sheetNo=3; // sheet的数量
        List<String> sheetNameList=Arrays.asList("用户信息", "人员信息", "角色信息"); // sheet名称
        List<UserInfo> dataList=this.selectUserInfo(); // 模拟数据库查询
        EasyExcelUtils.multipleSheet(response, dataList, UserInfo.class, fileName, sheetNo, sheetNameList);
}

效果图：

备注：

2）如果你需要设置行高，列宽请参考2.2和2.4章节。