Java生成Excel表格数据的两种方式（Easy Excel和SXSSFWorkbook）以及测试方法

一. 使用alibaba中的Easy Excel工具

在gradle文件中引入依赖

dependencies {
	implementation 'com.alibaba:easyexcel:3.2.0'
}

写的对象

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

使用ExcelWriter（该代码为写入内存中）

ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

        try (ExcelWriter demoDataWriter = EasyExcel.write(byteArrayOutputStream).build()) {
            demoDataWriter.write(demoDataList,
                    EasyExcel.writerSheet(1, "sheet1")
                            .head(DemoData.class) //引入表头
                            .build());
}

二. 使用apache中的SXSSFWorkbook工具

主方法

private SXSSFWorkbook generateDemoDataExcelWorkbook(List<DemoData> demoDataList) {
        return new ExcelWorkbookBuilder<DemoData>()
                .buildXSSWorkbook(demoDataList, getDemoDataColumnDefinitions(),
                        "sheet1");
}

构建SXSSFWorkbook

public class ExcelWorkbookBuilder<E> {
	
	public SXSSFWorkbook buildXSSWorkbook(List<E> dataList, List<ColumnDefinition<E>> columnMap, String sheetName) {
        ExcelTool excelTool = new ExcelTool();
        SXSSFWorkbook workbook = excelTool.withSheet(sheetName).getWorkbook();
        SXSSFSheet sheet = workbook.getSheetAt(0);
        excelTool.withRowData(buildHeader(columnMap), 0);//写入表头
        dataList.forEach(data -> appendRowData(sheet, data, columnMap));//写入DemoData数据
        return workbook;
    }

	private Map<Integer, String> buildHeader(List<ColumnDefinition<E>> columnMap) {
        return IntStream.range(0, columnMap.size()).boxed()
                .collect(toMap(idx -> idx, index -> columnMap.get(index).getHeader()));
    }

    private void appendRowData(SXSSFSheet sheet, E data, List<ColumnDefinition<E>> columnMap) {
        SXSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
        IntStream.range(0, columnMap.size()).boxed().forEach(index -> {
            SXSSFCell cell = row.createCell(index);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(columnMap.get(index).getCalculator().apply(data));
        });
    }
}

定义表头

private List<ColumnDefinition<DemoData>> getDemoDataColumnDefinitions() {
        return Arrays.asList(
                new ColumnDefinition<>("字符串标题", DemoData::getString),
                new ColumnDefinition<>("日期标题", DemoData::getDate),
                new ColumnDefinition<>("数字标题", DemoData::getDoubleData)
                );
}

需要额外构建的类

• SXSSFWorkbook以及SXSSFSheet被封装到ExcelTool对象中

public class ExcelTool {
	    
	    private SXSSFWorkbook workbook;
	    private SXSSFSheet workSheet;
	
	    public ExcelTool() {
	        this.workbook = new SXSSFWorkbook(500);
	    }
	
		public ExcelTool withSheet(String sheetName) {
	        this.workSheet = this.workbook.createSheet(sheetName);
	        return this;
	    }
	
		public SXSSFWorkbook getWorkbook() {
	        return this.workbook;
	    }
		
	//将传入的DemoData数据写入指定sheet中
		public ExcelTool withRowData(Map<Integer, String> rowData, int rowNum) {
	        SXSSFRow row = this.workSheet.createRow(rowNum);
	        for (Map.Entry<Integer, String> entry : rowData.entrySet()) {
	            SXSSFCell cell = row.createCell(entry.getKey());
	            cell.setCellType(CellType.STRING);
	            cell.setCellValue(entry.getValue());
	        }
	        return this;
	    }
}

• 定义表头的ColumnDefinition类

public class ColumnDefinition<E> {
	    private final String header;
	    private final Function<E, String> calculator;
	
	    public ColumnDefinition(String header, Function<E, String> calculator) {
	        this.header = header;
	        this.calculator = calculator;
	    }
	
	    public String getHeader() {
	        return header;
	    }
	
	    public Function<E, String> getCalculator() {
	        return calculator;
	    }
	}

三. 关于excel表格内容测试的构建

1. 测试生成excel数据的方法，返回一个字节数组

2. 由字节数组bytes构建XSSFWorkbook

XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bytes, 0, bytes.length));

3. 得到任意sheet中的任意一行数据

XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(1);

4. 断言任意一个单元格数据

assertEquals(mockResponse.get(0).getString(), row.getCell(1).toString());