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;
	}

	@Override
	public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

	}

	@Override
	public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

	}

	@Override
	public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

	}

	@Override
	public 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 : 当前行的数据
	 */
	@Override
	public void invoke(Object object, AnalysisContext context) {
		//当前行
		// context.getCurrentRowNum()
		if (object != null) {
			datas.add(object);
		}
	}


	/**
	 * 解析完所有数据后会调用该方法
	 */
	@Override
	public 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

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