SpringBoot实现Excel文件批量上传导入数据库
Springboot+Springdatajpa+Thymeleaf
批量插入+POI读取+文件上传
pom.xml:
org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17
upload.html:
文件
如果自己的项目中使用了Spring security,页面提交文件之后,会出现403的错误,最快的解决办法,如下:
http.csrf().ignoringAntMatchers("/upload/**").
在security的配置文件中,加入上边的代码即可。当然还有其他的办法,大家可在网上查找。
Controller:
packageorg.meng.project.controller; importorg.meng.project.service.ExcelService; importorg.springframework.beans.factory.annotation.Autowired; importorg.springframework.stereotype.Controller; importorg.springframework.ui.Model; importorg.springframework.web.bind.annotation.*; importorg.springframework.web.multipart.MultipartFile; importjavax.servlet.http.HttpServletRequest; /** *上传Controller类
*上传文件的Controller
*@AuthorMengMeng *@Date2018/10/6 *@version:0.1 *@sinceJDK1.80_144 */ @Controller @RequestMapping("/upload") publicclassUploadController{ @Autowired privateHttpServletRequestrequest; @Autowired privateExcelServiceexcelService; //跳转到上传文件的页面 @RequestMapping(value="",method=RequestMethod.GET) publicStringgoUpload(){ //跳转到templates中tools目录下的upload.html return"tools/upload"; } @RequestMapping(value="/excel",method=RequestMethod.POST) publicStringupload(MultipartFilefile,Modelmodel)throwsException{ booleanflag=excelService.getExcel(file); if(flag){ model.addAttribute("Message","上传成功"); }else{ model.addAttribute("Message","上传失败"); } return"tools/upload"; } }
Excel实体:
packageorg.meng.project.entity; importjavax.persistence.*; importjava.io.Serializable; importjava.util.Objects; /** *用户实体类
*@ClassNameUser *@AuthorMengMeng *@Date2018/10/6 *@Version:0.1 *@SinceJDK1.80_171 */ @Entity @Table(name="test",schema="project") publicclassExcelimplementsSerializable{ privatestaticfinallongserialVersionUID=1L; @Id @Column(length=36) privateStringid; @Column(length=45,nullable=false,unique=true) privateStringusername; @Column(length=100,nullable=false,unique=true) privateStringemail; @Column(length=45,nullable=false) privateStringpassword; @Column(length=45) privateStringrole; publicExcel(){ } publicExcel(Exceluser){ this.id=user.getId(); this.username=user.getUsername(); this.role=user.getRole(); this.email=user.getEmail(); this.password=user.getPassword(); } //get和set }
Service:
packageorg.meng.project.service; importcom.alibaba.fastjson.JSON; importorg.meng.project.entity.*; importorg.springframework.data.jpa.repository.Modifying; importorg.springframework.data.jpa.repository.Query; importorg.springframework.web.multipart.MultipartFile; importjavax.servlet.http.HttpServletRequest; importjavax.servlet.http.HttpServletResponse; importjavax.transaction.Transactional; /** *Excel的Service类接口
*Excel的Service类接口,与Excel有关的业务逻辑方法
*@AuthorMengMeng *@Date2018/10/6 *@version:0.1 *@sinceJDK1.80_144 */ publicinterfaceExcelService{ booleangetExcel(MultipartFilefile)throwsException; }
ServiceImpl:
packageorg.meng.project.service; importorg.apache.poi.EncryptedDocumentException; importorg.apache.poi.openxml4j.exceptions.InvalidFormatException; importorg.apache.poi.ss.usermodel.*; importorg.apache.poi.ss.usermodel.WorkbookFactory; importorg.meng.project.entity.*; importorg.meng.project.repository.ExcelRepository; importorg.springframework.beans.factory.annotation.Autowired; importorg.springframework.stereotype.Service; importorg.springframework.web.multipart.MultipartFile; importjava.io.IOException; importjava.util.*; /** *Excel的Service类
*Excel的Service类,与User有关的业务逻辑方法
*@AuthorMengMeng *@Date2018/10/6 *@version:0.1 *@sinceJDK1.80_144 */ @Service publicclassExcelServiceImplimplementsExcelService{ @Autowired privateExcelRepositoryexcelRepository; @Override publicbooleangetExcel(MultipartFilefile)throwsException{ //TODOAuto-generatedmethodstub Listlist=newArrayList (); //1.得到上传的表 Workbookworkbook2=WorkbookFactory.create(file.getInputStream()); //2、获取test工作表 Sheetsheet2=workbook2.getSheet("test"); //获取表的总行数 intnum=sheet2.getLastRowNum(); //System.out.println(num); //总列数 intcol=sheet2.getRow(0).getLastCellNum(); //遍历excel每一行 for(intj=0;j<=num;j++){ Rowrow1=sheet2.getRow(j); //如果单元格中有数字或者其他格式的数据,则调用setCellType()转换为string类型 Cellcell1=row1.getCell(0); cell1.setCellType(CellType.STRING); //获取表中第i行,第2列的单元格 Cellcell2=row1.getCell(1); //excel表的第i行,第3列的单元格 Cellcell3=row1.getCell(2); cell3.setCellType(CellType.STRING); Cellcell4=row1.getCell(3); Cellcell5=row1.getCell(4); //这里new一个对象,用来装填从页面上传的Excel数据,字段根据上传的excel决定 Excelexcel=newExcel(); excel.setId(cell1.getStringCellValue()); excel.setEmail(cell2.getStringCellValue()); excel.setPassword(cell3.getStringCellValue()); excel.setRole(cell4.getStringCellValue()); excel.setUsername(cell5.getStringCellValue()); list.add(excel); } excelRepository.saveAll(list); returntrue; } }
注意:需统一数据库和Excel表中数据值的类型,不然会出现CannotgetaSTRINGvaluefromaNUMERICcell等错误。
Repository:
packageorg.meng.project.repository; importorg.meng.project.entity.Excel; importorg.meng.project.repository.base.BaseRepository; importorg.springframework.data.domain.Page; importorg.springframework.data.domain.Pageable; importorg.springframework.data.jpa.repository.Query; importorg.springframework.stereotype.Repository; /** *Excel的DAO类接口
*Excel的DAO类接口,与Excel有关的持久化操作方法
*@AuthorMengMeng *@Date2018/10/6 *@version:0.1 *@sinceJDK1.80_144 */ @Repository publicinterfaceExcelRepositoryextendsBaseRepository{ }
即可实现文件上传:
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。