//获取数据
public Resp export(AssessStatisticsVO assessStatisticsVO, HttpServletResponse response) {
AssessStatisticsDTO assessStatisticsDTO = getAssessStatistics(assessStatisticsVO);
Workbook workbook = new XSSFWorkbook();
// 公共样式
CellStyle borderStyle = createBorderStyle(workbook);
CellStyle titleStyle = createTitleStyle(workbook);
try {
createSheet(workbook, "类型统计", assessStatisticsDTO.getTypeStatisticsInfo(), getTypeStatisticsHeaders(), borderStyle, titleStyle);
createSheet(workbook, "评估方向统计", assessStatisticsDTO.getDirectionStatisticsInfo(), getDirectionStatisticsHeaders(), borderStyle, titleStyle);
createSheet(workbook, "评估方向-类型统计", assessStatisticsDTO.getDirectionTypeInfo(), getDirectionTypeInfoHeaders(), borderStyle, titleStyle);
createSheet(workbook, "评估方向总费用统计", assessStatisticsDTO.getAssessDirectionCostInfo(), getAssessDirectionCostHeaders(), borderStyle, titleStyle);
createSheet(workbook, "评估方向各范围费用统计", assessStatisticsDTO.getAssessDirectionRangeCostInfo(), getAssessDirectionRangeCostHeaders(), borderStyle, titleStyle);
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("评估统计指标", "UTF-8") + ".xls");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
return Resp.ok("评估统计导出成功");
} catch (Exception e) {
log.error("评估统计导出失败", e);
return Resp.ok("评估统计导出失败");
}
}
//填充数据
private void createSheet(Workbook workbook, String sheetName, List<?> data, String[] headers, CellStyle borderStyle, CellStyle titleStyle) {
Sheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(20);
Row titleRow = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
sheet.setColumnWidth(i, 20 * 256);
Cell titleCell = titleRow.createCell(i);
titleCell.setCellValue(headers[i]); // 设置中文标题名称
titleCell.setCellStyle(titleStyle); // 设置标题单元格的样式
}
// 填充数据
int rowIndex = 1;
for (Object item : data) {
Row row = sheet.createRow(rowIndex++);
boolean applyStyle = false;
int cellIndex = 0;
for (Field field : item.getClass().getDeclaredFields()) {
field.setAccessible(true);
try {
Cell cell = row.createCell(cellIndex++);
Object value = field.get(item);
if (value != null) {
if (value.toString().equals("快速评估")) {
applyStyle = true;
}
cell.setCellValue(value.toString());
} else {
cell.setCellValue(""); // 空值处理,确保不会出现 null 值
}
if (applyStyle) {
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell1 = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
CellStyle dataStyle = dataStyle(workbook); // 假设这个方法返回您想要的样式
cell1.setCellStyle(dataStyle);
}
}
} catch (IllegalAccessException e) {
log.error("获取字段值失败", e);
}
}
}
}
//公共边框样式
private CellStyle createBorderStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
return style;
}
//公共标题样式
private CellStyle createTitleStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.cloneStyleFrom(createBorderStyle(workbook));
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = workbook.createFont();
font.setColor(IndexedColors.BLACK.getIndex());
font.setBold(true);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);
font.setFontHeightInPoints((short) 16);
return style;
}
//数据单元格样式
private CellStyle dataStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.cloneStyleFrom(createBorderStyle(workbook));
// 设置背景颜色为黄色(使用 IndexedColors 枚举中的常量)
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
// 设置填充样式为实心填充
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return cellStyle;
}
// 定义各个工作表的中文标题数组
private String[] getTypeStatisticsHeaders() {
return new String[]{"统计类型", "数量"};
}
private String[] getDirectionStatisticsHeaders() {
return new String[]{"评估方向", "数量"};
}
private String[] getDirectionTypeInfoHeaders() {
return new String[]{"评估方向", "类型", "数量"};
}
private String[] getAssessDirectionCostHeaders() {
return new String[]{"评估方向", "开发费用", "运维费用", "总费用", "评估类型"};
}
private String[] getAssessDirectionRangeCostHeaders() {
return new String[]{"评估方向", "评估范围", "开发费用", "运维费用", "总费用", "评估类型"};
}
java 导出excel 多个sheet
最新推荐文章于 2024-06-27 08:00:00 发布