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

<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>

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; // 质检合格原因

}

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;

   }

}

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 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>