导出excel合并行列

最新推荐文章于 2024-06-26 18:13:59 发布

原创最新推荐文章于 2024-06-26 18:13:59 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

excel导出专栏收录该内容

2 篇文章

订阅专栏

该博客介绍了如何使用Apache POI库来实现复杂动态表头和合并单元格的Excel导出功能。通过创建workbook、设置列宽、定义样式以及处理多行表头，实现了自定义的月度业务明细报表。代码示例详细展示了创建标题、表头和内容样式的步骤，包括行高、字体样式、单元格合并等细节。

做项目的时候一个导出的需求，使用框架封装的导出功能不能满足复杂动态表头的导出，因此，使用POI原生导出，借鉴了一些网上的导出demo。
本文介绍 HSSFWorkbook 导出Excel多行表头、合并单元格的表格。
表头样式如下：

在这里插入图片描述
直接上代码：

**
* 导出excel(月度业务明细)
*/
@GetMapping("/export")
public void exportExcel(String startTime,BusinessDetail businessDetail){
//表头动态日期
String year= startTime.substring(0,4);
String month = startTime.substring(5,7);
String day = startTime.substring(8,10);
Integer srcyear = (Integer.parseInt(year)-1);
//1.创建一个workbook,对应一个excel文件
HSSFWorkbook wb = new HSSFWorkbook();

    //2.在workbook中添加一个sheet,对应Excel中的sheet
    HSSFSheet sheet = wb.createSheet("月度业务明细");

	//设置每一列的列宽
	sheet.setColumnWidth(0,256*15);
    sheet.setColumnWidth(1,256*15);
    sheet.setColumnWidth(2,256*15);
    sheet.setColumnWidth(3,256*15);
    sheet.setColumnWidth(4,256*15);
    sheet.setColumnWidth(5,256*15);
    sheet.setColumnWidth(6,256*15);
    sheet.setColumnWidth(7,256*15);
    sheet.setColumnWidth(8,256*15);
    sheet.setColumnWidth(9,256*15);
    sheet.setColumnWidth(10,256*15);
    sheet.setColumnWidth(11,256*15);
    sheet.setColumnWidth(12,256*15);
    sheet.setColumnWidth(13,256*15);
    sheet.setColumnWidth(14,256*15);
    sheet.setColumnWidth(15,256*15);

    //3.设置样式以及字体样式
    HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
    HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
    HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);

    //4.创建标题，合并标题单元格
    //行号
    int rowNum = 0;

    //创建第一行，索引从0开始(标题行)
    HSSFRow row0 = sheet.createRow(rowNum++);
    row0.setHeight((short) 800);// 设置行高
    String title = "月度业务明细";
    HSSFCell c00 = row0.createCell(0);
    c00.setCellValue(title);
    c00.setCellStyle(titleStyle);
    // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 15));//标题合并单元格操作，总列数为16

    //第二行
    HSSFRow row1 = sheet.createRow(rowNum++);
    row1.setHeight((short)500);
    String[] row_first = {"客户",month+"月发车",srcyear+"年12月31日前货款回收(期前欠款)",year+"年货款回收","截止"+year+"年"+month+"月"+day+"日业务明细","","","","","","","","","",month+"月份新增欠款",month+"月收款合计"};
    for (int i = 0; i < row_first.length; i++) {
        HSSFCell tempCell = row1.createCell(i);
        tempCell.setCellValue(row_first[i]);
        tempCell.setCellStyle(headerStyle);
    }
    //合并单元格
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));//经销商或客户
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));//12月发车
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));//期前欠款
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 3, 3));//2019年货款回收
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 13));//截止2019年12月27日业务明细
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 14, 14));//12月份新增加欠款
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 15, 15));//12月份收款合计

    //第三行
    HSSFRow row2 = sheet.createRow(rowNum++);
    row2.setHeight((short)500);
    String[] row_second = {"","","","",month+"月合同发车","","",month+"月分期管理费及利息","","",month+"月代收代付运费","","",month+"月预付款","",""};
    for (int i = 0; i < row_second.length; i++) {
        HSSFCell tempCell = row2.createCell(i);
        tempCell.setCellValue(row_second[i]);
        tempCell.setCellStyle(headerStyle);
    }

    //合并单元格
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 6));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 9));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 10, 12));
    sheet.addMergedRegion(new CellRangeAddress(2, 3, 13, 13));

    //第四行
    HSSFRow row3 = sheet.createRow(rowNum++);
    row3.setHeight((short)500);
    String[] row_third = {"","","","","应收","实收","欠款","应收","实收","欠款","应收","实收","欠款","","",""};
    for (int i = 0; i < row_third.length; i++) {
        HSSFCell tempCell = row3.createCell(i);
        tempCell.setCellValue(row_third[i]);
        tempCell.setCellStyle(headerStyle);
    }

    //查询月度明细列表
    List<BusinessDetail> list = businessService.selectMonthBusinessList(businessDetail);

    for(int i = 0;i<list.size();i++){
        HSSFRow tempRow = sheet.createRow(rowNum++);
        tempRow.setHeight((short)500);
        //循环单元格填入数据
        for(int j=0;j<16;j++){
            HSSFCell tempCell = tempRow.createCell(j);
            tempCell.setCellStyle(contentStyle);
            String cellValue = "";
            if(j ==0){
                //经销商
                cellValue = list.get(i).getClientName();
            }else if(j == 1){
                cellValue = list.get(i).getDespatchAmount();
            }else if(j ==2){
                if(list.get(i).getBalance() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getBalance().toString();
                }
            }else if(j == 3){
                if(list.get(i).getLoanRecovery() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getLoanRecovery().toString();
                }
            }else if(j ==4){
                if(list.get(i).getTotalMoney() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getTotalMoney().toString();
                }
            }else if(j == 5){
                if(list.get(i).getRepayTotal() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getRepayTotal().toString();
                }
            }else if(j == 6){
                if(list.get(i).getRestTotal() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getRestTotal().toString();
                }
            }else if(j ==7){
                if(list.get(i).getTotalManage() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getTotalManage().toString();
                }
            }else if(j == 8){
                if(list.get(i).getRepayManage() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getRepayManage().toString();
                }
            }else if(j == 9){
                if(list.get(i).getRestManage() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getRestManage().toString();
                }
            }else if(j == 10){
                if(list.get(i).getTotalFreight() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getTotalFreight().toString();
                }
            }else if(j == 11){
                if(list.get(i).getRepayFreight() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getRepayFreight().toString();
                }
            }else if( j == 12){
                if(list.get(i).getRestFreight() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getRestFreight().toString();
                }
            }else if(j == 13){
                if(list.get(i).getImprest() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getImprest().toString();
                }
            }else if(j ==14){
                if(list.get(i).getNewBalance() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getNewBalance().toString();
                }
            }else if(j == 15){
                if(list.get(i).getRepayTotal() ==null){
                    cellValue = "0";
                }else{
                    cellValue = list.get(i).getRepayTotal().toString();
                }
            }
            tempCell.setCellValue(cellValue);
        }
    }

    //导出excel
    HttpServletResponse response = this.getResponse();
    String fileName = "月度业务明细.xls";
    try {
        fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
        response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
        OutputStream stream = response.getOutputStream();
        if(null != wb && null != stream){
            wb.write(stream);
            wb.close();
            stream.close();
        }
    }catch (Exception e){
        e.printStackTrace();
    }
}

设置标题样式、表头样式以及内容样式：
单独写了一个ExcelUtils类，代码如下：

/**
* 创建标题样式
* @param wb
* @return
*/
public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb){
HSSFCellStyle cellStyle = wb.createCellStyle();
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//背景颜色
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());

    HSSFFont headerFont1 = (HSSFFont) wb.createFont();
    //字体加粗
    headerFont1.setBold(true);
    //字体类型
    headerFont1.setFontName("黑体");
    //字体大小
    headerFont1.setFontHeightInPoints((short)15);
    cellStyle.setFont(headerFont1);
    return cellStyle;
}

/**
 * 创建表头样式
 * @param wb
 * @return
 */
public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb){
    HSSFCellStyle cellStyle = wb.createCellStyle();
    //设置自动换行
    cellStyle.setWrapText(true);
    //设置背景颜色
    cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    //水平居中
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    //垂直对齐
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
    //下边框
    cellStyle.setBorderBottom(BorderStyle.THIN);
    //左边框
    cellStyle.setBorderLeft(BorderStyle.THIN);
    //右边框
    cellStyle.setBorderRight(BorderStyle.THIN);
    //上边框
    cellStyle.setBorderTop(BorderStyle.THIN);

    //创建字体样式
    HSSFFont headerFont = (HSSFFont)wb.createFont();
    //字体加粗
    headerFont.setBold(true);
    //字体类型
    headerFont.setFontName("黑体");
    //字体大小
    headerFont.setFontHeightInPoints((short)12);
    //为标题样式添加字体样式
    cellStyle.setFont(headerFont);

    return cellStyle;
}

/**
 *  设置表格内容样式
 * @param wb
 * @return
 */
public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb){
    HSSFCellStyle cellStyle = wb.createCellStyle();
    //水平居中
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    //垂直居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    //设置自动换行
    cellStyle.setWrapText(true);
    //上边框
    cellStyle.setBorderTop(BorderStyle.THIN);
    //下边框
    cellStyle.setBorderBottom(BorderStyle.THIN);
    //左边框
    cellStyle.setBorderLeft(BorderStyle.THIN);
    //右边框
    cellStyle.setBorderRight(BorderStyle.THIN);

    //设置字体
    HSSFFont font = (HSSFFont)wb.createFont();
    font.setColor((short)8);
    font.setFontHeightInPoints((short)12);

    return cellStyle;
}