Pentaho kettle integration with Java

Here I am trying execute a transformation from Java. The java code is:

public class PDI {

    public static void main(String[] args) {
        // TODO Auto-generated method stub

        try {
            KettleEnvironment.init();
            System.out.println("1");
            //StepLoader.init();
            EnvUtil.environmentInit();
            System.out.println("2");
            TransMeta metaData = new TransMeta("D:/pdi/external/ExcelSheet/person.ktr");
            System.out.println("3");
           Trans trans = new Trans( metaData );
           System.out.println("4");
           trans.execute(null);
           System.out.println("5");
          trans.waitUntilFinished();
          System.out.println("6");
            if ( trans.getErrors() > 0 ) {
                System.out.print( "Error Executing transformation" );
            }

        } catch (KettleException e) {

             e.printStackTrace();
        }
}

    }

But I'm getting this error :

1
2
3
4
2018/10/05 21:04:31 - person - Dispatching started for transformation [person]
5
java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellType()I
    at org.pentaho.di.trans.steps.excelinput.poi.PoiCell.getType(PoiCell.java:43)child index = 0, logging object : org.pentaho.di.core.logging.LoggingObject@5789a7e2 parent=718e171d-3be6-4174-a9d0-7ce8c54b2fb3
2018/10/05 21:04:32 - Microsoft Excel Input.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)

    at org.pentaho.di.trans.steps.excelinput.ExcelInput.fillRow(ExcelInput.java:138)
    at org.pentaho.di.trans.steps.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:665)
    at org.pentaho.di.trans.steps.excelinput.ExcelInput.processRow(ExcelInput.java:471)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    at java.lang.Thread.run(Thread.java:748)
2018/10/05 21:04:32 - Microsoft Excel Input.0 - ERROR (version 5.0.0.1.19046, build 1 from 2013-09-11_13-51-13 by buildguy) : Unexpected error
2018/10/05 21:04:32 - Microsoft Excel Input.0 - ERROR (version 5.0.0.1.19046, build 1 from 2013-09-11_13-51-13 by buildguy) : java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellType()I
2018/10/05 21:04:32 - Microsoft Excel Input.0 -     at org.pentaho.di.trans.steps.excelinput.poi.PoiCell.getType(PoiCell.java:43)
2018/10/05 21:04:32 - Microsoft Excel Input.0 -     at org.pentaho.di.trans.steps.excelinput.ExcelInput.fillRow(ExcelInput.java:138)
2018/10/05 21:04:32 - Microsoft Excel Input.0 -     at org.pentaho.di.trans.steps.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:665)
2018/10/05 21:04:32 - Microsoft Excel Input.0 -     at org.pentaho.di.trans.steps.excelinput.ExcelInput.processRow(ExcelInput.java:471)
2018/10/05 21:04:32 - Microsoft Excel Input.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2018/10/05 21:04:32 - Microsoft Excel Input.0 -     at java.lang.Thread.run(Thread.java:748)
2018/10/05 21:04:32 - person - person
2018/10/05 21:04:32 - person - person
2018/10/05 21:04:32 - person - ERROR (version 5.0.0.1.19046, build 1 from 2013-09-11_13-51-13 by buildguy) : Errors detected!6

Error Executing transformation.

Solution

So your transformation involves an Input Excel step. This step is based on the Apache poi library.

1) Make sure your transformation is running in spoon. 2) Make sure the poi-.jar is on your java classpath.

You'll find the jar in the lib directory which was shipped wit your distribution. And to avoid that kind of surprises at a later stage, you may find useful to include all this lib on your classpath.