环境:idea
语言:java
框架:springboot
难点:熟悉easyExcel工具的使用
思路:
查询时间段内的所有历史数据
根据历史数据合成表头
根据历史数据合成表内容
导出excel
部分代码参考
/**
* 文件下载(失败了会返回一个有部分数据的Excel)
* <p>
* 1. 创建excel对应的实体对象 参照{@link Object}
* <p>
* 2. 设置返回的 参数
* <p>
* 3. 直接写,这里注意,finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
*/
@PostMapping("/exportExcel")
public void exportExcel(HttpServletResponse response, @RequestBody PointValueDto pointValueDto){
try {
// ExcelFunObj excelFunObj = new ExcelFunObj(typeId,funCodeId,driverId);
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String sheetName = "历史数据" + DateUtil.format(new Date(),"yyMMddHHmmss");
String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
ExcelObj excelObj = deviceInfoService.exportExcel(pointValueDto);
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.head(excelObj.getHeadList()).sheet(sheetName).doWrite(excelObj.getDataList());
}catch (Exception ex){
log.error(ex.getMessage());
return;
}
}
/**
* 根据查询参数获取历史数据,并合成表头和表内容
* @param pointValueDto 查询参数对象
* @return
*/
@Override
public ExcelObj exportExcel(PointValueDto pointValueDto) {
String deviceId = pointValueDto.getDeviceId();
Map<Integer, Object> pointValueMap = this.getPointValueMap(deviceId,"60");
int size = pointValueMap.size();
List<PointValue> pointValues = pointValueClient.list(pointValueDto).getData().getRecords();
//按照origintime倒序排列
List<PointValue> pointValuesSort = pointValues.stream().sorted(Comparator.comparing(PointValue::getOriginTime).reversed()).collect(Collectors.toList());
//合成表头
List<List<String>> headList = this.getExcelHead(pointValuesSort,size);
//合成表内容
List<List<Object>> dataList = this.getExcelBody(pointValuesSort,size);
ExcelObj excelObj = new ExcelObj(headList,dataList);
return excelObj;
}
/**
* 合成excel表头,外面的list是表头行(多行)的具体内容,里面的list是表示单列(可多行数据,形成不同的表头),根据这个合成多行表头
* 这里只需要合成第一行的表头
* @param pointValuesSort
* @param size
* @return
*/
private List<List<String>> getExcelHead(List<PointValue> pointValuesSort,int size){
List<List<String>> head = new ArrayList<>();
List<String> head01 = new ArrayList<>();
head01.add("序号");
head.add(head01);
List<String> head02 = new ArrayList<>();
head02.add("设备时间");
head.add(head02);
List<String> head03 = new ArrayList<>();
head03.add("系统时间");
head.add(head03);
for(int i=0;i<size;i++){
PointValue pointValue = pointValuesSort.get(i);
List<String> headP = new ArrayList<>();
headP.add(pointValue.getPointDisplayName());
head.add(headP);
}
return head;
}
/**
* 合成excel body数据,需要每一行的绘制
* @param pointValuesSort
* @param size
* @return
*/
private List<List<Object>> getExcelBody(List<PointValue> pointValuesSort,int size){
List<List<Object>> body = new ArrayList<>();
List<PointValue> pointValueList = null;
int j=1;
for(int i=0;i<pointValuesSort.size();i++){
if (i % size == 0) {
pointValueList = new ArrayList<>();
}
pointValueList.add(pointValuesSort.get(i));
if ((i + 1) % size == 0) {
List<Object> oneRow = this.getExcelOneRow(j++,pointValueList);
body.add(oneRow);
}
}
return body;
}
/**
* 合成excel 一行的数据
* @param order
* @param pointValueList
* @return
*/
private List<Object> getExcelOneRow(int order,List<PointValue> pointValueList){
List<Object> oneRow = new ArrayList<>();
oneRow.add(order);
oneRow.add(DateUtil.formatDateTime(pointValueList.get(0).getOriginTime()));
oneRow.add(DateUtil.formatDateTime(pointValueList.get(0).getCreateTime()));
for (int i = 0; i < pointValueList.size(); i++) {
PointValue pointValue = pointValueList.get(i);
oneRow.add(pointValue.getValue());
}
return oneRow;
}
@Data
public class ExcelObj {
List<List<String>> headList;
List<List<Object>> dataList;
public ExcelObj(){}
public ExcelObj(List<List<String>> headList,
List<List<Object>> dataList){
this.setDataList(dataList);
this.setHeadList(headList);
}
}