快捷導(dǎo)航

Java實(shí)現(xiàn)上傳Excel文件并導(dǎo)入數(shù)據(jù)庫

更新時(shí)間：2021年09月01日 14:47:36 作者：山巔自相見

這篇文章主要介紹了在java的基礎(chǔ)上學(xué)習(xí)上傳Excel文件并導(dǎo)出到數(shù)據(jù)庫，感興趣的小伙伴不要錯(cuò)過奧

Java實(shí)現(xiàn)上傳Excel文件并導(dǎo)出到數(shù)據(jù)庫

1、導(dǎo)入依賴

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.2</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml-schemas</artifactId>
  <version>4.1.2</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.1.2</version>
</dependency>
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>1.16.20</version>
</dependency>

2、domain

實(shí)體類的字段根據(jù)自己Excel的表頭來決定，數(shù)據(jù)庫的表字段最好和實(shí)體類一一對(duì)應(yīng)

import lombok.Data;

// Excel實(shí)體類
@Data
public class Detailed {
  public Long id; // 主鍵
  public String auditDate; // 審核日期
  public Integer sceneId; // 場(chǎng)景id
  public String sceneName; // 場(chǎng)景名稱
  public String entityid; // entityid
  public Long housingResourcesId; // 房源id/實(shí)拍視頻id
  public Integer cityid; // cityid/城市id
  public String firstInstanceOA; // 初審oa/一審審核人oa
  public String firstInstance; // 初審方/一審核員部門名稱
  public String preliminaryResults; // 初審結(jié)果/一審審核結(jié)果
  public String reasonsForFirstInstance; // 初審原因/一審審核原因
  public String timeOfInitialExaminationAndStorage; // 初審入庫時(shí)間
  public String initialAuditTime; // 初審審核時(shí)間
  public Double shenheshichang; // shenheshichang
  public String timeoutStatus; // 超時(shí)狀態(tài)
  public String qualityInspectionResults; // 質(zhì)檢結(jié)果/質(zhì)檢審核結(jié)果
  public String qualityInspectionReasons; // 質(zhì)檢原因/質(zhì)檢審核原因
  public String qualificationStatus; // 質(zhì)檢合格原因
}

3、utils

獲取Excel表格中的數(shù)據(jù),其中// 獲取目標(biāo)單元格的值并存進(jìn)對(duì)象中根據(jù)自己的實(shí)體類進(jìn)行編寫

import com.iyunfish.tongcheng.domain.Detailed;
import org.apache.poi.ss.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {
  public static List<Detailed> excelToShopIdList(InputStream inputStream) throws IOException {
    Workbook workbook = WorkbookFactory.create(inputStream);
    inputStream.close();
    // 在工作簿獲取目標(biāo)工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 獲取到最后一行
    int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    // 該集合用來儲(chǔ)存行對(duì)象
    ArrayList<Detailed> detaileds = new ArrayList<>();
    // 遍歷整張表，從第二行開始，第一行的表頭不要，循環(huán)次數(shù)不大于最后一行的值
    for (int i = 1; i < physicalNumberOfRows; i++) {
      // 該對(duì)象用來儲(chǔ)存行數(shù)據(jù)
      Detailed detailed = new Detailed();
      // 獲取當(dāng)前行數(shù)據(jù)
      Row row = sheet.getRow(i);
      // 獲取目標(biāo)單元格的值并存進(jìn)對(duì)象中
      detailed.setAuditDate(row.getCell(0).getStringCellValue());
      detailed.setSceneId(Integer.valueOf(row.getCell(1).getStringCellValue()));
      detailed.setSceneName(row.getCell(2).getStringCellValue());
      detailed.setEntityid(row.getCell(3).getStringCellValue());
      detailed.setHousingResourcesId(Long.valueOf(row.getCell(4).getStringCellValue()));
      detailed.setCityid(Integer.valueOf(row.getCell(5).getStringCellValue()));
      detailed.setFirstInstanceOA(row.getCell(6).getStringCellValue());
      detailed.setFirstInstance(row.getCell(7).getStringCellValue());
      detailed.setPreliminaryResults(row.getCell(8).getStringCellValue());
      detailed.setReasonsForFirstInstance(row.getCell(9).getStringCellValue());
      detailed.setTimeOfInitialExaminationAndStorage(row.getCell(10).getStringCellValue());
      detailed.setInitialAuditTime(row.getCell(11).getStringCellValue());
      detailed.setShenheshichang(Double.valueOf(row.getCell(12).getStringCellValue()));
      detailed.setTimeoutStatus(row.getCell(13).getStringCellValue());
      detailed.setQualityInspectionResults(row.getCell(14).getStringCellValue());
      detailed.setQualityInspectionReasons(row.getCell(15).getStringCellValue());
      detailed.setQualificationStatus(row.getCell(16).getStringCellValue());
      // 把對(duì)象放到集合里
      detaileds.add(detailed);
      System.out.println("獲取到的當(dāng)前行數(shù)據(jù)：" + detailed);
    }
    return detaileds;
  }
}

4、Controller

數(shù)據(jù)庫表的id點(diǎn)上自增，這樣添加的時(shí)候就不用再添加id了

import com.iyunfish.tongcheng.domain.Detailed;
import com.iyunfish.tongcheng.service.ReadService;
import com.iyunfish.tongcheng.utils.ExcelUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;

@Controller
public class UploadController {
  @Autowired
  private ReadService readService;

  @RequestMapping("/file/upload")
  public void pubggupload(@RequestParam("file") MultipartFile file) throws Exception {
    String name = file.getOriginalFilename();
    if(name.length() < 5 || !name.substring(name.length() - 5).equals(".xlsx")) {
      throw new Exception("文件格式錯(cuò)誤");
    }
    // 獲取Excel中的數(shù)據(jù)
    List<Detailed> detaileds = ExcelUtils.excelToShopIdList(file.getInputStream());
    // 向數(shù)據(jù)庫遍歷添加數(shù)據(jù)庫
    for (int i = 0; i < detaileds.size(); i++) {
      // 獲取行信息
      Detailed detailed = detaileds.get(i);
      // 先根據(jù)eneityid查詢數(shù)據(jù)庫里有沒有一樣的，沒有就進(jìn)行添加
      List<Long> longs = readService.queryDetailedByEneityid(detailed.getEntityid());
      if (longs.size() <= 0) {
        readService.addDetailed(detailed);
      } else {
        System.out.println("error：該條信息已存在 message：" + detailed);
      }
    }
  }
}

邏輯層接口和實(shí)現(xiàn)類沒有邏輯，直接到控制層的xml文件

5、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.iyunfish.tongcheng.mapper.ReadMapper">
  <select id="queryDetailedByEneityid" parameterType="String" resultType="com.itZhao.domain.Detailed">
    SELECT
        id 
    FROM
        detailed 
    WHERE
        entityid = #{entityid}
  </select>

  <insert id="addDetailed" parameterType="com.iyunfish.tongcheng.domain.Detailed">
    INSERT INTO `tongcheng`.`detailed` (
      `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>

到此這篇關(guān)于Java實(shí)現(xiàn)上傳Excel文件并導(dǎo)入數(shù)據(jù)庫的文章就介紹到這了,更多相關(guān)Java上傳Excel文件導(dǎo)入數(shù)據(jù)庫內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: