EasyPoi实现Excel导入导出

EasyPoi实现Excel导入导出

1. pom文件

   <!-- easypoi -->
           <dependency>
               <groupId>cn.afterturn</groupId>
               <artifactId>easypoi-base</artifactId>
               <version>4.0.0</version>
           </dependency>
           <dependency>
               <groupId>cn.afterturn</groupId>
               <artifactId>easypoi-web</artifactId>
               <version>4.0.0</version>
           </dependency>
           <dependency>
               <groupId>cn.afterturn</groupId>
               <artifactId>easypoi-annotation</artifactId>
               <version>4.0.0</version>
          </dependency>
   

2. Excel工具类

   package com.xpf.utils;
   
   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.commons.lang3.StringUtils;
   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;
   
   /**
    * @Title:ExcelUtil
    * @Package:com.xpf.utils
    * @Author: xiapf
    * @Date:2019/9/11
    * @Descrption:
    */
   public class ExcelUtil {
   
       /**
        * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
        *
        * @param list 导出的实体类
        * @param title 表头名称
        * @param sheetName sheet表名
        * @param pojoClass 映射的实体类
        * @param isCreateHeader 是否创建表头
        * @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);
       }
   
   
       /**
        * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
        *
        * @param list 导出的实体类
        * @param title 表头名称
        * @param sheetName sheet表名
        * @param pojoClass 映射的实体类
        * @param fileName
        * @param response
        * @return
        */
       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 集合导出
        *
        * @param list 实体集合
        * @param fileName 导出的文件名称
        * @param response
        * @return
        */
       public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
           defaultExport(list, fileName, response);
       }
   
       /**
        * 功能描述：默认导出方法
        *
        * @param list 导出的实体集合
        * @param fileName 导出的文件名
        * @param pojoClass pojo实体
        * @param exportParams ExportParams封装实体
        * @param response
        * @return
        */
       private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
           Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
           if (workbook != null) {
               downLoadExcel(fileName, response, workbook);
           }
       }
   
       /**
        * 功能描述：Excel导出
        *
        * @param fileName 文件名称
        * @param response
        * @param workbook Excel对象
        * @return
        */
       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  RuntimeException(e);
           }
       }
   
       /**
        * 功能描述：默认导出方法
        *
        * @param list 导出的实体集合
        * @param fileName 导出的文件名
        * @param response
        * @return
        */
       private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
           Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
           if (workbook != null) ;
           downLoadExcel(fileName, response, workbook);
       }
   
   
       /**
        * 功能描述：根据文件路径来导入Excel
        *
        * @param filePath 文件路径
        * @param titleRows 表标题的行数
        * @param headerRows 表头行数
        * @param pojoClass Excel实体类
        * @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 RuntimeException("Excel不能为空");
           } catch (Exception e) {
               e.printStackTrace();
   
           }
           return list;
       }
   
       /**
        * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
        * @param file 上传的文件
        * @param titleRows 表标题的行数
        * @param headerRows 表头行数
        * @param pojoClass Excel实体类
        * @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 RuntimeException("excel文件不能为空");
           } catch (Exception e) {
               throw new RuntimeException(e.getMessage());
   
           }
           return list;
       }
   
   }
   
   

3. 实体

   package com.xpf.dto;
   
   import cn.afterturn.easypoi.excel.annotation.Excel;
   
   
   import java.util.Date;
   
   /**
    * @Title:ExcelDTO
    * @Package:com.xpf.dto
    * @Author: xiapf
    * @Date:2019/9/9
    * @Descrption: ExcelDTO
    */
    @Setter
    @Getter
   public class ExcelDTO {
   
       @Excel(name = "序号", orderNum = "1",width = 30)
       private String id;
   
       @Excel(name = "用户名", orderNum = "2",width = 30)
       private String userName;
   
       @Excel(name = "真实姓名", orderNum = "3",width = 30)
       private String realName;
   
       @Excel(name = "时间", exportFormat = "yyyy-MM-dd HH:mm:ss", orderNum = "4")
       private Date date;
   
   }
   
   

4. 应用

   @GetMapping("export")
       public String export(HttpServletResponse response) throws Exception{
           ExcelUtil.exportExcel(new ArrayList<>(),"title","sheetName",ExcelDTO.class,"记录表.xls",response);
           return "success";
       }