SpringBoot2：web开发常用功能实现及原理解析-整合EasyExcel实现Excel导入导出功能

1、工程包结构

主要是这5个Java类

2、导入EasyExcel包

这里同时贴出其他相关springboot的基础包

        <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-configuration-processor</artifactId><optional>true</optional></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- 引入easyExcel依赖 --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency>

3、Java代码

ExcelSysUser

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import lombok.ToString;
import org.apache.poi.ss.usermodel.HorizontalAlignment;@ContentRowHeight(25)
@HeadRowHeight(15)
@ColumnWidth(25)
@HeadFontStyle(fontHeightInPoints=9)
@ContentStyle(horizontalAlignment= HorizontalAlignment.CENTER)
@Data
@ToString
public class ExcelSysUser {public ExcelSysUser(){}public ExcelSysUser(String loginName, String userName, String userPwd){this.loginName = loginName;this.userName = userName;this.userPwd = userPwd;}@ExcelProperty(value = "登录名",index = 0)private String loginName;@ExcelProperty(value = "用户名",index = 1)private String userName;@ExcelProperty(value = "密码",index = 2)private String userPwd;}

ExcelFillCellMergeHandler

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/*** 合并单元格处理类*/
public class ExcelFillCellMergeHandler implements CellWriteHandler {//需要合并的列private int[] mergeColumnIndex;//从哪一列开始合并private int mergeRowIndex;public ExcelFillCellMergeHandler() {}public ExcelFillCellMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {int curRowIndex = cell.getRowIndex();int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}/*** 当前单元格向上合并** @param writeSheetHolder* @param cell             当前单元格* @param curRowIndex      当前行* @param curColIndex      当前列*/private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();// 将当前单元格数据与上一个单元格数据比较Boolean dataBool = preData.equals(curData);Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());if (dataBool && bool) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}}

ExcelListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;import java.util.ArrayList;
import java.util.List;/*** 解析监听器，* 每解析一行会回调invoke()方法。* 整个excel解析结束会执行doAfterAllAnalysed()方法*/
public class ExcelListener extends AnalysisEventListener {private List<Object> datas = new ArrayList<>();public List<Object> getDatas() {return datas;}public void setDatas(List<Object> datas) {this.datas = datas;}/*** 逐行解析* object : 当前行的数据*/@Overridepublic void invoke(Object object, AnalysisContext context) {//当前行// context.getCurrentRowNum()if (object != null) {datas.add(object);}}/*** 解析完所有数据后会调用该方法*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//解析结束销毁不用的资源}
}

EasyExcelUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.atguigu.boot.handler.ExcelFillCellMergeHandler;
import com.atguigu.boot.listener.ExcelListener;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;/*** EasyExcel工具类*/
public class EasyExcelUtil {/*** 读取 Excel(多个 sheet)** @param excel 文件* @param rowModel 实体类映射* @return Excel 数据 list*/public static List<Object> readExcel(MultipartFile excel, Object rowModel) throws IOException {String filename = excel.getOriginalFilename();if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {throw new RuntimeException("文件格式错误！");}ExcelListener excelListener = new ExcelListener();EasyExcel.read(excel.getInputStream(),rowModel.getClass(),excelListener).doReadAll();return excelListener.getDatas();}/*** 导出 Excel ：一个 sheet，带表头** @param response HttpServletResponse* @param list 数据 list* @param fileName 导出的文件名* @param sheetName 导入文件的 sheet 名* @param object 映射实体类，Excel 模型*/public static void writeExcel(HttpServletResponse response, List<?> list, String fileName,String sheetName, Object object) {EasyExcel.write(getOutputStream(fileName, response),object.getClass()).excelType(ExcelTypeEnum.XLSX).autoCloseStream(Boolean.TRUE).sheet(sheetName).doWrite(list);}/*** 导出 Excel 自动合并单元格* @param response HttpServletResponse* @param list 数据 list* @param fileName 导出的文件名* @param sheetName 导入文件的 sheet 名* @param object 映射实体类，Excel 模型* @param mergeColumnIndex 需要合并的列* @param mergeRowIndex 从哪一列开始合并*/public static void writeMergeExcel(HttpServletResponse response, List<?> list, String fileName,String sheetName, Object object, int[] mergeColumnIndex, int mergeRowIndex) {EasyExcel.write(getOutputStream(fileName, response),object.getClass()).excelType(ExcelTypeEnum.XLSX).autoCloseStream(Boolean.TRUE).registerWriteHandler(new ExcelFillCellMergeHandler(mergeRowIndex,mergeColumnIndex)).sheet(sheetName).doWrite(list);}/*** 导出文件时为Writer生成OutputStream*/private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {//创建本地文件fileName = fileName + ".xls";try {fileName = new String(fileName.getBytes(), "ISO-8859-1");response.addHeader("Content-Disposition", "attachment;filename=" + fileName);return response.getOutputStream();} catch (Exception e) {throw new RuntimeException("导出异常！");}}}

EasyExcelLoadsController

import com.atguigu.boot.bean.ExcelSysUser;
import com.atguigu.boot.utils.EasyExcelUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;@RestController
@RequestMapping("/excel")
public class EasyExcelLoadsController {/*** 用户信息导出Excel*/@RequestMapping(value = "/exportSysUser",method = RequestMethod.GET)public void exportSysUser(HttpServletResponse response){List<ExcelSysUser> excelSysUsers = new ArrayList<>();ExcelSysUser excelSysUser1 = new ExcelSysUser("张三", "zhangsan002", "zsmm123");ExcelSysUser excelSysUser2 = new ExcelSysUser("张三", "zhangsan003", "zsmm456");ExcelSysUser excelSysUser3 = new ExcelSysUser("张三", "zhangsan001", "zsmm789");ExcelSysUser excelSysUser4 = new ExcelSysUser("李四", "zhangsan001", "zsmm123");ExcelSysUser excelSysUser5 = new ExcelSysUser("李四", "zhangsan001", "zsmm456");excelSysUsers.add(excelSysUser1);excelSysUsers.add(excelSysUser2);excelSysUsers.add(excelSysUser3);excelSysUsers.add(excelSysUser4);excelSysUsers.add(excelSysUser5);EasyExcelUtil.writeExcel(response, excelSysUsers,"用户信息","用户信息", new ExcelSysUser());}/*** 用户信息导出Excel(合并单元格)*/@RequestMapping(value = "/exportMergeSysUser",method = RequestMethod.GET)public void exportMergeSysUser(HttpServletResponse response){List<ExcelSysUser> excelSysUsers = new ArrayList<>();ExcelSysUser excelSysUser1 = new ExcelSysUser("张三", "zhangsan002", "zsmm123");ExcelSysUser excelSysUser2 = new ExcelSysUser("张三", "zhangsan003", "zsmm456");ExcelSysUser excelSysUser3 = new ExcelSysUser("张三", "zhangsan001", "zsmm789");ExcelSysUser excelSysUser4 = new ExcelSysUser("李四", "zhangsan001", "zsmm123");ExcelSysUser excelSysUser5 = new ExcelSysUser("李四", "zhangsan001", "zsmm456");excelSysUsers.add(excelSysUser1);excelSysUsers.add(excelSysUser2);excelSysUsers.add(excelSysUser3);excelSysUsers.add(excelSysUser4);excelSysUsers.add(excelSysUser5);int[] mergeColumnIndex = {0,1};int mergeRowIndex = 0;EasyExcelUtil.writeMergeExcel(response, excelSysUsers,"用户信息","用户信息", new ExcelSysUser(), mergeColumnIndex, mergeRowIndex);}@RequestMapping(value = "/importSysUser",method = RequestMethod.POST)public void importSysUser(MultipartFile excel){List<Object> dataList = null;try {dataList = EasyExcelUtil.readExcel(excel, new ExcelSysUser());} catch (IOException e) {e.printStackTrace();}dataList.forEach(o -> System.out.println(o.toString()));}}

4、测试Excel

导入需要准备下图中的数据，导出直接浏览器访问接口即可。