EasyPoi进行导入和导出

之前也整理过一些EasyExcel的导入导出代码，感觉和EasyPoi 比起来的话各有优缺点吧。
每次写EasyExcel 都要写一个Listener，EasyPoi简单的导入导出更方便些。
今天这篇文章主要是整理EasyPoi进行导入和导出。

下面是代码简要示例：
代码当中使用了swagger的一些注解。对你没有帮助的话，可以删掉。

1 依赖

    <!--EasyPoi-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

2 controller

 @PostMapping("/EasyPoiExport.do")
    @ApiOperation(value = "导出")
    public void export(HttpServletResponse response) throws IOException {
        IPage<Employee> page = employeeService.selectListByPage(1, 10);
        List<Employee> records = page.getRecords();
        String name = "雇员列表";
        ExcelPoiUtils.exportExcel(records, name, name, Employee.class, name, response);
    }

    @PostMapping("/EasyPoiImport.do")
    @ApiOperation(value = "导入")
    public void EasyPoiImport(MultipartFile file) throws IOException {

        ExcelImportResult<Employee> requestList = ExcelPoiUtils.importExcelMore(file.getInputStream(), 0, 1, Employee.class);
        List<Employee> employeeList = requestList.getList();
        employeeList.forEach( p-> employeeService.save(p));
    }

3 model

@Data
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
@TableName("employee")
@ApiModel(value="Employee对象", description="雇员信息")
public class Employee implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "employee_id", type = IdType.AUTO)
    @Excel(name = "唯一号",  width = 18)
    private Integer employeeId;

    @Excel(name = "状态",  width = 18, replace = {"入职中_1", "在职_2", "离职中_3", "离职_4", "_null"})
    private Integer employeeStatus;

    @Excel(name = "雇员姓名", width = 18)
    private String employeeName;

    @Excel(name = "证件类型", width = 18, replace = {"身份证_1", "中国护照_2", "军人证_3", "港澳通行证_4", "台胞证_5", "护照_6",
            "回乡证_7", "其他_8", "_null"})
    private Integer cardType;

    @Excel(name = "证件号", width = 24)
    private String cardId;

}

4 工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author hanmw
 */
public class ExcelPoiUtils
{

    /**
     * 默认的 excel 导出
     *
     * @param list     数据列表
     * @param fileName 导出时的excel名称
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
        throws IOException
    {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param response
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response
        , ExportParams exportParams)
        throws IOException
    {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param title     表格内数据标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
        HttpServletResponse response)
        throws IOException
    {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }


    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
        throws IOException
    {
        try
        {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx",
                "UTF-8"));
            workbook.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   表格内数据标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> ExcelImportResult<T> importExcelMore(InputStream inputStream, Integer titleRows,
        Integer headerRows,
        Class<T> pojoClass)
        throws IOException
    {
        if (inputStream == null)
        {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedVerify(true);
        try
        {
            return ExcelImportUtil.importExcelMore(inputStream, pojoClass, params);
        }
        catch (NoSuchElementException e)
        {
            throw new IOException("excel文件不能为空");
        }
        catch (Exception e)
        {
            throw new IOException(e.getMessage());
        }
    }

}

5 数据库表结构

CREATE TABLE `employee` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_status` int(11) DEFAULT NULL,
  `employee_name` varchar(50) DEFAULT NULL,
  `card_type` int(11) DEFAULT NULL,
  `card_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`employee_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

6 导出

7 导入

导入这里要区分两个概念。
titleRows：标题行数
headerRows：表头行数。

第一种情况：
没有标题。titleRows值为0。

第二种情况：
有标题。titleRows值为1。