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

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

目录java实现上传Excel文件并导出到数据库1、导入依赖2、domain3、utils4、Controller5、xml

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

1、导入依赖

org.apache.poi

poi-ooxml

4.1.2

org.apache.poi

poi-ooxml-schemas

4.1.2

org.apache.poi

poi

4.1.2

org.projectlombok

lombok

1.16.20

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 excelToShopIdList(InputStream inputStream) throws IOException {

Workbook workbook = WorkbookFactory.create(inputStream);

inputStream.close();

// 在工作簿获取目标工作表

Sheet sheet = workbook.getSheetAt(0);

// 获取到最后一行

int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

// 该集合用来储存行对象

ArrayList 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 detaileds = ExcelUtils.excelToShopIdList(file.getInputStream());

// 向数据库遍历添加数据库

for (int i = 0; i < detaileds.size(); i++) {

// 获取行信息

Detailed detailed = detaileds.get(i);

// 先根据eneityid查询数据库里有没有一样的，没有就进行添加

List longs = readService.queryDetailedByEneityid(detailed.getEntityid());

if (longs.size() <= 0) {

readService.addDetailed(detailed);

} else {

System.out.println("error：该条信息已存在 message：" + detailed);

}

}

}

}

逻辑层接口和实现类没有逻辑，直接到控制层的xml文件

5、xml

SELECT

id

FROM

detailed

WHERE

entityid = #{entityid}

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。