【Java】使用XSSFWorkbook导出Excel文件 【Java】使用XSSFWorkbook导出Excel文件

【和上一篇中的集合合并是关联的🤗】今天在项目中遇到一个需要导出的需求，导出的sheet页比较多，并且有的sheet页中的表头标题数据不确定（标题数量是一个不确定长度的List集合和List.size()个固定标题），表格中的数据也是根据标题中的List集合中元素的多少来决定的，比较特殊，因此记录一下，如下：

 导出代码展示如下：

public void exportCollect(Long periodId, HttpServletResponse response) {
 BmscorePeriod period = scorePeriodMapper.selectById(periodId);
 if (period != null) {
 String exportName = "打分汇总"+ TimeUtil.formatDate(new Date(),"yyyyMMddHHmmss");
 //创建工作薄对象
 XSSFWorkbook wb = new XSSFWorkbook();
 //创建标题行样式
 XSSFCellStyle headStyle = headStyle(wb);
 //创建内容行样式
 XSSFCellStyle contentCenterStyle = contentStyle(wb, HorizontalAlignment.CENTER);
 XSSFCellStyle contentLeftStyle = contentStyle(wb, HorizontalAlignment.LEFT);
 XSSFCellStyle contentRightStyle = contentStyle(wb, HorizontalAlignment.RIGHT);
 // 第一个sheet页【券商汇总排名】
 XSSFSheet sheet1 = wb.createSheet("券商汇总排名");
 createSheet1(sheet1, periodId, headStyle, contentCenterStyle);
 // 第二个sheet页【行业汇总排名】
 XSSFSheet sheet2 = wb.createSheet("行业汇总排名");
 createSheet2(sheet2, periodId, headStyle, contentCenterStyle);
 // 第三个sheet页【券商行业排名】
 XSSFSheet sheet3 = wb.createSheet("券商行业排名");
 createSheet3(sheet3, periodId, headStyle, contentCenterStyle);
 
 // 第四个sheet页【券商占比统计】
 XSSFSheet sheet4 = wb.createSheet("券商占比统计");
 createSheet4(sheet4, periodId, headStyle, contentCenterStyle);
 
 OutputStream os = null;
 try {
 String fileName = new String(exportName.getBytes(),"ISO-8859-1");
 response.setContentType("application/vnd.ms-excel");
 response.setHeader("Content-Disposition","attachment; filename=" + fileName + ".xlsx");
 os = response.getOutputStream();
 wb.write(os);
 } catch (Exception ex){
 log.info("导出失败.",ex);
 } finally {
 if(null != os){
 try {
 os.close();
 } catch (IOException e) {
 log.info("导出失败.",e);
 }
 }
 }
 }
 }
/**
 * 创建第一个sheet【券商汇总排名】
 * @param periodId
 * @param headStyle
 * @param contentCenterStyle
 */
 private void createSheet1(XSSFSheet sheet, Long periodId, XSSFCellStyle headStyle, XSSFCellStyle contentCenterStyle) {
 // 设置列宽
 sheet.setColumnWidth(0, 20 * 256);
 sheet.setColumnWidth(1, 20 * 256);
 sheet.setColumnWidth(2, 20 * 256);
 sheet.setColumnWidth(3, 20 * 256);
 sheet.setColumnWidth(4, 20 * 256);
 //创建标题行
 XSSFRow headRow = sheet.createRow(0);
 //创建标题列
 XSSFCell headCell1 = headRow.createCell(0);
 headCell1.setCellValue("券商名称");
 headCell1.setCellStyle(headStyle);
 XSSFCell headCell2 = headRow.createCell(1);
 headCell2.setCellValue("常规服务汇总得分");
 headCell2.setCellStyle(headStyle);
 XSSFCell headCell3 = headRow.createCell(2);
 headCell3.setCellValue("定制服务汇总得分");
 headCell3.setCellStyle(headStyle);
 XSSFCell headCell4 = headRow.createCell(3);
 headCell4.setCellValue("汇总得分");
 headCell4.setCellStyle(headStyle);
 XSSFCell headCell5 = headRow.createCell(4);
 headCell5.setCellValue("排名");
 headCell5.setCellStyle(headStyle);
 List list = findBrokerCollectRank(periodId, null);
 if (CollectionUtils.isNotEmpty(list)) {
 int rowNum = 1;
 for (MarkCollectResultDTO data : list) {
 XSSFRow contentRow = sheet.createRow(rowNum);
 contentRow.setHeight((short)800);
 XSSFCell contentCell1 = contentRow.createCell(0);
 contentCell1.setCellStyle(contentCenterStyle);
 contentCell1.setCellValue(StringUtil.safeToString(data.getBrokerName(), ""));
 XSSFCell contentCell2 = contentRow.createCell(1);
 contentCell2.setCellStyle(contentCenterStyle);
 contentCell2.setCellValue(StringUtil.safeToString(data.getRoutineScore(), ""));
 XSSFCell contentCell3 = contentRow.createCell(2);
 contentCell3.setCellStyle(contentCenterStyle);
 contentCell3.setCellValue(StringUtil.safeToString(data.getCustomScore(), ""));
 XSSFCell contentCell4 = contentRow.createCell(3);
 contentCell4.setCellStyle(contentCenterStyle);
 contentCell4.setCellValue(StringUtil.safeToString(data.getSumScore(), ""));
 XSSFCell contentCell5 = contentRow.createCell(4);
 contentCell5.setCellStyle(contentCenterStyle);
 contentCell5.setCellValue(StringUtil.safeToString(data.getRank(), ""));
 rowNum++;
 }
 }
 }
/**
 * 【行业汇总排名】
 * @param sheet
 * @param periodId
 * @param headStyle
 * @param contentCenterStyle
 */
 private void createSheet2(XSSFSheet sheet, Long periodId, XSSFCellStyle headStyle, XSSFCellStyle contentCenterStyle) {
 sheet.setColumnWidth(0, 20 * 256);
 sheet.setColumnWidth(1, 20 * 256);
 sheet.setColumnWidth(2, 20 * 256);
 sheet.setColumnWidth(3, 20 * 256);
 sheet.setColumnWidth(4, 20 * 256);
 //创建标题行
 XSSFRow headRow = sheet.createRow(0);
 //创建标题列
 XSSFCell headCell1 = headRow.createCell(0);
 headCell1.setCellValue("行业名称");
 headCell1.setCellStyle(headStyle);
 XSSFCell headCell2 = headRow.createCell(1);
 headCell2.setCellValue("常规服务汇总得分");
 headCell2.setCellStyle(headStyle);
 XSSFCell headCell3 = headRow.createCell(2);
 headCell3.setCellValue("定制服务汇总得分");
 headCell3.setCellStyle(headStyle);
 XSSFCell headCell4 = headRow.createCell(3);
 headCell4.setCellValue("汇总得分");
 headCell4.setCellStyle(headStyle);
 XSSFCell headCell5 = headRow.createCell(4);
 headCell5.setCellValue("排名");
 headCell5.setCellStyle(headStyle);
 List list = findIndustryCollectRank(periodId, null);
 if (CollectionUtils.isNotEmpty(list)) {
 int rowNum = 1;
 for (MarkCollectResultDTO data : list) {
 XSSFRow contentRow = sheet.createRow(rowNum);
 contentRow.setHeight((short)800);
 XSSFCell contentCell1 = contentRow.createCell(0);
 contentCell1.setCellStyle(contentCenterStyle);
 contentCell1.setCellValue(StringUtil.safeToString(data.getIndustryName(), ""));
 XSSFCell contentCell2 = contentRow.createCell(1);
 contentCell2.setCellStyle(contentCenterStyle);
 contentCell2.setCellValue(StringUtil.safeToString(data.getRoutineScore(), ""));
 XSSFCell contentCell3 = contentRow.createCell(2);
 contentCell3.setCellStyle(contentCenterStyle);
 contentCell3.setCellValue(StringUtil.safeToString(data.getCustomScore(), ""));
 XSSFCell contentCell4 = contentRow.createCell(3);
 contentCell4.setCellStyle(contentCenterStyle);
 contentCell4.setCellValue(StringUtil.safeToString(data.getSumScore(), ""));
 XSSFCell contentCell5 = contentRow.createCell(4);
 contentCell5.setCellStyle(contentCenterStyle);
 contentCell5.setCellValue(StringUtil.safeToString(data.getRank(), ""));
 rowNum++;
 }
 }
 }
/**
 * 【券商行业排名】
 * @param sheet
 * @param periodId
 * @param headStyle
 * @param contentCenterStyle
 */
 private void createSheet3(XSSFSheet sheet, Long periodId, XSSFCellStyle headStyle, XSSFCellStyle contentCenterStyle) {
 //创建标题行
 XSSFRow headRow = sheet.createRow(0);
 //创建标题列
 ArrayList titleList = findAllIndustry(periodId, null);
 int num = 0;
 for (int i = 0; i < titleList.size(); i++) {
 int j = (2 * i) + 1;
 sheet.setColumnWidth(i * 2, 20 * 256);
 sheet.setColumnWidth(j, 20 * 256);
 XSSFCell headCell1 = headRow.createCell(i * 2);
 headCell1.setCellValue("券商名称");
 headCell1.setCellStyle(headStyle);
 XSSFCell headCell2 = headRow.createCell(j);
 headCell2.setCellValue(titleList.get(i));
 headCell2.setCellStyle(headStyle);
 if (i == titleList.size() - 1) {
 num = j;
 }
 }
 // 最后增加排名列
 XSSFCell rankCell = headRow.createCell(num + 1);
 rankCell.setCellValue("排名");
 rankCell.setCellStyle(headStyle);
 List list = findBrokerAndIndustryRank(periodId, null, null);
 if (CollectionUtils.isNotEmpty(list)) {
 int rowNum = 1;
 for (LinkedHashMap data : list) {
 XSSFRow contentRow = sheet.createRow(rowNum);
 contentRow.setHeight((short)800);
 for (int i = 0; i < titleList.size(); i++) {
 int j = (2 * i) + 1;
 XSSFCell contentCell1 = contentRow.createCell(i*2);
 contentCell1.setCellStyle(contentCenterStyle);
 contentCell1.setCellValue(StringUtil.safeToString(data.get("brokername" + (i + 1)), ""));
 XSSFCell contentCell2 = contentRow.createCell(j);
 contentCell2.setCellStyle(contentCenterStyle);
 contentCell2.setCellValue(StringUtil.safeToString(data.get("sumscore" + (i + 1)), ""));
 }
 XSSFCell contentCell = contentRow.createCell(num + 1);
 contentCell.setCellStyle(contentCenterStyle);
 contentCell.setCellValue(StringUtil.safeToString(data.get("rank"), ""));
 rowNum++;
 }
 }
 }
/**
 * 创建第四个sheet页【券商占比统计】
 * @param sheet
 * @param periodId
 * @param headStyle
 * @param contentCenterStyle
 */
 private void createSheet4(XSSFSheet sheet, Long periodId, XSSFCellStyle headStyle, XSSFCellStyle contentCenterStyle) {
 sheet.setColumnWidth(0, 20 * 256);
 sheet.setColumnWidth(1, 20 * 256);
 sheet.setColumnWidth(2, 20 * 256);
 sheet.setColumnWidth(3, 20 * 256);
 sheet.setColumnWidth(4, 20 * 256);
 //创建标题行
 XSSFRow headRow = sheet.createRow(0);
 //创建标题列
 XSSFCell headCell1 = headRow.createCell(0);
 headCell1.setCellValue("券商名称");
 headCell1.setCellStyle(headStyle);
 XSSFCell headCell2 = headRow.createCell(1);
 headCell2.setCellValue("行业方向");
 headCell2.setCellStyle(headStyle);
 XSSFCell headCell3 = headRow.createCell(2);
 headCell3.setCellValue("研究员");
 headCell3.setCellStyle(headStyle);
 XSSFCell headCell4 = headRow.createCell(3);
 headCell4.setCellValue("分数");
 headCell4.setCellStyle(headStyle);
 XSSFCell headCell5 = headRow.createCell(4);
 headCell5.setCellValue("占比");
 headCell5.setCellStyle(headStyle);
 MarkCollectRequestDTO dto = new MarkCollectRequestDTO();
 dto.setPeriodId(periodId);
 List list = findBrokerStatistics(dto);
 if (CollectionUtils.isNotEmpty(list)) {
 int rowNum = 1;
 for (BrokerStatisticsResultDTO data : list) {
 XSSFRow contentRow = sheet.createRow(rowNum);
 contentRow.setHeight((short)800);
 XSSFCell contentCell1 = contentRow.createCell(0);
 contentCell1.setCellStyle(contentCenterStyle);
 contentCell1.setCellValue(StringUtil.safeToString(data.getBrokerName(), ""));
 XSSFCell contentCell2 = contentRow.createCell(1);
 contentCell2.setCellStyle(contentCenterStyle);
 contentCell2.setCellValue(StringUtil.safeToString(data.getIndustryName(), ""));
 XSSFCell contentCell3 = contentRow.createCell(2);
 contentCell3.setCellStyle(contentCenterStyle);
 contentCell3.setCellValue(StringUtil.safeToString(data.getResearcherName(), ""));
 XSSFCell contentCell4 = contentRow.createCell(3);
 contentCell4.setCellStyle(contentCenterStyle);
 contentCell4.setCellValue(StringUtil.safeToString(data.getScore(), ""));
 XSSFCell contentCell5 = contentRow.createCell(4);
 contentCell5.setCellStyle(contentCenterStyle);
 contentCell5.setCellValue(StringUtil.safeToString(data.getStatistics(), ""));
 rowNum++;
 }
 }
 }

private static XSSFCellStyle headStyle(XSSFWorkbook wb){
 //创建样式对象
 XSSFCellStyle headStyle = wb.createCellStyle();
 //创建字体
 XSSFFont headFont = wb.createFont();
 headFont.setFontName("微软雅黑");
 headFont.setFontHeightInPoints((short)12);
 //字体粗细
 headFont.setBold(true);
 headStyle.setAlignment(HorizontalAlignment.CENTER);
 headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 //设置自动换行
 headStyle.setWrapText(true);
 headStyle.setFont(headFont);
 return headStyle;
 }
 private static XSSFCellStyle contentStyle(XSSFWorkbook wb, HorizontalAlignment align){
 XSSFCellStyle contentStyle = wb.createCellStyle();
 //居中
 XSSFFont contentFont = wb.createFont();
 contentFont.setFontName("微软雅黑");
 contentFont.setColor(HSSFFont.COLOR_NORMAL);
 contentFont.setFontHeightInPoints((short)10);
 contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 contentStyle.setAlignment(align);
 //设置自动换行
 contentStyle.setWrapText(true);
 contentStyle.setFont(contentFont);
 return contentStyle;
 }