【EasyExcel】多sheet、追加列

业务-EasyExcel多sheet、追加列

背景

最近接到一个导出Excel的业务，需求就是多sheet，每个sheet导出不同结构，第一个sheet里面能够根据最后一列动态的追加列。原本使用的 pig4cloud 架子，使用 @ResponseExcel注解方式组装返回数据即可，但是实现过程中发现并不是所想要的效果。

组件地址：https://github.com/pig-mesh/excel-spring-boot-starter

这样写能够实现多 sheet 导出，但是动态的移除列然后在追加列我尝试了并没有好的方案，有可能也是我没有找到，我找到的是下面面动态的修改列名称。

多 sheet导出，只需要返 List<List> 即可。

@ResponseExcel(name = "不同Sheet的导出", sheet = {"sheet1", "sheet2"})
@PostMapping("/export")
public List<List> export(@RequestBody queryModel model) {
    model.setSize(-1);
    return userService.userExcelList(model);
}

导出并自定义头信息

@Data
public class SimpleData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Integer number;
    // 忽略
    @ExcelIgnore
    private String ignore;
}

自定义头信息生成器：

注意需要实现 HeadGenerator 接口，且注册为一个 spring bean.

@Component
public class SimpleDataHeadGenerator implements HeadGenerator {
    @Override
    public HeadMeta head(Class<?> clazz) {
        HeadMeta headMeta = new HeadMeta();
        headMeta.setHead(simpleDataHead());
        // 排除 number 属性
        headMeta.setIgnoreHeadFields(new HashSet<>(Collections.singletonList("number")));
        return headMeta;
    }

    private List<List<String>> simpleDataHead() {
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("自定义字符串标题" + System.currentTimeMillis());
        List<String> head1 = new ArrayList<>();
        head1.add("自定义日期标题" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        return list;
    }
}

该头生成器，将固定返回 自定义字符串标题 和 自定义日期标题 两列头信息，实际使用时可根据业务动态处理，方便在一些权限控制时动态修改或者增删列头。

@RequestMapping("/head")
@RestController
public class ExcelHeadTestController {

    @ResponseExcel(name = "customHead", headGenerator = SimpleDataHeadGenerator.class)
    @GetMapping
    public List<SimpleData> multi() {
        List<SimpleData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            SimpleData simpleData = new SimpleData();
            simpleData.setString("str" + i);
            simpleData.setNumber(i);
            simpleData.setDate(new Date());
            list.add(simpleData);
        }
        return list;
    }
}

那就只能放弃使用组件方式，自己写 EasyExcel 拦截器。

代码实现

导出工具

exHealthSheetDy 静态方法如下，实现了 2 个 sheet 不同结构导出。

/**
 * 2 sheet 动态追加列
 *
 * @param response      响应
 * @param dataMap       dataMap
 * @param fileName      Excel名称
 * @param sheetNameList sheet名称
 * @throws Exception Exception
 */
public static void exHealthSheetDy(HttpServletResponse response, Map<Integer, List<? extends Object>> dataMap, String fileName, List<String> sheetNameList, List<String> labelGroupName) throws Exception {
	// 表头样式
	WriteCellStyle headWriteCellStyle = new WriteCellStyle();
	// 设置表头居中对齐
	headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
	// 内容样式
	WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
	// 设置内容剧中对齐
	contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
	HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
	ExcelWriter build = EasyExcel.write(getOutputStream(fileName, response))
			.excelType(ExcelTypeEnum.XLSX)
			.registerWriteHandler(horizontalCellStyleStrategy)
			.build();
	for (String s : sheetNameList) {
		WriteSheet writeSheet;
		if (s.equals("风险")) {
			// 风险
			writeSheet = EasyExcel.writerSheet(s)
					.head(HealthUserOneExcelVo.class)
					.registerWriteHandler(new LabelGroupNameRowWriteHandler(labelGroupName))
					.build();
			build.write(dataMap.get(0), writeSheet);
		} else {
			// 指标
			writeSheet = EasyExcel.writerSheet(s)
					.head(HealthUserExcelIndexVo.class)
					.build();
			build.write(dataMap.get(1), writeSheet);
		}
	}
	build.finish();
}

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");

	return response.getOutputStream();
}

拦截器

业务需求是根据 13 列切割追加列。

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 行拦截器 将字符串换成多列数据
 *
 * @author William
 */
@Slf4j
public class LabelGroupNameRowWriteHandler implements RowWriteHandler {

	/**
	 * 样式，与其他列保持一样的样式
	 */
	private CellStyle firstCellStyle;

	/**
	 * 体检标签分组列表
	 */
	private List<String> labelGroupName;

	public LabelGroupNameRowWriteHandler(List<String> labelGroupName) {
		this.labelGroupName = labelGroupName;
	}

	/**
	 * 字符串转
	 */
	@Override
	public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean isHead) {
		// ONE 13 行, 我的是 13 列 具体根据自己的 Excel 定位
		Cell cell = row.getCell(13);
		row.removeCell(cell);
		Map<String, Cell> map = new LinkedHashMap<>();
		int cellIndex = 0;
		for (int i = 0; i < labelGroupName.size(); i++) {
			if (StrUtil.isBlank(labelGroupName.get(i)) || map.containsKey(labelGroupName.get(i))) {
				continue;
			}
			Cell fi = row.createCell(cellIndex + 13);
			map.put(labelGroupName.get(i), fi);
			cellIndex++;
		}
		if (!isHead) {
			String stringCellValue = cell.getStringCellValue();
			try {
				String[] split = stringCellValue.split(",");
				for (Map.Entry<String, Cell> stringCellEntry : map.entrySet()) {
					boolean equalsRes = false;
					for (String s : split) {
						if (stringCellEntry.getKey().equals(s)) {
							equalsRes = true;
							break;
						}
					}
					if (equalsRes) {
						stringCellEntry.getValue().setCellValue("有");
					} else {
						stringCellEntry.getValue().setCellValue("无");
					}
				}
			} catch (Exception e) {
				log.error("afterRowDispose Exception:{}", e.getMessage(), e);
			}
		} else {
			Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
			firstCellStyle = firstCellStyle(workbook);
			for (Map.Entry<String, Cell> stringCellEntry : map.entrySet()) {
				stringCellEntry.getValue().setCellValue(stringCellEntry.getKey());
				stringCellEntry.getValue().setCellStyle(firstCellStyle);
				stringCellEntry.getValue().setCellStyle(firstCellStyle);
				stringCellEntry.getValue().setCellStyle(firstCellStyle);
			}
		}
	}

	/**
	 * excel首列序号列样式
	 *
	 * @param workbook Workbook
	 * @return CellStyle
	 */
	public CellStyle firstCellStyle(Workbook workbook) {
		CellStyle cellStyle = workbook.createCellStyle();

		// 居中
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

		//  灰色
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

		// 设置边框
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);

		// 文字
		Font font = workbook.createFont();
		font.setFontHeightInPoints((short) 14);
		font.setFontName("宋体");
		font.setBold(Boolean.TRUE);
		cellStyle.setFont(font);

		return cellStyle;
	}

	@Override
	public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {
	}

	@Override
	public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
	}
}