<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.7</version> </dependency>2. 對(duì)讀取excel內(nèi)容（批量添加）

@PostMapping('plUpdate') public R plUpdate(@RequestParam('filename') MultipartFile file) throws IOException { //String Originalfilename = file.getOriginalFilename(); // String fileName = file.getName(); // System.out.println('orname='+Originalfilename+';'+'filename'+file.getName()); // 獲取文件全名 String fileName = file.getOriginalFilename(); //設(shè)置文件路徑 String templatePath = 'G:/excel/'; File dest0 = new File(templatePath); File dest = new File(dest0, fileName); //文件上傳-覆蓋 try { // 檢測(cè)是否存在目錄 if (!dest0.getParentFile().exists()) {dest0.getParentFile().mkdirs();//檢測(cè)文件是否存在 } if (!dest.exists()) {dest.mkdirs(); } file.transferTo(dest); } catch (Exception e) { return R.error(); } String finameUrl = templatePath+fileName; ExcelReader excelReader = null; try { //TeacherExcel.class對(duì)應(yīng)的是和模板一樣的實(shí)體類， //eduTeacherService對(duì)應(yīng)持久層的接口 excelReader = EasyExcel.read(finameUrl, TeacherExcel.class, new DemoDataListener(eduTeacherService)).build(); ReadSheet readSheet = EasyExcel.readSheet(0).build(); excelReader.read(readSheet); } finally { if (excelReader != null) {// 這里千萬(wàn)別忘記關(guān)閉，讀的時(shí)候會(huì)創(chuàng)建臨時(shí)文件，到時(shí)磁盤會(huì)崩的excelReader.finish(); } } return R.ok(); }

創(chuàng)建一個(gè)監(jiān)聽(tīng)類：

package com.atguigu.excel;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.atguigu.eduservice.entity.EduTeacher;import com.atguigu.eduservice.entity.vo.TeacherExcel;import com.atguigu.eduservice.service.EduTeacherService;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.util.ArrayList;import java.util.List;// 有個(gè)很重要的點(diǎn) DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進(jìn)去public class DemoDataListener extends AnalysisEventListener<TeacherExcel> { private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class); //這里寫持久層的類 private EduTeacherService eduTeacherService; /** * 如果使用了spring,請(qǐng)使用這個(gè)構(gòu)造方法。每次創(chuàng)建Listener的時(shí)候需要把spring管理的類傳進(jìn)來(lái) * * @param eduTeacherService */ public DemoDataListener( EduTeacherService eduTeacherService) {//進(jìn)行持久層的類 this.eduTeacherService = eduTeacherService; } /** * 每隔5條存儲(chǔ)數(shù)據(jù)庫(kù)，實(shí)際使用中可以3000條，然后清理list ，方便內(nèi)存回收 */ private static final int BATCH_COUNT = 5; List<TeacherExcel> list = new ArrayList<TeacherExcel>(); /** * 假設(shè)這個(gè)是一個(gè)DAO，當(dāng)然有業(yè)務(wù)邏輯這個(gè)也可以是一個(gè)service。當(dāng)然如果不用存儲(chǔ)這個(gè)對(duì)象沒(méi)用。 */ //private DemoDAO demoDAO; public DemoDataListener() { // 這里是demo，所以隨便new一個(gè)。實(shí)際使用如果到了spring,請(qǐng)使用下面的有參構(gòu)造函數(shù) //demoDAO = new DemoDAO(); } /** * 如果使用了spring,請(qǐng)使用這個(gè)構(gòu)造方法。每次創(chuàng)建Listener的時(shí)候需要把spring管理的類傳進(jìn)來(lái) * * @param demoDAO */ /* public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; }*/ /** * 這個(gè)每一條數(shù)據(jù)解析都會(huì)來(lái)調(diào)用 * * @param data * one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(TeacherExcel data, AnalysisContext context) { LOGGER.info('解析到一條數(shù)據(jù):{}',data.toString()); list.add(data); // 達(dá)到BATCH_COUNT了，需要去存儲(chǔ)一次數(shù)據(jù)庫(kù)，防止數(shù)據(jù)幾萬(wàn)條數(shù)據(jù)在內(nèi)存，容易OOM } /** * 所有數(shù)據(jù)解析完成了 都會(huì)來(lái)調(diào)用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 這里也要保存數(shù)據(jù)，確保最后遺留的數(shù)據(jù)也存儲(chǔ)到數(shù)據(jù)庫(kù) saveData(); LOGGER.info('所有數(shù)據(jù)解析完成！'); } /** * 加上存儲(chǔ)數(shù)據(jù)庫(kù) */ private void saveData() { LOGGER.info('{}條數(shù)據(jù)，開(kāi)始存儲(chǔ)數(shù)據(jù)庫(kù)！', list.size()); //因?yàn)槲襡xcel模板的實(shí)體和插入數(shù)據(jù)庫(kù)實(shí)體的類不一樣，所以需要進(jìn)行轉(zhuǎn)化 for (int i =0;i<list.size();i++ ){ EduTeacher teacher = new EduTeacher(); teacher.setLevel(list.get(i).getLevel()); teacher.setCareer(list.get(i).getCareer()); teacher.setName(list.get(i).getName()); teacher.setSort(list.get(i).getSort()); teacher.setIntro(list.get(i).getIntro()); boolean save = eduTeacherService.save(teacher); if (save){System.out.println('第'+i+'添加成功'); } } } }

例如我的excel模板是：

實(shí)體類：

字段上ExcelProperty的注解可以使用index聲明字段在模板中的順序，使用value聲明模板各個(gè)字段的名稱。例如：

@ExcelProperty(value = “講師簡(jiǎn)介”,index = 1)private String intro;

模板的實(shí)體類要和excel字段一樣對(duì)應(yīng)，不然會(huì)出錯(cuò)

效果：

創(chuàng)建一個(gè)工具類：

package com.atguigu.eduservice.config;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.support.ExcelTypeEnum;import com.alibaba.excel.write.metadata.style.WriteCellStyle;import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;import org.apache.poi.ss.usermodel.HorizontalAlignment;import javax.servlet.http.HttpServletResponse;import java.io.OutputStream;import java.net.URLEncoder;import java.util.List;/** * @author linjiazeng * @version 1.0 * @date 2020/12/28 22:29 **/public class ExcelUtil { /** * 導(dǎo)出 * @param response * @param data * @param fileName * @param sheetName * @param clazz * @throws Exception */ public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception { //表頭樣式 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //設(shè)置表頭居中對(duì)齊 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //內(nèi)容樣式 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); //設(shè)置內(nèi)容靠左對(duì)齊 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data); } private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception { fileName = URLEncoder.encode(fileName, 'UTF-8'); response.setContentType('application/vnd.ms-excel'); response.setCharacterEncoding('utf8'); response.setHeader('Content-Disposition', 'attachment;filename=' + fileName + '.xlsx'); response.addHeader('Access-Control-Expose-Headers', 'Content-disposition'); return response.getOutputStream(); }}

調(diào)用工具類下載模板

/*下載模板*/ @GetMapping('/download/template') public void downloadTemplate(HttpServletResponse response){ String fileName = '導(dǎo)入講師模板'; String sheetName='導(dǎo)入講師模板'; List<TeacherExcel> teacherExcelList = new ArrayList<>(); TeacherExcel teacherExcel = new TeacherExcel(); teacherExcel.setName('ljz'); teacherExcel.setIntro('清華畢業(yè)，高材生'); teacherExcel.setCareer('資深講師'); teacherExcel.setSort(1); teacherExcel.setLevel(1); teacherExcelList.add(teacherExcel); try { //TeacherExcel.class對(duì)應(yīng)你的模板類 //teacherExcelList模板的例子 //也可以使用這種方式導(dǎo)出你查詢出數(shù)據(jù)excel文件 ExcelUtil.writeExcel(response,teacherExcelList,fileName,sheetName,TeacherExcel.class); } catch (Exception e) { System.out.println(e.getCause()); } }

效果：

有問(wèn)題可以互相交流，也可以去EasyExcel官網(wǎng)學(xué)習(xí)

到此這篇關(guān)于Spring Boot整合EasyExcel(完整版包含上傳解析excel和下載模板)的文章就介紹到這了,更多相關(guān)SpringBoot整合EasyExcel內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！