Java如何将Excel数据导入数据库

魁首哥

作者

Java如何将Excel数据导入数据库

这篇文章给大家分享的是有关Java如何将Excel数据导入数据库的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

1、根据业务需求设计数据库表

2、根据数据库表设计一个Excel模板

模板的每列属性必须与表字段一一对应

3、环境准备

我这里项目环境是基于SpringBoot单体式架构，持久层用的公司框架，内置了基于MyBatis-Plus的各种单表操作的方法。

导入依赖

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>

4、通过插件生成表对应的实体类

/***TbZbzs:值班值守表*@authorzs*@date2021-12-1708:46:31**/@Data@ApiModel(value="值班值守表,对应表tb_zbzs",description="适用于新增和修改页面传参")publicclassTbZbzsextendsProBaseEntity<TbZbzs>{privatestaticfinallongserialVersionUID=1L;@ApiModelProperty(value="id")privateStringid;//id@ApiModelProperty(value="部门")privateStringbm;//部门@ApiModelProperty(value="值班上报")privateStringzbsb;//值班上报@ApiModelProperty(value="值班人员")privateStringzbry;//值班人员@ApiModelProperty(value="上报时间")@DateTimeFormat(pattern="yyyy-MM-ddHH:mm:ss")privatejava.util.Datesbsj;//上报时间@ApiModelProperty(value="结束时间")@DateTimeFormat(pattern="yyyy-MM-ddHH:mm:ss")privatejava.util.Datejssj;//结束时间@ApiModelProperty(value="联系方式")privateStringlxfs;//联系方式@ApiModelProperty(value="状态")privateStringzt;//状态/***逻辑删除*/@ApiModelProperty(value="逻辑删除")privateStringdelFlag;/***创建时间*/@ApiModelProperty(value="创建时间")@DateTimeFormat(pattern="yyyy-MM-ddHH:mm:ss")privateDatecreateDate;}

5、自定义编写工具类

这里提供的是一个基础模板，根据业务的需求可以增加转换条件

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importjava.io.IOException;importjava.io.InputStream;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.List;/***新增值班排班表导入Excel表工具类*zyw*/publicclassImportExcelUtil{privatefinalstaticStringexcel2003L=".xls";//2003-版本的excelprivatefinalstaticStringexcel2007U=".xlsx";//2007+版本的excel/***描述：获取IO流中的数据，组装成List<List<Object>>对象*@paramin,fileName*@return*@throwsException*/publicstaticList<List<Object>>getListByExcel(InputStreamin,StringfileName)throwsException{List<List<Object>>list=null;//创建Excel工作薄Workbookwork=ImportExcelUtil.getWorkbook(in,fileName);if(null==work){thrownewException("创建Excel工作薄为空！");}Sheetsheet=null;Rowrow=null;Cellcell=null;list=newArrayList<List<Object>>();//遍历Excel中所有的sheetfor(inti=0;i<work.getNumberOfSheets();i++){sheet=work.getSheetAt(i);if(sheet==null){continue;}//遍历当前sheet中的所有行for(intj=sheet.getFirstRowNum();j<sheet.getLastRowNum()+1;j++){row=sheet.getRow(j);if(row==null||row.getFirstCellNum()==j){continue;}//遍历所有的列List<Object>li=newArrayList<Object>();for(inty=row.getFirstCellNum();y<row.getLastCellNum();y++){cell=row.getCell(y);li.add(ImportExcelUtil.getCellValue(cell));}list.add(li);}}//work.close();returnlist;}/***描述：根据文件后缀，自适应上传文件的版本*@paraminStr,fileName*@return*@throwsException*/publicstaticWorkbookgetWorkbook(InputStreaminStr,StringfileName)throwsException{Workbookwb=null;StringfileType=fileName.substring(fileName.lastIndexOf("."));if(excel2003L.equals(fileType)){wb=newHSSFWorkbook(inStr);//2003-}elseif(excel2007U.equals(fileType)){wb=newXSSFWorkbook(inStr);//2007+}else{thrownewException("解析的文件格式有误！");}returnwb;}/***描述：对表格中数值进行格式化*@paramcell*@return*/publicstaticObjectgetCellValue(Cellcell){Objectvalue=null;DecimalFormatdf=newDecimalFormat("0");//格式化numberString字符SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss");//SimpleDateFormatsdf=newSimpleDateFormat("yyy-MM-dd");//日期格式化//DecimalFormatdf2=newDecimalFormat("0.00");//格式化数字if(cell!=null){switch(cell.getCellType()){caseCell.CELL_TYPE_STRING:value=cell.getRichStringCellValue().getString();break;caseCell.CELL_TYPE_NUMERIC:if("General".equals(cell.getCellStyle().getDataFormatString())){value=df.format(cell.getNumericCellValue());}elseif("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){value=sdf.format(cell.getDateCellValue());}else{value=sdf.format(cell.getDateCellValue());}break;caseCell.CELL_TYPE_BOOLEAN:value=cell.getBooleanCellValue();break;caseCell.CELL_TYPE_BLANK:value="";break;default:break;}}returnvalue;}}

6、编写具体业务逻辑Service

主要思想：通过工具类将Excel文件解析成Object泛型的集合，再将集合循环遍历，在遍历中，将每行数据一次填入对象中，再每次循环中，将赋值后的对象存入一个list集合，最后统一将集合执行批量上传的方法，存入数据库。

publicMap<String,Object>importTprkxx(MultipartFilefile){Map<String,Object>resultMap=newHashMap<>();SimpleDateFormatsimpleDateFormat=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss");List<TbZbzs>tbZbzsList=newArrayList<>();try{//获取数据List<List<Object>>olist=ImportExcelUtil.getListByExcel(file.getInputStream(),file.getOriginalFilename());resultMap.put("导入成功",200);//封装数据for(inti=0;i<olist.size();i++){List<Object>list=olist.get(i);if(list.get(0)==""||("序号").equals(list.get(0))){continue;}TbZbzstbZbzs=newTbZbzs();tbZbzs.setId(UUID.randomUUID().toString().replace("-","").substring(0,20));//根据下标获取每一行的每一条数据if(String.valueOf(list.get(0))==null){resultMap.put("state","部门不能为空");continue;}tbZbzs.setBm(String.valueOf(list.get(0)));if(String.valueOf(list.get(1))==null){resultMap.put("state","值班上报不能为空");continue;}tbZbzs.setZbsb(String.valueOf(list.get(1)));if(String.valueOf(list.get(2))==null){resultMap.put("state","值班人员不能为空");continue;}tbZbzs.setZbry(String.valueOf(list.get(2)));if(String.valueOf(list.get(3))==null){resultMap.put("state","导入失败,上报时间不能为空");continue;}StringdateStr3=String.valueOf(list.get(3));Datedate3=simpleDateFormat.parse(dateStr3);tbZbzs.setSbsj(date3);if(String.valueOf(list.get(4))==null){resultMap.put("state","导入失败,结束时间不能为空");continue;}StringdateStr4=String.valueOf(list.get(4));Datedate4=simpleDateFormat.parse(dateStr4);tbZbzs.setJssj(date4);if(String.valueOf(list.get(5))==null){resultMap.put("state","联系方式不能为空");continue;}tbZbzs.setLxfs(String.valueOf(list.get(5)));if(String.valueOf(list.get(6))==null){resultMap.put("state","状态不能为空");continue;}tbZbzs.setZt(String.valueOf(list.get(6)));if(String.valueOf(list.get(7))==null){resultMap.put("state","逻辑删除不能为空");continue;}tbZbzs.setDelFlag(String.valueOf(list.get(7)));if(String.valueOf(list.get(8))==null){resultMap.put("state","导入失败,创建时间不能为空");continue;}StringdateStr8=String.valueOf(list.get(8));Datedate8=simpleDateFormat.parse(dateStr8);tbZbzs.setCreateDate(date8);tbZbzsList.add(tbZbzs);}inti=tbZbzsDao.insertTbZbzsList(tbZbzsList);if(i!=0){resultMap.put("state","导入成功");}else{resultMap.put("state","导入失败");}}catch(Exceptione){e.printStackTrace();resultMap.put("state","导入失败");}returnresultMap;}

7、在dao层对应的xml文件中，编写批量上传的方法

<insertid="insertTbZbzsList"parameterType="java.util.List">insertintotb_zbzs(id,bm,zbsb,zbry,sbsj,jssj,lxfs,zt,del_flag,create_date)VALUES<foreachcollection="list"item="item"separator=",">(#{item.id},#{item.bm},#{item.zbsb},#{item.zbry},#{item.sbsj},#{item.jssj},#{item.lxfs},#{item.zt},#{item.delFlag},#{item.createDate})</foreach></insert>

8、Controller实现业务的控制

/***@方法名称:excelProTbZbzs*@实现功能:导入值班值守表ExcelTODO:方法入参根据页面对象设置*@paramfile*@returnjava.lang.String*@createbyzywat2022-03-1716:49:31**/@ApiOperation(value="导入值班值守表Excel",notes="返回导入情况接口",response=TbZbzs.class)@PostMapping(value="/excelProTbZbzs")publicStringexcelProTbZbzs(@RequestParam("file")MultipartFilefile){try{returnbuildResultStr(service.importTprkxx(file).get("state").equals("导入成功")?buildSuccessResultData():buildErrorResultData(service.importTprkxx(file).get("state").toString()));}catch(RuntimeExceptione){logError(log,e);returnbuildResultStr(buildErrorResultData(e));}}