easyExcel应用

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

Y.实现功能

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

Y. pom引用包

<!-- easyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

<!--条形码-->
<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.barcode.free</artifactId>
    <version>2.6.2-RELEASE</version>
</dependency>
<!--pdf-->
<dependency>
    <groupId>com.aspose</groupId>
    <artifactId>aspose-cells</artifactId>
    <version>8.5.2-RELEASE</version>
</dependency>

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.pdf</artifactId>
    <version>4.8.1-RELEASE</version>
</dependency>

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<CellRangeAddress> 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;
            }
        }

    }

}

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;
    }

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

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

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

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

/**
 * @param list
 * @return
 */
public static <T> List<List<T>> averageAssign(List<T> list,int n){
    List<List<T>> result=new ArrayList<List<T>>();
    int remaider=list.size()%n;  //(先计算出余数)
    int number=list.size()/n;  //然后是商
    int offset=0;//偏移量
    for(int i=0;i<n;i++){
        List<T> 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;
}

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;
    }

}

public String printPickingOrders(List<ServiceOrderPo> 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<EntrustItemPo> 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<EntrustItemPo> entrustItemPos = entrustItemMap.get(serviceOrderPo.getServiceNo());
        if (CollectionUtils.isEmpty(entrustItemPos))return;
        // 切分数据 新增多sheet分页
        List<List<EntrustItemPo>> 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<String, Object>();
            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<Integer> totalPackageNumber = new AtomicReference<>(0);
            AtomicReference<BigDecimal> totalWeight = new AtomicReference<>(new BigDecimal(BigInteger.ZERO));
            AtomicReference<BigDecimal> 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<Map<String, Object>> paramsList = new ArrayList<>();
            groupList.get(i).forEach(detail -> {
                Map<String, Object> 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;
}