.net 导出excel_Spring Boot 2.X手册：EasyPoi导入导出Excel

easypoi 功能如同名字 easy，主打的功能就是容易，让一个没见接触过 poi 的人员可以方便地写出 Excel 导出、Excel 模板导出、Excel 导入、Word 模板导出，通过简单的注解和模板语言(熟悉的表达式语法)，完成以前复杂的写法。

EasyPoi的官网上是这样介绍的：EasyPoi的目标不是替代poi,而是让一个不懂导入导出的快速使用poi完成Excel和word的各种操作,而不是看很多api才可以完成这样工作。

用过EasyPoi的人才会体会到EasyPoi的便捷，一个Excel的导出基本上两三行代码便可以搞定，除此之外它还支持Word导出。

今天我们用EasyPoi做一个Excel的导入导出的小例子，看看EasyPoi到底怎么使用吧！

工具包导入

目前EasyPoi的最新版本是4.2.0，我在Spring Boot 项目中使用的是4.1.0，在pom文件中引入maven坐标

<dependency>    <groupId>cn.afterturngroupId>    <artifactId>easypoi-baseartifactId>    <version>4.1.0version>dependency><dependency>    <groupId>cn.afterturngroupId>    <artifactId>easypoi-webartifactId>    <version>4.1.0version>dependency><dependency>    <groupId>cn.afterturngroupId>    <artifactId>easypoi-annotationartifactId>    <version>4.1.0version>dependency>

编写导入导出工具类

package com.easypoi.em;/** * Excel枚举类型 * @author: brb * @date: 2020年8月29日 */public enum ExcelTypeEnum {   XLS("xls"), XLSX("xlsx");   private String value;   private ExcelTypeEnum(String value) {       this.value = value;   }   public String getValue() {       return value;   }   public void setValue(String value) {       this.value = value;   }}

package com.easypoi.util;import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import com.easypoi.em.ExcelTypeEnum;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Component;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.net.URLEncoder;import java.util.List;import java.util.Map;/** * Excel导出工具类 * @author brb * @date 2020年8月29日 */@Componentpublic class ExcelExportUtils {    @Autowired    private HttpServletResponse response;    /**     * 导出excel     * @param list  泛型数据     * @param pojoClass 需要导出的对象     * @param title 标题     * @param sheetName sheet的名称     * @param fileName  文件名称     * @param isCreateHeader    是否创建表头     */    public void exportExcel(List> list, Class> pojoClass, String title,                             String sheetName, String fileName, boolean isCreateHeader) throws IOException {        final ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);        exportParams.setCreateHeadRows(isCreateHeader);        baseExport(list, pojoClass, fileName, exportParams);    }    /**     * 导出Excel     * @param list  泛型数据     * @param pojoClass 需要导出的对象     * @param title 标题     * @param sheetName sheet的名称     * @param fileName  文件名称     */    public void exportExcel(List> list, Class> pojoClass, String title,                             String sheetName, String fileName) throws IOException {        baseExport(list, pojoClass, fileName, new ExportParams(title, sheetName, ExcelType.XSSF));    }    /**     * 导出Excel     * @param list  泛型数据     * @param pojoClass 需要导出的对象     * @param fileName  文件名称     * @param exportParams  导出文件属性     */    public void exportExcel(List> list, Class> pojoClass, String fileName,                            ExportParams exportParams) throws IOException {        baseExport(list, pojoClass, fileName, exportParams);    }    /**     * 多个sheet导出     * @param list     * @param fileName     */    public void exportExcel(List> list,                             String fileName) throws IOException {        baseExport(list, fileName);    }    /**     * 最基础的对象导出     * @param list  数据列表     * @param pojoClass 导出对象     * @param fileName  文件名称     * @param exportParams  导出文件属性     */    private void baseExport(List> list, Class> pojoClass,                             String fileName, ExportParams exportParams) throws IOException {        final Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);        downLoadExcel(fileName, workbook);    }    /**     * 最基础的多sheet导出     * @param list  多个不同数据对象的列表     * @param fileName  文件名称     */    private void baseExport(List> list, String fileName) throws IOException {        final Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);        downLoadExcel(fileName, workbook);    }    /**     * 文件下载     * @param fileName  文件名称     * @param workbook  exce对象     */    private void downLoadExcel(String fileName, Workbook workbook) throws IOException {        ServletOutputStream output = null;        try {            final String downloadName = URLEncoder                    .encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8");            response.setCharacterEncoding("UTF-8");            response.setHeader("content-Type", "application/vnd.ms-excel");            response.setHeader("Content-Disposition", "attachment;filename=" + downloadName);            output = response.getOutputStream();            workbook.write(output);        } catch (final Exception e) {            throw new IOException(e.getMessage());        } finally {            if (output != null) {                output.flush();                output.close();            }        }    }}

package com.easypoi.util;import cn.afterturn.easypoi.excel.ExcelImportUtil;import cn.afterturn.easypoi.excel.entity.ImportParams;import org.apache.logging.log4j.util.Strings;import org.springframework.stereotype.Component;import org.springframework.web.multipart.MultipartFile;import java.io.File;import java.io.InputStream;import java.util.List;/** * Excel导入工具类 * @author brb * @date 2020年08月29日 */@Componentpublic class ExcelImportUtils {    /**     * 从指定位置获取文件后进行导入     * @param filePath     * @param titleRows     * @param headerRows     * @param pojoClass     * @param      * @return     */    public ListimportExcel(String filePath, Integer titleRows,                                    Integer headerRows, Class> pojoClass) {        if (Strings.isEmpty(filePath)) {            return null;        } else {            final ImportParams params = new ImportParams();            //表格标题行数，默认0            params.setTitleRows(titleRows);            //表头行数，默认1            params.setHeadRows(headerRows);            //是否需要保存上传的Excel            params.setNeedSave(true);            //保存上传的Excel目录            params.setSaveUrl("/excel/");            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);        }    }    /**     * 上传文件导入     * @param file     * @param titleRows 标题行     * @param headerRows    表头行     * @param needVerfiy    是否检验excel内容     * @param pojoClass 导入的对象     * @param      * @return     * @throws Exception     */    public ListimportExcel(MultipartFile file, Integer titleRows,                                    Integer headerRows, boolean needVerfiy,                                   Class pojoClass) throws Exception {        if (file == null) {            return null;        } else {            return baseImport(file.getInputStream(), titleRows,                     headerRows, needVerfiy, pojoClass);        }    }    /**     * 最基础导入     * @param inputStream     * @param titleRows     * @param headerRows     * @param needVerfiy     * @param pojoClass     * @param      * @return     */    private ListbaseImport(InputStream inputStream,                                    Integer titleRows, Integer headerRows,                                   boolean needVerfiy, Class pojoClass) throws Exception {        if (inputStream == null) {            return null;        } else {            final ImportParams params = new ImportParams();            params.setTitleRows(titleRows);            params.setHeadRows(headerRows);            params.setSaveUrl("/excel/");            params.setNeedSave(true);            params.setNeedVerify(needVerfiy);            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);        }    }}

编写导入导出对象

package com.easypoi.domain;import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data;import java.math.BigDecimal;import java.util.Date;/** * UserDomain * @author brb * @date 2020年08月30日 */@Data@TableName(value = "e_user")public class UserDomain {    private Integer userId;    private Integer sex;    private BigDecimal money;    private String userName;    private Float price;    private Date now;}

package com.easypoi.vo;import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;import java.io.Serializable;import java.math.BigDecimal;import java.text.NumberFormat;import java.util.Date;/** * UserVo * @author brb * @date 2020年08月30日 */@Datapublic class UserVo implements Serializable {    private static final long serialVersionUID = -1509488199440369183L;    @Excel(name = "用户id", orderNum = "0", width = 15)    private String userId;    @Excel(name = "性别", orderNum = "1", width = 15, replace = {"男_1", "女_1"}, suffix = "孩")    private String sex;    @Excel(name = "金钱", orderNum = "2", width = 15)    private String money;    @Excel(name = "用户信息", orderNum = "3", width = 15)    private String userName;    @Excel(name = "价格", orderNum = "4", width = 15)    private String price;    @Excel(name = "时间", orderNum = "5", width = 15, format = "yyyy-MM-dd")    private Date now;}

编写转换工具

package com.easypoi.dozer;import org.dozer.DozerBeanMapper;import org.springframework.stereotype.Component;import java.util.ArrayList;import java.util.List;/** * vo与domain互转工具类 * @author brb * @date 2020年08月30日 */@Componentpublic class DozerBeanMapperWrapper {    private static DozerBeanMapper dozerBeanMappee = new DozerBeanMapper();    public T mapper(Object source, Class destinationClass) {        return dozerBeanMappee.map(source, destinationClass);    }    public void mapper(Object source, Object destinationSource) {        dozerBeanMappee.map(source, destinationSource);    }    public ListmapperList(List> sources, Class destinationClass) {        List targetList = new ArrayList();        for (Object source : sources) {            targetList.add(dozerBeanMappee.map(source, destinationClass));        }        return targetList;    }}

编写测试方法

package com.easypoi.controller;import com.easypoi.domain.UserDomain;import com.easypoi.dozer.DozerBeanMapperWrapper;import com.easypoi.service.UserService;import com.easypoi.util.ExcelExportUtils;import com.easypoi.util.ExcelImportUtils;import com.easypoi.vo.UserVo;import io.swagger.annotations.Api;import io.swagger.annotations.ApiOperation;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;import java.io.IOException;import java.util.List;/** * excel导出导出 * @author brb * @date 2020年08月30日 */@Api(tags = { "APP服务：数据接口"})@RestController@RequestMapping("view/ie")public class ImportExportController {    @Autowired    private ExcelExportUtils excelExportUtils;    @Autowired    private ExcelImportUtils excelImportUtils;    @Autowired    private DozerBeanMapperWrapper dozerBeanMapper;    @Autowired    private UserService userService;    /**     * 导出用户信息     * @throws IOException     */    @ApiOperation(value = "导出excel")    @GetMapping(value = "/exportExcel")    public void exportExcel() throws IOException {        //NumberFormat nf = NumberFormat.getNumberInstance()        final List userDomainList = userService.list();        final List userList = dozerBeanMapper.mapperList(userDomainList, UserVo.class);        excelExportUtils.exportExcel(userList, UserVo.class, "用户信息", "员工信息的sheet", "用户信息表");    }    /**     * 导入用户信息     * @param file     * @return     */    @ApiOperation(value = "导入excel")    @GetMapping(value = "/importExcel")    public void importExcel(@RequestParam("file") MultipartFile file) throws Exception {        final List userList = excelImportUtils.importExcel(file, 1, 1, false, UserVo.class);        final List userDomainList = dozerBeanMapper.mapperList(userList, UserDomain.class);        userService.saveBatch(userDomainList);        System.out.println(userList.toString());        System.out.println("----------");        System.out.println(userDomainList);    }}

package com.easypoi.service.impl;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import com.easypoi.mapper.UserMapper;import com.easypoi.domain.UserDomain;import com.easypoi.service.UserService;import org.springframework.stereotype.Service;/** * UserServiceImpl * @author brb * @date 2020年08月30日 */@Servicepublic class UserServiceImpl extends ServiceImpl implements UserService {}

package com.easypoi.service;import com.baomidou.mybatisplus.extension.service.IService;import com.easypoi.domain.UserDomain;import org.springframework.stereotype.Service;/** * UserService * @author brb * @date 2020年08月30日 */@Servicepublic interface UserService extends IService {}

package com.easypoi.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.easypoi.domain.UserDomain;/** * UserMapper * @author brb * @date 2020年08月30日 */public interface UserMapper extends BaseMapper<UserDomain> {}

导出结果

导出接口测试，直接在浏览器输入地址接口，结果如截图所示：

导入结果

把刚刚导出的文件，直接导入进去，这里采用postMan操作，其中红色的圈圈标出来是需要注意的点(导入之前先把数据库中的数据删除)

扫码二维码

获取更多精彩

90后程序猿

具体的代码我已经放在GitHub上，大家可以关注公众号回复EasyPoi获取代码。