Excel导入导出功能
这个项目中excel版本需要是2007+版本，该程序在office 2016上测试通过，导出的excel文件后缀是.xls，导入的excel文件后缀是.xlsx
先看一下项目整体架构
一、下载Maven依赖，这里给出pom文件，注意<resources>
<?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.5.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.zk</groupId>
<artifactId>thymeleaf</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-thymeleaf-excel</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--thymeleaf-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
<version>2.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>net.sourceforge.nekohtml</groupId>
<artifactId>nekohtml</artifactId>
<version>1.9.22</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<!-- maven项目中src源代码下的xml等资源文件编译进classes文件夹，
注意：如果没有这个，它会自动搜索resources下是否有mapper.xml文件，
如果没有就会报org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.pet.mapper.PetMapper.selectByPrimaryKey-->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<!--将resources目录下的配置文件编译进classes文件 -->
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
</build>
</project>
二、application.properties（核心配置文件）
#修改端口号 server.port=8096 ####MyBatis配置 #数据库驱动 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver #数据库连接地址 spring.datasource.url=jdbc:mysql://localhost:3306/springboot-integration-example?useSSL=false& #数据库用户名 spring.datasource.username=root #数据库用户密码 spring.datasource.password=root #映射文件的位置 mybatis.mapper-locations=classpath:com/zk/thymeleaf/dao/**/*Dao.xml #类型别名 mybatis.type-aliases-package=com.zk.thymeleaf.entity #####Thymeleaf配置文件 spring.thymeleaf.cache=false #spring.thymeleaf.mode=HTML spring.thymeleaf.mode=LEGACYHTML5 #编码 spring.thymeleaf.encoding=UTF-8 spring.thymeleaf.check-template=true #类型 spring.thymeleaf.servlet.content-type=text/html #前缀 spring.thymeleaf.prefix=classpath:/templates/ #后缀 spring.thymeleaf.suffix=.html #日志 logging.level.root=info logging.level.com.zk.thymeleaf.dao=debug
三、Controller
package com.zk.thymeleaf.controller;
import com.zk.thymeleaf.service.CarService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
@Controller
@RequestMapping(value = "springboot")
public class CarController {
@Autowired
private CarService carService;
@RequestMapping("/index")
public String carList() {
return "car";
}
// 将excel导入到数据库
@RequestMapping("/insertCarByExcel")
public String insertCarByExcel(@RequestParam("file") MultipartFile multipartFile, ModelMap map) {
Integer integer = carService.insertCarByExcel(multipartFile);
if (integer > 0) {
map.addAttribute("msg", "通过Excel插入成功！");
return "success";
}
map.addAttribute("msg", "通过Excel插入失败！");
return "success";
}
// 将数据库导出成excel
@RequestMapping("/exportCarByExcel")
public void exportCarByExcel(HttpServletResponse response) {
HSSFWorkbook workbook = carService.exportExcel();
// 获取输出流
OutputStream os = null;
try {
// 获取输出流
os = response.getOutputStream();
// 重置输出流
response.reset();
// 设定输出文件头
response.setHeader("Content-disposition",
"attachment; filename=" + new String("car".getBytes("GB2312"), "8859_1") + ".xls");
// 定义输出类型
response.setContentType("application/msexcel");
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
assert os != null;
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
四、CarService
package com.zk.thymeleaf.service;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
public interface CarService {
Integer insertCarByExcel(MultipartFile multipartFile);
HSSFWorkbook exportExcel();
}
五、CarServiceImpl
package com.zk.thymeleaf.service.impl;
import com.zk.thymeleaf.dao.CarDao;
import com.zk.thymeleaf.entity.Car;
import com.zk.thymeleaf.service.CarService;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
@Service
public class CarServiceImpl implements CarService {
@Autowired
private CarDao carDao;
@Override
public Integer insertCarByExcel(MultipartFile multipartFile) {
List<Car> carList = new ArrayList<>();
try {
// 创建都Excel工作簿文件的引用
XSSFWorkbook sheets = new XSSFWorkbook(multipartFile.getInputStream());
// 获取Excel工作表总数
int numberOfSheets = sheets.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
XSSFSheet sheet = sheets.getSheetAt(i);
for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
Car car = new Car();
for (int k = 1; k < sheet.getRow(j).getPhysicalNumberOfCells(); k++) {
DataFormatter dataFormatter = new DataFormatter();
String stringCellValue = dataFormatter.formatCellValue(sheet.getRow(j).getCell(k));
switch (k) {
case 1:
car.setName(stringCellValue);
break;
case 2:
car.setPrice(Integer.parseInt(stringCellValue));
break;
case 3:
car.setColour(stringCellValue);
break;
case 4:
car.setBrand(stringCellValue);
break;
}
}
carList.add(car);
}
}
} catch (IOException e) {
e.printStackTrace();
}
return carDao.insertCar(carList);
}
@Override
public HSSFWorkbook exportExcel() {
// 创建Execl工作薄
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表
HSSFSheet sheet = hssfWorkbook.createSheet("car");
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(new HSSFRichTextString("主键(id)"));
row.createCell(1).setCellValue(new HSSFRichTextString("名称(name)"));
row.createCell(2).setCellValue(new HSSFRichTextString("价格(price)"));
row.createCell(3).setCellValue(new HSSFRichTextString("颜色(colour)"));
row.createCell(4).setCellValue(new HSSFRichTextString("品牌(brand)"));
List<Car> cars = carDao.carList();
Iterator<Car> iterator = cars.iterator();
int num = 1;
while (iterator.hasNext()) {
Car car = iterator.next();
HSSFRow rowNum = sheet.createRow(num);
rowNum.createCell(0).setCellValue(new HSSFRichTextString(car.getId().toString()));
rowNum.createCell(1).setCellValue(new HSSFRichTextString(car.getName()));
rowNum.createCell(2).setCellValue(new HSSFRichTextString(car.getPrice().toString()));
rowNum.createCell(3).setCellValue(new HSSFRichTextString(car.getColour()));
rowNum.createCell(4).setCellValue(new HSSFRichTextString(car.getBrand()));
num++;
}
return hssfWorkbook;
}
}
六、CarDao
package com.zk.thymeleaf.dao;
import com.zk.thymeleaf.entity.Car;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface CarDao {
/**
* 查询全部的车信息
*
* @return
*/
List<Car> carList();
/**
* 批量添加车信息
*
* @param cars
* @return
*/
Integer insertCar(List<Car> cars);
}
七、CarDao.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.zk.thymeleaf.dao.CarDao">
<select id="carList" resultType="com.zk.thymeleaf.entity.Car">
select id,name,price,colour,brand from car
</select>
<insert id="insertCar">
insert into car values
<foreach collection="list" separator="," item="item">
(0,#{item.name},#{item.price},#{item.colour},#{item.brand})
</foreach>
</insert>
</mapper>
八、Entity
package com.zk.thymeleaf.entity;
import lombok.Data;
@Data
public class Car {
private Integer id;
private String name;
private Integer price;
private String colour;
private String brand;
}
九、前端
1、car.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>首页</title>
</head>
<body>
<h1>导入的excel表头必须和数据库字段一致</h1>
<form action="/springboot/insertCarByExcel" enctype="multipart/form-data" method="post">
<input type="file" name="file">
<input type="submit" value="导入"/>
</form>
<br>
<input type="button" value="导出" onclick="exportExcel()"/>
<script>
function exportExcel() {
window.location.href = "/springboot/exportCarByExcel"
}
</script>
</body>
</html>
2、success.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>success</title>
</head>
<body>
<h1>success</h1>
<h1 th:text="${msg}"></h1>
</body>
</html>
十、数据库脚本
DROP TABLE IF EXISTS `car`; CREATE TABLE `car` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(255) DEFAULT NULL COMMENT '名称', `price` int(11) DEFAULT NULL COMMENT '价格', `colour` varchar(255) DEFAULT NULL COMMENT '颜色', `brand` varchar(255) DEFAULT NULL COMMENT '品牌', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='汽车'; LOCK TABLES `car` WRITE; INSERT INTO `car` VALUES (1,'东风',150,'黑色','东风'),(2,'丰田',100,'白色','丰田'),(3,'本田',120,'蓝色','本田'),(4,'东风',150,'黑色','东风'),(5,'丰田',100,'白色','丰田'),(6,'本田',120,'蓝色','本田'),(7,'东风',150,'黑色','东风'); UNLOCK TABLES;
now ,fight for future
