Excel导入导出利器——easy-poi

郭意

2023-12-01

文档说明参考：http://easypoi.mydoc.io/#text_217704
easy-poi是在传统poi基础上进行了封装和简化，非常易于上手。

一、导入pom

easy-poi依赖apach的commons-collections4

<!--easy-poi-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.0.3</version>
</dependency>

<!--common-collections4-->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>

二、对需要进行导入导出的POJO类进行注解

注解就是@Excel，可以设置很多值，注意：必须有无参构造函数

@Data
@NoConstucotr
public class Goods {
    /**
     * 主键
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    /**
     * 状态：0-无效 1-有效
     */
    @Excel(name = "状态", orderNum = "0", width = 10)
    private Integer status;

    /**
     * 商品码code
     */
    @Excel(name = "商品code码", orderNum = "1", width = 20)
    private String goodCode;

    /**
     * 商品名称
     */
    @Excel(name = "商品名称", orderNum = "3", width = 20)
    private String goodName;
}

属性	类型	默认值	说明
name	String	null	列名
needMerge	Boolean	false	纵向合并单元格
orderNum	String	“0”	列的排序,支持name_id
replace	String[]	{}	值的替换导出是{a_id,b_id} 导入反过来
savePath	String	“upload”	导入文件保存路径
type	int	1	导出类型 1 是文本 2 是图片,3 是函数,10 是数字默认是文本
width	double	10	列宽
height	double	10	列高
isStatistics	boolean	fasle	自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点
isHyperlink	boolean	false	超链接,如果是需要实现接口返回对象
isImportField	boolean	true	列校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id
exportFormat	String	“”	导出的时间格式,以这个是否为空来判断是否需要格式化日期
importFormat	String	“”	导入的时间格式,以这个是否为空来判断是否需要格式化日期
format	String	“”	时间格式,相当于同时设置了exportFormat 和 importFormat
databaseFormat	String	“yyyyMMddHHmmss”	导出时间设置,如果字段是Date类型则不需要设置数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出
numFormat	String	“”	数字格式化,参数是Pattern,使用的对象是DecimalFormat
imageType	int	1	导出类型 1 从file读取 2 是从数据库中读取默认是文件同样导入也是一样的
suffix	String	“”	文字后缀,如% 90 变成90%
isWrap	boolean	true	是否换行即支持\n
mergeRely	int[]	{}	合并单元格依赖关系,比如第二列合并是基于第一列则{1}就可以了
mergeVertical	boolean	false	纵向合并内容相同的单元格

三、通用工具类

直接copy即可

/**
 * EXCEL工具类
 */
@Slf4j
public class ExcelUtil {

    /**
     * 下载模板文件:需要兼容浏览器
     */
    public static void downFile(String path, String filename, HttpServletRequest request,
                                HttpServletResponse response) {

        InputStream fis = null;
        OutputStream os = null;
        try {
            File file = new File(path);// path
            String agent = request.getHeader("User-Agent").toUpperCase();
            if (agent.contains(SymbolConstants.FIREFOX)) {
                filename = new String(filename.getBytes(), StandardCharsets.ISO_8859_1);// firefox浏览器
            } else if (agent.contains(SymbolConstants.MSIE)) {
                filename = URLEncoder.encode(filename, "UTF-8");// IE浏览器
            } else if (agent.contains(SymbolConstants.CHROME)) {
                filename = URLEncoder.encode(filename, "UTF-8");// chrome谷歌
            } else {
                filename = URLEncoder.encode(filename, "UTF-8");// 其他（包括360）
            }
            response.reset();
            // 先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
            response.addHeader("Content-Disposition",
                    "attachment;filename=" + new String(filename.replaceAll(" ", "").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            response.addHeader("Content-Length", "" + file.length());
            response.setContentType("application/octet-stream");

            //文件流处理
            fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[5];
            int hasRead;
            os = new BufferedOutputStream(response.getOutputStream());
            while ((hasRead = fis.read(buffer)) != -1){
                os.write(buffer,0, hasRead);
            }
            os.flush();
            os.close();
            fis.close();
        } catch (Exception e) {
            log.error("下载模板文件异常！", e);
        } finally {
            if (os != null) {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    log.error("OutputStream输出流关闭异常!", e);
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    log.error("InputStream输入流关闭异常!", e);
                }
            }
        }
    }

    /**
     * list集合导出
     * @param isCreateHeader 是否创建头
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * list导出excel
     * @param list 导出对象的list
     * @param title 表格内的头标题（自动合并单元格），如果为null则不生成
     * @param sheetName 工作簿名称，可以为null
     * @param pojoClass list对象
     * @param fileName 文件名称，必须有".xls"文件格式
     * @param response http的response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(); //控制导出的样式等等ExportParams
        exportParams.setTitle(title);
        exportParams.setSheetName(sheetName);
        exportParams.setStyle(ExcelExportStylerImpl.class); //将自定义的样式导入
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * map集合导出
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    /**
     * 默认导出样式
     * @param exportParams 可以自定义导出样式
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载excel表格：与downFile方法一样，没有做浏览器兼容给处理
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.error("easyPoi-现在加载excel异常!", e);
        }
    }

    /**
     * map集合默认导出
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 导入文件
     * @param filePath 文件路径
     * @param titleRows 表格标题所占行数
     * @param headerRows  表头所占行数
     * @param pojoClass 类型
     * @param <T> 泛型
     * @return 导入后的结果
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            log.error("easyPoi-导入excel异常!模板不能为空", e);
        } catch (Exception e) {
            log.error("easyPoi-导入excel异常!", e);
        }
        return list;
    }

    /**
     * 导入文件
     * @param file 上传的文件
     * @param titleRows 表格标题所占行数
     * @param headerRows  表头所占行数
     * @param pojoClass 类型
     * @param <T> 泛型
     * @return 导入后的结果
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows); //控制导入的行数、工作簿等等ImportParams
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            log.error("easyPoi-导入excel异常!excel文件不能为空", e);
        } catch (Exception e) {
            log.error("easyPoi-导入excel异常!", e);
        }
        return list;
    }

    /**
     * 大数量导出，最大支持一百万
     * @param entity
     *            表格标题属性
     * @param pojoClass
     *            Excel对象Class
     * @param dataSet
     *            Excel对象数据List
     */
    public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
                                          Collection<?> dataSet) {
        ExcelBatchExportServer batachServer = ExcelBatchExportServer
                .getExcelBatchExportServer(entity, pojoClass);
        return batachServer.appendData(dataSet);
    }

    /**
     * 关闭大数量导出
     */
    public static void closeExportBigExcel() {
        ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null,
                null);
        batachServer.closeExportBigExcel();
    }
}

四、导出部分

1、自定义导出样式

需要继承抽象类AbstractExcelExportStyler，并实现一个接口IExcelExportStyler，重写相关方法

public class ExcelExportStylerImpl extends AbstractExcelExportStyler implements IExcelExportStyler {
    public ExcelExportStylerImpl(Workbook workbook) {
        super.createStyles(workbook);
    }

    /**
     * 设置标题样式
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = this.workbook.createFont();
        font.setBold(true); // 字体加粗
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);//居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        titleStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());//设置颜色（黑色）
        titleStyle.setBorderRight(BorderStyle.THIN);//设置右边框
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    /**
     * 设置主标题
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        font.setBold(true); // 字体加粗
        titleStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());//设置颜色（黑色）
        titleStyle.setBorderBottom(BorderStyle.THIN); //设置下边框
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return titleStyle;
    }

    /**
     * 正文样式
     */
    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    /**
     * 空格样式
     */
    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }
}

2、百万大数据量导出处理

需要使用ExcelUtil工具类中的exportBigExcel和closeExportBigExcel

/**
 * 大数据量处理（最大支持一百万）
 */
private void exportBigData(List<Goods> goods, HttpServletResponse response) {

    //调用easy-poi导出数据
    ExportParams exportParams = new ExportParams();
    exportParams.setTitle("商品表");
    exportParams.setSheetName(null);
    exportParams.setStyle(ExcelExportStylerImpl.class); //将自定义的样式导入
    
    //临时list
    List<Goods> copyList = Lists.newArrayList();
    Workbook workbook = null;
    for (Goods good : goods) {
        copyList.add(good);
        
        //每一万条追加一次
        if (copyList.size() == 10000) {
            workbook = ExcelUtil.exportBigExcel(exportParams, Goods.class, copyList);
            copyList.clear();//清空
        }
    }
    ExcelExportUtil.closeExportBigExcel();
    ExcelUtil.downLoadExcel("商品表" + DateUtil.dateToString(new Date(), "yyyyMMddHHmmss") + ".xlsx", response, workbook);
}

五、导入部分

导入部分非常简单，直接调用ExcelUtil的importExcel方法即可，需要注意的是titleRows指的是标题所占行数，headerRows指的是表头所占行数。
导入和导出都使用ImportParams和ExportParams进行控制，其他参数如下：

属性	类型	默认值	说明
titleRows	int	0	标题所占行数
headRows	int	1	表头所占行数
startRows	int	0	字段真正值和列标题之间的距离默认0
keyIndex	int	0	主键设置,如何这个cell没有值,就跳过或者认为这个是list的下面的值，这一列必须有值,不然认为这列为无效数据
startSheetIndex	int	0	开始读取的sheet位置,默认为0
sheetNum	int	1	上传表格需要读取的sheet 数量,默认为1
needSave	boolean	false	是否需要保存上传的Excel
needVerfiy	boolean	false	是否需要校验上传的Excel
saveUrl	String	“upload/excelUpload”	保存上传的Excel目录,默认是如 TestEntity这个类保存路径就是:upload/excelUpload/Test/yyyyMMddHHmss* 保存名称上传时间五位随机数
verifyHanlder	IExcelVerifyHandler	null	校验处理接口,自定义校验
lastOfInvalidRow	int	0	最后的无效行数,不读的行数
readRows	int	0	手动控制读取的行数
importFields	String[]	null	导入时校验数据模板,是不是正确的Excel
keyMark	String	“:”	Key-Value 读取标记,以这个为Key,后面一个Cell 为Value,多个改为ArrayList
readSingleCell	boolean	false	按照Key-Value 规则读取全局扫描Excel,但是跳过List读取范围提升性能，仅仅支持titleRows + headRows + startRows 以及 lastOfInvalidRow
dataHanlder	IExcelDataHandler	null	数据处理接口,以此为主,replace,format都在这后面

Excel导入导出利器——easy-poi

一、导入pom

二、对需要进行导入导出的POJO类进行注解

三、通用工具类

四、导出部分

1、自定义导出样式

2、百万大数据量导出处理

五、导入部分

相关阅读

相关文章

相关问答

相关文档