天天看点
返回

SpringBoot Data jpa +EasyPoi导入导出Excel

一、

添加相关依赖

<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.ysd</groupId>
	<artifactId>SpringBootEasypoi</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<!-- Spring Boot 启动父依赖 -->
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.1.RELEASE</version>
	</parent> <!-- 项目全局属性 -->
	<dependencies>
	<!-- Spring Boot JPA 依赖 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency> <!-- MySQL 连接驱动依赖 -->
		
		<!-- web -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- MySQL 连接驱动依赖 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<!-- <version>${mysql-connector}</version> -->
		</dependency>
		<!--lombok依赖  lombok主要是来简化实体类方法 -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
			<version>1.16.12</version>
		</dependency>
		<!-- io常用工具类 -->
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.6</version>
		</dependency>

		<!-- 文件上传工具类 -->
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.4</version>
		</dependency>

		<!-- easypoi 依赖 -->
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-web</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-annotation</artifactId>
			<version>3.2.0</version>
		</dependency>
		<!--gson 依赖 -->
		<dependency>
			<groupId>com.google.code.gson</groupId>
			<artifactId>gson</artifactId>
			<version>2.6.2</version>
		</dependency>
		</dependencies>
</project>

创建application.properties文件

## 数据源éÂ
ç½® 
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
 spring.datasource.username=root
 spring.datasource.password=admin
 spring.datasource.driver-class-name=com.mysql.jdbc.Driver
 ##Spring Data JPA éÂ
ç½®
 spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
##运行时输出 jpa 执行的 sql 语句 
spring.jpa.show-sql=true
## spring-boot-starter-data-jpa 自动映射创建表动作 éÂ
ç½®: 有表更新,无表创建 
spring.jpa.hibernate.ddl-auto=update
 #应用端口 
server.port=8080
#应用项目名称
 #server.context-path=/mydemo 
#修改 tomcat 的 URIEncoding 为 UTF-8 
server.tomcat.uri-encoding=UTF-8
#集中解决各种编码问题 
#banner.charset=UTF-8 
spring.http.encoding.charset=UTF-8
spring.http.encoding.enabled=true
spring.http.encoding.force=true
spring.messages.encoding=UTF-8
#jackson 对日期时间格式化设置:时间格式 
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
#jackson 对日期时间格式化设置:时区设置 
spring.jackson.time-zone=GMT+8

导入工具类

package com.ysd.util;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import com.ysd.entity.Member;

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;

/**
 * @author PIGS
 * @version 1.0
 * @date 2020/4/25 14:16
 * @effect :
 * 表格数据工具类
 */
public final class EasyPoiUtils {

    private EasyPoiUtils() {
    }

    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);
        }
    }

    private static <T> void defaultExport(List<T> dataList, Class<?> clz, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clz, dataList);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(dataList, clz, fileName, response, exportParams);
    }

    public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, HttpServletResponse response) {
        defaultExport(dataList, clz, fileName, response, new ExportParams(title, sheetName));
    }

    private static void defaultExport(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static void exportExcel(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
        defaultExport(dataList, fileName, response);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> clz) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);

        try {
            return ExcelImportUtil.importExcel(new File(filePath), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> clz) {
        if (file == null) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);

        try {
            return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 导入数据
     * userEnity 你自己新建的实体类 实体类代码在下面
     * @param file
     * @param clz
     * @return
     */
    public static List<Member> importExcel(MultipartFile file, Class<Member> clz) {
        if (file == null) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        try {
            return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

导出工具类

package com.ysd.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 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;

//Excel导入导出工具类
public class ExcelUtils {
    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);
    }

    /**
     *
     * @param list 数据列表
     * @param title 标题
     * @param sheetName sheet名字
     * @param pojoClass 导出对象的Class类型
     * @param fileName 文件名
     * @param response
     */
    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));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    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);
    }

    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 NormalException(e.getMessage());
        }
    }

    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);
    }

    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 NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
        }
        return list;
    }

    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 NormalException("excel文件不能为空");
        } catch (Exception e) {
            // throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }

}

创建实体类

package com.ysd.entity;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import org.springframework.format.annotation.DateTimeFormat;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "Membertb")
public class Member {
		@Id
		@GeneratedValue(strategy = GenerationType.AUTO)
		@Excel(name = "id", width = 15)
		private Integer id;//����
		@Column(unique = true)
		@Excel(name = "memberId", width = 15)
		private Integer memberId;//��Ա����
		@Column(length = 10)
		@Excel(name = "name", width = 15)
		private String  name;//����
		@Column(length = 1)
		@Excel(name = "sex", width = 15)
		private String sex;//�Ա�
		@Column(length = 1)
		@Excel(name = "category", width = 15)
		private Integer category;//���  0ѧ�� 1 ��ʦ
		@Column(length = 18,unique = true)
		@Excel(name = "card", width = 15)
		private Integer card;//���֤��
		@Excel(name = "department", width = 15)
		private String department;//����ϵ
		@Excel(name = "profession", width = 15)
		private String profession;//��ѧרҵ
		@Excel(name = "grade", width = 15)
		private String grade;//�꼶
		@Excel(name = "phone", width = 15)
		private String phone;//�绰
		@Excel(name = "regdate", width = 15)
		@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
		private Date regdate;//���ʱ�� ��ȷ��ʱ����
		@Excel(name = "status", width = 15)
		private Integer status;//0��ʾû��ʹ���κ���Դ ����״̬��д��ԴId
		@Excel(name = "remark", width = 15)
		private String remark;//��ע
		
		
		
}

创建接口

package com.ysd.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.ysd.entity.Member;

/**
 * 
 * * 用户模块的dao接口
 * JpaRepository 用于JPA简单查询
 * JpaSpecificationExecutor 复杂动态查询，继承此接口才能在service层使用多条件查询方法
 * @author String
 *
 */
public interface MemberRepository extends JpaRepository<Member, Integer>,JpaSpecificationExecutor<Member>{

}

创建Controller（这里省略的Server层）

package com.ysd.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.ysd.entity.Member;
import com.ysd.repository.MemberRepository;
import com.ysd.util.EasyPoiUtils;
import com.ysd.util.ExcelUtils;
import com.ysd.util.Result;
@RestController
public class ExcelController {
	 /**
     * 从表格插入数据
     * 接收并返回前台
     *
     * @param file
     * @return
     * @throws IOException
     */
	/**导入
	 * 将从Excel中获取的数据写入表中
	 * */
	@Autowired
	private MemberRepository memberRepository;
	@RequestMapping("/uploadExcels")
    public Object uploadExcel(@RequestParam("file") MultipartFile file) throws IOException {
        List<Member> checkingIns = EasyPoiUtils.importExcel(file, Member.class);
        List<Member> listmam=memberRepository.save(checkingIns);
        if(listmam!=null) {
        	return Result.toClient("0", "批量添加成功", listmam);
        }else {
        	return Result.toClient("1", "批量添加失败", listmam);
        }    
    }	
	
	
	/**导出
	 * 将表中获取的数据写入Excel中
	 * */
	
	//导出接口
    @GetMapping("/exportExcel")
    public void export(HttpServletResponse response) {
        System.out.println("开始导出");
        // 模拟从数据库获取需要导出的数据 (偷懒，嘻嘻！)
        List<Member> personList = memberRepository.findAll();
        //设置序号（将id字段作为序号，导出后实现序号递增）
         Integer i =1;
        for (Member users : personList) {
            users.setId(i++);
        }
        // 导出操作
        ExcelUtils.exportExcel(personList, "easypoi导出功能(用户表)", "导出sheet1", Member.class, "测试Users.xls", response);

    }
}

创建html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>上传excel</title>
</head>
<body>
<h1>上传excel文件</h1>
<form action="/uploadExcels" method="post" enctype="multipart/form-data">
    <p>文件上传</p>
    <input type="file" name="file">
    <p><input type="submit" value="提交"></p>
</form>
</body>
</html>

导入测试

SpringBoot Data jpa +EasyPoi导入导出Excel

这个字段名跟实体类 @Excel(name = “用户ID”, width = 15) 需要一致不然会接收不到参数的

SpringBoot Data jpa +EasyPoi导入导出Excel

导出测试

SpringBoot Data jpa +EasyPoi导入导出Excel
SpringBoot Data Jpa easypoi
上一篇: Spring data jpa sort多属性排序问题
下一篇: mysql 查询 优化 实例_一个实例讲解MySQL查询优化（二）

继续阅读