Spring boot + Spring data jpa + Thymeleaf
批量插入 + POI讀取 + 文件上傳
創(chuàng)新互聯(lián)公司專業(yè)提供四川雅安服務(wù)器托管服務(wù),為用戶提供五星數(shù)據(jù)中心、電信、雙線接入解決方案,用戶可自行在線購買四川雅安服務(wù)器托管服務(wù),并享受7*24小時金牌售后服務(wù)。
pom.xml:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
upload.html:
<form enctype="multipart/form-data" method="post" action="/upload/excel"> 文件 <input type="file" name="file" /> <input type="submit" value="上傳" /> </form>
如果自己的項目中使用了Spring security,頁面提交文件之后,會出現(xiàn)403的錯誤,最快的解決辦法,如下:
http.csrf().ignoringAntMatchers("/upload/**").
在security的配置文件中,加入上邊的代碼即可。當(dāng)然還有其他的辦法,大家可在網(wǎng)上查找。
Controller:
package org.meng.project.controller; import org.meng.project.service.ExcelService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletRequest; /** *<p><b>上傳Controller類</b></p> *<p> 上傳文件的Controller</p> * @Author MengMeng * @Date 2018/10/6 </p> * @version: 0.1 * @since JDK 1.80_144 */ @Controller @RequestMapping("/upload") public class UploadController { @Autowired private HttpServletRequest request; @Autowired private ExcelService excelService; //跳轉(zhuǎn)到上傳文件的頁面 @RequestMapping(value = "", method = RequestMethod.GET) public String goUpload() { //跳轉(zhuǎn)到 templates 中tools目錄下的 upload.html return "tools/upload"; } @RequestMapping(value = "/excel",method = RequestMethod.POST) public String upload(MultipartFile file, Model model) throws Exception { boolean flag = excelService.getExcel(file); if(flag){ model.addAttribute("Message", "上傳成功"); }else{ model.addAttribute("Message", "上傳失敗"); } return "tools/upload"; } }
Excel實體:
package org.meng.project.entity; import javax.persistence.*; import java.io.Serializable; import java.util.Objects; /** * <p><b>用戶實體類</b></p> * @ClassName User * @Author MengMeng * @Date 2018/10/6 </p> * @Version: 0.1 * @Since JDK 1.80_171 */ @Entity @Table(name = "test", schema = "project") public class Excel implements Serializable { private static final long serialVersionUID = 1L; @Id @Column(length=36) private String id; @Column(length=45,nullable=false,unique=true) private String username; @Column(length=100,nullable=false,unique=true) private String email; @Column(length=45,nullable=false) private String password; @Column(length=45) private String role; public Excel() { } public Excel(Excel user){ this.id = user.getId(); this.username = user.getUsername(); this.role = user.getRole(); this.email = user.getEmail(); this.password = user.getPassword(); } //get 和 set }
Service:
package org.meng.project.service; import com.alibaba.fastjson.JSON; import org.meng.project.entity.*; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.transaction.Transactional; /** *<p><b>Excel的Service類接口</b></p> *<p> Excel的Service類接口,與Excel有關(guān)的業(yè)務(wù)邏輯方法</p> * @Author MengMeng * @Date 2018/10/6 </p> * @version: 0.1 * @since JDK 1.80_144 */ public interface ExcelService { boolean getExcel(MultipartFile file) throws Exception; }
ServiceImpl:
package org.meng.project.service; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.meng.project.entity.*; import org.meng.project.repository.ExcelRepository; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.util.*; /** * <p><b>Excel的Service類</b></p> * <p>Excel的Service類,與User有關(guān)的業(yè)務(wù)邏輯方法</p> * @Author MengMeng * @Date 2018/10/6 * @version: 0.1 * @since JDK 1.80_144 */ @Service public class ExcelServiceImpl implements ExcelService { @Autowired private ExcelRepository excelRepository; @Override public boolean getExcel(MultipartFile file) throws Exception { // TODO Auto-generated method stub List<Excel> list = new ArrayList<Excel>(); //1.得到上傳的表 Workbook workbook2 = WorkbookFactory.create(file.getInputStream()); //2、獲取test工作表 Sheet sheet2 = workbook2.getSheet("test"); //獲取表的總行數(shù) int num = sheet2.getLastRowNum(); //System.out.println(num); //總列數(shù) int col = sheet2.getRow(0).getLastCellNum(); //遍歷excel每一行 for (int j = 0; j <= num; j++) { Row row1 = sheet2.getRow(j); //如果單元格中有數(shù)字或者其他格式的數(shù)據(jù),則調(diào)用setCellType()轉(zhuǎn)換為string類型 Cell cell1 = row1.getCell(0); cell1.setCellType(CellType.STRING); //獲取表中第i行,第2列的單元格 Cell cell2 = row1.getCell(1); //excel表的第i行,第3列的單元格 Cell cell3 = row1.getCell(2); cell3.setCellType(CellType.STRING); Cell cell4 = row1.getCell(3); Cell cell5 = row1.getCell(4); //這里new 一個對象,用來裝填從頁面上傳的Excel數(shù)據(jù),字段根據(jù)上傳的excel決定 Excel excel= new Excel(); 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); return true; } }
注意:需統(tǒng)一數(shù)據(jù)庫和Excel表中數(shù)據(jù)值的類型,不然會出現(xiàn)Cannot get a STRING value from a NUMERIC cell等錯誤。
Repository:
package org.meng.project.repository; import org.meng.project.entity.Excel; import org.meng.project.repository.base.BaseRepository; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframework.data.jpa.repository.Query; import org.springframework.stereotype.Repository; /** *<p><b>Excel的DAO類接口</b></p> *<p> Excel的DAO類接口,與Excel有關(guān)的持久化操作方法</p> * @Author MengMeng * @Date 2018/10/6 </p> * @version: 0.1 * @since JDK 1.80_144 */ @Repository public interface ExcelRepository extends BaseRepository<Excel, String> { }
即可實現(xiàn)文件上傳:
以上就是本文的全部內(nèi)容,希望對大家的學(xué)習(xí)有所幫助,也希望大家多多支持創(chuàng)新互聯(lián)。
當(dāng)前題目:SpringBoot實現(xiàn)Excel文件批量上傳導(dǎo)入數(shù)據(jù)庫
網(wǎng)頁鏈接:http://muchs.cn/article44/ihedee.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供營銷型網(wǎng)站建設(shè)、域名注冊、外貿(mào)建站、自適應(yīng)網(wǎng)站、企業(yè)網(wǎng)站制作、電子商務(wù)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)