1.首先新建一个spring boot项目（忽略不写了）
yml文件：
server: port: 8081 spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true & characterEncoding=utf-8 & useSSL=true & serverTimezone=Asia/Shanghai username: root password: 123456 mybatis: mapper-locations: classpath:/mapper/*.xml type-aliases-package: com.xiangshu.test.entity
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.5.6</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.xiangshu</groupId> <artifactId>test</artifactId> <version>0.0.1-SNAPSHOT</version> <name>test</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-jdbc</artifactId> </dependency> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </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.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.0</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </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> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
2.编辑实体类（要和数据库的表结构一致）
set和get方法快捷键是alt+insert,篇幅太长 ，这里忽略，
package com.xiangshu.test.entity; /** * @author Javen * @Email zyw205@gmail.com * */ public class StuEntity { private Integer id; private String code; private String name; private String type; private String parent_code; private String create_time; private String code_2020; private String name_2020; private String lon; private String lat; }
3.便捷dao层也就是mapper
package com.xiangshu.test.dao; import com.xiangshu.test.entity.StuEntity; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface StuDao { Integer insert(List<StuEntity> list); List<StuEntity> stuList(); Integer updata(List<StuEntity> list); Integer deleteById(Integer id); }
4.编辑对应的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.xiangshu.test.dao.StuDao"> <insert id="insert" parameterType="java.util.ArrayList"> insert into `stu`(code,name,type,parent_code,create_time,code_2020,name_2020,lon,lat) values <foreach collection="list" index="index" separator="," item="item"> (#{item.code},#{item.name},#{item.type},#{item.parent_code},#{item.create_time},#{item.code_2020},#{item.name_2020},#{item.lon},#{item.lat}) </foreach> </insert> <select id="stuList" resultType="stuEntity"> select id,code,name,type,parent_code,create_time,code_2020,name_2020,lon,lat from stu </select> <delete id="deleteById"> delete from stu where id = #{id,jdbcType=NUMERIC} </delete> <!--批量更新--> <insert id="updata" parameterType="java.util.ArrayList"> insert into `stu`(id,code,name,type,parent_code,create_time,code_2020,name_2020,lon,lat) values <foreach collection="list" index="index" separator="," item="item"> (#{item.id},#{item.code},#{item.name},#{item.type},#{item.parent_code},#{item.create_time},#{item.code_2020},#{item.name_2020},#{item.lon},#{item.lat}) </foreach> ON DUPLICATE KEY UPDATE id=VALUES(id),code=VALUES(code),name=VALUES(name),type=VALUES(type),parent_code=VALUES(parent_code),create_time=VALUES(create_time),code_2020=VALUES(code_2020),name_2020=VALUES(name_2020),lon=VALUES(lon),lat=VALUES(lat) </insert> </mapper>
5.编辑service业务层 是两种方式的对Excel表操作，一种是jxl包下的方法(可能需要手动添加maven，去pom文件添加)，另外一种是POI目前比较主流的包
package com.xiangshu.test.service; import com.xiangshu.test.dao.StuDao; import com.xiangshu.test.entity.StuEntity; import jxl.Sheet; import jxl.Workbook; 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.poifs.filesystem.POIFSFileSystem; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; /** * @author Javen * @Email zyw205@gmail.com */ @Service public class StuService { @Autowired private StuDao stuDao; /** * 查询指定目录中电子表格中所有的数据 * * @param file 文件完整路径 * @return */ public Integer getAllByExcel(String file) { List<StuEntity> list = new ArrayList<StuEntity>(); try { Workbook rwb = Workbook.getWorkbook(new File(file)); Sheet rs = rwb.getSheet(0);//或者rwb.getSheet(0) int clos = rs.getColumns();//得到所有的列 int rows = rs.getRows();//得到所有的行 System.out.println(clos + " rows:" + rows); for (int i = 1; i < rows; i++) { for (int j = 0; j < clos; j++) { //第一个是列数，第二个是行数 String id = rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++ String code = rs.getCell(j++, i).getContents(); String name = rs.getCell(j++, i).getContents(); String type = rs.getCell(j++, i).getContents(); String parent_code = rs.getCell(j++, i).getContents(); String create_time = rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++ String code_2020 = rs.getCell(j++, i).getContents(); String name_2020 = rs.getCell(j++, i).getContents(); String lon = rs.getCell(j++, i).getContents(); String lat = rs.getCell(j++, i).getContents(); System.out.println("id:" + id + " name:" + name + " code:" + code + " type:" + type + " parent_code:" + parent_code + " create_time:" + create_time + " code_2020:" + code_2020 + " name_2020:" + name_2020 + " lon:" + lon + " lat:" + lat); list.add(new StuEntity(Integer.parseInt(id), code, name, type, parent_code, create_time, code_2020, name_2020, lon, lat)); } } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return stuDao.insert(list); } public HSSFWorkbook exportExcel() throws IOException { // 创建Execl工作薄 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); // 在Excel工作簿中建一工作表 HSSFSheet sheet = hssfWorkbook.createSheet("stu"); HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue(new HSSFRichTextString("id")); row.createCell(1).setCellValue(new HSSFRichTextString("code")); row.createCell(2).setCellValue(new HSSFRichTextString("name")); row.createCell(3).setCellValue(new HSSFRichTextString("type")); row.createCell(4).setCellValue(new HSSFRichTextString("parent_code")); row.createCell(5).setCellValue(new HSSFRichTextString("create_time")); row.createCell(6).setCellValue(new HSSFRichTextString("code_2020")); row.createCell(7).setCellValue(new HSSFRichTextString("name_2020")); row.createCell(8).setCellValue(new HSSFRichTextString("lon")); row.createCell(9).setCellValue(new HSSFRichTextString("lat")); List<StuEntity> list = stuDao.stuList(); Iterator<StuEntity> iterator = list.iterator(); int num = 1; while (iterator.hasNext()) { StuEntity stuEntity = iterator.next(); HSSFRow rowNum = sheet.createRow(num); rowNum.createCell(0).setCellValue(new HSSFRichTextString(stuEntity.getId().toString())); rowNum.createCell(1).setCellValue(new HSSFRichTextString(stuEntity.getCode())); rowNum.createCell(2).setCellValue(new HSSFRichTextString(stuEntity.getName())); rowNum.createCell(3).setCellValue(new HSSFRichTextString(stuEntity.getType())); rowNum.createCell(4).setCellValue(new HSSFRichTextString(stuEntity.getParent_code())); rowNum.createCell(5).setCellValue(new HSSFRichTextString(stuEntity.getCreate_time())); rowNum.createCell(6).setCellValue(new HSSFRichTextString(stuEntity.getCode_2020())); rowNum.createCell(7).setCellValue(new HSSFRichTextString(stuEntity.getName_2020())); rowNum.createCell(8).setCellValue(new HSSFRichTextString(stuEntity.getLon())); rowNum.createCell(9).setCellValue(new HSSFRichTextString(stuEntity.getLat())); num++; } return hssfWorkbook; } public int deleteById(Integer id) { return stuDao.deleteById(id); } //更新操作 public Integer updataExcel(String file) { List<StuEntity> list = new ArrayList<StuEntity>(); try { Workbook rwb = Workbook.getWorkbook(new File(file)); Sheet rs = rwb.getSheet(0);//或者rwb.getSheet(0) int clos = rs.getColumns();//得到所有的列 int rows = rs.getRows();//得到所有的行 System.out.println(clos + " rows:" + rows); for (int i = 1; i < rows; i++) { for (int j = 0; j < clos; j++) { //第一个是列数，第二个是行数 String id = rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++ String code = rs.getCell(j++, i).getContents(); String name = rs.getCell(j++, i).getContents(); String type = rs.getCell(j++, i).getContents(); String parent_code = rs.getCell(j++, i).getContents(); String create_time = rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++ String code_2020 = rs.getCell(j++, i).getContents(); String name_2020 = rs.getCell(j++, i).getContents(); String lon = rs.getCell(j++, i).getContents(); String lat = rs.getCell(j++, i).getContents(); System.out.println("id:" + id + " name:" + name + " code:" + code + " type:" + type + " parent_code:" + parent_code + " create_time:" + create_time + " code_2020:" + code_2020 + " name_2020:" + name_2020 + " lon:" + lon + " lat:" + lat); list.add(new StuEntity(Integer.parseInt(id), code, name, type, parent_code, create_time, code_2020, name_2020, lon, lat)); } } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return stuDao.updata(list); } }
最后controller层
package com.xiangshu.test.controller; import com.xiangshu.test.service.StuService; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; @RestController public class StuController { @Autowired private StuService stuService; @RequestMapping("/Excel") public void insertCarByExcel() { String file = "C:\Users\Administrator\Desktop\xiangshu\indicator_administrative.xls"; //ModelAndView modelAndView = new ModelAndView("complete"); Integer integer = stuService.getAllByExcel(file); if (integer > 0) { System.out.println("导入成功"); } } @PostMapping("/Excel1") public String insertCarByExcel1(String file) { Integer integer = stuService.getAllByExcel(file); if (integer > 0) { System.out.println("导入成功"); return "导入成功"; }else { return "导入失败"; } } // 将数据库导出成excel @RequestMapping("/exportCarByExcel") public void exportCarByExcel(HttpServletResponse response) throws IOException { HSSFWorkbook workbook = stuService.exportExcel(); // 获取输出流 OutputStream os = null; try { // 获取输出流 os = response.getOutputStream(); // 重置输出流 response.reset(); // 设定输出文件头 response.setHeader("Content-disposition", "attachment; filename=" + new String("stu".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(); } } } //删除 @RequestMapping("/deleteById/{id}") public void deleteById(@PathVariable("id") Integer id){ stuService.deleteById(id); } @PostMapping("/Excel2") public String updataExcel1(String file) { Integer integer = stuService.updataExcel(file); if (integer > 0) { System.out.println("更新成功"); return "更新成功"; }else { return "导入失败"; } } }
最后通过postman测试一下即可
----》可能会出现测试传参的时候抱错，是Tomcat的问题 另一篇博客有记载
mysql对应的表结构：
CREATE TABLE `stu` ( `id` int(255) UNSIGNED NOT NULL AUTO_INCREMENT, `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `parent_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `code_2020` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name_2020` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `lon` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `lat` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 29787 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;
如果需要Excel测试，私信吧（私信直接发邮箱）
目录结构
