devbox
AllinToyou
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Spring Boot集成easypoi快速入门Demo

作者：AllinToyou | 2024-04-17 08:09:47

赞

踩

Spring Boot集成easypoi快速入门Demo

1.什么是easypoi？

Easypoi功能如同名字easy，主打的功能就是容易，让一个没见接触过poi的人员就可以方便的写出Excel导出，Excel模板导出，Excel导入，Word模板导出，通过简单的注解和模板语言(熟悉的表达式语法)，完成以前复杂的写法。

2.代码工程

实验目的：实现excel导入和导出

pom.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <parent>
  6.         <artifactId>springboot-demo</artifactId>
  7.         <groupId>com.et</groupId>
  8.         <version>1.0-SNAPSHOT</version>
  9.     </parent>
  10.     <modelVersion>4.0.0</modelVersion>
  11.  
  12.  
  13.     <artifactId>eaypoi</artifactId>
  14.  
  15.  
  16.     <properties>
  17.         <maven.compiler.source>8</maven.compiler.source>
  18.         <maven.compiler.target>8</maven.compiler.target>
  19.     </properties>
  20.     <dependencies>
  21.  
  22.  
  23.         <dependency>
  24.             <groupId>org.springframework.boot</groupId>
  25.             <artifactId>spring-boot-starter-web</artifactId>
  26.         </dependency>
  27.  
  28.  
  29.         <dependency>
  30.             <groupId>org.springframework.boot</groupId>
  31.             <artifactId>spring-boot-autoconfigure</artifactId>
  32.         </dependency>
  33.         <dependency>
  34.             <groupId>org.springframework.boot</groupId>
  35.             <artifactId>spring-boot-starter-test</artifactId>
  36.             <scope>test</scope>
  37.         </dependency>
  38.         <dependency>
  39.             <groupId>cn.afterturn</groupId>
  40.             <artifactId>easypoi-spring-boot-starter</artifactId>
  41.             <version>4.1.2</version>
  42.         </dependency>
  43.         <dependency>
  44.             <groupId>org.projectlombok</groupId>
  45.             <artifactId>lombok</artifactId>
  46.         </dependency>
  47.         <dependency>
  48.             <groupId>com.alibaba</groupId>
  49.             <artifactId>fastjson</artifactId>
  50.             <version>2.0.40</version>
  51.         </dependency>
  52.     </dependencies>
  53. </project>

HelloWorldController.java

  1. package com.et.easypoi.controller;
  2.  
  3.  
  4. import com.alibaba.fastjson.JSONObject;
  5. import com.et.easypoi.Util.FileUtil;
  6. import com.et.easypoi.model.GoodType;
  7. import com.et.easypoi.model.Goods;
  8. import org.springframework.stereotype.Controller;
  9. import org.springframework.web.bind.annotation.RequestMapping;
  10. import org.springframework.web.bind.annotation.ResponseBody;
  11. import org.springframework.web.bind.annotation.RestController;
  12.  
  13.  
  14. import javax.servlet.http.HttpServletResponse;
  15. import java.util.*;
  16.  
  17.  
  18. @RestController
  19. public class HelloWorldController {
  20.     @RequestMapping("/hello")
  21.     public Map<String, Object> showHelloWorld(){
  22.         Map<String, Object> map = new HashMap<>();
  23.         map.put("msg", "HelloWorld");
  24.         return map;
  25.     }
  26.     @RequestMapping("/exportExcel")
  27.     public void export(HttpServletResponse response) throws Exception {
  28.         //mock datas
  29.         Goods goods1 = new Goods();
  30.         List<GoodType>  goodTypeList1 = new ArrayList<>();
  31.         GoodType goodType1  =  new GoodType();
  32.         goodType1.setTypeId("apple-1");
  33.         goodType1.setTypeName("apple-red");
  34.         goodTypeList1.add(goodType1);
  35.         GoodType goodType2  =  new GoodType();
  36.         goodType2.setTypeId("apple-2");
  37.         goodType2.setTypeName("apple-white");
  38.         goodTypeList1.add(goodType2);
  39.         goods1.setNo(110);
  40.         goods1.setName("apple");
  41.         goods1.setShelfLife(new Date());
  42.         goods1.setGoodTypes(goodTypeList1);
  43.  
  44.  
  45.  
  46.  
  47.         Goods goods2 = new Goods();
  48.         List<GoodType>  goodTypeList2 = new ArrayList<>();
  49.         GoodType goodType21  =  new GoodType();
  50.         goodType21.setTypeId("wine-1");
  51.         goodType21.setTypeName("wine-red");
  52.         goodTypeList2.add(goodType21);
  53.         GoodType goodType22  =  new GoodType();
  54.         goodType22.setTypeId("wine-2");
  55.         goodType22.setTypeName("wine-white");
  56.         goodTypeList2.add(goodType22);
  57.         goods2.setNo(111);
  58.         goods2.setName("wine");
  59.         goods2.setShelfLife(new Date());
  60.         goods2.setGoodTypes(goodTypeList2);
  61.  
  62.  
  63.  
  64.  
  65.         List<Goods> goodsList = new ArrayList<Goods>();
  66.         goodsList.add(goods1);
  67.         goodsList.add(goods2);
  68.  
  69.  
  70.  
  71.  
  72.         for (Goods goods : goodsList) {
  73.             System.out.println(goods);
  74.         }
  75.         //export
  76.         FileUtil.exportExcel(goodsList, Goods.class,"product.xls",response);
  77.     }
  78.  
  79.  
  80.     @RequestMapping("/importExcel")
  81.     public void importExcel() throws Exception {
  82.         //loal file
  83.         String filePath = "C:\\Users\\Dell\\Downloads\\product.xls";
  84.         //anaysis excel
  85.         List<Goods> goodsList = FileUtil.importExcel(filePath,0,1,Goods.class);
  86.         //also use MultipartFile,invoke FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)
  87.         System.out.println("load data count【"+goodsList.size()+"】row");
  88.  
  89.  
  90.         //TODO save datas
  91.         for (Goods goods:goodsList) {
  92.             JSONObject.toJSONString(goods);
  93.         }
  94.     }
  95. }

model

  1. package com.et.easypoi.model;
  2.  
  3.  
  4. import cn.afterturn.easypoi.excel.annotation.Excel;
  5. import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
  6. import lombok.Data;
  7.  
  8.  
  9. import java.io.Serializable;
  10. import java.util.Date;
  11. import java.util.List;
  12.  
  13.  
  14. @Data
  15. public class Goods implements Serializable {
  16.  
  17.  
  18.  
  19.  
  20.  
  21.  
  22.     @Excel(name = "NO",needMerge = true ,width = 20)
  23.     private Integer no;
  24.  
  25.  
  26.     @Excel(name = "name",needMerge = true ,width = 20)
  27.     private String name;
  28.  
  29.  
  30.     @Excel(name = "shelfLife",width = 20,needMerge = true ,exportFormat = "yyyy-MM-dd")
  31.     private Date shelfLife;
  32.  
  33.  
  34.     @ExcelCollection(name = "goodTypes")
  35.     private List<GoodType> goodTypes;
  36.  
  37.  
  38. }
  1. package com.et.easypoi.model;
  2.  
  3.  
  4. import cn.afterturn.easypoi.excel.annotation.Excel;
  5. import lombok.Data;
  6.  
  7.  
  8. /**
  9.  * @author liuhaihua
  10.  * @version 1.0
  11.  * @ClassName GoodType
  12.  * @Description todo
  13.  * @date 2024年04月15日 11:02
  14.  */
  15. @Data
  16. public class GoodType {
  17.     @Excel(name = "typeId",  width = 20,height = 8)
  18.     private String typeId;
  19.     @Excel(name = "typeName",  width = 20,height = 8)
  20.     private String typeName;
  21. }

FileUtil

通用工具类，导出和导出封装

  1. package com.et.easypoi.Util;
  2.  
  3.  
  4. import cn.afterturn.easypoi.excel.ExcelExportUtil;
  5. import cn.afterturn.easypoi.excel.ExcelImportUtil;
  6. import cn.afterturn.easypoi.excel.entity.ExportParams;
  7. import cn.afterturn.easypoi.excel.entity.ImportParams;
  8. import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
  9. import com.et.easypoi.service.ExcelExportStyler;
  10. import org.apache.commons.lang3.StringUtils;
  11. import org.apache.poi.ss.usermodel.CellStyle;
  12. import org.apache.poi.ss.usermodel.FillPatternType;
  13. import org.apache.poi.ss.usermodel.IndexedColors;
  14. import org.apache.poi.ss.usermodel.Workbook;
  15. import org.springframework.web.multipart.MultipartFile;
  16.  
  17.  
  18. import javax.servlet.ServletOutputStream;
  19. import javax.servlet.http.HttpServletResponse;
  20. import java.io.File;
  21. import java.io.IOException;
  22. import java.io.OutputStream;
  23. import java.net.URLEncoder;
  24. import java.util.List;
  25. import java.util.Map;
  26. import java.util.NoSuchElementException;
  27.  
  28.  
  29. public class FileUtil {
  30.  
  31.  
  32.     public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws Exception {
  33.         ExportParams exportParams = new ExportParams(title, sheetName);
  34.         exportParams.setCreateHeadRows(isCreateHeader);
  35.         defaultExport(list, pojoClass, fileName, response, exportParams);
  36.     }
  37.     public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName,  HttpServletResponse response) throws Exception {
  38.         ExportParams exportParams = new ExportParams();
  39.         exportParams.setStyle(ExcelExportStyler.class); // set style
  40.         defaultExport(list, pojoClass, fileName, response, exportParams);
  41.     }
  42.     public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response) throws Exception {
  43.         defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
  44.     }
  45.     public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
  46.         defaultExport(list, fileName, response);
  47.     }
  48.  
  49.  
  50.     private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws Exception {
  51.         Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
  52.         if (workbook != null);
  53.         downLoadExcel(fileName, response, workbook);
  54.     }
  55.  
  56.  
  57.     private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception {
  58.         OutputStream outputStream=null;
  59.         try {
  60.             response.setCharacterEncoding("UTF-8");
  61.             response.setHeader("content-Type", "application/vnd.ms-excel");
  62.             response.setHeader("Content-Disposition",
  63.                     "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
  64.             outputStream = response.getOutputStream();
  65.  
  66.  
  67.             workbook.write(outputStream);
  68.         } catch (IOException e) {
  69.             throw new Exception(e.getMessage());
  70.         }finally {
  71.             try {
  72.                 outputStream.close();
  73.             } catch (IOException e) {
  74.                 e.printStackTrace();
  75.             }
  76.         }
  77.     }
  78.     private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
  79.         Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
  80.         if (workbook != null);
  81.         downLoadExcel(fileName, response, workbook);
  82.     }
  83.  
  84.  
  85.     public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass) throws Exception {
  86.         if (StringUtils.isBlank(filePath)){
  87.             return null;
  88.         }
  89.         ImportParams params = new ImportParams();
  90.         params.setTitleRows(titleRows);
  91.         params.setHeadRows(headerRows);
  92.         List<T> list = null;
  93.         try {
  94.             list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
  95.         }catch (NoSuchElementException e){
  96.             throw new Exception("template not null");
  97.         } catch (Exception e) {
  98.             e.printStackTrace();
  99.             throw new Exception(e.getMessage());
  100.         }
  101.         return list;
  102.     }
  103.     public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
  104.         if (file == null){
  105.             return null;
  106.         }
  107.         ImportParams params = new ImportParams();
  108.         params.setTitleRows(titleRows);
  109.         params.setHeadRows(headerRows);
  110.         List<T> list = null;
  111.         try {
  112.             list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
  113.         }catch (NoSuchElementException e){
  114.             throw new Exception("excel file not null");
  115.         } catch (Exception e) {
  116.             throw new Exception(e.getMessage());
  117.         }
  118.         return list;
  119.     }
  120. }

ExcelExportStyler

设置表头，单元格样式

  1. package com.et.easypoi.service;
  2.  
  3.  
  4. import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
  5. import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
  6. import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
  7. import org.apache.poi.ss.usermodel.*;
  8.  
  9.  
  10. public class ExcelExportStyler implements IExcelExportStyler {
  11.     private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
  12.     private static final short FONT_SIZE_TEN = 10;
  13.     private static final short FONT_SIZE_ELEVEN = 11;
  14.     private static final short FONT_SIZE_TWELVE = 12;
  15.     /**
  16.      * header style
  17.      */
  18.     private CellStyle headerStyle;
  19.     /**
  20.      * title style
  21.      */
  22.     private CellStyle titleStyle;
  23.     /**
  24.      * cell style
  25.      */
  26.     private CellStyle styles;
  27.  
  28.  
  29.     public ExcelExportStyler(Workbook workbook) {
  30.         this.init(workbook);
  31.     }
  32.  
  33.  
  34.     /**
  35.      * init
  36.      *
  37.      * @param workbook
  38.      */
  39.     private void init(Workbook workbook) {
  40.         this.headerStyle = initHeaderStyle(workbook);
  41.         this.titleStyle = initTitleStyle(workbook);
  42.     }
  43.  
  44.  
  45.     @Override
  46.     public CellStyle getHeaderStyle(short color) {
  47.         return headerStyle;
  48.     }
  49.  
  50.  
  51.  
  52.  
  53.     @Override
  54.     public CellStyle getTitleStyle(short color) {
  55.         return titleStyle;
  56.     }
  57.  
  58.  
  59.  
  60.  
  61.     @Override
  62.     public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
  63.         return styles;
  64.     }
  65.  
  66.  
  67.  
  68.  
  69.     @Override
  70.     public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
  71.         return getStyles(true, entity);
  72.     }
  73.  
  74.  
  75.     @Override
  76.     public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
  77.         return null;
  78.     }
  79.  
  80.  
  81.     /**
  82.      * init --HeaderStyle
  83.      *
  84.      * @param workbook
  85.      * @return
  86.      */
  87.     private CellStyle initHeaderStyle(Workbook workbook) {
  88.         CellStyle style = getBaseCellStyle(workbook);
  89.         style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
  90.         return style;
  91.     }
  92.  
  93.  
  94.     /**
  95.      * init-TitleStyle
  96.      *
  97.      * @param workbook
  98.      * @return
  99.      */
  100.     private CellStyle initTitleStyle(Workbook workbook) {
  101.         CellStyle style = getBaseCellStyle(workbook);
  102.         style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
  103.         // ForegroundColor
  104.         style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  105.         style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  106.         return style;
  107.     }
  108.  
  109.  
  110.     /**
  111.      * BaseCellStyle
  112.      *
  113.      * @return
  114.      */
  115.     private CellStyle getBaseCellStyle(Workbook workbook) {
  116.         CellStyle style = workbook.createCellStyle();
  117.         style.setBorderBottom(BorderStyle.THIN);
  118.         style.setBorderLeft(BorderStyle.THIN);
  119.         style.setBorderTop(BorderStyle.THIN);
  120.         style.setBorderRight(BorderStyle.THIN);
  121.         style.setAlignment(HorizontalAlignment.CENTER);
  122.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  123.         style.setWrapText(true);
  124.         return style;
  125.     }
  126.  
  127.  
  128.     /**
  129.      * Font
  130.      *
  131.      * @param size
  132.      * @param isBold
  133.      * @return
  134.      */
  135.     private Font getFont(Workbook workbook, short size, boolean isBold) {
  136.         Font font = workbook.createFont();
  137.         font.setFontName("宋体");
  138.         font.setBold(isBold);
  139.         font.setFontHeightInPoints(size);
  140.         return font;
  141.     }
  142. }
  1. package com.et.easypoi.service;
  2.  
  3.  
  4. import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
  5. import org.apache.poi.ss.usermodel.CellStyle;
  6.  
  7.  
  8. public interface IExcelExportStyler {
  9.     /**
  10.      * heder style
  11.      * @param headerColor
  12.      * @return
  13.      */
  14.     public CellStyle getHeaderStyle(short headerColor);
  15.     /**
  16.      * title style
  17.      * @param color
  18.      * @return
  19.      */
  20.     public CellStyle getTitleStyle(short color);
  21.     /**
  22.      * getstyle
  23.      * @param Parity
  24.      * @param entity
  25.      * @return
  26.      */
  27.     public CellStyle getStyles(boolean Parity, ExcelExportEntity entity);
  28. }

以上只是一些关键代码，所有代码请参见下面代码仓库

代码仓库

  • https://github.com/Harries/springboot-demo

3.测试

启动Spring Boot应用

导出

访问http://127.0.0.1:8088/importExcelb5043bf02dcd0f9dcd291e008581e387.png

导入

访问http://127.0.0.1:8088/importExcel

  1. Goods(no=110, name=apple, shelfLife=Mon Apr 15 13:28:36 CST 2024, goodTypes=[GoodType(typeId=apple-1, typeName=apple-red), GoodType(typeId=apple-2, typeName=apple-white)])
  2. Goods(no=111, name=wine, shelfLife=Mon Apr 15 13:28:36 CST 2024, goodTypes=[GoodType(typeId=wine-1, typeName=wine-red), GoodType(typeId=wine-2, typeName=wine-white)])
  3. load data count【3】row

4.引用

  • http://www.ibloger.net/article/3391.html

  • http://www.liuhaihua.cn/archives/710424.html

  • http://doc.wupaas.com/docs/easypoi/easypoi-1c0u9a1bv66f4

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AllinToyou/article/detail/439087
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号