poi 自动合并excel中的单元格

之前都是使用sheet.addMergedRegion()方法一个一个计算合并单元格,还浪费大量时间,后来我自己特意封装了一个方法分享给大家,即拿即用,加几个就合并几个单元格方法。下面会给大家提供完整代码!

使用方法:

//合并单元格(你的sheet页,你的标题所占用的行,你的哪一列单元格需要合并)
mergeCell(sheet,1,3);

合并单元格封装的方法:

/**
 * 合并单元格
 * @author tongyao
 * @param sheet sheet页
 * @param titleColumn 标题占用行
 * @param cellIndex 那个单元格
 */
public static void mergeCell(HSSFSheet sheet,int titleColumn,int cellIndex){
    //多少行
    int rowCount = sheet.getPhysicalNumberOfRows();
    String cellText = "";
    //开始下标
    int startIndex = 0;
    //结束下标
    int endIndex = 0;
    HSSFRow row = null;
    Cell cell = null;
    for(int i = titleColumn;i<rowCount;i++){
        row = sheet.getRow(i);
        cell = row.getCell(cellIndex);

        if(!cell.getStringCellValue().equals(cellText)){
            if(startIndex != 0){
                endIndex = (i-1);
                sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
            }
            cellText = cell.getStringCellValue();
            startIndex = i;
        }

    }

    endIndex = (rowCount-1);
    sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
}

完整代码:

@GetMapping("/export")
    public void export(HttpServletResponse response){
        HSSFWorkbook wb = new HSSFWorkbook();
        // 根据页面index 获取sheet页
        HSSFSheet sheet = wb.createSheet("Sheet1");
        sheet.setColumnWidth(0,10000);
        sheet.setColumnWidth(1,10000);
        sheet.setColumnWidth(2,10000);
        sheet.setColumnWidth(3,10000);

        HSSFRow row1Title1 = sheet.createRow(0);
        
        Cell row1Cell1Title1 = row1Title1.createCell(0);
        row1Cell1Title1.setCellValue("序号");

        Cell row1Cell1Title2 = row1Title1.createCell(1);
        row1Cell1Title2.setCellValue("码云主页");

        Cell row1Cell1Title3 = row1Title1.createCell(2);
        row1Cell1Title3.setCellValue("CSDN主页");

        Cell row1Cell1Title4 = row1Title1.createCell(3);
        row1Cell1Title4.setCellValue("姓名");

        for(int i = 1;i< 20;i++){
            HSSFRow row = sheet.createRow(i);

            Cell row_cell1 = row.createCell(0);
            if(i <= 5){
                row_cell1.setCellValue("1");
            }else if(i <= 10 && i > 5){
                row_cell1.setCellValue("2");
            }else{
                row_cell1.setCellValue("3");
            }

            Cell row_cell2 = row.createCell(1);
            row_cell2.setCellValue("码云:https://gitee.com/Super_TongYao");

            Cell row_cell3 = row.createCell(2);
            row_cell3.setCellValue("CSDN主页:https://blog.csdn.net/u014641168");

            Cell row_cell4 = row.createCell(3);
            row_cell4.setCellValue("Tongyao");

        }

        //合并单元格(你的sheet页,你的标题所占用的行,你的哪一列单元格需要合并)
        mergeCell(sheet,1,0);
        mergeCell(sheet,1,3);

        try {
            OutputStream output = response.getOutputStream();
            response.reset();

            String fileName = "导出合并后的表单(码云:gitee.com/Super_TongYao).xls";
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));

            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 合并单元格
     * @author tongyao
     * @param sheet sheet页
     * @param titleColumn 标题占用行
     * @param cellIndex 那个单元格
     */
    public static void mergeCell(HSSFSheet sheet,int titleColumn,int cellIndex){
        //多少行
        int rowCount = sheet.getPhysicalNumberOfRows();
        String cellText = "";
        //开始下标
        int startIndex = 0;
        //结束下标
        int endIndex = 0;
        HSSFRow row = null;
        Cell cell = null;
        for(int i = titleColumn;i<rowCount;i++){
            row = sheet.getRow(i);
            cell = row.getCell(cellIndex);

            if(!cell.getStringCellValue().equals(cellText)){
                if(startIndex != 0){
                    endIndex = (i-1);
                    sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
                }
                cellText = cell.getStringCellValue();
                startIndex = i;
            }

        }

        endIndex = (rowCount-1);
        sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
    }
来做第一个评论吧!~

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇