cover

SpringBoot基础-Excel导入到数据库

poi导入Excel到数据库，上传文件

AGi_

1847人浏览 · 2022-05-18 09:50:27

AGi_ · 2022-05-18 09:50:27 发布

创建项目

创建项目，导入依赖

<dependencies>
        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
        <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>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.72</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</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>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

配置application.yml文件

mybatis:
  mapper-locations: classpath:/mybatis/**/*.xml
  type-aliases-package: com.example.note.domain
  map-underscore-to-camel-case: true
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  typeAliasesPackage: com.example.note.domain  #存放实体类的目录路径
  mapperLocations: classpath:mybatis/**/*.xml
  # 全局配置id自增  =>
  global-config:
    db-config:
      id-type: auto

server:
  port: 8080
spring:
  application:
    name: note
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    name: defaultDataSource
    password: '123456789'
    url: jdbc:mysql://localhost:3306/note?serverTimezone=UTC
    username: 'root'

准备数据

确定excel表结构(以下面的表为例)

创建数据库和对应的数据表

create database note;
use note;
CREATE TABLE `students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

编写项目

创建Student实体类

package com.example.note.student.domain;

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;

@Data
public class Student {
    @Value(value = "序号")
    private Long id;
    @Value(value = "姓名")
    private String name;
    @Value(value = "学号")
    private String number;
}

创建StudentMapper接口

package com.example.note.student.mapper;

import com.example.note.student.domain.Student;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface StudentMapper {
    public Integer batchInsert(@Param("students")List<Student> students);
}

创建StudentMapper.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.example.note.student.mapper.StudentMapper">
    <select id="batchInsert" resultType="com.example.noteback.student.domain.Student">
        insert into students (`name`,`number`) values
        <foreach collection="students" index="index" item="item" separator=",">
            (#{item.name},#{item.number})
        </foreach>
    </select>
</mapper>

创建IStudentService接口

package com.example.note.student.service;

import com.example.note.student.domain.Student;

import java.util.List;

public interface IStudentService {
    public Integer batchInsert(List<Student> students);
}

创建StudentServiceImpl

package com.example.note.student.service.impl;

import com.example.note.student.domain.Student;
import com.example.note.student.mapper.StudentMapper;
import com.example.note.student.service.IStudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
@Service
public class StudentServiceImpl implements IStudentService {
    @Autowired
    private StudentMapper studentMapper;
    @Override
    public Integer batchInsert(List<Student> students) {
        return this.studentMapper.batchInsert(students);
    }
}

数据准备完成

上传文件

编写上传文件方法(在controller里面写)

@Controller
@RequestMapping("student")
public class StudentController {
    @Autowired
    private StudentServiceImpl studentService;

    @PostMapping("/upload")
    @ResponseBody
    public String uploadFile(@RequestParam("file") MultipartFile file) throws Exception {
        try {
            // 上传文件路径
            String filePath = "E:\\DATA\\SHIXUN2\\data\\";
            // 为了防止文件名冲突，获取当前时间+文件原名生成新的文件名
            String fileName=System.currentTimeMillis()+file.getOriginalFilename();

            // 上传文件
            File f=new File(filePath+fileName);
            file.transferTo(f);
            fileName=filePath+fileName;
            
            return "ok";
            

        } catch (Exception e) {
            return "failure";
        }
    }
}

前端代码:

<!DOCTYPE html>
<html xmlns="http://www.thymeleaf.org"></html>
<head>
    <meta charset="UTF-8">
    <title>导入Excel</title>
</head>
<body>
    <h1>选择Excel</h1>
    <form th:action="@{/student/upload}" method="post" enctype="multipart/form-data">
        <!-- name 的值与@RequestParam的值相同-->
        <input type="file" name="file"/><br/>
        <input type="submit" value="上传"/><br/>
    </form>

</body>
</html>

POI导入Excel到数据库

编写excel导入方法

@Controller
@RequestMapping("student")
public class StudentController {
    @Autowired
    private StudentServiceImpl studentService;

    @PostMapping("/upload")
    @ResponseBody
    public String uploadFile(@RequestParam("file") MultipartFile file) throws Exception {
        try {
            // 上传文件路径
            String filePath = "E:\\DATA\\SHIXUN2\\data\\";
            // 为了防止文件名冲突，获取当前时间+文件原名生成新的文件名
            String fileName=System.currentTimeMillis()+file.getOriginalFilename();

            // 上传文件
            File f=new File(filePath+fileName);
            file.transferTo(f);
            fileName=filePath+fileName;
            
            //输入流，获取刚刚上传文件转成输入流
            FileInputStream fileInputStream = new FileInputStream(new File(fileName));
            //定义一个list变量，模拟excel结构
            List<List<Object>> list = ExcelUtils.getListByExcel(fileInputStream, fileName);
            //定义firstRows变量，用来获取第一行，就是标题，每列名字
            List<Object> firstRows = null;
            //定义studentList变量，用来存储文件内容(学生信息)
            List<Student> studentList=new ArrayList<>();
            //如果 list 不为空，大小大于0则获取第一行存到firstRows 里面
            if (list != null && list.size() > 0) {
                firstRows = list.get(0);
            }else {
            //否则返回 failure 
                return "failure";
            }
            //对list进行遍历，因为第一行是标题，不用存到数据库，所以从第二行开始遍历
            for (int i = 1; i < list.size(); i++) {
                //获取第i行数据
                List<Object> rows = list.get(i);
                //定义student遍历，存储第i行数据 
                Student student = new Student();
                //对第i行数据进行遍历，
                for (int j = 0; j < rows.size(); j++) {
                    //获取第i行第j列数据，存到cellVal 变量里面
                    String cellVal = (String) rows.get(j);
                    //调用setFileValueByFieldName函数，把数据存到student对应的属性里面
                    ExcelUtils.setFileValueByFieldName(student, firstRows.get(j).toString().trim(), cellVal);
                }
                //把student变量加到studentList
                studentList.add(student);
            }
            //调用批量插入方法，把数据存到数据库
            Integer flag=this.studentService.batchInsert(studentList);
            if(flag!=0){
                return "ok";
            }else {
                return "failure";
            }
            

        } catch (Exception e) {
            return "failure";
        }
    }
}

编写getListByExcel方法

public class ExcelUtils {
    //上传的是以.xls后缀的
    private final static String excel2003L =".xls";
    //上传的是以.xlsx后缀的
    private final static String excel2007U =".xlsx";

    public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
        //创建 list 模拟Excel表结构
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;  //页数
        Row row = null;  //行数
        Cell cell = null;  //列数
      
        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet页
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    //获取第i页第j行第y列的值
                    cell = row.getCell(y);
                    li.add(getValue(cell));
                }
                list.add(li);
            }
        }
        return list;
    }
    public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
        Workbook wb = null;
        //获取Excel后缀
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        //跟据后缀创建工作簿
        if(excel2003L.equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if(excel2007U.equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }
    public static String getValue(Cell cell) {
        String value = "";
        if(null==cell){
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = format.format(date);;
                }else {// 纯数字
                    BigDecimal big=new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                    //解决1234.0  去掉后面的.0
                    if(null!=value&&!"".equals(value.trim())){
                        String[] item = value.split("[.]");
                        if(1<item.length&&"0".equals(item[1])){
                            value=item[0];
                        }
                    }
                }
                break;
            //字符串类型
            case STRING:
                //如果是string类型进行下划线转驼峰处理
                value = cell.getStringCellValue();
                break;
            // 公式类型
            case FORMULA:
                //读公式计算值
                value = String.valueOf(cell.getNumericCellValue());
                if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getStringCellValue();
                }
                break;
            // 布尔类型
            case BOOLEAN:
                value = " "+ cell.getBooleanCellValue();
                break;
            default:
                value = cell.getStringCellValue();
        }
        if("null".endsWith(value.trim())){
            value="";
        }
        return value;
    }
}

在ExcelUtils类里面编写setFileValueByFieldName方法

public static void setFileValueByFieldName(Object object, String fieldName, Object val) {
        //获取object类的所有属性
        Field[] fields = object.getClass().getDeclaredFields();
        try {
            //对所有属性进行遍历
            for (int i = 0; i < fields.length; i++) {
                //获取第i个属性
                Field field = fields[i];
                //如果Excel的某一列的列名(fieldName)与
                //在第i个属性上面添加的@Value注解的value一样，进行下面的操作
                if (fieldName.equals(field.getAnnotation(Value.class).value())) {
                    //如果field为私有属性，也可以对它进行操作
                    field.setAccessible(true);
                    //获取该属性的数据类型，如果是Integer类型的
                    if (field.getType() == Integer.class) {
                        //把val转成Integer存到该属性里面
                        field.set(object, Integer.valueOf(val.toString()));
                    } else if (field.getType() == Long.class) {
                        field.set(object, Long.valueOf(val.toString()));
                    //如果时LocalDateTime类型的
                    } else if (field.getType() == LocalDateTime.class) {
                        //先把他格式化
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                        LocalDateTime time = LocalDateTime.parse(val.toString(), formatter);            
                        field.set(object, time);
                    //如果其他类型的直接存
                    } else {
                        field.set(object, val);
                    }
                    return;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

通过HPA+CronHPA组合应对业务复杂弹性伸缩场景

华为云开发者联盟

cover

FT-FMEA融合混沌演练，零售运营系统韧性架构在线验证实践

华为云开发者联盟

cover

如何使用Python和Plotly绘制3D图形

华为云开发者联盟

所有评论(0)

查看更多评论

AGi_

@weixin_46899412

已为社区贡献6条内容