Java实现上传Excel文件并导入数据库

目录

• Java实现上传Excel文件并导出到数据库

• 1、导入依赖

• 2、domain

• 3、utils

• 4、Controller

• 5、xml

Java实现上传Excel文件并导出到数据库

1、导入依赖

<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

实体类的字段根据自己Excel的表头来决定，数据库的表字段最好和实体类一一对应

import lombok.Data;

// Excel实体类
@Data
public class Detailed {
 public Long id; // 主键
 public String auditDate; // 审核日期
 public Integer sceneId; // 场景id
 public String sceneName; // 场景名称
 public String entityid; // entityid
 public Long housingResourcesId; // 房源id/实拍视频id
 public Integer cityid; // cityid/城市id
 public String firstInstanceOA; // 初审oa/一审审核人oa
 public String firstInstance; // 初审方/一审核员部门名称
 public String preliminaryResults; // 初审结果/一审审核结果
 public String reasonsForFirstInstance; // 初审原因/一审审核原因
 public String timeOfInitialExaminationAndStorage; // 初审入库时间
 public String initialAuditTime; // 初审审核时间
 public Double shenheshichang; // shenheshichang
 public String timeoutStatus; // 超时状态
 public String qualityInspectionResults; // 质检结果/质检审核结果
 public String qualityInspectionReasons; // 质检原因/质检审核原因
 public String qualificationStatus; // 质检合格原因
}

3、utils

获取Excel表格中的数据,其中// 获取目标单元格的值并存进对象中根据自己的实体类进行编写

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();
   // 在工作簿获取目标工作表
   Sheet sheet = workbook.getSheetAt(0);
   // 获取到最后一行
   int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
   // 该集合用来储存行对象
   ArrayList<Detailed> detaileds = new ArrayList<>();
   // 遍历整张表，从第二行开始，第一行的表头不要，循环次数不大于最后一行的值
   for (int i = 1; i < physicalNumberOfRows; i++) {
     // 该对象用来储存行数据
     Detailed detailed = new Detailed();
     // 获取当前行数据
     Row row = sheet.getRow(i);
     // 获取目标单元格的值并存进对象中
     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());
     // 把对象放到集合里
     detaileds.add(detailed);
     System.out.println("获取到的当前行数据：" + detailed);
   }
   return detaileds;
 }
}

4、Controller

数据库表的id点上自增，这样添加的时候就不用再添加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("文件格式错误");
   }
   // 获取Excel中的数据
   List<Detailed> detaileds = ExcelUtils.excelToShopIdList(file.getInputStream());
   // 向数据库遍历添加数据库
   for (int i = 0; i < detaileds.size(); i++) {
     // 获取行信息
     Detailed detailed = detaileds.get(i);
     // 先根据eneityid查询数据库里有没有一样的，没有就进行添加
     List<Long> longs = readService.queryDetailedByEneityid(detailed.getEntityid());
     if (longs.size() <= 0) {
       readService.addDetailed(detailed);
     } else {
       System.out.println("error：该条信息已存在 message：" + detailed);
     }
   }
 }
}

逻辑层接口和实现类没有逻辑，直接到控制层的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>

来源：https://www.jianshu.com/p/79ab13472cc6