hutool-all-4.1.19.jar通过ExcelWriter导出复杂表格

hutool-all-4.1.19.jar通过ExcelWriter导出复杂表格

已下规则复杂表头也同样适用
//首先看一下效果

这边只讲述复杂导出平常导出请前往官方文档

https://hutool.cn/docs/#/poi/Excel%E5%B7%A5%E5%85%B7-ExcelUtil

//首先创建表头 
List<String> rowHead = CollUtil.newArrayList("门店", "套餐名称", "所属分类", "套餐类型", "套餐销售金额", "上下架", "编号", "商品名称", "替换商品(替换套餐)");
// 通过工具类创建writer，默认创建xls格式
ExcelWriter writer = ExcelUtil.getWriter();
try {
            //写入表头
            writer.writeHeadRow(rowHead);
            //定义启始行
            int a = 1;
            List<List<Object>> rows = new LinkedList<>();
            for (BsDishes bsDishes : dishesByAreaIdAndDishesType) {
                //可选套餐
                if (bsDishes.getMealtype() == 1) {
                    if (mapBuilder.map().containsKey(bsDishes.getId())) {
                        List<BsDishesPackage> packages = (List<BsDishesPackage>) mapBuilder.map().get(bsDishes.getId());                   Collections.sort(packages,Comparator.comparing(BsDishesPackage::getDishesid1));
                        if (packages.size()==1){
                            a = a + packages.size();
                        }else {
                            //规则编写
                            mergeRule(writer, a, a + packages.size() - 1);
                            a = a + packages.size();
                        }
                        for (BsDishesPackage aPackage : packages) {
                            List<Object> rowA = CollUtil.newArrayList(
                                    bsDishes.getStoreName()
                                    , bsDishes.getName()
                                    , bsDishes.getClsname()
                                    , "可选套餐"
                                    , bsDishes.getPrice()
                                    , bsDishes.getState() == 1 ? "上架" : "下架"
                                    , bsDishes.getCode()
                                    , dishesIdAndDishesName.get(aPackage.getDishesid1())
                                    , ""
                            );
                            rows.add(rowA);
                        }
                    } else{
                        mergeRule(writer, a, ++a);
                        List<Object> rowA = CollUtil.newArrayList(
                                bsDishes.getStoreName()
                                , bsDishes.getName()
                                , bsDishes.getClsname()
                                , "可选套餐"
                                , bsDishes.getPrice()
                                , bsDishes.getState() == 1 ? "上架" : "下架"
                                , bsDishes.getCode()
                                , ""
                                , ""
                        ); List<Object> rowB = CollUtil.newArrayList(
                                bsDishes.getStoreName()
                                , bsDishes.getName()
                                , bsDishes.getClsname()
                                , "可选套餐"
                                , bsDishes.getPrice()
                                , bsDishes.getState() == 1 ? "上架" : "下架"
                                , bsDishes.getCode()
                                , ""
                                , ""
                        );
                        a++;
                        rows.add(rowA);
                        rows.add(rowB);
                    }
                }
                //替换套餐
                else if (bsDishes.getMealtype() == 2){
                    if (mapBuilder.map().containsKey(bsDishes.getId())) {
                        List<BsDishesReplace> replicas = (List<BsDishesReplace>) mapBuilder.map().get(bsDishes.getId());
                        Collections.sort(replicas,Comparator.comparing(BsDishesReplace::getDishesid1));
                        Map<Long, List<BsDishesReplace>> collect = replicas.stream().collect(Collectors.groupingBy(BsDishesReplace::getDishesid1));
                        TreeMap<Long, List<BsDishesReplace>> treeMap=new TreeMap<Long, List<BsDishesReplace>>(collect);
                        if (replicas.size()==1){
                            a = a + replicas.size();
                        }else {
                            //规则编写
                            mergeRule(writer, a, a + replicas.size()-1);
                            int replaceNum=a;
                            for (Long aLong : treeMap.keySet()) {
                                if (collect.get(aLong).size()>1) {
                                    writer.merge(replaceNum, replaceNum + collect.get(aLong).size()-1, 7, 7, null, true);**
                                }
                                replaceNum=replaceNum+collect.get(aLong).size();
                            }
                            a = a + replicas.size();
                        }
                        for (BsDishesReplace replica : replicas) {
                            List<Object> rowA = CollUtil.newArrayList(
                                    bsDishes.getStoreName()
                                    , bsDishes.getName()
                                    , bsDishes.getClsname()
                                    , "替换套餐"
                                    , bsDishes.getPrice()
                                    , bsDishes.getState() == 1 ? "上架" : "下架"
                                    , bsDishes.getCode()
                                    , dishesIdAndDishesName.get(replica.getDishesid1())
                                    , dishesIdAndDishesName.get(replica.getDishesid2())
                            );
                            rows.add(rowA);
                        }
                    } else {
                        //规则编写
                        mergeRule(writer, a, ++a);
                        List<Object> rowA = CollUtil.newArrayList(
                                bsDishes.getStoreName()
                                , bsDishes.getName()
                                , bsDishes.getClsname()
                                , "替换套餐"
                                , bsDishes.getPrice()
                                , bsDishes.getState() == 1 ? "上架" : "下架"
                                , bsDishes.getCode()
                                , ""
                                , ""
                        );List<Object> rowB = CollUtil.newArrayList(
                                bsDishes.getStoreName()
                                , bsDishes.getName()
                                , bsDishes.getClsname()
                                , "替换套餐"
                                , bsDishes.getPrice()
                                , bsDishes.getState() == 1 ? "上架" : "下架"
                                , bsDishes.getCode()
                                , ""
                                , ""
                        );
                        a++;
                        rows.add(rowA);
                        rows.add(rowB);
                    }
                }
            }
            writer.write(rows);
            //设置宽度自适应
            writer.setColumnWidth(-1, 22);
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");

            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(("套餐导出").getBytes("UTF-8"), "ISO-8859-1") + ".xls");
            ServletOutputStream out = response.getOutputStream();

            //out为OutputStream，需要写出到的目标流
            writer.flush(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            writer.close();
        }

定义规则

//定好合并规则
private void mergeRule(ExcelWriter writer, int firstNum, int lastNum) {
    writer.merge(firstNum, lastNum, 0, 0, null, true);
    writer.merge(firstNum, lastNum, 1, 1, null, true);
    writer.merge(firstNum, lastNum, 2, 2, null, true);
    writer.merge(firstNum, lastNum, 3, 3, null, true);
    writer.merge(firstNum, lastNum, 4, 4, null, true);
    writer.merge(firstNum, lastNum, 5, 5, null, true);
    writer.merge(firstNum, lastNum, 6, 6, null, true);

}
###例子
// writer.merge(1, 2, 0, 0, null, true);
// writer.merge(3, 4, 0, 0, null, true);
// writer.merge(1, 2, 1, 1, null, true);
// writer.merge(3, 4, 1, 1, null, true);

write为合并的规则合并该行,该列不能重复如果本行没有合并需求就进行跳过不要写入不然会报异常

如果不定义规则导出

定义合并规则导出为