POI原生自定义导出（多Sheet页）
代码记录
@ApiOperation("导出")
@ApiOperationSupport(order = 6)
@PostMapping("/export")
@Log(title = "工点施工项目", businessType = BusinessType.EXPORT)
@PreAuthorize("@auth.hasPermi('base:pointItem:export')")
public void export(HttpServletResponse response, PointItemDTO dto) {
WorkPointDTO wpd = new WorkPointDTO();
wpd.setProjectId(dto.getProjectId());
wpd.setWorkAreaId(dto.getWorkAreaId());
wpd.setSerial(dto.getSerial());
wpd.setRegion(dto.getRegion());
wpd.setType(dto.getType());
wpd.setId(dto.getWorkPointId());
List<WorkPointVO> templateData = workPointService.getTemplateData(wpd);
try (
XSSFWorkbook workbook = new XSSFWorkbook();
) {
// 写入各条记录,每条记录对应excel表中的一行
CreationHelper createHelper = workbook.getCreationHelper();
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font titleFont = workbook.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 14);
titleFont.setBold(true);
titleFont.setColor(IndexedColors.WHITE.getIndex());
titleStyle.setFont(titleFont);
titleStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
CellStyle numStyle = workbook.createCellStyle();
numStyle.setAlignment(HorizontalAlignment.CENTER);
numStyle.setVerticalAlignment(VerticalAlignment.CENTER);
numStyle.setBorderRight(BorderStyle.THIN);
numStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
numStyle.setBorderLeft(BorderStyle.THIN);
numStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
numStyle.setBorderTop(BorderStyle.THIN);
numStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
numStyle.setBorderBottom(BorderStyle.THIN);
numStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font numFont = workbook.createFont();
numFont.setFontName("Arial");
numFont.setFontHeightInPoints((short) 10);
numStyle.setFont(numFont);
numStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
numStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
numStyle.setDataFormat(createHelper.createDataFormat().getFormat("#,##0.00"));
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
dataStyle.setBorderRight(BorderStyle.THIN);
dataStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
dataStyle.setBorderLeft(BorderStyle.THIN);
dataStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
dataStyle.setBorderTop(BorderStyle.THIN);
dataStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
dataStyle.setBorderBottom(BorderStyle.THIN);
dataStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = workbook.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
dataStyle.setFont(dataFont);
dataStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
CellStyle data4NameStyle = workbook.createCellStyle();
data4NameStyle.setAlignment(HorizontalAlignment.LEFT);
data4NameStyle.setVerticalAlignment(VerticalAlignment.CENTER);
data4NameStyle.setBorderRight(BorderStyle.THIN);
data4NameStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
data4NameStyle.setBorderLeft(BorderStyle.THIN);
data4NameStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
data4NameStyle.setBorderTop(BorderStyle.THIN);
data4NameStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
data4NameStyle.setBorderBottom(BorderStyle.THIN);
data4NameStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font data4NameFont = workbook.createFont();
data4NameFont.setFontName("Arial");
data4NameFont.setFontHeightInPoints((short) 10);
data4NameFont.setBold(true);
data4NameStyle.setFont(data4NameFont);
data4NameStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
data4NameStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
for (WorkPointVO vo : templateData) {
XSSFSheet sheet = workbook.createSheet(vo.getSerial());
XSSFRow titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(26);
XSSFCell itemTitleCell1 = titleRow.createCell(0);
itemTitleCell1.setCellStyle(titleStyle);
itemTitleCell1.setCellValue("项目名称");
XSSFCell itemTitleCell2 = titleRow.createCell(1);
itemTitleCell2.setCellStyle(titleStyle);
itemTitleCell2.setCellValue("单位");
XSSFCell itemTitleCell3 = titleRow.createCell(2);
itemTitleCell3.setCellStyle(titleStyle);
itemTitleCell3.setCellValue("额定编号");
XSSFCell itemTitleCell4 = titleRow.createCell(3);
itemTitleCell4.setCellStyle(titleStyle);
itemTitleCell4.setCellValue("数量");
XSSFCell itemTitleCell5 = titleRow.createCell(4);
itemTitleCell5.setCellStyle(titleStyle);
itemTitleCell5.setCellValue("综合单价（元）");
XSSFCell itemTitleCell6 = titleRow.createCell(5);
itemTitleCell6.setCellStyle(titleStyle);
itemTitleCell6.setCellValue("综合合价（元）");
XSSFCell itemTitleCell7 = titleRow.createCell(6);
itemTitleCell7.setCellStyle(titleStyle);
itemTitleCell7.setCellValue("已完成数量");
XSSFCell itemTitleCell8 = titleRow.createCell(7);
itemTitleCell8.setCellStyle(titleStyle);
itemTitleCell8.setCellValue("已完综合合价（元）");
List<PointItemVO> items = vo.getItems();
for (int i = 0; i < items.size(); i++) {
PointItemVO item = items.get(i);
XSSFRow dataRow = sheet.createRow(i + 1);
dataRow.setHeightInPoints(24);
XSSFCell dataRowCell1 = dataRow.createCell(0);
dataRowCell1.setCellStyle("1".equals(item.getLeaf()) ? dataStyle : data4NameStyle);
dataRowCell1.setCellValue(item.getName());
if ("1".equals(item.getLeaf())) {
XSSFCell dataRowCell2 = dataRow.createCell(1);
dataRowCell2.setCellStyle(dataStyle);
dataRowCell2.setCellValue(item.getUnit());
XSSFCell dataRowCell3 = dataRow.createCell(2);
dataRowCell3.setCellStyle(dataStyle);
dataRowCell3.setCellValue(item.getSerial());
BigDecimal num = item.getNum();
num = num == null ? BigDecimal.ZERO : num;
XSSFCell dataRowCell4 = dataRow.createCell(3);
dataRowCell4.setCellStyle(numStyle);
dataRowCell4.setCellValue(num.toString());
BigDecimal price = item.getPrice();
price = price == null ? BigDecimal.ZERO : price;
XSSFCell dataRowCell5 = dataRow.createCell(4);
dataRowCell5.setCellStyle(numStyle);
dataRowCell5.setCellValue(price.toString());
XSSFCell dataRowCell6 = dataRow.createCell(5);
dataRowCell6.setCellStyle(numStyle);
dataRowCell6.setCellValue(item.getTotal().toString());
BigDecimal completeNum = item.getCompleteNum();
completeNum = completeNum == null ? BigDecimal.ZERO : completeNum;
XSSFCell dataRowCell7 = dataRow.createCell(6);
dataRowCell7.setCellStyle(numStyle);
dataRowCell7.setCellValue(completeNum.toPlainString());
} else {
dataRow.createCell(1).setCellStyle(dataStyle);
dataRow.createCell(2).setCellStyle(dataStyle);
dataRow.createCell(3).setCellStyle(dataStyle);
dataRow.createCell(4).setCellStyle(dataStyle);
dataRow.createCell(5).setCellStyle(dataStyle);
dataRow.createCell(6).setCellStyle(dataStyle);
}
BigDecimal completeTotal = item.getCompleteTotal();
completeTotal = completeTotal == null ? BigDecimal.ZERO : completeTotal;
XSSFCell dataRowCell8 = dataRow.createCell(7);
dataRowCell8.setCellStyle(numStyle);
dataRowCell8.setCellValue(completeTotal.toString());
}
XSSFRow sumRow = sheet.createRow(items.size() + 1);
sumRow.setHeightInPoints(24);
sheet.addMergedRegion(new CellRangeAddress(items.size() + 1, items.size() + 1, 0, 2));
XSSFCell dataRowCell1 = sumRow.createCell(0);
dataRowCell1.setCellStyle(data4NameStyle);
dataRowCell1.setCellValue("合计");
sumRow.createCell(1).setCellStyle(dataStyle);
sumRow.createCell(2).setCellStyle(dataStyle);
sumRow.createCell(3).setCellStyle(dataStyle);
sumRow.createCell(4).setCellStyle(dataStyle);
BigDecimal totalSum = items.stream().filter(e -> "1".equals(e.getLeaf())).map(f -> f.getTotal() == null ? BigDecimal.ZERO : f.getTotal()).reduce(BigDecimal.ZERO, BigDecimal::add);
XSSFCell sumRowCell4 = sumRow.createCell(5);
sumRowCell4.setCellStyle(numStyle);
sumRowCell4.setCellValue(totalSum.toPlainString());
sumRow.createCell(6).setCellStyle(dataStyle);
BigDecimal completeSum = items.stream().filter(e -> "1".equals(e.getLeaf())).map(f -> f.getCompleteTotal() == null ? BigDecimal.ZERO : f.getCompleteTotal()).reduce(BigDecimal.ZERO, BigDecimal::add);
XSSFCell sumRowCell8 = sumRow.createCell(7);
sumRowCell8.setCellStyle(numStyle);
sumRowCell8.setCellValue(completeSum.toPlainString());
int width = 20 * 256;
sheet.setColumnWidth(0, 7680);
sheet.setColumnWidth(1, width);
sheet.setColumnWidth(2, width);
sheet.setColumnWidth(3, width);
sheet.setColumnWidth(4, width);
sheet.setColumnWidth(5, width);
sheet.setColumnWidth(6, width);
sheet.setColumnWidth(7, 30 * 256);
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
workbook.write(response.getOutputStream());
} catch (IOException ioe) {
logger.error(ioe.getMessage(), ioe);
}
}
评论区