Excel复合表头问题的解决

最新推荐文章于 2024-11-06 13:25:56 发布

原创最新推荐文章于 2024-11-06 13:25:56 发布 · 779 阅读

1 ·

CC 4.0 BY-SA版权

jAVA 专栏收录该内容

29 篇文章

订阅专栏

先上图

由于公司需求，我需要上传这种具有多表头的excel，之前也没做过，看网上得demo有一点点问题，在这里我把我查看好几个人得到的结果搬过来，谢谢很多大佬哈。

1正常生成excel，这里代码我之前写过，就不复制了，可以产看https://blog.youkuaiyun.com/qq_39004307/article/details/86169879

2我们需要多级表头，以我这边为例，首先我们声明String数组，并初始化元素（表头名称）

String[] excelHeader0={"序号","楼层","区域","商铺房号","商铺名称","POS刷卡","POS刷卡","POS刷卡","POS刷卡","会员","会员","会员","会员"};
            //合并“起始行，截止行，起始列，截止列”
            String[] headnum0 ={"0,1,0,0","0,1,1,1","0,1,2,2","0,1,3,3","0,1,4,4","0,0,5,8","0,0,9,12"};
            String[] excelHeader1 = {"笔数","占楼层比","金额","占楼层比","笔数","占楼层比","金额","占楼层比"};
            String[] headnum1 ={"1,1,5,5","1,1,6,6","1,1,7,7","1,1,8,8","1,1,9,9","1,1,10,10","1,1,11,11","1,1,12,12"};

这里合并的规则上面已经说了

 for (int i = 0; i < excelHeader0.length; i++) {
                sheet.autoSizeColumn((short) i, true);// 根据字段长度自动调整列的宽度
                HSSFCell cell = row.createCell((short) i);
                cell.setCellValue(excelHeader0[i]);
                cell.setCellStyle(style);
                if (i >= 0 && i <= 12) {
                    for (int j = 0; j < excelHeader0.length; j++) {
                        // 从第j列开始填充
                        cell = row.createCell((short) j);
                        // 填充excelHeader1[j]第j个元素
                        cell.setCellValue(excelHeader0[j]);
                        cell.setCellStyle(style);
                    }
                }
            }
            // 动态合并单元格
            for (int i = 0; i < headnum0.length; i++) {
                sheet.autoSizeColumn((short) i, true);
                String[] temp = headnum0[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new Region(startrow.shortValue(), startcol.shortValue(),overrow.shortValue(),  overcol.shortValue()));
            }
            row = sheet.createRow(1);
            for (int i = 0; i < excelHeader1.length; i++) {

                sheet.autoSizeColumn((short) i, true);// 自动调整宽度
                HSSFCell cell = row.createCell((short) (i + 5));
                cell.setCellValue(excelHeader1[i]);
                cell.setCellStyle(style);

                if (i >= 0 && i <= 8) {
                    for (int j = 0; j < excelHeader1.length; j++) {
                        // 从第j+1列开始填充
                        cell = row.createCell((short) (j + 2));
                        // 填充excelHeader1[j]第j个元素
                        cell.setCellValue(excelHeader1[j]);
                        cell.setCellStyle(style);
                    }
                }
            }

            // 动态合并单元格
            for (int i = 0; i < headnum1.length; i++) {

                sheet.autoSizeColumn((short) i, true);
                String[] temp = headnum1[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new Region(startrow.shortValue(),startcol.shortValue(), overrow.shortValue(),  overcol.shortValue()));
            }

切记下面动态合并单元格时有人用过下面的方法

sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));

我这里的jar包不支持好像，一直报错，具体什么我没深入了解，我的jar包如图所示，用的不是maven，是gradle

因此我找了新方法代替，方法如下，注意这里参数顺序也变了，依次是开始行，开始列，结束行，结束列

sheet.addMergedRegion(new Region(startrow.shortValue(),startcol.shortValue(), overrow.shortValue(),  overcol.shortValue()));

结束了在感谢一些大佬给出他们的链接

https://blog.youkuaiyun.com/yuzhenling/article/details/48132939

https://blog.youkuaiyun.com/yuer2008200820008/article/details/80152848