Java中使用easyexcel导入导出数据

　　工作中常常遇到导入导出Excel数据，还需要设置表格边框、合并单元格、字体居中等等各种样式，尝试了多种方式觉得阿里的easyexcel挺好使，记录一下使用、表格样式如下：

　　导入依赖

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.2.3</version>
</dependency>

　　创建导出实体类

@HeadRowHeight(38)　　//设置标题行高
@Data
public class SwhcBdcVo implements Serializable {

    @ExcelProperty(value = { "基本信息", "序号" }, index = 0)  //需要在表格显示的列
    @ColumnWidth(6)   //每列占用的宽度，列宽
    private String xh;

    @ExcelProperty(value = { "基本信息", "姓名" }, index = 1)
    @ColumnWidth(8)
    private String qlrmc;

    @ExcelProperty(value = { "基本信息", "性别" }, index = 2)
    @ColumnWidth(6)
    private String xb;

    @ExcelProperty(value = { "基本信息", "身份证号" }, index = 3)
    @ColumnWidth(16)
    private String qlrzjh;

    @ExcelProperty(value = {"不动产信息", "房产来源（去向）" }, index = 4)
    @ColumnWidth(30)
    private String qx;

    @ExcelProperty(value = {"不动产信息", "具体地址" }, index = 5)
    @ColumnWidth(30)
    private String dz;

    @ExcelProperty(value = {"不动产信息", "建筑面积（m²）" }, index = 6)
    @ColumnWidth(11)
    private BigDecimal jzmj;

    @ExcelProperty(value = {"不动产信息", "产权性质" }, index = 7)
    @ColumnWidth(11)
    private String cqxz;

    @ExcelProperty(value = {"不动产信息", "登记时间(年月)" }, index = 8)
    @ColumnWidth(11)
    private String djsj;

    @ExcelProperty(value = {"不动产信息", "交易价格(万元)" }, index = 9)
    @ColumnWidth(11)
    private BigDecimal jyjg;

    @ExcelProperty(value = "备注", index = 10)
    @ColumnWidth(24)
    private String bz;

    @ExcelIgnore　　//不需要在表格展示的字段
    private String ywh;

    @ExcelIgnore
    private Date compareDjsj;

}

　　生成Excel模板

public void generateTemplate(HttpServletResponse response) {
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");
		String fileName = URLEncoder.encode(String.format("%s.xlsx", sdf2.format(new Date())),
				StandardCharsets.UTF_8.toString());
		response.setContentType("application/force-download");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

		// 这里 需要指定写用哪个class去写
		ExcelWriter writer = new ExcelWriterBuilder()
				.autoCloseStream(true)
				.excelType(ExcelTypeEnum.XLSX)
				.file(response.getOutputStream())
				.head(SwhcBdcVo.class)
				// 核心代码：表头和正文的样式在此
				.registerWriteHandler(setConfigure())
				.build();
		WriteSheet writeSheetfw = new WriteSheet();
		writeSheetfw.setSheetName("Sheet1");
		writer.write(null, writeSheetfw);
		// 关闭流
		writer.finish();
	}

　　数据导入读取

public R dr(MultipartFile file) {
		if (!StrUtil.endWithAny(file.getOriginalFilename(), ".xls", ".xlsx")) {
			return R.failed("上传的文件不是excel");
		}
		List<SwhcBdcVo> drList = EasyExcelUtil.readExcel(file, "Sheet1", SwhcBdcVo.class, 2);    //2为表格标题占用的行数，读取跳过标题
		if (CollUtil.isEmpty(drList )) {
			return R.failed("未获取到导入表格数据");
		}
}

　　EasyExcelUtil工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class EasyExcelUtil {

	public static <T> List<T> readExcel(MultipartFile excel, String sheetName, Class<T> clazz, int headLineNum)
			throws IOException {
		ExcelReaderBuilder readerBuilder = getReader(excel);
		if (readerBuilder == null) {
			return null;
		}
		ExcelReaderSheetBuilder sheet = readerBuilder.sheet(sheetName);
		if (sheet == null) {
			return null;
		}
		sheet.head(clazz).headRowNumber(headLineNum);

		return sheet.doReadSync();
	}

	private static ExcelReaderBuilder getReader(MultipartFile excel) throws IOException {
		String filename = excel.getOriginalFilename();
		if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
			InputStream is = new BufferedInputStream(excel.getInputStream());
			return EasyExcel.read(is);
		} else {
			return null;
		}
	}
}

　　数据生成到表格

log.info("-------开始数据写表操作------------------");
        //查询到的数据写入Excel表格，表格文件传入minio
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        //需要合并的列
        int[] mergeColumeIndex = {0,1,2,3};
        //从第三行后开始合并
        int mergeRowIndex = 2;
        // 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
        ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex);
        EasyExcel.write(out, SwhcBdcVo.class)
                .autoCloseStream(true)
                .excelType(ExcelTypeEnum.XLSX)
                .head(SwhcBdcVo.class)
                // 核心代码：表头和正文的样式在此
                .registerWriteHandler(setConfigure())
                .registerWriteHandler(excelFillCellMergeStrategy)
                .sheet("Sheet1")
                .doWrite(totalPersonBdcList);
        byte[] bs = out.toByteArray();
        out.close();
        try (XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bs));
             OutputStream out1 = new FileOutputStream("./测试文件.xlsx")) {
            workbook.write(out1);
            log.info("-----------写入文件结束------------");
        }

　　设置标题样式和内容样setConfigure()方法

//配置字体，表头背景等
    private static HorizontalCellStyleStrategy setConfigure() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex()); // 白色背景
        WriteFont headWriteFont = new WriteFont();

        // 加粗
//        headWriteFont.setBold(true);
//        headWriteFont.setFontHeightInPoints((short) 14); // 设置行高，不重要
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
//        contentWriteFont.setFontHeightInPoints((short) 14);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //导出数据垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //导出数据水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置边框
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        //设置 自动换行
        //contentWriteCellStyle.setWrapped(true);

        //设置
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

　　设置内容合并策略，指定列的内容相同，单元格合并

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 lombok.Data;
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;

@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {

    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(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();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData)) {
            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);
            }
        }
    }
}