博主亲测通过,废话不多说上代码!!!
数据表结构:
实体类:
package com.excel.demo.domain;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Person implements Serializable {
private static final long serialVersiionUID = -6118059334688579641L;
@Excel(name = "名字", orderNum = "0")
private String name;
@Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1")
private String sex;
@Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "2")
private Date birthday;
}
application.yml配置文件
可根据自己的需要修改
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/ExcelDemo?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true
username: root
password: 123456
thymeleaf:
cache: false
prefix: classpath:/templates/
mybatis:
type-aliases-package: com.excel.demo
mapper-locations: classpath:mapper/*.xml
pom文件
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.4</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.excel</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<easypoi.version>4.0.0</easypoi.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.21</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.21</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>${easypoi.version}</version>
</dependency>
<!-- 文件上传 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!--mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.2.4</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Utils工具类
package com.excel.demo.utils;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
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 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 lombok.extern.slf4j.Slf4j;
/**
* @author 王瑞祥
* @date 2021年4月26日
*/
@Slf4j
public class FileUtils {
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);
}
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());
log.error("down load excel: {}", e);
}
}
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("模板不能为空");
log.error("import excel NoSuchElementException: {}", e);
} catch (Exception e) {
e.printStackTrace();
// throw new NormalException(e.getMessage());
log.error("import excel Exception: {}", e);
}
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文件不能为空");
log.error("import excel NoSuchElementException: {}", e);
} catch (Exception e) {
// throw new NormalException(e.getMessage());
log.error("import excel Exception: {}", e);
}
return list;
}
}
mapper接口
package com.excel.demo.mapper;
import com.excel.demo.domain.Person;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface PersonMapper {
/**
* SpringBoot+easyPoi一行代码搞定excel导入导出
* @return
*/
List<Person> getExportDatas();
/**
* 保存导入的用户
* @return
*/
void saveImport(Person person);
}
mapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.excel.demo.mapper.PersonMapper">
<select id="getExportDatas" resultType="com.excel.demo.domain.Person">
select * from person
</select>
<insert id="saveImport" parameterType="com.excel.demo.domain.Person">
insert into person(name, sex, birthday)
values(#{name}, #{sex}, #{birthday})
</insert>
</mapper>
Service接口
package com.excel.demo.service;
import com.excel.demo.domain.Person;
import java.util.List;
public interface PersonService {
/**
* SpringBoot+easyPoi一行代码搞定excel导入导出
* @return
*/
List<Person> getExportDatas();
/**
* 保存导入的用户
* @return
*/
void saveImport(Person person);
}
service接口实现类!!
package com.excel.demo.service.impl;
import com.excel.demo.domain.Person;
import com.excel.demo.mapper.PersonMapper;
import com.excel.demo.service.PersonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class PersonServiceImpl implements PersonService
{
@Autowired
private PersonMapper personMapper;
@Override
public List<Person> getExportDatas() {
return personMapper.getExportDatas();
}
@Override
public void saveImport(Person person) {
personMapper.saveImport(person);
}
}
controller 控制层
package com.excel.demo.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.excel.demo.domain.Person;
import com.excel.demo.service.PersonService;
import com.excel.demo.utils.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;
@RestController
@RequestMapping("/common/excel")
public class ExcelController {
Logger log = LoggerFactory.getLogger(getClass());
@Autowired
private PersonService personService;
@GetMapping("/add")
public ModelAndView toadd(ModelAndView modelAndView){
modelAndView.setViewName("add");
return modelAndView;
}
/**
* 导出
* @param response
*/
@RequestMapping(value = "/export", method = RequestMethod.GET)
public void toExport(HttpServletResponse response) {
List<Person> exportDatas = personService.getExportDatas();
FileUtils.exportExcel(exportDatas, "测试导出 title" , "测试导出 sheet", Person.class, "测试导出.xls", response);
}
/**
* 导入
* @param file
*/
@RequestMapping(value = "/import", method = RequestMethod.POST)
public ModelAndView toImport(@RequestParam("file") MultipartFile file,ModelAndView modelAndView) {
//...
ImportParams importParams = new ImportParams();
importParams.setHeadRows(1);//表头行数,默认1
importParams.setTitleRows(1);//表格标题行数,默认0
importParams.setNeedVerify(false);// 需要验证
Person person = new Person();
try {
List<Person> users = ExcelImportUtil.importExcel(file.getInputStream(), Person.class, importParams);
for (Person user : users) {
person.setSex(user.getSex());
person.setName(user.getName());
person.setBirthday(user.getBirthday());
personService.saveImport(person);
}
modelAndView.setViewName("success");
} catch (IOException e) {
log.error("io exception: {}", e);
} catch (Exception e) {
log.error("exception: {}", e);
}
return modelAndView;
}
}
add.html 上传下载页面
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org" xmlns="http://www.w3.org/1999/html">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form action="/common/excel/import" method="post" enctype="multipart/form-data">
<input type="file" name="file"/>
<input type="submit" value="上传文件"/>
</form>
</br>
<input id="dowloadBtn" type="button" th:onclick="templateDowload()" value="下载">
</body>
<script>
//Excel模板下载
function templateDowload(){
window.location.href="/common/excel/export";
}
</script>
</html>
success.html页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>success!!!</h1>
</body>
</html>