B4A Library aPOI - A wrapper for jPOI for Android - reading and writing XLS, XLSX on Android 5+

M

marvin944

Member
Licensed User
Longtime User
I am getting this error:

*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Unexpected event (missing RaiseSynchronousEvents): zxslv_scan_error
Check the unfiltered logs for the full stack trace.
Error occurred on line: 125 (Main)
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: '/data/user/0/JHS.zxScannerLiveView/files/data.xlsx'
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:112)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:225)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:187)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:74)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:311)
at de.donmanfred.XSSFWorkbookwrapper.Initialize(XSSFWorkbookwrapper.java:69)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:777)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:354)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:146)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:197)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
at android.view.View.performClick(View.java:7506)
at android.view.View.performClickInternal(View.java:7483)
at android.view.View.-$$Nest$mperformClickInternal(Unknown Source:0)
at android.view.View$PerformClick.run(View.java:29335)
at android.os.Handler.handleCallback(Handler.java:942)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loopOnce(Looper.java:201)
at android.os.Looper.loop(Looper.java:288)
at android.app.ActivityThread.main(ActivityThread.java:7898)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:548)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:936)
Caused by: java.util.zip.ZipException: error in opening zip file
at java.util.zip.ZipFile.open(Native Method)
at java.util.zip.ZipFile.<init>(ZipFile.java:265)
at java.util.zip.ZipFile.<init>(ZipFile.java:187)
at java.util.zip.ZipFile.<init>(ZipFile.java:201)
at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipFile(ZipHelper.java:174)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:110)
... 26 more
 

DonManfred

Expert
Licensed User
Longtime User
marvin944 said:
I am getting this error
Click to expand...
- Create a NEW THREAD in the Questionsforum.
- Upload a small project showing the problem.
Looks like you are trying to access a folder not accessible.
The problem maybe is in JHS.zxScannerLiveView

No one can help here without seeing the code used.
 

Sergio Haurat

Active Member
Licensed User
Longtime User
DonManfred said:
write (outputstream As java.iutputStream)
Click to expand...

Encontré un problema en la versión 11, usar la 08. La historia completa aquí: https://www.b4x.com/android/forum/threads/apoi.149190/#post-945631
 
T

Tim Chapman

Active Member
Licensed User
Longtime User
pesfronau said:
Where can i find list of Cell.CellType integer values?
0 Numeric
1 String
2 Formula
....
Click to expand...
See here:

aPOI celltypes [SOLVED]

In the exmaple for aPOI that you can download here: https://www.b4x.com/android/forum/threads/apoi-a-wrapper-for-jpoi-for-android-reading-and-writing-xls-xlsx-on-android-5.110652/ The celltype shows in the log. Type 0 and 1 show. In the jPOI library...
www.b4x.com
 
T

Tim Chapman

Active Member
Licensed User
Longtime User
I added to the original example.
1. Clarified celltypes.
2. Modified code from https://www.b4x.com/android/forum/threads/jpoi-cell-type-date.110607/#post-690260 to allow checking if a cell contains a date.

B4A: 
#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

#AdditionalJar: poi-3.12-android-a
#AdditionalJar: poi-ooxml-schemas-3.12-20150511-a
#MultiDex: true

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim xls As XSSFWorkbook
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    
    File.Copy(File.DirAssets,"book1.xlsx",File.DirInternal,"book1.xlsx")
    xls.Initialize("",File.Combine(File.DirInternal,"book1.xlsx"))
    
    Log($"ActiveSheetIndex=${xls.ActiveSheetIndex}"$) 'Shows which sheet was active when saved.  0 is the first one.
    Dim sheet As XLSSheet = xls.getSheetAt(xls.ActiveSheetIndex) 'Selects the active sheet as "sheet".  The active sheet is the one that was active when it was saved.
    
    Log($"ActiveSheet.ActiveCell=${sheet.ActiveCell}"$) 'This is the cell that was active when the spreadsheet was saved.
    Log($"ActiveSheet.hasComments=${sheet.hasComments}"$) 'These refer to excel comments that can be added to a cell.
    
    Dim firstrow As Int = sheet.FirstRowNum 'The row numbering starts at 0.  Not 1 like normal spreadsheet cell addresses.  If the first row is blank, this will be 1.  If the first row is not blank, this will be 0.
    Dim lastrow As Int = sheet.LastRowNum 'This is the last row which is not blank.
    Log($"Row FirstRow=${firstrow}, LastRow=${lastrow}"$)
    
    For i= firstrow To lastrow
        Dim row As XSSFRow =sheet.getRow(i)
        'Log(row)
        Dim firstcell As Int = row.FirstCellNum
        Dim lastcell As Int = row.LastCellNum 'This will be one beyond the last occupied cell.
        Log("...")

        Log("Row #"&i& "FirstCell="&row.FirstCellNum&", LastCell="&row.LastCellNum)

        For X = firstcell To lastcell-1
            Dim cell As XSSFCell =row.getCell(X)
            Log($"Cell #${X}"$)
            'Log(cell.IsInitialized)
            If cell.IsInitialized Then
                'Log("Raw:"&cell.RawValue)'Log("Raw:"&cell.RawValue)
                
                Log($"CellValueType=${cell.CellType}"$)
                If cell.CellType = 2 Then 'Formula cell type.
                    Log("Cell Formula = "&cell.CellFormula)
                End If
                                
'Cell Types: Numeric is type 0. 1 is Text. 2 is Formula. 3 is Blank. 4 is Boolean. 5 is error.
                
                If cell.CellType = 1 Then 'Text is type 1
                    Log(cell.StringCellValue)
                else if cell.CellType = 0 Then 'Numerice is type 0.
                    Log(cell.NumericCellValue)
                    Log("Date check: "&isCellInternalDateFormatted(cell))
                End If
                'Log(cell)
            End If
            'Log(row)
        Next
    Next
    
    Dim row As XSSFRow = sheet.createRow(30)
    Dim cell As XSSFCell =row.createCell(0)
    cell.NumericCellValue = 29
    Dim cell As XSSFCell =row.createCell(1)
    cell.StringCellValue = "Erel Rocks"
    Dim cell As XSSFCell =row.createCell(2)
    cell.StringCellValue = "B4X :-)"
    Dim cell As XSSFCell =row.createCell(3)
    cell.NumericCellValue = 99
    Dim cell As XSSFCell =row.createCell(4)
    cell.NumericCellValue = 2019
    Dim cell As XSSFCell =row.createCell(5)
    cell.StringCellValue = "aPOI"
    Dim cell As XSSFCell =row.createCell(6)
    cell.NumericCellValue = 9.9

    Starter.rp.CheckAndRequest(Starter.rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
    wait for Activity_PermissionResult (Permission As String, Result As Boolean)
    If Result Then
        Dim outstream As OutputStream = File.OpenOutput(File.DirRootExternal,"excelout.xlsx",False)
        xls.write(outstream)
        outstream.Close
    End If
    
'    Dim rows As List = sheet.Rows
'    If rows <> Null And rows.IsInitialized And rows.Size > 0 Then
'        For i= 0 To rows.Size-1
'            Dim row As XSSFRow = rows.Get(i)
'            Dim cols As List = row.Cells
'            For o= 0 To cols.Size-1
'                Dim cell As XSSFCell = cols.Get(o)
'                Log(cell.toString)
'            Next
'        Next
'    End If
    
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub isCellInternalDateFormatted(Cell As XSSFCell)
    Dim JODateFormated As JavaObject
    JODateFormated.InitializeStatic("org.apache.poi.ss.usermodel.DateUtil")
    Return JODateFormated.RunMethod("isCellInternalDateFormatted",Array(Cell))
End Sub
 
T

Tim Chapman

Active Member
Licensed User
Longtime User
I added to the original example.
1. Clarified celltypes.
2. Modified code from https://www.b4x.com/android/forum/threads/jpoi-cell-type-date.110607/#post-690260 to allow checking if a cell contains a date.

B4A: 
#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

#AdditionalJar: poi-3.12-android-a
#AdditionalJar: poi-ooxml-schemas-3.12-20150511-a
#MultiDex: true

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim xls As XSSFWorkbook
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
   
    File.Copy(File.DirAssets,"book1.xlsx",File.DirInternal,"book1.xlsx")
    xls.Initialize("",File.Combine(File.DirInternal,"book1.xlsx"))
   
    Log($"ActiveSheetIndex=${xls.ActiveSheetIndex}"$) 'Shows which sheet was active when saved.  0 is the first one.
    Dim sheet As XLSSheet = xls.getSheetAt(xls.ActiveSheetIndex) 'Selects the active sheet as "sheet".  The active sheet is the one that was active when it was saved.
   
    Log($"ActiveSheet.ActiveCell=${sheet.ActiveCell}"$) 'This is the cell that was active when the spreadsheet was saved.
    Log($"ActiveSheet.hasComments=${sheet.hasComments}"$) 'These refer to excel comments that can be added to a cell.
   
    Dim firstrow As Int = sheet.FirstRowNum 'The row numbering starts at 0.  Not 1 like normal spreadsheet cell addresses.  If the first row is blank, this will be 1.  If the first row is not blank, this will be 0.
    Dim lastrow As Int = sheet.LastRowNum 'This is the last row which is not blank.
    Log($"Row FirstRow=${firstrow}, LastRow=${lastrow}"$)
   
    For i= firstrow To lastrow
        Dim row As XSSFRow =sheet.getRow(i)
        'Log(row)
        Dim firstcell As Int = row.FirstCellNum
        Dim lastcell As Int = row.LastCellNum 'This will be one beyond the last occupied cell.
        Log("...")

        Log("Row #"&i& "FirstCell="&row.FirstCellNum&", LastCell="&row.LastCellNum)

        For X = firstcell To lastcell-1
            Dim cell As XSSFCell =row.getCell(X)
            Log($"Cell #${X}"$)
            'Log(cell.IsInitialized)
            If cell.IsInitialized Then
                'Log("Raw:"&cell.RawValue)'Log("Raw:"&cell.RawValue)
               
                Log($"CellValueType=${cell.CellType}"$)
                If cell.CellType = 2 Then 'Formula cell type.
                    Log("Cell Formula = "&cell.CellFormula)
                End If
                               
'Cell Types: Numeric is type 0. 1 is Text. 2 is Formula. 3 is Blank. 4 is Boolean. 5 is error.
               
                If cell.CellType = 1 Then 'Text is type 1
                    Log(cell.StringCellValue)
                else if cell.CellType = 0 Then 'Numerice is type 0.
                    Log(cell.NumericCellValue)
                    Log("Date check: "&isCellInternalDateFormatted(cell))
                End If
                'Log(cell)
            End If
            'Log(row)
        Next
    Next
   
    Dim row As XSSFRow = sheet.createRow(30)
    Dim cell As XSSFCell =row.createCell(0)
    cell.NumericCellValue = 29
    Dim cell As XSSFCell =row.createCell(1)
    cell.StringCellValue = "Erel Rocks"
    Dim cell As XSSFCell =row.createCell(2)
    cell.StringCellValue = "B4X :-)"
    Dim cell As XSSFCell =row.createCell(3)
    cell.NumericCellValue = 99
    Dim cell As XSSFCell =row.createCell(4)
    cell.NumericCellValue = 2019
    Dim cell As XSSFCell =row.createCell(5)
    cell.StringCellValue = "aPOI"
    Dim cell As XSSFCell =row.createCell(6)
    cell.NumericCellValue = 9.9

    Starter.rp.CheckAndRequest(Starter.rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
    wait for Activity_PermissionResult (Permission As String, Result As Boolean)
    If Result Then
        Dim outstream As OutputStream = File.OpenOutput(File.DirRootExternal,"excelout.xlsx",False)
        xls.write(outstream)
        outstream.Close
    End If
   
'    Dim rows As List = sheet.Rows
'    If rows <> Null And rows.IsInitialized And rows.Size > 0 Then
'        For i= 0 To rows.Size-1
'            Dim row As XSSFRow = rows.Get(i)
'            Dim cols As List = row.Cells
'            For o= 0 To cols.Size-1
'                Dim cell As XSSFCell = cols.Get(o)
'                Log(cell.toString)
'            Next
'        Next
'    End If
   
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub isCellInternalDateFormatted(Cell As XSSFCell) As Boolean
    Dim JODateFormated As JavaObject
    JODateFormated.InitializeStatic("org.apache.poi.ss.usermodel.DateUtil")
    Return JODateFormated.RunMethod("isCellInternalDateFormatted",Array(Cell))
End Sub
 
You must log in or register to reply here.

Similar Threads

T
  • Question
Android Question aPOI celltypes [SOLVED]
Replies
1
Views
303
Tim Chapman
T
  • Solved
Android Question aPOI
Replies
8
Views
923
Sergio Haurat
  • Question
Android Question aPOI error
Replies
0
Views
467
elitevenkat
D
  • Question
Android Question aPOI FORMULA
Replies
0
Views
608
DAKTECXXL
D
P
  • Question
Android Question aPoi saving problem
Replies
1
Views
620
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…