springboot导入Excel表格数据到MySQL数据库/从数据库导出

SpringBoot-导入/导入Excel

所谓导出，就是把数据库中表的数据导出到 excel文件中；导入，就是把 excel文件中的数据导入到数据库表中。这功能类似数据库的导入导出功能，只是区别在于这个操作者是普通用户，是在浏览器操作的，使用excel更易于阅读。

Apache POI

Apache POI 是 Apache 软件基金会的开放源码函式库，POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能。

• poi中关于excel的概念

Workbook（对应为一个excel）

Sheet（excel中的表）

Row（表中的行）

Column（表中的列）

Cell（表中的单元格，由行号和列号组成）

• 添加依赖

该段代码可在：下文完整代码——pom.xml中找到

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
    <scope>compile</scope>
</dependency>

实现导入功能

将Excel中的数据导入MySQL数据库

这里为了方便操作数据库我集成了mybatis-plus

校验Excel版本

该段代码可在：下文完整代码——service——impl中找到

//校验上传的文件是否是Excel表格
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
    throw new BusinessException(CommonCodeMsg.IMPORT_TYPE_INVALID);
}
boolean isExcel2003 = true;
//判断Excel版本
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
    isExcel2003 = false;
}
InputStream is = null;
Workbook wb = null;
try {
    is = file.getInputStream();
    if (isExcel2003) {
        wb = new HSSFWorkbook(is);
    } else {
        wb = new XSSFWorkbook(is);
    }
} catch (IOException e) {
    e.printStackTrace();
}

核心代码

该段代码可在：下文完整代码——service——impl中找到

Sheet sheet = wb.getSheetAt(0);//读取第一页
int lastRowNum = sheet.getLastRowNum(); //获取最后一行的索引
for (int i = 1; i <= lastRowNum; i++) {
    Row row = sheet.getRow(i);
    row.getCell(0).setCellType(CellType.STRING);
    String sno = row.getCell(0).getStringCellValue();
    if (sno == null || sno.equals("")) {
        return "第"+ i+"行sno为空";
    }
    String sname = row.getCell(1).getStringCellValue();
    String ssex = row.getCell(2).getStringCellValue();
    int sage = (int) row.getCell(3).getNumericCellValue();
    String sdept = row.getCell(4).getStringCellValue();
    Student student = new Student();
}

完整代码

数据库表

CREATE TABLE `student` (
  `sno` char(4) NOT NULL,
  `sname` char(8) DEFAULT NULL,
  `ssex` char(2) DEFAULT NULL,
  `sage` smallint DEFAULT NULL,
  `sdept` char(20) DEFAULT NULL,
  PRIMARY KEY (`sno`),
  UNIQUE KEY `stusname` (`sname`),
  CONSTRAINT `student_chk_1` CHECK ((`ssex` in (_utf8mb4'男',_utf8mb4'女'))),
  CONSTRAINT `student_chk_2` CHECK (((0 < `sage`) < 150))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

pom.xml

<?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>cn.czh0123</groupId>
    <artifactId>spring-boot-operating-Excel</artifactId>
    <version>1.0.0</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <parent>
        <artifactId>spring-boot-starter-parent</artifactId>
        <groupId>org.springframework.boot</groupId>
        <version>2.3.2.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>
    </dependencies>

</project>

application.yaml

在resource包下创建一个文件application.yaml或application.yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/你的数据库名?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&tinyInt1isBit=false
    username: 你的用户名
    password: 你的密码
    driver-class-name: com.mysql.cj.jdbc.Driver

spring boot启动类

@SpringBootApplication
public class Run {
    public static void main(String[] args) {
        SpringApplication.run(Run.class, args);
    }
}

domain

新建一个domian包，在该包下新建一个类student

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("student")
public class Student {
    private String sno;
    private String sname;
    private String ssex;
    private Integer sage;
    private String sdept;
}

mapper

新建一个mapper包，在该包下新建一个接口StudentMapper

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
}

service

新建一个service包，在该包下新建一个接口IStudentService

public interface IStudentService extends IService<Student> {
    String importExcel(MultipartFile file);
}

impl

在service包下新建一个包impl，在该包下新建一个实现类StudentServiceImpl

• 这里为了省事，只对sno做了参数校验

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements IStudentService {
    @Override
    public String importExcel(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return "文件上传格式不正确";
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = null;
        Workbook wb = null;
        try {
            is = file.getInputStream();
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            Sheet sheet = wb.getSheetAt(0);//读取第一页
            int lastRowNum = sheet.getLastRowNum(); //获取最后一行的索引
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                row.getCell(0).setCellType(CellType.STRING);
                String sno = row.getCell(0).getStringCellValue();
                if (sno == null || sno.equals("")) {
                    return "第"+ i+"行sno为空"; 
                }
                String sname = row.getCell(1).getStringCellValue();
                String ssex = row.getCell(2).getStringCellValue();
                int sage = (int) row.getCell(3).getNumericCellValue();
                String sdept = row.getCell(4).getStringCellValue();
                Student student = new Student();
                student.setSno(sno);
                student.setSname(sname);
                student.setSsex(ssex);
                student.setSage(sage);
                student.setSdept(sdept);
                System.err.println(student);
                boolean isSuccess = this.save(student);
                if (!isSuccess) {
                    return "导入失败";
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "导入成功";
    }
}

controller

新建一个controller包，在该包下新建一个接口StudentController

@RestController
@RequestMapping("/students")
public class StudentController {

    @Resource
    private IStudentService studentService;

    @PostMapping("/importExcel")
    public Object importExcel(@RequestParam("file") MultipartFile file) {
        return studentService.importExcel(file);
    }
}

页面

在resource包下，新建一个static包，并且在该包下新建文件index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>操作Excel</title>
</head>
<body>
<form action="/students/importExcel" enctype="multipart/form-data" method="post">
    <input type="file" name="file">
    <input type="submit" value="导入">
</form>
</body>
</html>

运行

• 启动Spring Boot项目

---

• 新建一个Excel表格，如下图

---

• 浏览器访问localhost:8080/index.html，如下图
  

• 选择需要上传的Excel点击导入，导入后控制会会输出以下数据
  

---

• 查看数据库是否添加成功
  
  由上图可知，Excel数据已成功导入数据库

实现导出功能

未完待续。。。