Java使用easypoi快速導(dǎo)入導(dǎo)出的實(shí)現(xiàn)

更新時(shí)間：2021年01月14日 09:44:51 作者：子_軒

這篇文章主要介紹了實(shí)現(xiàn)Java使用easypoi快速導(dǎo)入導(dǎo)出的實(shí)現(xiàn)，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧

簡介

easypoi功能如同名字easy,主打的功能就是容易,讓一個(gè)沒見接觸過poi的人員就可以方便的寫出Excel導(dǎo)入,導(dǎo)出,通過簡單的注解和模板語言(熟悉的表達(dá)式語法),完成以前復(fù)雜的寫法。

集成

pom 中引入依賴即可

    <!--easypoi-->
    <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>

整合工具類 EasyPoiUtil

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 {
    /**
    * 導(dǎo)出Excel，包括文件名以及表名。是否創(chuàng)建表頭
    *
    * @param list 導(dǎo)出的實(shí)體類
    * @param title 表頭名稱
    * @param sheetName sheet表名
    * @param pojoClass 映射的實(shí)體類
    * @param isCreateHeader 是否創(chuàng)建表頭
    * @param fileName 文件名
    * @param response
    * @return 
    */
  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);

  }

  /**
   * 導(dǎo)出Excel 默認(rèn)格式 默認(rèn)有創(chuàng)建表頭
   */
  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形式導(dǎo)出
   * @param list
   * @param fileName
   * @param response
   */
  public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
    defaultExport(list, fileName, response);
  }

  /**
   * 常規(guī)默認(rèn)導(dǎo)出方式
   * @param list
   * @param pojoClass
   * @param fileName
   * @param response
   * @param exportParams
   */
  private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
    ExcelExportUtil.closeExportBigExcel();
    if (workbook != null);
    downLoadExcel(fileName, response, workbook);
  }

  /**
   * 多sheet默認(rèn)導(dǎo)出方式
   * @param list
   * @param fileName
   * @param response
   */
  private static void defaultExport(List<Map<String, Object>> 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());
    }
  }

  /**
   * 導(dǎo)入 文件路徑形式
   * @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){
      throw new ZXException("模板不能為空");
    } catch (Exception e) {
      e.printStackTrace();
      throw new ZXException(e.getMessage());
    }
    return list;
  }

  /**
   * 導(dǎo)入 MultipartFile 形式
   * @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);
    List<T> 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;
  }

}

使用示例

實(shí)體類

public class BlackListExport {
  @Excel(name = "客戶姓名", width = 15, orderNum = "2")
  private String name;
  @Excel(name = "備注", width = 10, orderNum = "1")
  private String remark;
  @Excel(name = "手機(jī)號(hào)", 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 導(dǎo)出測試")
  @GetMapping(value = "/poi/export1")
  public void export1(HttpServletResponse response){
    List<BlackListExport> 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數(shù)據(jù)列表使用相同得list對(duì)象進(jìn)行填充的話，
   * 會(huì)出現(xiàn)第一次填充得sheet有數(shù)據(jù)，后續(xù)其他使用相同list對(duì)象進(jìn)行data填充得sheet沒有數(shù)據(jù)展示。
   * @param response
   */
  @ApiOperation(value = "多sheet 導(dǎo)出測試")
  @GetMapping(value = "/poi/export2")
  public void export2(HttpServletResponse response){
    // 查詢數(shù)據(jù),此處省略
    List list = new ArrayList<>();
    list.add(new BlackListExport("姓名1","備注1","手機(jī)1")) ;
    list.add(new BlackListExport("姓名2","備注2","手機(jī)2")) ;
    list.add(new BlackListExport("姓名3","備注3","手機(jī)3")) ;
    List list2 = new ArrayList<>();
    list2.add(new BlackListExport("姓名-1","備注-1","手機(jī)-1")) ;
    list2.add(new BlackListExport("姓名-2","備注-2","手機(jī)-2")) ;
    list2.add(new BlackListExport("姓名-3","備注-3","手機(jī)-3")) ;
    List<Map<String,Object>> sheetsList = new ArrayList<>();
    for(int i=1;i<=4;i++){
    // 設(shè)置導(dǎo)出配置
    // 創(chuàng)建參數(shù)對(duì)象（用來設(shè)定excel得sheet得內(nèi)容等信息）
    ExportParams params = new ExportParams() ;
    // 設(shè)置sheet得名稱
    params.setSheetName("表格"+i);

    //創(chuàng)建sheet使用的map
    Map<String,Object> dataMap = new HashMap<>();
    // title的參數(shù)為ExportParams類型，目前僅僅在ExportParams中設(shè)置了sheetName
    dataMap.put("title",params) ;
    // 模版導(dǎo)出對(duì)應(yīng)得實(shí)體類型
    dataMap.put("entity",BlackListExport.class) ;
    // sheet中要填充得數(shù)據(jù)
    if(i%2==0){
      dataMap.put("data",list) ;
    }else {
      dataMap.put("data",list2) ;
    }

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

到此這篇關(guān)于Java使用easypoi快速導(dǎo)入導(dǎo)出的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)Java easypoi導(dǎo)入導(dǎo)出內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: