• easyExcel应用

    打印A4数据 引用easyExcel模板实现

    Y.实现功能

    1).批量数据填充
    2).批量数据超过范围实现分页
    3).不同类型数据引用不同模板
    4).每页模板需插入特定数据条形码图片
    5).分页实现引用多sheet方法实现
    6).excel转pdf用于打印

    Y. pom引用包

    

    com.alibaba
    easyexcel
    2.2.5


    org.apache.poi
    poi
    3.17


    org.apache.poi
    poi-ooxml
    3.17




    e-iceblue
    spire.barcode.free
    2.6.2-RELEASE



    com.aspose
    aspose-cells
    8.5.2-RELEASE



    e-iceblue
    spire.pdf
    4.8.1-RELEASE


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    E. 模板配置

    在这里插入图片描述
    excel样例
    在这里插入图片描述

    S.实现代码
    S-Y. excel合并单元格

    继承AbstractMergeStrategy

    public class ExcelMergeStrategy extends AbstractMergeStrategy {
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if(relativeRowIndex==null ||relativeRowIndex==0){
            return;
        }
        int rowIndex = cell.getRowIndex();
        int colIndex = cell.getColumnIndex();
        sheet=cell.getSheet();
        Row preRow = sheet.getRow(rowIndex - 1);
        Cell preCell = preRow.getCell(colIndex);//获取上一行的该格
        List list = sheet.getMergedRegions();
        CellStyle cs = cell.getCellStyle();
        cell.setCellStyle(cs);
        for (int i = 0; i < list.size(); i++) {
            CellRangeAddress cellRangeAddress = list.get(i);
            if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
                int lastColIndex = cellRangeAddress.getLastColumn();
                int firstColIndex = cellRangeAddress.getFirstColumn();
                CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
                sheet.addMergedRegion(cra);
                return;
            }
        }

    }

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    S-E. 工具类

    1). 生成条形码 barCodeUtil

    /**
 * 生成条形码(类型=Code_128)
 * params barCodeString 生成条形码字符串
 * params widht 图宽
 * params height 图高
 * params showText 是否展示条形码下方字符串
 * @return 条形码的byte[]
 */
@SneakyThrows
public static ByteArrayOutputStream barcodeImageToOutPutStream(String barCodeString, Integer widht, Integer height,Boolean showText) {
    BarcodeSettings settings = new BarcodeSettings();
    settings.setType(BarCodeType.Code_128);
    settings.setData(barCodeString);
    settings.setShowText(showText);
    settings.setShowTextOnBottom(true);
//        settings.setImageHeight(1.32f);
//        settings.setImageWidth(6.4f);
    settings.setAutoResize(true);
    //设置边框不可见
    settings.hasBorder(false);
    BarCodeGenerator barCodeGenerator = new BarCodeGenerator(settings);
    BufferedImage bufferedImage = barCodeGenerator.generateImage();
    if (height != null && widht != null) {
        bufferedImage = resize(bufferedImage, height, widht);
    }
//        String uuidString = UUID.randomUUID().toString();
//        File file = new File(uuidString + ".png");
//        ImageIO.write(bufferedImage,"png",file);
//        return file;
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    ImageIO.write(bufferedImage, "png", outputStream);
//        byte[] bytes = outputStream.toByteArray();
//        outputStream.close();

//        String a = BarcodeScanner.scanOne(new ByteArrayInputStream(outputStream.toByteArray()));
    return outputStream;
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37

    2). List分组 ListUtil
    切分数组转成 List

     /**
 * 将集合按len数量分成若干个list
 * @param list
 * @param len 每个集合的数量
 * @return
 */
public static  List> splitList(List list, int len) {
    if (list == null || list.size() == 0 || len < 1) {
        return null;
    }
    List> result = new ArrayList>();

    int size = list.size();
    int count = (size + len - 1) / len;

    for (int i = 0; i < count; i++) {
        List subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
        result.add(subList);
    }
    return result;
}

/**
 * @param list
 * @return
 */
public static  List> averageAssign(List list,int n){
    List> result=new ArrayList>();
    int remaider=list.size()%n;  //(先计算出余数)
    int number=list.size()/n;  //然后是商
    int offset=0;//偏移量
    for(int i=0;i value=null;
        if(remaider>0){
            value=list.subList(i*number+offset, (i+1)*number+offset+1);
            remaider--;
            offset++;
        }else{
            value=list.subList(i*number+offset, (i+1)*number+offset);
        }
        result.add(value);
    }
    return result;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44

    3). 生成Pdf PdfUtil
    excel转pdf

    public class PdfUtil {

    public static boolean getLicense1() {
        boolean result = false;
        try {

            InputStream is = PdfUtil.class.getClassLoader()
                    .getResourceAsStream("license.xml"); // license.xml应放在..\WebRoot\WEB-INF\classes路径下
            com.aspose.cells.License aposeLic = new com.aspose.cells.License();
            aposeLic.setLicense(is);
            result = true;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * excel转pdf 默认为a4纸大小
     * @param excelFileName 
     * @param pdfFileName
     * @return
     */
    public static Boolean excelToPdf(String excelFileName, String pdfFileName) {
        // 验证License 若不验证则转化出的pdf文档会有水印产生
        if (!getLicense1()) {
            return false;
        }

        try {
            //文件操作
            File file = new File(pdfFileName); // 新建一个空白pdf文档
            FileOutputStream os = new FileOutputStream(file);
            Workbook wb = new Workbook(excelFileName);// 原始excel路径
            FileOutputStream fileOS = new FileOutputStream(file);
            wb.save(fileOS, com.aspose.cells.SaveFormat.PDF);
            fileOS.close();
            return true;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * excel转pdf 
     * @param excelFileName 
     * @param pdfFileName
     * @param paperSizeType  纸张大小
     * @return
     */
    public static Boolean excelToPdfV2(String excelFileName, String pdfFileName,int paperSizeType) {
        // 验证License 若不验证则转化出的pdf文档会有水印产生
        if (!getLicense1()) {
            return false;
        }

        try {
            //文件操作
            File file = new File(pdfFileName); // 新建一个空白pdf文档
            FileOutputStream os = new FileOutputStream(file);

            Workbook wb = new Workbook(excelFileName);// 原始excel路径
            int pageSize = wb.getWorksheets().getCount();
            for (int i = 0; i < pageSize; i++) {
                Worksheet worksheet = wb.getWorksheets().get(i);
                worksheet.getPageSetup().setPaperSize(paperSizeType);
            }
            FileOutputStream fileOS = new FileOutputStream(file);
            wb.save(fileOS, com.aspose.cells.SaveFormat.PDF);
            fileOS.close();
            return true;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    public String printPickingOrders(List params) {
    // 第一页数量
    int firstNum = ServiceOrderConstants.ExcelValue.firstNum;
    // 平均数量
    int avgNum = ServiceOrderConstants.ExcelValue.avgNum;
    
    //excel模板
    String templateFileName = "C:\\Users\\Administrator\\Desktop\\excel\\模板.xlsx";
    File file = new File(templateFileName);
    
    // 定义模板sheet数量
    AtomicInteger num = new AtomicInteger(2);
    
    @Cleanup XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    //设置sheetNo1的模板名称 用于记录
    workbook.setSheetName(0, "模板1");
    //设置sheetNo2的名称 用于记录
    workbook.setSheetName(1, "模板2");
    
    @Cleanup XSSFWorkbook finalWorkbook = workbook;
    
    // 页数
    AtomicInteger size = new AtomicInteger(0);
    // sheet名后缀
    AtomicInteger sheetNum = new AtomicInteger(1);
    serviceOrderPos.forEach(serviceOrderPo -> {
        // 明细列表数据
        List entrustItemPos = entrustItemMap.get(serviceOrderPo.getServiceNo());
        if (CollectionUtils.isEmpty(entrustItemPos))return;
        // 当前单页数
        int pageSize = (entrustItemPos.size() - firstNum) > 0 ? (entrustItemPos.size() - firstNum) / avgNum + 2 : 1;
    
        // 3).不同类型数据引用不同模板  根据需求克隆sheet模板
        for (int i = 0; i < pageSize; i++) {
            if (!serviceOrderPo.getCrossBorderFlag()) {
                finalWorkbook.cloneSheet(1, "sheet" + (sheetNum.get()));
            } else {
                finalWorkbook.cloneSheet(0, "sheet" + (sheetNum.get()));
            }
            sheetNum.getAndIncrement();
        }
    
        for (int i = 0; i < pageSize; i++) {
            XSSFDrawing patriarch = finalWorkbook.getSheetAt(size.get() + 2).createDrawingPatriarch();
            // 生成条形码
            ByteArrayOutputStream barCodeImageFile = ExcelUtil.barcodeImageToOutPutStream(serviceOrderPo.getServiceNo(), 280, 80, false);
            //放置条形码位置
            XSSFClientAnchor anchor = new XSSFClientAnchor();
            if (!serviceOrderPo.getCrossBorderFlag()) {
                anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 5, 2, (short) 7, 3);
            } else {
                anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 5, 2, (short) 6, 3);
            }
            // 插入条形码
            patriarch.createPicture(anchor, finalWorkbook.addPicture(barCodeImageFile.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            size.getAndIncrement();
        }
    });
    
    // 删除模板sheet并 写入流
    finalWorkbook.removeSheetAt(1);
    finalWorkbook.removeSheetAt(0);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    finalWorkbook.write(bos);
    
    byte[] bArray = bos.toByteArray();
    @Cleanup InputStream byteArrayInputStream = new ByteArrayInputStream(bArray);
    // 填充数据
    ExcelWriter  excelWriter =EasyExcel.write(filePath).withTemplate(byteArrayInputStream).registerWriteHandler(new ExcelMergeStrategy()).build();
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    
    serviceOrderPos.forEach(serviceOrderPo -> {
        // 明细列表数据
        List entrustItemPos = entrustItemMap.get(serviceOrderPo.getServiceNo());
        if (CollectionUtils.isEmpty(entrustItemPos))return;
        // 切分数据 新增多sheet分页
        List> groupList = ListUtil.splitList(entrustItemPos, avgNum);
        for (int i = 0; i < groupList.size(); i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + (num.get() - 1)).build();
            writeSheet.setSheetNo(num.get() - 2);
    
            //填充到表头数据
            Map map = new HashMap();
            map.put("orderIdOut", serviceOrderPo.getRelationCode());
            map.put("billLadingNo", serviceOrderPo.getBillLadingNo());
            map.put("inStoreNo", String.join("\n", serviceNoMap.get(serviceOrderPo.getServiceNo())));
            map.put("serviceNo", serviceOrderPo.getServiceNo());
            map.put("printDate", printDate);
            map.put("currentSize", i + 1);
            map.put("pageSize", groupList.size());
    
            // 统计数据
            AtomicReference totalPackageNumber = new AtomicReference<>(0);
            AtomicReference totalWeight = new AtomicReference<>(new BigDecimal(BigInteger.ZERO));
            AtomicReference totalVolume = new AtomicReference<>(new BigDecimal(BigInteger.ZERO));
            entrustItemPos.forEach(po -> {
                totalPackageNumber.updateAndGet(v -> v + po.getEntrustNum());
                totalVolume.set(totalVolume.get().add(Objects.isNull(po.getEntrustVolume()) ? BigDecimal.ZERO : po.getEntrustVolume()));
                totalWeight.set(totalWeight.get().add(Objects.isNull(po.getEntrustWeight()) ? BigDecimal.ZERO : po.getEntrustWeight()));
            });
    
            if (i == groupList.size() - 1) {
                map.put("totalData", "汇总数据");
                map.put("totalPackageNumber", totalPackageNumber.toString());
                map.put("totalWeight", String.valueOf(totalWeight));
                map.put("totalVolume", String.valueOf(totalVolume));
                map.put("tray", "托盘数量");
                map.put("trayNumber", entrustItemPos.size());
            }
    
            // 列表数据
            List> paramsList = new ArrayList<>();
            groupList.get(i).forEach(detail -> {
                Map paramsMap = new MapUtils()
                        .put("stagingAreaCode", Objects.nonNull(stagingAreaMap.get(detail.getTrayNo())) ? stagingAreaMap.get(detail.getTrayNo()).getStagingAreaCode() : null)
                        .put("trayNo", detail.getTrayNo())
                        .put("packageNumber", detail.getEntrustNum())
                        .put("weight", detail.getEntrustWeight())
                        .put("volume", detail.getEntrustVolume())
                        .put("goldjetTrayFlag", detail.getGoldjetTrayFlag())
                        .put("createTime", stagingAreaMap.get(detail.getTrayNo()).getCreateTime().format(DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss")));
                paramsList.add(paramsMap);
            });
    
            excelWriter.fill(map, writeSheet);
            excelWriter.fill(paramsList, fillConfig, writeSheet);
            num.getAndIncrement();
        }
    
    });
    excelWriter.finish();
    // excel转pdf
    Stirng pdfPath="C:\\Users\\Administrator\\Desktop\\excel\\"+UUID.randomUUID().toString().replace("-","")+".pdf";
    PdfUtil.excelToPdf(fileName, pdfPath);
    System.out.println(pdfPath);
    File file = new File(filePath);
    if (file.exists() && file.isFile()) {
        file.delete();
    }
    return null;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
  • 相关阅读:
    RebatMq消息中间件（一） 各个中间件介绍
    十三、Mysql的存储引擎
    MockingBird，手把手教你克隆您的声音，AI代言人，惊艳你的耳朵！
    【前端验证】fork-join_none线程立即执行的一次代码优化记录
    Linux 线程控制 —— 线程清理 pthread_cleanup_push
    14. 从零开始编写一个类nginx工具, HTTP文件服务器的实现过程及参数
    Python表白比心
    Apache Flink 1.16重磅发布，仅22年Flink跨越3个大版本
    2023最新SSM计算机毕业设计选题大全（附源码+LW）之java散酒营销系统w5at6
    互联网摸鱼日报（2022-11-08）
  • 原文地址：https://blog.csdn.net/END_Loving/article/details/127446995