springboot+easyexcel实现固定表头+动态表头的数据导出

/**
 * 固定标头的实体类
 */
@Data
public class BaseExportExcelStringVo implements Serializable {
    private static final long serialVersionUID = 1L;

    @ExcelProperty( value = "日期时间",index = 0)
    @ApiModelProperty("日期时间")
    @ColumnWidth(20)
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private String time;

    @ColumnWidth(40)
    @ExcelProperty( value = "数据",index = 1)
    @ApiModelProperty("数据")
    private String data;
}
/**
 * 数据结果实体类
 */
@Data
public class DeviceExportExcelVo implements Serializable {
    private static final long serialVersionUID = 1L;
	
	@ExcelProperty( value = "日期时间",index = 0)
    @ApiModelProperty("日期时间")
    @ColumnWidth(20)
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private String time;

    @ColumnWidth(20)
    @ExcelProperty( value = "评分",index = 9)
    @ApiModelProperty("评分")
    private BigDecimal score;

    @ApiModelProperty("单体电压")
    private BigDecimal cellVolt;

    @ApiModelProperty("单体温度")
    private BigDecimal cellTemp;
}

private void exportDetailExcelInOneSheet(HttpServletResponse response) throws Exception {
	//固定列
	List<DeviceExportExcelVo> voList = new ArrayList<>();
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	//单体电压 动态列 1
	List<BaseExportExcelStringVo> voltVoList = new ArrayList<>();
	//单体温度 动态列 2
	List<BaseExportExcelStringVo> tempVoList = new ArrayList<>();
	
	List<List<String>> commonList = new ArrayList<>();
	List<List<String>> voltHeadList = new ArrayList<>();
	List<List<String>> tempHeadList = new ArrayList<>();
	List<List<String>> headList = new ArrayList<>();
	List<List<Object>> sheetDataList = new ArrayList<>();
	// 临时列 用于存放固定列和动态列的数据
	List<BaseExportExcelStringVo> sheetVoList = new ArrayList<>();
	
	//************中间固定值列不再描述先用666代替*****
	for (DeviceExportExcelVo vo : dataList) {			
		String timeStr = vo.getTime();
		vo.setScore(666);
		//单体电压
		BaseExportExcelStringVo baseVoltExportExcelStringVo = new BaseExportExcelStringVo();
		baseVoltExportExcelStringVo.setTime(timeStr);
		baseVoltExportExcelStringVo.setData(vo.getVoltageList().toString());
		voltVoList.add(baseVoltExportExcelStringVo);
		//单体温度
		BaseExportExcelStringVo baseTempExportExcelStringVo = new BaseExportExcelStringVo();
		baseTempExportExcelStringVo.setTime(timeStr);
		baseTempExportExcelStringVo.setData(vo.getTempList().toString());
		tempVoList.add(baseTempExportExcelStringVo);
		voList.add(vo);
		//所有数据放到一行，然后动态导出excel
		BaseExportExcelStringVo sheetVo = new BaseExportExcelStringVo();
		sheetVo.setTime(timeStr);
		//主要的想法就是把固定列和动态列放到一个列表中，这样就可以导出一个动态列就可以了
		sheetVo.setData(timeStr.concat(",")
				.concat(String.valueOf(vo.getScore())).concat(",")
						.concat(vo.getVoltageList().toString()).concat(",")
						.concat(vo.getTempList().toString())
		);
		sheetVoList.add(sheetVo);


		if (voltVoList.size() > 0) {
			voltHeadList = getHeadList(voltVoList, "单体电压");
		}
		if (tempVoList.size() > 0) {
			tempHeadList = getHeadList(tempVoList, "单体温度");
		}
		sheetDataList = getDataList(sheetVoList);
		}
		
		String fileFinalPath = pyPath + File.separator + param.getSn() + ".xlsx";
		File file = new File(fileFinalPath);

		addCommonHead(commonList, "时间,评分");
		headList.addAll(commonList);
		headList.addAll(voltHeadList);
		headList.addAll(tempHeadList);
		EasyExcel.write(fileFinalPath).head(headList).sheet(0, "数据").doWrite(sheetDataList);
		//导出流
		export2File(file, response);
	}
	
}

/**
 * 填充固定表头
 *
 * @param commonList 表头
 * @param title      名称
 */
private void addCommonHead(List<List<String>> commonList, String title) {
	if (StringUtils.isNotBlank(title)) {
		List<String> titleList = Arrays.asList(title.split(","));
		if (titleList.size() > 0) {
			titleList.forEach(o -> {
				List<String> list = new ArrayList<>();
				list.add(o);
				commonList.add(list);
			});
		}
	}
}

/**
 * 动态头
 *
 * @return java.util.List<java.util.List < java.lang.String>>
 */
public List<List<String>> getHeadList(List<BaseExportExcelStringVo> dataVoList, String prefix) {
	List<List<String>> head = new ArrayList<>();
	int len = dataVoList.size();
	if (len > 0) {
		String[] split = dataVoList.get(0).getData().replaceAll("[\\[|\\]]", " ").split(",");
		List<String> titleList = Arrays.asList(split);
		for (int i = 0; i < titleList.size(); i++) {
			List<String> list = new ArrayList<>();
			list.add(prefix + (i + 1));
			head.add(list);
		}
	}
	return head;
}

/**
 * 动态数据
 *
 * @return java.util.List<java.util.List < java.lang.String>>
 */
public List<List<Object>> getDataList(List<BaseExportExcelStringVo> dataVoList) {
	List<List<Object>> dataLists = new ArrayList<>();

	//建立行列表
	for (int i = 1; i <= dataVoList.size(); i++) {
		if (dataLists.size() < i) {
			List<Object> objectList = ListUtils.newArrayList();
			dataLists.add(objectList);
		}
	}
	for (int i = 0; i < dataLists.size(); i++) {
		for (int j = 0; j < dataVoList.size(); j++) {
			int finalI = i;
			if (finalI == j) {
				String[] splitData = dataVoList.get(j).getData().replaceAll("[\\[|\\]]", " ").split(",");
				List<String> list = Arrays.asList(splitData);
				list.forEach(o -> {
					dataLists.get(finalI).add(o);
				});
				break;
			}
		}
	}
	return dataLists;
}

效果图如下

x