使用 EasyExcel 按模板生成 .xls,在转成PDF文件 附带jar包

背景：SpringBoot

        :  Fastfds

今天有需求做这么个功能，磕磕绊绊，记录一下以后好找

1.导包   meven 库下载不了该jar  所以需要在项目里指定路径

坐标

jar包地址：https://pan.baidu.com/s/1l5W4GRr-CCCaMi1D9BHZjw?pwd=6666

2.ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).withTemplate("C:\\Users\\ZHOULIJIA\\Desktop\\mb.xls").file(os).build(); excelWriter 如何获取 InputStream 问题

下方已写 ，重点是 ByteArrayOutputStream 为空问题 原因 未关闭 WriteSheet

测试类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.biotechleague.commons.exception.DaoException;
import com.biotechleague.fdfs.FastDFSClient;
import com.biotechleague.fileupload.pojo.FileInfo;
import com.biotechleague.order.pojo.Order;
import com.biotechleague.utils.PdfUtil;
import lombok.Data;
import org.elasticsearch.search.sort.MinAndMax;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ClassName TEXT
 * @description:
 * @author: mr.zhou
 * @create: 2023-10-25 10:54
 * @Version 1.0
 **/
public class TEXT {
    public static void main(String[] args) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String fileName = "BillingPage"+System.currentTimeMillis()+".xls";
        String pdfFileName = "BillingPage"+System.currentTimeMillis()+".pdf";
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).withTemplate("C:\\Users\\ZHOULIJIA\\Desktop\\mb.xls").file(os).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();

            Map<String, Object> map = new HashMap<>();
      
            map.put("invoiceDate", "invoiceDate");
        
            map.put("balanceDue", 666);

            map.put("zf", "CC");
            map.put("pnon", "33333232323232323");
          
            map.put("netTerm", "30");
         
            map.put("dueDate",  sdf.format(new Date()));
           
            map.put("orderDate", sdf.format(new Date()));
          
            map.put("orderId","3233");
           
            map.put("subtotal", 111);

            excelWriter.fill(map, writeSheet);

            excelWriter.fill( new FillWrapper(dataList2(5)), fillConfig, writeSheet);

            excelWriter.finish();

            byte[] buffer = os.toByteArray();
            InputStream inputStream = new ByteArrayInputStream(buffer);
            PdfUtil.excel2pdf(inputStream, pdfFileName,null);

        }catch (Exception e){
            throw new DaoException("生成发票失败");
        }finally {
            excelWriter.finish();
        }
    }

    private static List<DemoDate>  dataList2(int i) {
        List<DemoDate> dataList = new ArrayList<DemoDate>();
        for (int j = 0; j < i; j++) {
            DemoDate fillData = new DemoDate();
            if (j == 0) {
                fillData.setOrderId("orderId1");
                fillData.setOrderDate("orderDate1");
            } else {
                fillData.setOrderId("");
                fillData.setOrderDate("");
            }
            
            fillData.setShipTo("shipTo");

            
            fillData.setBillTo("billTo3");

            
            fillData.setCatalogName("catalogName3");

          
            fillData.setDescription("description3");

           
            fillData.setQty("qty3");

           
            fillData.setRate(33);

          
            fillData.setAmount(44);
            dataList.add(fillData);

        }
        return dataList;
    }


    @Data
    private static class DemoDate {
        String shipTo;
        String billTo;
        String catalogName;
        String description;
        String qty;
        Integer rate;
        Integer amount;
        String orderDate;
        String orderId;

    }
}

工具类

package com.biotechleague.utils;
import com.aspose.cells.License;
import com.aspose.cells.PdfSaveOptions;
import com.aspose.cells.Workbook;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.biotechleague.commons.pojo.BioTechLeagueResult;
import com.biotechleague.fdfs.FastDFSClient;
import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Value;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.Date;

/**
 * @ClassName PDFUtil
 * @description:
 * @author: mr.zhou
 * @create: 2023-10-24 15:40
 * @Version 1.0
 **/
public class PdfUtil {
    
    /**
     * excel 转 pdf
     *
     * @param inputStream   文件流
     * @param fileName   pdf文件名称
     * @param convertSheets 需要转换的sheet
     */
    public static  String[] excel2pdf(InputStream inputStream, String fileName, int[] convertSheets) {
        String[] result = null;
        try {
            // 验证 License
            getLicense();
            Workbook wb = new Workbook(inputStream);
            String pdfFilePath = "C:\\Users\\ZHOULIJIA\\Desktop\\mb.pdf";
            FileOutputStream fileOS = new FileOutputStream(pdfFilePath);
            PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
            pdfSaveOptions.setOnePagePerSheet(true);
            if (null != convertSheets) {
                printSheetPage(wb, convertSheets);
            }
            wb.save(fileOS, pdfSaveOptions);

            IOUtils.copy(inputStream, fileOS);


//            result = FastDFSClient.uploadFile(inputStream, fileName);

            fileOS.close();
            inputStream.close();
            System.out.println("convert success");
        } catch (Exception e) {
            System.out.println("convert failed");
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 获取 生成的 pdf 文件路径，默认与源文件同一目录
     *
     * @param excelFilePath excel文件
     * @return 生成的 pdf 文件
     */
    private static String getPdfFilePath(String excelFilePath) {
        return excelFilePath.split(".")[0] + ".pdf";
    }

    /**
     * 获取 license 去除水印
     * 若不验证则转化出的pdf文档会有水印产生
     */
    private static void getLicense() {
        String licenseFilePath = "excel-license.xml";
        try {
            InputStream is = PdfUtil.class.getClassLoader().getResourceAsStream(licenseFilePath);
            License license = new License();
            license.setLicense(is);
        } catch (Exception e) {
            System.out.println("license verify failed");
            e.printStackTrace();
        }
    }

    /**
     * 隐藏workbook中不需要的sheet页。
     *
     * @param sheets 显示页的sheet数组
     */
    private static void printSheetPage(Workbook wb, int[] sheets) {
        for (int i = 1; i < wb.getWorksheets().getCount(); i++) {
            wb.getWorksheets().get(i).setVisible(false);
        }
        if (null == sheets || sheets.length == 0) {
            wb.getWorksheets().get(0).setVisible(true);
        } else {
            for (int i = 0; i < sheets.length; i++) {
                wb.getWorksheets().get(i).setVisible(true);
            }
        }
    }
}

效果