java导入导出excel 用easypoi 太简单了，简单的让我感动的热泪盈眶，因为你让提高了效率让我早点下班

N多年用的poi导入导出实在写太多的代码，挺麻烦，现在有了这个 easypoi 这个小可爱，事情就变的如此简单了。

pom.xml导入包

<dependency>
            <groupId>cn.afterturngroupId>
            <artifactId>easypoi-spring-boot-starterartifactId>
            <version>4.2.0version>
        dependency>

配置实体类

package com.giikin.ads.fb.adasset.entity.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.checkerframework.checker.i18nformatter.qual.I18nFormat;
import org.springframework.util.StringUtils;

import javax.validation.constraints.Min;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Objects;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget(value = "BatchDto")
public class FbBatchDto {
    @Excel(name = "平台*" , orderNum = "0" , width = 20.0)
    private String platform;
    @Excel(name = "开户批次号*" , orderNum = "1" ,width = 20.0)
    private String batchNum;
    @Excel(name = "申请时间*" , orderNum = "2" , format = "yyyy/MM/dd" , width = 20.0)
    private LocalDateTime applyTime;
    @Excel(name = "开户优化组长ID*" ,orderNum = "3" , width = 20.0)
    private Integer optLeaderId;//优化组长
    @Excel(name = "开户人ID*" ,orderNum = "4" , width = 20.0)
    private Integer creatorId;//创建人
   
}

excel导入代码 excel直接导入成了java对象 放到了list里面

MultipartFile file //上传过来的文件对象

ImportParams params = new ImportParams();
params.setTitleRows(0);
params.setHeadRows(1);
params.setSheetNum(i);
List batchs = ExcelImportUtil.importExcel(file.getInputStream(), FbBatchDto.class, params);

excel导出代码

ExportParams params = new ExportParams();
        params.setSheetName("无运营");
        Workbook workbook = ExcelExportUtil.exportExcel(params, PageComparisonResult.class, Collections.emptySet());
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        byte[] bytes;
        try {
            workbook.write(outputStream);
            bytes = outputStream.toByteArray();
        } catch (IOException e) {
            bytes = new byte[2];
        }

另外赠送一下相关工具类

package com.giikin.ads.fb.util;

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 org.apache.poi.hssf.usermodel.HSSFWorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

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

public class EasyPoiUtil {

    /**
     * 导出Excel,包括文件名以及表名，是否创建表头
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param fileName 文件名成
     * @param isCreateHeader 是否创建表头
     * @param response 响应对象
     */
    public static void exportExcel(List<?> list , String title , String sheetName , Class<?> pojoClass , String fileName , boolean isCreateHeader , HttpServletResponse response){
        ExportParams params = new ExportParams(title , sheetName);
        params.setCreateHeadRows(isCreateHeader);
        defaultExport(list ,pojoClass , fileName , response , params);
    }

    /**
     * 导出Excel，默认创建表头
     * @param list
     * @param title
     * @param sheetName
     * @param pojoClass
     * @param fileName
     * @param response
     */
    public static void exportExcel(List<?> list , String title ,String sheetName , Class<?> pojoClass , String fileName , HttpServletResponse response){
        defaultExport(list , pojoClass , fileName , response , new ExportParams(title , sheetName));
    }

    /**
     * map多sheet形式导出
     * @param list
     * @param fileName
     * @param response
     */
    public static void exportExcel(List> list , String fileName , HttpServletResponse response){
        defaultExport(list , fileName , response);
    }

    /**
     * 常规默认导出方式
     * @param list
     * @param pojoClass
     * @param fileName
     * @param response
     * @param params
     */
    private static void defaultExport(List<?> list , Class<?> pojoClass , String fileName , HttpServletResponse response , ExportParams params){
        Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, list);
        if (workbook != null)downLoadExcel(fileName , response , workbook);
    }

    /**
     * 多sheet默认导出方式
     * @param list
     * @param fileName
     * @param response
     */
    private static void defaultExport(List> list , String fileName , HttpServletResponse response){
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null)downLoadExcel(fileName , response , workbook);
    }

    private 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) {
            e.printStackTrace();
        }
    }

    /**
     * 通过文件路径形式导入
     * @param filePath
     * @param titleRows
     * @param headerRows
     * @param pojoClass
     * @param 
     * @return
     */
    public static  List importExcel(String filePath , Integer titleRows , Integer headerRows , Class pojoClass){
        if (!StringUtils.hasText(filePath))return null;
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("模板不能为空");
        } catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }

    /**
     * 通过流的形式导入
     * @param file
     * @param titleRows
     * @param headerRows
     * @param pojoClass
     * @param 
     * @return
     */
    public static  List importExcel(MultipartFile file , Integer titleRows , Integer headerRows , Class pojoClass){
        if (file == null)return null;
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (Exception e) {
            throw new RuntimeException("文件导入出错");
        }
        return list;
    }

    /**
     * 得到workbook对象
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        return HSSFWorkbookFactory.create(inputStream);
    }


}