关于使用jacob+poi插件实现编辑Excel转PDF操作！

最近在项目中，出现了Excel转PDF的需求，找了很多方法都不尽人意。今天就将我实现的方法分享出来，所需下载，都已备好，放心实验！

1.需要的jar包和控件

jacob-1.19.jar

poi-3.13.jar

poi-ooxml-3.13.jar

poi-ooxml-schemas-3.13.jar

jacob-1.19-x63.dll或者是jacob-1.19-x86.dll(根据电脑系统选择)

上述jar和配置文件链接：https://pan.baidu.com/s/1pGEgN-Q8s4TV4h6shmcGPw  提取码 ：vkxf

2.配置控件

将jacob-1.19-x63.dll或者是jacob-1.19-x86.dll放到jdk的bin目录下。（一般这样就好）

有的电脑，如果报Comthread的错误，将dll文件放入对应的

jar包放到jdk/jre目录下

例：%JAVA_HOME%\jre\jacob.jar 配置环境变量，jar包放在对应的位置C:\Windows\System32或者C:\Windows\SysWOW64（全都放更放心）

导入jar包即可

如果是maven工程参考下方pom文件，（build标签根据自己创建的类选择）。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.qpq</groupId>
    <artifactId>Topdf</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>crms-manager Maven Webapp</name>
    <url>http://maven.apache.org</url>

    <properties>
        <maven.compiler.source>1.6</maven.compiler.source>
        <maven.compiler.target>1.6</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    </properties>

    <dependencies>

        <dependency>
            <groupId>com.jacob</groupId>
            <artifactId>jacob</artifactId>
            <version>1.19</version>
            <scope>system</scope>
            <systemPath>D:/temp/maven3/repo/jacob/jacob.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.13</version>
        </dependency>

    </dependencies>

    <!--<build>-->
        <!--<finalName>MyApp</finalName>  &lt;!&ndash; 最终package打包的jar名称，注意这里没有版本号了。 &ndash;&gt;-->
        <!--<plugins>-->
            <!--<plugin>-->
                <!--<groupId>org.springframework.boot</groupId>-->
                <!--<artifactId>spring-boot-maven-plugin</artifactId>-->
                <!--<configuration>-->
                    <!--<mainClass>Demo</mainClass>-->
                <!--</configuration>-->
            <!--</plugin>-->
        <!--</plugins>-->
    <!--</build>-->


</project>

3.简单实现转PDF

代码实现：

package com.crms.common.util;

import com.crms.common.constants.SystemConstants;
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;

public class JacobExcel {
    private static final Logger logger = LoggerFactory.getLogger(JacobExcel.class);
    private static final Integer EXCEL_TO_PDF_OPERAND = 0;

    public static String createBook(String inFilePath) throws Exception {
        InputStream in = new FileInputStream(inFilePath);
        String pathCopy = null;
        if(inFilePath.endsWith("xls")) {
            HSSFWorkbook workBook = new HSSFWorkbook(in);
            int index = inFilePath.lastIndexOf(".");
            String s1 = inFilePath.substring(0,index)+"pdf";
            String s2 = inFilePath.substring(index,inFilePath.length());
            pathCopy = s1+s2;
            File file = new File(pathCopy);
            if (!file.exists()) {
                file.createNewFile();
            }
            FileOutputStream fileOut = null;
            try {
                fileOut = new FileOutputStream(file);
                workBook.write(fileOut);
                in.close();
                fileOut.flush();
            }catch (RuntimeException e){
                e.printStackTrace();
            }finally {
                fileOut.close();
            }
        }else if(inFilePath.endsWith("xlsx")){
            XSSFWorkbook workBook = new XSSFWorkbook(in);
            //workBook.removeName("输入数据2");
            int index = inFilePath.lastIndexOf(".");
            String s1 = inFilePath.substring(0,index)+"pdf";
            String s2 = inFilePath.substring(index,inFilePath.length());
            pathCopy = s1+s2;
            File file = new File(pathCopy);
            if (!file.exists()) {
                file.createNewFile();
            }
            FileOutputStream fileOut = null;
            try {
                fileOut = new FileOutputStream(file);
                workBook.write(fileOut);
                fileOut.flush();

            }catch (RuntimeException e){
                e.printStackTrace();
            }finally {
                in.close();
                fileOut.close();
            }
        }
        return  pathCopy;
    }

    /**
     * 判断输入文件是否需要修改
     * @param inFilePath
     * @return
     * @throws Exception
     */
    public synchronized static String createPatacBook(String inFilePath) throws Exception {
        logger.info("操作ExceL(删除无用sheet)");
        InputStream in = new FileInputStream(inFilePath);
        String pathCopy = null;
        //判断excel格式
        if(inFilePath.endsWith("xls")) {
            HSSFWorkbook workBook = new HSSFWorkbook(in);
            //System.out.println(workBook);
            //删除需求不要的sheet
            if(workBook.getSheetIndex("数据输入1") != -1){
                workBook.removeSheetAt(workBook.getSheetIndex("数据输入1"));
                logger.info("删除数据输入1成功");
            }
            if(workBook.getSheetIndex("数据输入2") != -1){
                workBook.removeSheetAt(workBook.getSheetIndex("数据输入2"));
                logger.info("删除数据输入2成功");
            }

            //System.out.println("删除成功");
            //更改之后另存新的excel
            int index = inFilePath.lastIndexOf(".");
            String s1 = inFilePath.substring(0,index)+"pdf";
            String s2 = inFilePath.substring(index,inFilePath.length());
            //新Excel路径
            pathCopy = s1+s2;
            File file = new File(pathCopy);
            if (!file.exists()) {
                file.createNewFile();
            }
            //文件流输出
            FileOutputStream fileOut = null;
            try {
                fileOut = new FileOutputStream(file);
                workBook.write(fileOut);
                in.close();
                fileOut.flush();
            }catch (RuntimeException e){
                e.printStackTrace();
                logger.error("文件流输出"+e.toString());
                //System.out.println("文件流输出"+e.toString());
            }finally {
                fileOut.close();
            }
        }else if(inFilePath.endsWith("xlsx")){
            XSSFWorkbook workBook = new XSSFWorkbook(in);
            //System.out.println(workBook);
            //删除需求不要的sheet
            if(workBook.getSheetIndex("数据输入1") != -1){
                workBook.removeSheetAt(workBook.getSheetIndex("数据输入1"));
                logger.info("删除数据输入1成功");
            }
            if(workBook.getSheetIndex("数据输入2") != -1){
                workBook.removeSheetAt(workBook.getSheetIndex("数据输入2"));
                logger.info("删除数据输入2成功");
            }
            //System.out.println("删除成功");
            //更改之后另存新的excel
            int index = inFilePath.lastIndexOf(".");
            String s1 = inFilePath.substring(0,index)+"pdf";
            String s2 = inFilePath.substring(index,inFilePath.length());
            //新Excel路径
            pathCopy = s1+s2;
            File file = new File(pathCopy);
            if (!file.exists()) {
                file.createNewFile();
            }
            //文件流输出
            FileOutputStream fileOut = null;
            try {
                fileOut = new FileOutputStream(file);
                workBook.write(fileOut);
                fileOut.flush();

            }catch (RuntimeException e){
                e.printStackTrace();
                logger.error("文件流输出"+e.toString());
                //System.out.println("文件流输出"+e.toString());
            }finally {
                in.close();
                fileOut.close();
            }
        }
        logger.info("操作Excel结束。文件另存成功！");
        return  pathCopy;
    }

    public synchronized static void excelToPdf(String inFilePath, String outFilePath,String fileType) throws Exception {
        logger.info("线程开始");
        long old = System.currentTimeMillis();
        //System.out.println("转pdf开始时间："+(old / 1000.0) + "秒");
        logger.info("转pdf开始时间："+(old / 1000.0) + "秒");
        String pathCopy = "";
        if(fileType.equals(SystemConstants.FILE_TYPE_OTHER)) {
            pathCopy = inFilePath;

            ActiveXComponent ax = null;
            Dispatch excel = null;
            try {
                ComThread.InitSTA();
                //调用office转换pdf
                ax = new ActiveXComponent("Excel.Application");
                //调用WPS转换pdf
                //ax = new ActiveXComponent("KET.Application");

                ax.setProperty("Visible", new Variant(false));
                ax.setProperty("AutomationSecurity", new Variant(3)); // 禁用宏
                //得到excel的workbook
                Dispatch excels = ax.getProperty("Workbooks").toDispatch();
                //数据放到Object
                Object[] obj = new Object[]{
                        pathCopy,
                        new Variant(false),
                        new Variant(false)
                };
                //调用dispatch方法
                try {
                    excel = Dispatch.invoke(excels, "Open", Dispatch.Method, obj, new int[9]).toDispatch();
                }catch(Exception e){
                    e.printStackTrace();
                    logger.error("转换中异常："+e.toString());
                    //System.out.println("转换中异常："+e.toString());
                }
                Dispatch sheets= Dispatch.get(excel,"Worksheets").toDispatch();//获得所有的Sheet
                int SheetCount=Dispatch.get(sheets,"Count").getInt();//获得有多少个sheet
                // 转换格式
                Object[] obj2 = new Object[]{
                        new Variant(EXCEL_TO_PDF_OPERAND), // PDF格式=0
                        outFilePath,
                        new Variant(0)  //0=标准 (生成的PDF图片不会变模糊) ; 1=最小文件
                };
                Dispatch.invoke(excel, "ExportAsFixedFormat", Dispatch.Method,obj2, new int[1]);
            } catch (Exception es) {
                es.printStackTrace();
                logger.error("转换中异常："+es.toString());
                //System.out.println(es.toString());
            } finally {
                if (excel != null) {
                    Dispatch.call(excel, "Close", new Variant(false));
                }
                if (ax != null) {
                    ax.invoke("Quit", new Variant[] {});
                    ax = null;
                }
                long now = System.currentTimeMillis();
                //System.out.println("转pdf结束时间："+(now / 1000.0) + "秒");
                //logger.info("转pdf结束时间："+(now / 1000.0) + "秒");
                //System.out.println("Excel转pdf共耗时：" + ((old - now) / 1000.0) + "秒"); //转化用时
                logger.info("Excel转pdf共耗时：" + ((now - old) / 1000.0) + "秒");
                ComThread.Release();
                logger.info("线程结束,PDF文件为"+outFilePath);
            }
        }else if(fileType.equals(SystemConstants.FILE_TYPE_PATAC)){
            pathCopy = createPatacBook(inFilePath);

            ActiveXComponent ax = null;
            Dispatch excel = null;
            try {
                ComThread.InitSTA();
                //调用office转换pdf
                ax = new ActiveXComponent("Excel.Application");
                //调用WPS转换pdf
                //ax = new ActiveXComponent("KET.Application");

                ax.setProperty("Visible", new Variant(false));
                ax.setProperty("AutomationSecurity", new Variant(3)); // 禁用宏
                //得到excel的workbook
                Dispatch excels = ax.getProperty("Workbooks").toDispatch();
                //数据放到Object
                Object[] obj = new Object[]{
                        pathCopy,
                        new Variant(false),
                        new Variant(false)
                };
                //调用dispatch方法
                try {
                    excel = Dispatch.invoke(excels, "Open", Dispatch.Method, obj, new int[9]).toDispatch();
                }catch(Exception e){
                    e.printStackTrace();
                    logger.error("转换中异常："+e.toString());
                    //System.out.println("转换中异常："+e.toString());
                }
                Dispatch sheets= Dispatch.get(excel,"Worksheets").toDispatch();//获得所有的Sheet
                int SheetCount=Dispatch.get(sheets,"Count").getInt();//获得有多少个sheet
                // 转换格式
                Object[] obj2 = new Object[]{
                        new Variant(EXCEL_TO_PDF_OPERAND), // PDF格式=0
                        outFilePath,
                        new Variant(0)  //0=标准 (生成的PDF图片不会变模糊) ; 1=最小文件
                };
                Dispatch.invoke(excel, "ExportAsFixedFormat", Dispatch.Method,obj2, new int[1]);
            } catch (Exception es) {
                es.printStackTrace();
                logger.error("转换中异常："+es.toString());
                //System.out.println(es.toString());
            } finally {
                if (excel != null) {
                    Dispatch.call(excel, "Close", new Variant(false));
                }
                if (ax != null) {
                    ax.invoke("Quit", new Variant[] {});
                    ax = null;
                }
                long now = System.currentTimeMillis();
                //System.out.println("转pdf结束时间："+(now / 1000.0) + "秒");
                //logger.info("转pdf结束时间："+(now / 1000.0) + "秒");
                //System.out.println("Excel转pdf共耗时：" + ((old - now) / 1000.0) + "秒"); //转化用时
                logger.info("Excel转pdf共耗时：" + ((now - old) / 1000.0) + "秒");
                //System.out.println("线程结束，PDF文件为"+outFilePath);
                ComThread.Release();
                logger.info("线程结束,PDF文件为"+outFilePath);
                //删除生成新的Excel
                new File(pathCopy).delete();
                logger.info("另存文件删除");
            }
        }


    }
  public static void main(String[] args) throws Exception {
        final String path = "D:\\1\\2\\";


        // 异步导入

        ExecutorService singleThreadExecutor2 = Executors.newCachedThreadPool();
        singleThreadExecutor2.execute(new Runnable() {
            @Override
            public void run() {
                System.out.println("线程开始");
                long now = System.currentTimeMillis();
                System.out.println("开始时间："+(now / 1000.0) + "秒");
                try {

                   new JacobExcel().excelToPdf(path + "泛亚二和三合并版模板20190117.xls", path + "24.pdf",now);
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    long old = System.currentTimeMillis();
                    System.out.println("结束时间："+(old / 1000.0) + "秒");
                    System.out.println("Excel转pdf共耗时2：" + ((old - now) / 1000.0) + "秒"); //转化用时
                }
                System.out.println("线程2结束");
            }

        });
        singleThreadExecutor2.shutdown();


    }
}

代码主要是jacob调用office的组件实现转PDF，像“Excel.application”和“Word.Application”分别是excel和word的组件，另外wps的组件也是支持的，如“KET.Application”调用Excel功能。

因为在项目中，需要实现删除sheetName为“数据输入1”和“数据输入2”的sheet页，然后进行转换。我使用了poi实现Excel修改操作，另存转换。（转换时会出现一个转换进度条，如果项目在Windows环境下部署，是不会在前台出现的，放心）