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

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

1、导入依赖
2、domain
3、utils
4、Controller
5、xml

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

1、导入依赖

org.apache.poipoi-ooxml4.1.2org.apache.poipoi-ooxml-schemas4.1.2org.apache.poipoi4.1.2org.projectlomboklombok1.16.20

2、domain

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

import lombok.Data; // Excel实体类@Datapublic class Detailed {public Long id; // 主键public String auditDate; // 审核日期public Integer sceneId; // 场景idpublic String sceneName; // 场景名称public String entityid; // entityidpublic Long housingResourcesId; // 房源id/实拍视频idpublic Integer cityid; // cityid/城市idpublic String firstInstanceOA; // 初审oa/一审审核人oapublic String firstInstance; // 初审方/一审核员部门名称public String preliminaryResults; // 初审结果/一审审核结果public String reasonsForFirstInstance; // 初审原因/一审审核原因public String timeOfInitialExaminationAndStorage; // 初审入库时间public String initialAuditTime; // 初审审核时间public Double shenheshichang; // shenheshichangpublic 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; @Controllerpublic class UploadController {@Autowiredprivate 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

SELECTid FROMdetailed WHEREentityid = #{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})

到此这篇关于Java实现上传Excel文件并导入数据库的文章就介绍到这了,更多相关Java上传Excel文件导入数据库内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！