Java实现读取本地Excel并将数据加入数据库

2021-06-10 本文已影响0人山巅自相见

废话不多说，直接上代码，简单易懂，好好学

架构：SpringBoot+Mybatis
数据库：Mysql5
导入依赖，版本随便

<!--poi-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.15-beta2</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>1.18.20</version>
  <scope>provided</scope>
</dependency>

实体类：每个字段对应Excel表格中的一个表头字段名字，实际字段根据自己的实际情况

import lombok.Data;
import java.io.Serializable;

@Data
public class Detailed implements Serializable {
  public Long id;
  public String auditDate;
  public Integer sceneId;
  public String sceneName;
  public String entityid;
  public Long housingResourcesId;
  public Integer cityid;
  public String firstInstanceOA;
  public String firstInstance;
  public String preliminaryResults;
  public String reasonsForFirstInstance;
  public String timeOfInitialExaminationAndStorage;
  public String initialAuditTime;
  public Double shenheshichang;
  public String timeoutStatus;
  public String qualityInspectionResults;
  public String qualityInspectionReasons;
  public String qualificationStatus;
}

两个utils：
- ReadLocalFileUtils：获取本地指定文件夹下的文件，文件夹里必须都是Excel文件

import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class ReadLocalFileUtils {
  public static List getFile(String path) {
    // 创建集合，用来储存文件名称
    ArrayList<Object> objects = new ArrayList<>();
    // 获取路径所在的文件列表
    File file = new File(path);
    // 获取文件夹列表
    File[] array = file.listFiles();
    for (int i = 0; i < array.length; i++) {
      if (array[i].isFile()) {
        objects.add(array[i]);
      }
    }
    return objects;
  }
}

- XlsxUtils：读取Excel中的数据。这里的// 获取目标单元格的值并存进对象中根据自己的实体类进行编写，实体类是String类型的不用转类型，不是得得转类型

import com.iyunfish.tongcheng.domain.Detailed;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class XlsxUtils {
  public static List<Detailed> read(String path) throws IOException {
    // 建立输入流
    FileInputStream fileInputStream = new FileInputStream(path);
    // 在输入流中获取工作簿
    XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
    // 在工作簿获取目标工作表
    XSSFSheet sheetAt = workbook.getSheetAt(0);
    // 获取到最后一行
    int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
    // 创建集合，用来储存储存行的对象
    ArrayList<Detailed> detaileds = new ArrayList<>();
    // 遍历整张表的所有行，从第二行开始，第一行表头不要，所以循环的下表从1开始
    for (int i = 1; i < physicalNumberOfRows; i++) {
      // 创建对象用来存行数据
      Detailed detailed = new Detailed();
      // 获取当前行数据，不是行数
      XSSFRow row1 = sheetAt.getRow(i);
      // 获取目标单元格的值并存进对象中
      detailed.setAuditDate(row1.getCell(0).getStringCellValue());
      detailed.setSceneId(Integer.valueOf(row1.getCell(1).getStringCellValue()));
      detailed.setSceneName(row1.getCell(2).getStringCellValue());
      detailed.setEntityid(row1.getCell(3).getStringCellValue());
      detailed.setHousingResourcesId(Long.valueOf(row1.getCell(4).getStringCellValue()));
      detailed.setCityid(Integer.valueOf(row1.getCell(5).getStringCellValue()));
      detailed.setFirstInstanceOA(row1.getCell(6).getStringCellValue());
      detailed.setFirstInstance(row1.getCell(7).getStringCellValue());
      detailed.setPreliminaryResults(row1.getCell(8).getStringCellValue());
      detailed.setReasonsForFirstInstance(row1.getCell(9).getStringCellValue());
      detailed.setTimeOfInitialExaminationAndStorage(row1.getCell(10).getStringCellValue());
      detailed.setInitialAuditTime(row1.getCell(11).getStringCellValue());
      detailed.setShenheshichang(Double.valueOf(row1.getCell(12).getStringCellValue()));
      detailed.setTimeoutStatus(row1.getCell(13).getStringCellValue());
      detailed.setQualityInspectionResults(row1.getCell(14).getStringCellValue());
      detailed.setQualityInspectionReasons(row1.getCell(15).getStringCellValue());
      detailed.setQualificationStatus(row1.getCell(16).getStringCellValue());
      if (detailed != null) {
        // 把对象放到集合里
        detaileds.add(detailed);
      }
    }
    // 返回集合
    return detaileds;
  }
}

Controller层。

import com.iyunfish.tongcheng.domain.Detailed;
import com.iyunfish.tongcheng.service.ReadService;
import com.iyunfish.tongcheng.utils.ReadLocalFileUtils;
import com.iyunfish.tongcheng.utils.XlsxUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.io.IOException;
import java.util.List;

@RestController
public class ExcelReadController {
  @Autowired
  private ReadService readService;

  @RequestMapping("/read/add")
  // 参数path是存放.xlsx文件的路径
  public void add(String path) throws IOException {
    // 获取指定文件夹下的所有文件
    List file = ReadLocalFileUtils.getFile(path);
    // 循环遍历每个文件
    for (int i = 0; i < file.size(); i++) {
      // 获取excel中的数据 集合里放n个对象，对象里放一行excel信息
      List<Detailed> read = XlsxUtils.read(file.get(i).toString());
      // 循环向数据库添加信息
      for (int a = 0; a < read.size(); a++) {
        // 获取到当前行的信息
        Detailed detailed = read.get(a);
        // 添加
        readService.addDetailed(detailed);
      }
    }
  }
}

往后的接口实现类就不写了，没有逻辑，一直到xml文件

ReadMapper.xml。添加的字段和XXX根据自己的实际情况编写

<?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="XXX">
    <insert id="XXX" parameterType="com.iyunfish.tongcheng.domain.Detailed">
        INSERT INTO XXX (
            `audit_date`,
            `scene_id`,
            `scene_name`,
            `entityid`,
            `housing_resources_id`,
            `cityid`,
            `firstInstanceOA`,
            `firstInstance`,
            `preliminary_results`,
            `reasons_for_firstInstance`,
            `time_ofInitial_examination_and_storage`,
            `initial_audit_time`,
            `shenheshichang`,
            `timeout_status`,
            `quality_inspection_results`,
            `quality_inspection_reasons`,
            `qualification_status`
        )
        VALUES
         (#{read.auditDate}, #{read.sceneId}, #{read.sceneName}, #{read.entityid}, #{read.housingResourcesId}, #{read.cityid}, #{read.firstInstanceOA}, #{read.firstInstance}, #{read.preliminaryResults}, #{read.reasonsForFirstInstance}, #{read.timeOfInitialExaminationAndStorage}, #{read.initialAuditTime}, #{read.shenheshichang}, #{read.timeoutStatus}, #{read.qualityInspectionResults}, #{read.qualityInspectionReasons}, #{read.qualificationStatus})
    </insert>
</mapper>

这样写完了直接运行会有报错，因为请求参数是个路径，例如：D:\a。参数里面有特殊符号，需要在启动类添加一个@Bean就好了，详情看我的另一篇文章：https://www.jianshu.com/p/79e57e4f0f71
代码到此结束，给个关注！

Java实现读取本地Excel并将数据加入数据库

猜你喜欢

热点阅读