背景及实现目标
XX单位下发任务给下属部门,下属部门每月反馈处置情况,单位导出下属部门每月的反馈情况,效果如图所示
相关技术
java版本:1.8
poi版本:4.0.0(导出xlsx)
数据准备
###任务相关数据
[
{
"assignedTime": 1690256963000,
"cycleUnit": "month",
"leadDeptName": "xx单位",
"dutyDeptName": "XX单位",
"limitTime": 1690624800000,
"parentTaskId": null,
"taskContent": "测试周期性调度编号及相关20230725001",
"taskId": "a3281916ff9c4b91bbfb1baae68d9a17",
"taskName": "测试周期性调度编号及相关20230725001",
"taskSn": "J20230305",
"taskTypeFirstName": "交办重点工作任务",
"taskTypeSecondName": "主要领导交办工作任务",
"replyContent":"5月落实情况"
},
{
"assignedTime": 1690263367000,
"cycleUnit": "month",
"leadDeptName": "xx单位",
"dutyDeptName": "XX单位",
"limitTime": 1695636000000,
"parentTaskId": "a3281916ff9c4b91bbfb1baae68d9a17",
"taskContent": "测试周期性调度编号及相关20230725001",
"taskId": "31bcf8eb8d514ebd8379c1058a417eb1",
"taskName": "测试周期性调度编号及相关20230725001",
"taskSn": "J20230305",
"taskTypeFirstName": "交办重点工作任务",
"taskTypeSecondName": "主要领导交办工作任务",
"replyContent":"6月落实情况"
},
{
"assignedTime": 1690263626000,
"cycleUnit": "month",
"leadDeptName": "xx单位",
"dutyDeptName": "XX单位",
"limitTime": 1698573600000,
"parentTaskId": "a3281916ff9c4b91bbfb1baae68d9a17",
"taskContent": "测试周期性调度编号及相关20230725001",
"taskId": "2abf0cedf315419d8f1f1ddec57ff4c2",
"taskName": "测试周期性调度编号及相关20230725001",
"taskSn": "J20230305",
"taskTypeFirstName": "交办重点工作任务",
"taskTypeSecondName": "主要领导交办工作任务",
"replyContent":"7月落实情况"
},
{
"assignedTime": 1690257253000,
"cycleUnit": "month",
"leadDeptName": "xx单位",
"dutyDeptName": "XX单位",
"limitTime": 1693303200000,
"parentTaskId": "a3281916ff9c4b91bbfb1baae68d9a17",
"taskContent": "测试周期性调度编号及相关20230725001",
"taskId": "93cb1f8f92cb4754b1846d5fb4a9ef77",
"taskName": "测试周期性调度编号及相关20230725001",
"taskSn": "J20230305",
"taskTypeFirstName": "交办重点工作任务",
"taskTypeSecondName": "主要领导交办工作任务",
"replyContent":"8月落实情况"
}
]
相关代码
实体类
/**
* 文件导出对象
*/
@Data
public class FileExportField {
private String name;
private Boolean isMerge = false;
private int firstRow;
private int lastRow;
private int firstCol;
private int lastCol;
private int width;
private List<FileExportField> children;
public FileExportField() {
}
public FileExportField(String name, Boolean isMerge, int firstRow, int lastRow, int firstCol, int lastCol) {
this.name = name;
this.isMerge = isMerge;
this.firstRow = firstRow;
this.lastRow = lastRow;
this.firstCol = firstCol;
this.lastCol = lastCol;
}
public FileExportField(String name, Boolean isMerge, int firstRow, int lastRow, int firstCol, int lastCol, int width) {
this.name = name;
this.isMerge = isMerge;
this.firstRow = firstRow;
this.lastRow = lastRow;
this.firstCol = firstCol;
this.lastCol = lastCol;
this.width = width;
}
public FileExportField(String name, Boolean isMerge, int firstRow, int lastRow, int firstCol, int lastCol, List<FileExportField> children) {
this.name = name;
this.isMerge = isMerge;
this.firstRow = firstRow;
this.lastRow = lastRow;
this.firstCol = firstCol;
this.lastCol = lastCol;
this.children = children;
}
}
import lombok.Data;
import java.util.Date;
import java.util.List;
/**
* 任务信息
*/
@Data
public class TaskSendInfoVo {
/**
* 任务ID
*/
private String taskId;
/**
* 任务编号
*/
private String taskSn;
/**
* 任务名称
*/
private String taskName;
/**
* 任务内容
*/
private String taskContent;
/**
* 牵头部门名称
*/
private String leadDeptName;
/**
* 责任部门名称
*/
private String dutyDeptName;
/**
* 完成时限
*/
private Date limitTime;
/**
* 责任单位完成时限
*/
private Date dutyUnitLimitTime;
/**
* 循环周期,周、月、季、年
*/
private String cycleUnit;
/**
* 交办时间
*/
private Date assignedTime;
/**
* 循环调度关联的父任务ID
*/
private String parentTaskId;
/**
* 落实情况汇总
*/
private String replyContent;
/**
* 任务大类名称
*/
private String taskTypeFirstName;
/**
* 任务二级类型名称
*/
private String taskTypeSecondName;
/**
* 子任务
*/
private List<TaskSendInfoVo> children;
}
controller层
/**
* 导出周期任务
* @param params
* @param response
*/
@RequestMapping("/exportCycleTask")
public void exportCycleTask(@RequestParam HashMap<String, Object> params, HttpServletResponse response) {
params.put("page", 1);
params.put("limit", Integer.MAX_VALUE);
params.put("queryType", "all");
params.put("cycleFlag", 1);
fileService.exportCycleTask(response, params);
}
service层:
/**
* 导出周期任务数据
* 1、制定header
* 2、查找对应的任务数据
* @param response
*/
public void exportCycleTask(HttpServletResponse response, Map<String, Object> params) {
OutputStream os = null;
String fileName = getFileNameByType("cycle");
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(fileName);
XSSFCellStyle cellStyle = getXssfCellStyle(wb);
List<FileExportField> header = getCycleExcelFileHeader();
List<List<FileExportField>> content = getCycleExcelFileContent(params);
//头
buildHeader(sheet, cellStyle, header);
//内容
buildContent(sheet, cellStyle, content);
try {
String date = DateUtils.format(new Date(), "yyyyMMddHHmmssSSS");
fileName = URLEncoder.encode(fileName,"UTF-8");
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+fileName+date+".xlsx");
os = response.getOutputStream();
wb.write(os);
os.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if(os != null) {
os.close();
}
if(wb != null) {
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
private String getFileNameByType(String type) {
String result = "任务导出";
switch (type) {
case "overview":
break;
case "warn":
result = "预警任务导出";
break;
case "cycle":
result = "周期任务导出";
break;
default:
break;
}
return result;
}
/**
* 单元格样式
* @param wb
* @return org.apache.poi.xssf.usermodel.XSSFCellStyle
*/
private XSSFCellStyle getXssfCellStyle(XSSFWorkbook wb) {
//设置单元格居中
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
//6.1创建字体
Font font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName("宋体");
cellStyle.setFont(font);
return cellStyle;
}
/**
* 获取周期任务header
* @return java.util.List<java.lang.String>
*/
private List<FileExportField> getCycleExcelFileHeader() {
List<FileExportField> header = new ArrayList<>();
FileExportField taskNumHeaderField = new FileExportField("序号", false, 0, 0, 0 , 0, 10);
FileExportField taskSnHeaderField = new FileExportField("编号", false, 0, 0, 1 , 1, 15);
FileExportField taskNameHeaderField = new FileExportField("任务名称", false, 0, 0, 2, 2, 20);
FileExportField cycleUnitHeaderField = new FileExportField("调度周期", false, 0, 0, 3, 3, 25);
FileExportField taskContentHeaderField = new FileExportField("任务内容", false, 0, 0, 4, 4, 40);
FileExportField taskTypeHeaderField = new FileExportField("任务类型", false, 0, 0, 5, 5, 15);
FileExportField leadUnitHeaderField = new FileExportField("牵头单位", false, 0, 0, 6, 6, 20);
FileExportField dutyUnitHeaderField = new FileExportField("责任单位", false, 0, 0, 7, 7, 20);
FileExportField assignedTimeHeaderField = new FileExportField("交办时间", false, 0, 0, 8, 8, 12);
FileExportField limitTimeHeaderField = new FileExportField("完成时限", false, 0, 0, 9, 9, 12);
FileExportField replyContentHeaderField = new FileExportField("落实情况", false, 0, 0, 10, 10, 40);
header.add(taskNumHeaderField);
header.add(taskSnHeaderField);
header.add(taskNameHeaderField);
header.add(cycleUnitHeaderField);
header.add(taskContentHeaderField);
header.add(taskTypeHeaderField);
header.add(leadUnitHeaderField);
header.add(dutyUnitHeaderField);
header.add(assignedTimeHeaderField);
header.add(limitTimeHeaderField);
header.add(replyContentHeaderField);
return header;
}
/**
* 周期任务导出内容
* @param params
* @return java.util.List<FileImportFieldInfo>
*/
private List<List<FileExportField>> getCycleExcelFileContent(Map<String, Object> params) {
List<List<FileExportField>> content = new ArrayList<>();
Query query = new Query(params);
List<TaskSendInfoVo> sendInfoVoList = taskCommonService.queryTask(query);
if(null != sendInfoVoList && sendInfoVoList.size() > 0) {
List<TaskSendInfoVo> result = dealSendInfoVoList(sendInfoVoList);
int size = 1;
for (int i = 0; i < result.size(); i++) {
int index = 0;
List<FileExportField> rowContent = new ArrayList<>();
TaskSendInfoVo info = result.get(i);
int childSize = info.getChildren() == null ? 0 : info.getChildren().size();
boolean isMerge = false;
if(childSize > 0) {
isMerge = true;
}
FileExportField taskNumContentField = new FileExportField((i + 1) + "", isMerge, i + size, i + size + childSize, index , index);
FileExportField taskSnContentField = new FileExportField(info.getTaskSn(), isMerge, i + size, i + size + childSize, index + 1 , index + 1);
FileExportField taskNameContentField = new FileExportField(info.getTaskName(), isMerge, i + size, i + size + childSize, index + 2 , index + 2);
FileExportField cycleUnitContentField = new FileExportField(dealCycleUnit(info.getCycleUnit()), isMerge, i + size, i + size + childSize, index + 3, index + 3);
FileExportField taskContentContentField = new FileExportField(info.getTaskContent(), isMerge, i + size, i + size + childSize, index + 4 , index + 4);
FileExportField taskTypeContentField = new FileExportField(info.getTaskTypeSecondName(), isMerge, i + size, i + size + childSize, index + 5, index + 5);
FileExportField leadUnitContentField = new FileExportField(info.getLeadDeptName(), isMerge, i + size, i + size + childSize, index + 6, index + 6);
FileExportField dutyUnitContentField = new FileExportField(info.getDutyDeptName(), isMerge, i + size, i + size + childSize, index + 7, index + 7);
FileExportField assignedTimeContentField = new FileExportField(null == info.getAssignedTime() ? "" : DateUtils.getFormatDateTime(info.getAssignedTime(), DateUtils.yyyyMMdd), false, i + size, i + size, index + 8, index + 8);
FileExportField limitTimeContentField = new FileExportField(null == info.getLimitTime() ? "" : DateUtils.getFormatDateTime(info.getLimitTime(), DateUtils.yyyyMMdd), false, i + size, i + size, index + 9, index + 9);
FileExportField replyContentContentField = new FileExportField(info.getReplyContent(), false, i + size, i + size, index + 10, index + 10);
List<FileExportField> assignedTimeContentChildrenField = new ArrayList<>();
List<FileExportField> limitTimeContentChildrenField = new ArrayList<>();
List<FileExportField> replyContentContentChildrenField = new ArrayList<>();
//处理子类
if(childSize > 0) {
List<TaskSendInfoVo> children = info.getChildren();
for (int j = 0; j < children.size(); j++) {
TaskSendInfoVo child = children.get(j);
assignedTimeContentChildrenField.add(new FileExportField(null == child.getAssignedTime() ? "" : DateUtils.getFormatDateTime(child.getAssignedTime(), DateUtils.yyyyMMdd), false, i + size + j + 1, i + size + j + 1, index + 8, index + 8));
limitTimeContentChildrenField.add(new FileExportField(null == child.getLimitTime() ? "" : DateUtils.getFormatDateTime(child.getLimitTime(), DateUtils.yyyyMMdd), false, i + size + j + 1, i + size + j + 1, index + 9, index + 9));
replyContentContentChildrenField.add(new FileExportField(child.getReplyContent(), false, i + size + j + 1, i + size + j + 1, index + 10, index + 10));
}
}
assignedTimeContentField.setChildren(assignedTimeContentChildrenField);
limitTimeContentField.setChildren(limitTimeContentChildrenField);
replyContentContentField.setChildren(replyContentContentChildrenField);
rowContent.add(taskNumContentField);
rowContent.add(taskSnContentField);
rowContent.add(taskNameContentField);
rowContent.add(cycleUnitContentField);
rowContent.add(taskContentContentField);
rowContent.add(taskTypeContentField);
rowContent.add(leadUnitContentField);
rowContent.add(dutyUnitContentField);
rowContent.add(assignedTimeContentField);
rowContent.add(limitTimeContentField);
rowContent.add(replyContentContentField);
content.add(rowContent);
size += childSize;
}
}
return content;
}
/**
* 处理数据,树结构
* @param sendInfoVoList
*/
private List<TaskSendInfoVo> dealSendInfoVoList(List<TaskSendInfoVo> sendInfoVoList) {
List<TaskSendInfoVo> result = new ArrayList<>();
//分离父节点和子节点
Map<String, List<TaskSendInfoVo>> map = new HashMap<>();
List<TaskSendInfoVo> parents = new ArrayList<>();
List<TaskSendInfoVo> children = new ArrayList<>();
for (int i = 0; i < sendInfoVoList.size(); i++) {
TaskSendInfoVo taskSendInfoVo = sendInfoVoList.get(i);
if(StringUtils.isBlank(taskSendInfoVo.getParentTaskId())) {
parents.add(taskSendInfoVo);
}else {
children.add(taskSendInfoVo);
}
}
return sortInfoToTree(parents, children);
}
/**
* 制定头
* @param sheet
* @param cellStyle
* @param cellFieldList
*/
private void buildContent(XSSFSheet sheet, XSSFCellStyle cellStyle, List<List<FileExportField>> cellFieldList) {
for(int i = 0; i < cellFieldList.size(); i ++) {
List<FileExportField> itemExportFieldList = cellFieldList.get(i);
if(null != itemExportFieldList && itemExportFieldList.size() > 0) {
for(int j = 0; j < itemExportFieldList.size(); j ++) {
FileExportField cellField = itemExportFieldList.get(j);
XSSFRow row = sheet.getRow(cellField.getFirstRow());
if(row == null) {
row = sheet.createRow(cellField.getFirstRow());
}
XSSFCell cell = row.createCell(cellField.getFirstCol());
cell.setCellValue(cellField.getName());
cell.setCellStyle(cellStyle);
if(cellField.getIsMerge()) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(cellField.getFirstRow(), cellField.getLastRow(),
cellField.getFirstCol(), cellField.getLastCol());
RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
sheet.addMergedRegion(cellRangeAddress);//解决合并单元格后边框没有问题
}
if(cellField.getChildren() != null && cellField.getChildren().size() > 0) {
List<List<FileExportField>> childCellFieldList = new ArrayList<>();
childCellFieldList.add(cellField.getChildren());
buildContent(sheet, cellStyle, childCellFieldList);
}
}
}
}
}
/**
* 制定头
* @param sheet
* @param cellStyle
* @param cellFieldList
*/
private void buildHeader(XSSFSheet sheet, XSSFCellStyle cellStyle, List<FileExportField> cellFieldList) {
for(int i = 0; i < cellFieldList.size(); i ++) {
FileExportField cellField = cellFieldList.get(i);
XSSFRow row = sheet.getRow(cellField.getFirstRow());
if(row == null) {
row = sheet.createRow(cellField.getFirstRow());
}
XSSFCell cell = row.createCell(cellField.getFirstCol());
cell.setCellValue(cellField.getName());
cell.setCellStyle(cellStyle);
if(cellField.getIsMerge()) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(cellField.getFirstRow(), cellField.getLastRow(),
cellField.getFirstCol(), cellField.getLastCol());
RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
sheet.addMergedRegion(cellRangeAddress);
}
sheet.setColumnWidth(i, cellField.getWidth() * 256);
if(cellField.getChildren() != null && cellField.getChildren().size() > 0) {
buildHeader(sheet, cellStyle, cellField.getChildren());
}
}
}
注意
导出excel不同格式使用poi不同的对象
Excel97-2003版本,扩展名为.xls:HSSF
Excel2007版本开始,扩展名为.xlsx:XSSF