EasyPoi

1 导入依赖
  1. <!--        EasyPoiUtil导入依赖-->
  2.         <dependency>
  3.             <groupId>cn.afterturn</groupId>
  4.             <artifactId>easypoi-base</artifactId>
  5.             <version>4.1.0</version>
  6.         </dependency>
  7.         <dependency>
  8.             <groupId>cn.afterturn</groupId>
  9.             <artifactId>easypoi-web</artifactId>
  10.             <version>4.1.0</version>
  11.         </dependency>
  12.         <dependency>
  13.             <groupId>cn.afterturn</groupId>
  14.             <artifactId>easypoi-annotation</artifactId>
  15.             <version>4.1.0</version>
  16.         </dependency>

踩坑警告

​ 如果项目中已经有其他excel工具类的依赖了，则把本依赖放到后面，

​ EasyPoiUtil的版本尽量选择新一些的，避免导入数据问题

2 工具类
  1. import cn.afterturn.easypoi.cache.manager.POICacheManager;
  2. import cn.afterturn.easypoi.excel.ExcelExportUtil;
  3. import cn.afterturn.easypoi.excel.ExcelImportUtil;
  4. import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil;
  5. import cn.afterturn.easypoi.excel.entity.ExcelToHtmlParams;
  6. import cn.afterturn.easypoi.excel.entity.ExportParams;
  7. import cn.afterturn.easypoi.excel.entity.ImportParams;
  8. import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
  9. import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
  10. import cn.afterturn.easypoi.word.WordExportUtil;
  11. import cn.afterturn.easypoi.word.parse.ParseWord07;
  12. import org.apache.commons.lang3.StringUtils;
  13. import org.apache.poi.ss.usermodel.Workbook;
  14. import org.apache.poi.ss.usermodel.WorkbookFactory;
  15. import org.apache.poi.xwpf.usermodel.XWPFDocument;
  16. import org.springframework.web.multipart.MultipartFile;
  18. import javax.servlet.http.HttpServletResponse;
  19. import java.io.File;
  20. import java.io.FileOutputStream;
  21. import java.io.IOException;
  22. import java.io.InputStream;
  23. import java.net.URLEncoder;
  24. import java.util.List;
  25. import java.util.Map;
  26. import java.util.NoSuchElementException;
  28. /**
  29.  * Excel导入导出工具类
  30.  */
  32. public class ExcelUtils2 {
  34.     /**
  35.      * excel 导出
  36.      *
  37.      * @param list     数据列表
  38.      * @param fileName 导出时的excel名称
  39.      * @param response
  40.      */
  41.     public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
  42.         defaultExport(list, fileName, response);
  43.     }
  45.     /**
  46.      * 默认的 excel 导出
  47.      *
  48.      * @param list     数据列表
  49.      * @param fileName 导出时的excel名称
  50.      * @param response
  51.      */
  52.     private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
  53.         //把数据添加到excel表格中
  54.         Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
  55.         downLoadExcel(fileName, response, workbook);
  56.     }
  58.     /**
  59.      * excel 导出
  60.      *
  61.      * @param list         数据列表
  62.      * @param pojoClass    pojo类型
  63.      * @param fileName     导出时的excel名称
  64.      * @param response
  65.      * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
  66.      */
  67.     private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
  68.         //把数据添加到excel表格中
  69.         Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
  70.         downLoadExcel(fileName, response, workbook);
  71.     }
  73.     /**
  74.      * excel 导出
  75.      *
  76.      * @param list         数据列表
  77.      * @param pojoClass    pojo类型
  78.      * @param fileName     导出时的excel名称
  79.      * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
  80.      * @param response
  81.      */
  82.     public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
  83.         defaultExport(list, pojoClass, fileName, response, exportParams);
  84.     }
  86.     /**
  87.      * excel 导出
  88.      *
  89.      * @param list      数据列表
  90.      * @param title     表格内数据标题
  91.      * @param sheetName sheet名称
  92.      * @param pojoClass pojo类型
  93.      * @param fileName  导出时的excel名称
  94.      * @param response
  95.      */
  96.     public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
  97.         defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
  98.     }
  102.     /**
  103.      * excel 导出
  104.      *
  105.      * @param list           数据列表
  106.      * @param title          表格内数据标题
  107.      * @param sheetName      sheet名称
  108.      * @param pojoClass      pojo类型
  109.      * @param fileName       导出时的excel名称
  110.      * @param isCreateHeader 是否创建表头
  111.      * @param response
  112.      */
  113.     public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
  114.         ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
  115.         exportParams.setCreateHeadRows(isCreateHeader);
  116.         defaultExport(list, pojoClass, fileName, response, exportParams);
  117.     }
  120.     /**
  121.      * excel下载
  122.      *
  123.      * @param fileName 下载时的文件名称
  124.      * @param response
  125.      * @param workbook excel数据
  126.      */
  127.     private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
  128.         try {
  129.             response.setCharacterEncoding("UTF-8");
  130.             response.setHeader("content-Type", "application/vnd.ms-excel");
  131.             response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
  132.             workbook.write(response.getOutputStream());
  133.         } catch (Exception e) {
  134.             throw new IOException(e.getMessage());
  135.         }
  136.     }
  140.     /**
  141.      * excel 导入
  142.      *
  143.      * @param file      excel文件
  144.      * @param pojoClass pojo类型
  145.      * @param <T>
  146.      * @return
  147.      */
  148.     public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
  149.         return importExcel(file, 1, 1, pojoClass);
  150.     }
  152.     /**
  153.      * excel 导入
  154.      *
  155.      * @param filePath   excel文件路径
  156.      * @param titleRows  表格内数据标题行
  157.      * @param headerRows 表头行
  158.      * @param pojoClass  pojo类型
  159.      * @param <T>
  160.      * @return
  161.      */
  162.     public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
  163.         if (StringUtils.isBlank(filePath)) {
  164.             return null;
  165.         }
  166.         ImportParams params = new ImportParams();
  167.         params.setTitleRows(titleRows);
  168.         params.setHeadRows(headerRows);
  169. //        params.setNeedSave(true); //不保存
  170.         params.setSaveUrl("/home/server/upload/excel/");
  171.         try {
  172.             return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
  173.         } catch (NoSuchElementException e) {
  174.             throw new IOException("模板不能为空");
  175.         } catch (Exception e) {
  176.             throw new IOException(e.getMessage());
  177.         }
  178.     }
  181.     /**
  182.      * excel 导入
  183.      *
  184.      * @param file       上传的文件
  185.      * @param titleRows  表格内数据标题行
  186.      * @param headerRows 表头行
  187.      * @param pojoClass  pojo类型
  188.      * @param <T>
  189.      * @return
  190.      */
  191.     public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
  192.         if (file == null) {
  193.             return null;
  194.         }
  195.         try {
  196.             return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
  197.         } catch (Exception e) {
  198.             throw new IOException(e.getMessage());
  199.         }
  200.     }
  202.     /**
  203.      * excel 导入
  204.      *
  205.      * @param inputStream 文件输入流
  206.      * @param titleRows   表格内数据标题行
  207.      * @param headerRows  表头行
  208.      * @param pojoClass   pojo类型
  209.      * @param <T>
  210.      * @return
  211.      */
  212.     public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
  213.         if (inputStream == null) {
  214.             return null;
  215.         }
  216.         ImportParams params = new ImportParams();
  217.         params.setTitleRows(titleRows);
  218.         params.setHeadRows(headerRows);
  219.         params.setSaveUrl("/home/server/upload/excel/");
  220. //        params.setNeedSave(true);  //不保存
  221.         try {
  222.             return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
  223.         } catch (NoSuchElementException e) {
  224.             throw new IOException("excel文件不能为空");
  225.         } catch (Exception e) {
  226.             throw new IOException(e.getMessage());
  227.         }
  228.     }
  229. }
3 导入导出dto
  1. package com.fongtech.cli.mbg.model.dto;
  3. import cn.afterturn.easypoi.excel.annotation.Excel;
  4. import com.fongtech.cli.framework.model.BaseModel;
  5. import lombok.Data;
  6. import lombok.EqualsAndHashCode;
  7. import lombok.NoArgsConstructor;
  9. import java.math.BigDecimal;
  10. import java.util.Date;
  12. /**
  13.  * @Author
  14.  * @Time 2020/11/3 0003
  15.  */
  16. @Data
  17. @NoArgsConstructor
  18. @EqualsAndHashCode(callSuper = false)
  19. public class CommodityImportDTO  extends BaseModel {
  20.     /**
  21.      * id
  22.      */
  23.     @Excel(name = "id")
  24.     private Integer id;
  25.     /**
  26.      * image
  27.      */
  28.     @Excel(name = "image")
  29.     private String image;
  30.     /**
  31.      * title
  32.      */
  33.     @Excel(name = "title")
  34.     private String title;
  35.     /**
  36.      * asin
  37.      */
  38.     @Excel(name = "asin")
  39.     private String asin;
  40.     /**
  41.      * storeName
  42.      */
  43.     @Excel(name = "storeName")
  44.     private String storeName;
  45.     /**
  46.      * price
  47.      */
  48.     @Excel(name = "price")
  49.     private String price;
  50.     /**
  51.      * currencySymbol
  52.      */
  53.     @Excel(name = "currencySymbol")
  54.     private String currencySymbol;
  55.     /**
  56.      * country
  57.      */
  58.     @Excel(name = "country")
  59.     private String country;
  61.     /**
  62.      * supportedReviewType
  63.      */
  64.     @Excel(name = "supportedReviewType")
  65.     private String supportedReviewType;
  67.     /**
  68.      * startDate
  69.      */
  70.     @Excel(name = "startDate")
  71.     private Date startDate;
  72.     /**
  73.      * endDate
  74.      */
  75.     @Excel(name = "endDate")
  76.     private Date endDate;
  77.     /**
  78.      * dailyTotal
  79.      */
  80.     @Excel(name = "dailyTotal")
  81.     private String dailyTotal;
  82.     /**
  83.      * total
  84.      */
  85.     @Excel(name = "total")
  86.     private String total;
  87.     /**
  88.      * commission
  89.      */
  90.     @Excel(name = "commission")
  91.     private String commission;
  92.     /**
  93.      * url
  94.      */
  95.     @Excel(name = "url")
  96.     private String url;
  97.     /**
  98.      * productSpecification
  99.      */
  100.     @Excel(name = "productSpecification")
  101.     private String productSpecification;
  102.     /**
  103.      * createdTime
  104.      */
  105.     @Excel(name = "createTime")
  106.     private Date createdTime;
  109. }

版权声明：本文为superlinb原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/superlinb/p/14886642.html