/**
* 固定标头的实体类
*/
@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