我的需求有些怪, 导出的excel的头某些是不固定的, 使用原生poi导出, 其中有很多循环数据库, 效率慢且数据量大容易宕机,之前的接口代码如下
public String export1(Long templateId, Date beginTime, Date endTime, Integer state, String examineNumber, Long createUserId) {
Cnd cnd = Cnd.where("templateId", "=", templateId);
cnd.andEX("createTime", ">=", beginTime);
cnd.andEX("createTime", "<=", endTime);
cnd.andEX("examineNumber", "like", examineNumber);
cnd.andEX("createUserId", "=", createUserId);
if (state != null && state != 0) {
switch (state) {
case 1: //已完成(包含已通过和已拒绝)
int[] states = new int[]{ExamineState.AGREE.key(), ExamineState.REFUSE.key()};
cnd.and("examineState", "in", states);
break;
case 2: //审批中
cnd.and("examineState", "=", ExamineState.UNDER_APPROVAL.key());
break;
case 3: //已撤销
cnd.and("examineState", "=", ExamineState.REVOKE.key());
break;
default:
break;
}
}
cnd.groupBy("templateVersion");
List<ExamineExport> versions = super._query(cnd, null, FieldMatcher.simple("templateVersion"));
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true); //数据格强制换行
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中;
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
titleStyle.setBorderTop(BorderStyle.THIN);//上边框
titleStyle.setBorderRight(BorderStyle.THIN);//右边框
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
for (ExamineExport version : versions) {
Cnd versionCnd = Cnd.where("templateId", "=", templateId);
versionCnd.and("templateVersion", "=", version.getTemplateVersion());
versionCnd.andEX("createTime", ">=", beginTime);
versionCnd.andEX("createTime", "<=", endTime);
versionCnd.andEX("examineNumber", "like", examineNumber);
versionCnd.andEX("createUserId", "=", createUserId);
if (state != null && state != 0) {
switch (state) {
case 1: //已完成(包含已通过和已拒绝)
int[] states = new int[]{ExamineState.AGREE.key(), ExamineState.REFUSE.key()};
versionCnd.and("examineState", "in", states);
break;
case 2: //审批中
versionCnd.and("examineState", "=", ExamineState.UNDER_APPROVAL.key());
break;
case 3: //已撤销
versionCnd.and("examineState", "=", ExamineState.REVOKE.key());
break;
default:
break;
}
}
List<ExamineExport> exports = super.query(versionCnd);
JSONObject formMap = JSONObject.parseObject(exports.get(0).getExamineForms());
XSSFSheet sheet = wb.createSheet(version.getTemplateVersion() == null ? "1" : version.getTemplateVersion());
//第一次需要设置表头, 根据提交的表单设置
int rowNum = 0;
int titleIndex = 0;
XSSFRow title = sheet.createRow(rowNum++);
short height = 20*30;
title.setHeight(height);
sheet.setColumnWidth(titleIndex, 21*256+184);
XSSFCell number = title.createCell(titleIndex);
number.setCellStyle(titleStyle);
number.setCellValue("审批编号");
titleIndex++;
sheet.setColumnWidth(titleIndex, 30*256+184);
XSSFCell name = title.createCell(titleIndex);
name.setCellStyle(titleStyle);
name.setCellValue("标题");
titleIndex++;
sheet.setColumnWidth(titleIndex, 10*256+184);
XSSFCell createUser = title.createCell(titleIndex);
createUser.setCellStyle(titleStyle);
createUser.setCellValue("提交人");
titleIndex++;
sheet.setColumnWidth(titleIndex, 21*256+184);
XSSFCell createTime = title.createCell(titleIndex);
createTime.setCellStyle(titleStyle);
createTime.setCellValue("发起时间");
titleIndex++;
sheet.setColumnWidth(titleIndex, 21*256+184);
XSSFCell completeTime = title.createCell(titleIndex);
completeTime.setCellStyle(titleStyle);
completeTime.setCellValue("完成时间");
titleIndex++;
sheet.setColumnWidth(titleIndex, 10*256+184);
XSSFCell stateCell = title.createCell(titleIndex);
stateCell.setCellStyle(titleStyle);
stateCell.setCellValue("审批状态");
titleIndex++;
for (String examineTitle : formMap.keySet()) {
XSSFCell formTitle = title.createCell(titleIndex);
formTitle.setCellStyle(titleStyle);
formTitle.setCellValue(examineTitle);
titleIndex++;
}
//设置结尾的历史审批人
sheet.setColumnWidth(titleIndex, 30*256+184);
XSSFCell processUser = title.createCell(titleIndex);
processUser.setCellStyle(titleStyle);
processUser.setCellValue("历史审批人");
//从第二行开始循环放置具体内容
for (ExamineExport export : exports) {
XSSFRow value = sheet.createRow(rowNum++);
int valueIndex = 0;
value.createCell(valueIndex++).setCellValue(export.getExamineNumber());
value.createCell(valueIndex++).setCellValue(export.getExamineTitle());
value.createCell(valueIndex++).setCellValue(export.getCreateUser());
value.createCell(valueIndex++).setCellValue(export.getCreateTime());
//此处存在实际数据中没有完成时间的情况，所以不需要这样判断，不然表格数据会左移一格
// if (StringUtils.isNotBlank(export.getCompleteTime())) {
value.createCell(valueIndex++).setCellValue(export.getCompleteTime());
// }
value.createCell(valueIndex++).setCellValue(export.getExamineStateDesc());
//放置表单
JSONObject jo = JSONObject.parseObject(export.getExamineForms());
for (String examineTitle : formMap.keySet()) {
value.createCell(valueIndex++).setCellValue(String.valueOf(jo.get(examineTitle)));
}
value.createCell(valueIndex++).setCellValue(export.getProcessUsers());
}
}
//导出Excel
String fileName = Moment.now().format("yyyyMMddHHmmssSSS")+ ".xlsx";
String fileUrl = "examine/report/" + fileName;
try {
File file = new File(fileLocalPath + fileUrl);
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return fileUrl;
}后来我接手后利用easyexcel重写了这个接口, 效果一样, 但是提高了效率, 最重要的是避免循环调数据库!!!
重写后的接口:
public String export(Long templateId, Date beginTime, Date endTime, Integer state, String examineNumber, Long createUserId) {
String fileName = Moment.now().format("yyyyMMddHHmmssSSS")+ ".xlsx";
String fileUrl = "examine/report/" + fileName;
File file = new File(fileLocalPath + fileUrl);
List<ExamineExport> list = getExportListNoPage(templateId, beginTime, endTime, state, examineNumber, createUserId);
if (CollectionUtils.isNotEmpty(list)) {
String examineForms = list.get(0).getExamineForms();
JSONObject jsonObject = JSONObject.parseObject(examineForms);
Set<String> strings = jsonObject.keySet();
List<String> keys = new ArrayList<>(strings);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//将excel文件写入byteArrayOutputStream中
EasyExcel.write(byteArrayOutputStream)
.head(head(keys))
.sheet("sheet1")
.doWrite(dataList(list, keys));
//创建inputStream流
InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
FileUtils.writeToFile(file, inputStream, true);
return fileUrl;
} else {
List<List<String>> keys = new ArrayList<>();
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//将excel文件写入byteArrayOutputStream中
EasyExcel.write(byteArrayOutputStream)
.head(keys)
.sheet("sheet1")
.doWrite(new ArrayList<>());
//创建inputStream流
InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
FileUtils.writeToFile(file, inputStream, true);
return fileUrl;
}
}
/**
* 无分页(为导出准备) // 此接口查回你需要的数据
* @param templateId
* @param beginTime
* @param endTime
* @param state
* @param examineNumber
* @param createUserId
* @return
*/
public List<ExamineExport> getExportListNoPage(Long templateId,
Date beginTime,
Date endTime,
Integer state,
String examineNumber,
Long createUserId) {
Cnd cnd = Cnd.where("templateId", "=", templateId);
cnd.andEX("createTime", ">=", beginTime);
cnd.andEX("createTime", "<=", endTime);
cnd.andEX("examineNumber", "like", examineNumber);
cnd.andEX("createUserId", "=", createUserId);
if (state != null && state != 0) {
switch (state) {
case 1: //已完成(包含已通过和已拒绝)
int[] states = new int[]{ExamineState.AGREE.key(), ExamineState.REFUSE.key()};
cnd.and("examineState", "in", states);
break;
case 2: //审批中
cnd.and("examineState", "=", ExamineState.UNDER_APPROVAL.key());
break;
case 3: //已撤销
cnd.and("examineState", "=", ExamineState.REVOKE.key());
break;
default:
break;
}
}
cnd.desc("createTime");
return this.query(cnd);
}
private List<List<String>> head(List<String> keys) {
List<List<String>> list = new ArrayList<List<String>>();
list.add(Collections.singletonList("审批编号"));
list.add(Collections.singletonList("标题"));
list.add(Collections.singletonList("提交人"));
list.add(Collections.singletonList("发起时间"));
list.add(Collections.singletonList("完成时间"));
list.add(Collections.singletonList("审批状态"));
keys.forEach(s -> {
List<String> head0 = new ArrayList<String>();
head0.add(s);
list.add(head0);
});
list.add(Collections.singletonList("历史审批人"));
return list;
}
private List<List<Object>> dataList(List<ExamineExport> realData, List<String> realKeys) {
List<List<Object>> list = ListUtils.newArrayList();
for (ExamineExport realDatum : realData) {
List<Object> data = ListUtils.newArrayList();
data.add(realDatum.getExamineNumber());
data.add(realDatum.getExamineTitle());
data.add(realDatum.getCreateUser());
data.add(realDatum.getCreateTime());
data.add(realDatum.getCompleteTime());
data.add(realDatum.getExamineStateDesc());
String examineForms = realDatum.getExamineForms();
JSONObject jsonObject = JSONObject.parseObject(examineForms);
realKeys.forEach(key -> data.add(jsonObject.get(key)));
data.add(realDatum.getProcessUsers());
list.add(data);
}
return list;
}这段代码需要一起拷贝下来, 有些例如cnd可能会报错(查数据库的), 此代码仅做思路, 非工具类, 勿喷
查阅很多资料发现这块内容较少, 所以写此贴给有同样需求的人提供思路
easyexcel官方文档地址: 关于Easyexcel | Easy Excel
另外可能会poi版本和easyexcel版本冲突, 这是因为easyexcel内置了poi, 两个poi版本冲突了
我easyexcel版本3.1.1, poi版本4.1.2, 可用, 仅供参考
419
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
