B4J Library jPOI - Supports Microsoft Excel xls and xlsx Workbooks

Discussion in 'B4J Libraries & Classes' started by Erel, Aug 19, 2015.

  1. DMW

    DMW Member Licensed User

    Erel likes this.
  2. Tayfur

    Tayfur Well-Known Member Licensed User

    thank you @Erel for reply
    i cant found sample code :(
     
  3. inakigarm

    inakigarm Well-Known Member Licensed User

  4. Erel

    Erel Administrator Staff Member Licensed User

    Start a new thread in the questions forum and I'll post the code.
     
  5. StarinschiAndrei

    StarinschiAndrei Active Member Licensed User

    Hello, is it possible to convert/ save as (in background) the excel file in PDF ?
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    jPOI creates Excel workbooks (xls or xlsx files). It doesn't support any other format.
     
  7. StarinschiAndrei

    StarinschiAndrei Active Member Licensed User

    Than
    Thank you.
     
  8. Mashiane

    Mashiane Expert Licensed User

    There is only poi 3.17 on the apache download links. version 1.10 of this lib supports poi 3.16, please advise.. and thanks.
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    All the files required are available in the first post.
     
  10. keirS

    keirS Well-Known Member Licensed User

    Could this library be updated to the latest version of POI (4.0)? I am trying to use features which are not supported in 3.16. Alternatively is it possible to have the source code for the library so I can rebuild it myself?
     
  11. Erel

    Erel Administrator Staff Member Licensed User

    If the new version is backwards compatible then you can just change the dependency lines in the bottom of the XML file.
     
  12. keirS

    keirS Well-Known Member Licensed User

    It doesn't seem to be backwards compatible. I downloaded POI 4.0 with all the dependencies Changed the dependency lines to.
    Code:
    <dependsOn>poi-4.0.0</dependsOn>
     <dependsOn>poi-ooxml-
    4.0.0</dependsOn>
     <dependsOn>poi-ooxml-schemas-
    4.0.0</dependsOn>
     <dependsOn>xmlbeans-
    3.0.1</dependsOn>
     <dependsOn>commons-collections4-
    4.2</dependsOn> 
    <dependsOn>commons-codec-
    1.10</dependsOn> 
    <dependsOn>commons-compress-
    1.18</dependsOn>
     <dependsOn>curvesapi-
    1.04</dependsOn>
    When setting a cell type:
    Code:
    ExcelCell.CellType = ExcelCell.TYPE_STRING
    I get the following error.

    Code:
    Waiting for debugger to connect...
    Program started.
    java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellType(I)V
        at anywheresoftware.b4j.objects.PoiCellWrapper.setCellType(PoiCellWrapper.java:
    44)
        at b4j.example.exceltotableview._buildview(exceltotableview.java:
    192)
        at b4j.example.main._appstart(main.java:
    133)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.shell.Shell.runMethod(
    Shell.java:628)
        at anywheresoftware.b4a.shell.Shell.raiseEventImpl(
    Shell.java:237)
        at anywheresoftware.b4a.shell.Shell.raiseEvent(
    Shell.java:168)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.BA.raiseEvent2(BA.java:
    90)
        at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:
    98)
        at anywheresoftware.b4a.BA.raiseEvent(BA.java:
    77)
        at b4j.example.main.start(main.java:
    38)
        at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$
    162(LauncherImpl.java:863)
        at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$
    175(PlatformImpl.java:326)
        at com.sun.javafx.application.PlatformImpl.lambda$
    null$173(PlatformImpl.java:295)
        at java.security.AccessController.doPrivileged(Native Method)
        at com.sun.javafx.application.PlatformImpl.lambda$runLater$
    174(PlatformImpl.java:294)
        at com.sun.glass.ui.InvokeLaterDispatcher$
    Future.run(InvokeLaterDispatcher.java:95)
        at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
        at com.sun.glass.ui.win.WinApplication.lambda$
    null$148(WinApplication.java:191)
        at java.lang.Thread.run(
    Thread.java:745)
    java.lang.RuntimeException: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellType(I)V
        at anywheresoftware.b4a.BA.raiseEvent2(BA.java:
    119)
        at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:
    98)
        at anywheresoftware.b4a.BA.raiseEvent(BA.java:
    77)
        at b4j.example.main.start(main.java:
    38)
        at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$
    162(LauncherImpl.java:863)
        at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$
    175(PlatformImpl.java:326)
        at com.sun.javafx.application.PlatformImpl.lambda$
    null$173(PlatformImpl.java:295)
        at java.security.AccessController.doPrivileged(Native Method)
        at com.sun.javafx.application.PlatformImpl.lambda$runLater$
    174(PlatformImpl.java:294)
        at com.sun.glass.ui.InvokeLaterDispatcher$
    Future.run(InvokeLaterDispatcher.java:95)
        at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
        at com.sun.glass.ui.win.WinApplication.lambda$
    null$148(WinApplication.java:191)
        at java.lang.Thread.run(
    Thread.java:745)
    Caused by: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellType(I)V
        at anywheresoftware.b4j.objects.PoiCellWrapper.setCellType(PoiCellWrapper.java:
    44)
        at b4j.example.exceltotableview._buildview(exceltotableview.java:
    192)
        at b4j.example.main._appstart(main.java:
    133)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.shell.Shell.runMethod(
    Shell.java:628)
        at anywheresoftware.b4a.shell.Shell.raiseEventImpl(
    Shell.java:237)
        at anywheresoftware.b4a.shell.Shell.raiseEvent(
    Shell.java:168)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.BA.raiseEvent2(BA.java:
    90)
        ... 
    12 more
     
  13. Erel

    Erel Administrator Staff Member Licensed User

    V1.20 released. It is based on POI v4.0.0.
     
    rboeck and keirS like this.
  14. keirS

    keirS Well-Known Member Licensed User

    Thanks for that Erel. It appears commons-math3 is also required for re-evaluating formulas. I am not quite sure why but Maven didn't show me this in the dependency list. I downloaded commons-math3-3.6.1.jar and added the dependency to the XML file and this solves the crash.

    Code:
    Waiting for debugger to connect...
    Program started.
    (XSSFCell) -
    62.5
    -
    62.5
    (XSSFCell) 
    8996.0
    8996.0
    (XSSFCell) 
    (XSSFCell) 
    51.5
    51.5
    (XSSFCell) 
    (XSSFCell) 
    47.5
    47.5
    (XSSFCell) 
    (XSSFCell) SUM(B1:I1)
    SUM(B1:I1)
    Error occurred on line: 
    196
    java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4j.object.JavaObject.RunMethod(
    JavaObject.java:131)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.shell.Shell.runMethod(
    Shell.java:628)
        at anywheresoftware.b4a.shell.Shell.raiseEventImpl(
    Shell.java:234)
        at anywheresoftware.b4a.shell.Shell.raiseEvent(
    Shell.java:168)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.BA.raiseEvent2(BA.java:
    90)
        at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:
    98)
        at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:
    61)
        at b4j.example.exceltotableview._poigetformattedcellvalue(exceltotableview.java:
    497)
        at b4j.example.exceltotableview._buildview(exceltotableview.java:
    335)
        at b4j.example.main._appstart(main.java:
    133)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.shell.Shell.runMethod(
    Shell.java:628)
        at anywheresoftware.b4a.shell.Shell.raiseEventImpl(
    Shell.java:237)
        at anywheresoftware.b4a.shell.Shell.raiseEvent(
    Shell.java:168)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
    62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
    43)
        at java.lang.reflect.Method.invoke(Method.java:
    498)
        at anywheresoftware.b4a.BA.raiseEvent2(BA.java:
    90)
        at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:
    98)
        at anywheresoftware.b4a.BA.raiseEvent(BA.java:
    77)
        at b4j.example.main.start(main.java:
    38)
        at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$
    162(LauncherImpl.java:863)
        at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$
    175(PlatformImpl.java:326)
        at com.sun.javafx.application.PlatformImpl.lambda$
    null$173(PlatformImpl.java:295)
        at java.security.AccessController.doPrivileged(Native Method)
        at com.sun.javafx.application.PlatformImpl.lambda$runLater$
    174(PlatformImpl.java:294)
        at com.sun.glass.ui.InvokeLaterDispatcher$
    Future.run(InvokeLaterDispatcher.java:95)
        at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
        at com.sun.glass.ui.win.WinApplication.lambda$
    null$148(WinApplication.java:191)
        at java.lang.Thread.run(
    Thread.java:745)
    Caused by: java.lang.NoClassDefFoundError: org/apache/commons/math3/linear/RealMatrix
        at org.apache.poi.ss.formula.functions.MatrixFunction.<clinit>(MatrixFunction.java:
    250)
        at org.apache.poi.ss.formula.eval.FunctionEval.produceFunctions(FunctionEval.java:
    147)
        at org.apache.poi.ss.formula.eval.FunctionEval.<clinit>(FunctionEval.java:
    59)
        at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:
    132)
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:
    534)
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:
    275)
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:
    216)
        at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:
    54)
        at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCell(BaseFormulaEvaluator.java:
    177)
        at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:
    991)
        at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:
    949)
        ... 
    46 more
    Caused by: java.lang.ClassNotFoundException: org.apache.commons.math3.linear.RealMatrix
        at java.net.URLClassLoader.findClass(URLClassLoader.java:
    381)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:
    424)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:
    331)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:
    357)
        ... 
    57 more
    (XSSFCell)
     
    Erel likes this.
  15. Erel

    Erel Administrator Staff Member Licensed User

    V1.21 released with this dependency.
     
    Kiffi and keirS like this.
  16. Philip Prins

    Philip Prins Active Member Licensed User

    Hello

    I am looking for a tutorial / example to read a Excel sheet and convert to Tableview/

    Regards
    Philip
     
  17. Erel

    Erel Administrator Staff Member Licensed User

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice