EasyPoi

最新推荐文章于 2024-03-10 00:07:41 发布

追梦梓辰

最新推荐文章于 2024-03-10 00:07:41 发布

阅读量432

点赞数 1

分类专栏： Java 文章标签： java 后端

本文链接：https://blog.csdn.net/weixin_63719049/article/details/126549231

版权

Java 专栏收录该内容

135 篇文章 3 订阅

订阅专栏

EasyPoi导入导出:

依赖：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>EasyPoi</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>EasyPoi Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.0</version>
      <scope>provided</scope>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.5</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.44</version>
    </dependency>

    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.2</version>
    </dependency>


    <!--EasyPoi依赖-->
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>3.1.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>3.1.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>3.1.0</version>
    </dependency>



    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.20</version>
      <scope>provided</scope>
    </dependency>



  </dependencies>



  <build>
    <finalName>EasyPoi</finalName>

  </build>
</project>

实体类:

package com.xj.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import javax.validation.constraints.NotNull;
import java.io.Serializable;

@Data
public class Student implements Serializable {
    //name为Excel中的表头
    @Excel(name = "姓名" ,orderNum = "1")
    @NotNull(message = "姓名不能为空")
    private String name;

    @Excel(name = "性别",orderNum = "2")
    private String sex;

}

utils:

package com.xj.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;
 
/**
 * @author 许缘
 * @company xxx公司
 * @create 2022-08-23 12:08
 * @Description: Excle 文件导入导出Util(easypoi)
 */
public class EasyPoiUtil {
   /**
    * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
    *
    * @author 许缘
    * @company xxx公司
    * @create 2022-08-23  13:07
    * @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，包括文件名以及表名,不创建表头
     *
     * @author 许缘
     * @company xxx公司
     * @create 2022-08-23 13:07
     * @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 集合导出
     *
     * @author 许缘
     * @company xxx公司
     * @create 2022-08-23  16:14
     * @param list 实体集合
     * @param fileName 导出的文件名称
     * @param response
     * @return
    */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }
 
    /**
     * 功能描述：默认导出方法
     *
     * @author 许缘
     * @company xxx公司
     * @create 2022-08-23  15:33
     * @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导出
     *
     * @author 许缘
     * @company xxx公司
     * @create 2022-08-23  15:35
     * @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);
        }
    }
 
    /**
     * 功能描述：默认导出方法
     *
     * @author 许缘
     * @company xxx公司
     * @create 2022-08-23  15:33
     * @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
     *
     * @author 许缘
     * @company xxx公司
     * @create 2022-08-23  14:17
     * @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("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
 
        }
        return list;
    }
 
    /**
     * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
     *
     * @author 许缘
     * @company xxx公司
     * @create 2022-08-23  14:17
     * @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);
            //调用dao方法增加到数据库中
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
 
        }
        return list;
    }
 
 
}

test:

public void testTestImportExcel() {
        //数据源
        String filePath = "C:\\Users\\xj\\Desktop\\student.xlsx";
        List<Student> list = EasyPoiUtil.importExcel(filePath,0,1,Student.class);
        for (Student student : list) {
            System.out.println(student);
        }


    }