Java使用easypoi快速导入导出的实现

    
    
      cn.afterturn
      easypoi-base
      3.0.3
    
    
      cn.afterturn
      easypoi-web
      3.0.3
    
    
      cn.afterturn
      easypoi-annotation
      3.0.3
    

package cn.common.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 cn.common.exception.ZXException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author huangy
 * @date 2019/6/28 14:57
 */
public class EasyPoiUtil {
    /**
    * 导出Excel，包括文件名以及表名。是否创建表头
    *
    * @param list 导出的实体类
    * @param title 表头名称
    * @param sheetName sheet表名
    * @param pojoClass 映射的实体类
    * @param isCreateHeader 是否创建表头
    * @param fileName 文件名
    * @param response
    * @return 
    */
  public static void exportExcel(List<&＃63;> list, String title, String sheetName, Class<&＃63;> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
    ExportParams exportParams = new ExportParams(title, sheetName);
    exportParams.setCreateHeadRows(isCreateHeader);
    defaultExport(list, pojoClass, fileName, response, exportParams);

  }

  /**
   * 导出Excel 默认格式 默认有创建表头
   */
  public static void exportExcel(List<&＃63;> list, String title, String sheetName, Class<&＃63;> 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 exportParams
   */
  private static void defaultExport(List<&＃63;> list, Class<&＃63;> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
    ExcelExportUtil.closeExportBigExcel();
    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);
    ExcelExportUtil.closeExportBigExcel();
    if (workbook != null);
    downLoadExcel(fileName, response, workbook);
  }

  /**
   * 下载excel
   * @param fileName
   * @param response
   * @param 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) {
      throw new ZXException(e.getMessage());
    }
  }

  /**
   * 导入 文件路径形式
   * @param filePath
   * @param titleRows
   * @param headerRows
   * @param pojoClass
   * @param 
   * @return
   */
  public static  List importExcel(String filePath,Integer titleRows,Integer headerRows, Class pojoClass){
    if (StringUtils.isBlank(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 ZXException("模板不能为空");
    } catch (Exception e) {
      e.printStackTrace();
      throw new ZXException(e.getMessage());
    }
    return list;
  }

  /**
   * 导入 MultipartFile 形式
   * @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 (NoSuchElementException e){
      throw new ZXException("excel文件不能为空");
    } catch (Exception e) {
      throw new ZXException(e.getMessage());
    }
    return list;
  }

}

public class BlackListExport {
  @Excel(name = "客户姓名", width = 15, orderNum = "2")
  private String name;
  @Excel(name = "备注", width = 10, orderNum = "1")
  private String remark;
  @Excel(name = "手机号", width = 15, orderNum = "0")
  private String phone;

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getRemark() {
    return remark;
  }

  public void setRemark(String remark) {
    this.remark = remark;
  }

  public String getPhone() {
    return phone;
  }

  public void setPhone(String phone) {
    this.phOne= phone;
  }

  public BlackListExport() {
  }

  public BlackListExport(String name, String remark, String phone) {
    this.name = name;
    this.remark = remark;
    this.phOne= phone;
  }
}

  @ApiOperation(value = "easyPoiUtil 导出测试")
  @GetMapping(value = "/poi/export1")
  public void export1(HttpServletResponse response){
    List list=new ArrayList<>();
    for(int i=0;i<10000;i++){
      list.add(new BlackListExport(i+"",i+"",i+""));
    }
    EasyPoiUtil.exportExcel(list,"zx","huangy",BlackListExport.class,"zx.xls",response);
  }

  /**
   * 如果填充不同sheet得data数据列表使用相同得list对象进行填充的话，
   * 会出现第一次填充得sheet有数据，后续其他使用相同list对象进行data填充得sheet没有数据展示。
   * @param response
   */
  @ApiOperation(value = "多sheet 导出测试")
  @GetMapping(value = "/poi/export2")
  public void export2(HttpServletResponse response){
    // 查询数据,此处省略
    List list = new ArrayList<>();
    list.add(new BlackListExport("姓名1","备注1","手机1")) ;
    list.add(new BlackListExport("姓名2","备注2","手机2")) ;
    list.add(new BlackListExport("姓名3","备注3","手机3")) ;
    List list2 = new ArrayList<>();
    list2.add(new BlackListExport("姓名-1","备注-1","手机-1")) ;
    list2.add(new BlackListExport("姓名-2","备注-2","手机-2")) ;
    list2.add(new BlackListExport("姓名-3","备注-3","手机-3")) ;
    List> sheetsList = new ArrayList<>();
    for(int i=1;i<=4;i++){
    // 设置导出配置
    // 创建参数对象（用来设定excel得sheet得内容等信息）
    ExportParams params = new ExportParams() ;
    // 设置sheet得名称
    params.setSheetName("表格"+i);

    //创建sheet使用的map
    Map dataMap = new HashMap<>();
    // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
    dataMap.put("title",params) ;
    // 模版导出对应得实体类型
    dataMap.put("entity",BlackListExport.class) ;
    // sheet中要填充得数据
    if(i%2==0){
      dataMap.put("data",list) ;
    }else {
      dataMap.put("data",list2) ;
    }

    sheetsList.add(dataMap);
    }
    EasyPoiUtil.exportExcel(sheetsList,"hy.xls",response);
  }