aspose-cells 表合并

2019独角兽企业重金招聘Python工程师标准>>>

supply_apply_tpl.xlsx //主表文件

 

supply_apply_item_tpl.xlsx  //子表文件

结果：

 

代码：

 try {
        List<ExportModel> applies = new ArrayList<>();
        ExportModel apply = new ExportModel();
        applies.add(apply);

        apply.setDeliveryTime("2018-05-16 12:50:30");
        apply.setOrderNo("Y2017-04-062");
        apply.setHouseName("xx仓库");
        apply.setStatus("审核中");
        apply.setStoreName("xx门店");
        apply.setOrderCreateTime("2018-05-17 09:24:0");

        List<Dish> details = new ArrayList<>();
        apply.setDishs(details);

        Dish detail = new Dish();
        detail.setApplyNum(1f);
        detail.setCheckNum(2f);
        detail.setName("干贝");
        detail.setUnit("份");
        detail.setCategoryName("分类1");
        detail.setCode("56454");
        detail.setSpec("xxx");
        details.add(detail);

        detail = new Dish();
        detail.setApplyNum(1f);
        detail.setCheckNum(2f);
        detail.setName("海胆");
        detail.setUnit("支");
        detail.setCategoryName("海鲜");
        detail.setCode("565");
        detail.setSpec("xxx");
        details.add(detail);

        //-----------------------
        apply = new ExportModel();
        applies.add(apply);

        apply.setDeliveryTime("2018-6-16 12:50:30");
        apply.setOrderNo("Y2027-031-162");
        apply.setHouseName("ss仓库");
        apply.setStatus("通过");
        apply.setStoreName("ss门店");
        apply.setOrderCreateTime("2018-15-17 09:24:0");


        File supply_apply_tpl = new File("C:\\Users\\Canaan\\Desktop\\supply_apply_tpl.xlsx");
        File supply_apply_item_tpl = new File("C:\\Users\\Canaan\\Desktop\\supply_apply_item_tpl.xlsx");
        File outFile = new File("C:\\Users\\Canaan\\Desktop\\bbbb.xlsx");
        Workbook rootWorkbook;


            rootWorkbook = new Workbook(FileUtils.openInputStream(supply_apply_tpl));
            Worksheet rootWorksheet = rootWorkbook.getWorksheets().get(0);

            WorkbookDesigner rootDesigner = new WorkbookDesigner(rootWorkbook);
            rootDesigner.setDataSource("title", "配送入库单");
            int totalRowCount = 1;
            Workbook subWorkBookTpl = new Workbook(FileUtils.openInputStream(supply_apply_item_tpl));
            for (ExportModel exportModel : applies) {
                Workbook subBook = new Workbook();
                subBook.copy(subWorkBookTpl);

                WorkbookDesigner designer = new WorkbookDesigner(subBook);
                designer.setDataSource("orderNo", exportModel.getOrderNo());
                designer.setDataSource("orderCreateTime", exportModel.getOrderCreateTime());
                designer.setDataSource("deliveryTime", exportModel.getDeliveryTime());
                designer.setDataSource("status", exportModel.getStatus());
                designer.setDataSource("storeName", exportModel.getStoreName());
                designer.setDataSource("houseName", exportModel.getHouseName());
                designer.setDataSource("item",new CollectionCellsTable(exportModel.getDishs()));
                designer.process();

                Range subBookRange = subBook.getWorksheets().get(0).getCells().getMaxDisplayRange();

 rootWorksheet.getCells().insertRows(totalRowCount+1, subBookRange.getRowCount());     //为子模板预留行
                Range destRange = rootWorksheet.getCells().createRange(totalRowCount, subBookRange.getFirstColumn(),
                        subBookRange.getRowCount(), subBookRange.getColumnCount());

                destRange.copy(subBookRange);

                totalRowCount = subBookRange.getRowCount() + totalRowCount + 2;
            }

            rootDesigner.process();
            rootWorkbook.save(new FileOutputStream(outFile), SaveFormat.XLSX);

        } catch (Exception e) {
           e.printStackTrace();
        } finally {
            System.out.println("end");
        }

 

转载于:https://my.oschina.net/u/2552286/blog/1802260