Android Question aPOI

[Sergio Haurat]

@DonManfred, I hope I don't bother you with the question but the only alternative I found that works to generate excel files on Android was yours. As far as you can see the code is out of date and in my project when I use it in debug mode it works, however when I compile the code in release mode, on completion of saving the file it stops working.

Do you have any intention of updating it in the short term? I have seen that @Erel has XLUtil but it is only for B4J.

Thank you very much for your time

Original post

https://www.b4x.com/android/forum/t...ing-and-writing-xls-xlsx-on-android-5.110652/

 

[Sergio Haurat]

Here is the code I'm using to try to save the B4XTable output to an Excel file. The procedure works until you finish saving the file by using the Write2 function

Players.xlsx template attached

...
Dim strFile As String = "PSAHB " & lblFed.Text & " " & lblClub.Text & " " & Starter.app_genero & " " & Starter.app_Cat & ".xlsx"
Dim ph As Phone 'phone lib
If ph.SdkVersion = 33 Then
  Starter.rp.CheckAndRequest("android.permission.READ_MEDIA_IMAGES")
Else
  Starter.rp.CheckAndRequest(Starter.rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
End If
Wait For B4XPage_PermissionResult (Permission As String, Result As Boolean)
If Result = True Then
  mdlFunctions.WriteTableToExcel("Players.xlsx", dgvView, strFile, True)
End If
...

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

Public Sub WriteTableToExcel(xlsBase As String, objTbl As B4XTable, strOutFile As String, blnShowToast As Boolean)
    Try
        If File.Exists(File.DirInternal, xlsBase) Then
            File.Delete(File.DirInternal, xlsBase)
        End If
        File.Copy(File.DirAssets, xlsBase, File.DirInternal, xlsBase)
        xls.Initialize("", File.Combine(File.DirInternal, xlsBase))
        Dim sheet As XLSSheet = xls.getSheetAt(0)
        Dim row As XSSFRow
        Dim mapRow As Map
        Dim intCnt As Int = 1
        Dim cell As XSSFCell
        Dim tblCol As B4XTableColumn
        row = sheet.createRow(1)
        For intCol = 0 To objTbl.Columns.Size - 1
            cell = row.createCell(intCol)
            tblCol = objTbl.Columns.Get(intCol)
            cell.StringCellValue = tblCol.Id
        Next
        Sleep(0)
        For intRow = 1 To objTbl.Size
            row = sheet.createRow(intRow + 1)
            mapRow = objTbl.GetRow(intRow)
            intCnt = 0
            For Each strKey As String In mapRow.Keys
                cell = row.createCell(intCnt)
                cell.StringCellValue = mapRow.Get(Starter.loc.Localize(strKey))
                intCnt = intCnt + 1
            Next
        Next
        If File.Exists(File.DirRootExternal,"/Documents/" & strOutFile) Then
            File.Delete(File.DirRootExternal,"/Documents/" & strOutFile)
        End If
        xls.write2(File.DirRootExternal,"/Documents/" & strOutFile)
        Sleep(0)
        If blnShowToast Then
            ToastMessageShow(Starter.loc.Localize("export_dgv_ok"), True)
        End If
    Catch
        If blnShowToast Then
            ToastMessageShow(LastException, True)
        End If
    End Try
    If File.Exists(File.DirInternal, xlsBase) Then
        File.Delete(File.DirInternal, xlsBase)
    End If
End Sub

 

[bdunkleysmith]

Perhaps post #4 in this thread Problem with DirRootExternal and DirDefaultExternal will help you given it says:

It cannot work. Whenever you see code with File.DirDefaultExternal or File.DirRootExternal you immediately know that it is broken code.

The correct solution depends on your requirements. In most cases you simply need to use XUI.DefaultFolder.

It's also mentioned at point 4 in this thread: "Code Smells" - common mistakes and other tips

 

[Sergio Haurat]

Perhaps post #4 in this thread Problem with DirRootExternal and DirDefaultExternal will help you given it says:



It's also mentioned at point 4 in this thread: "Code Smells" - common mistakes and other tips

Hi @bdunkleysmith, thanks for your response. I have seen this post but it is not clear to say. This folder and subfolders. I am using a /Documents/ subfolder, does it also affect that case?. inherits the permissions of the previous folder?

 

[bdunkleysmith]

I'm not an expert in this area, but if @Erel says not to use File.DirRootExternal then I would not use it and replace references to it with XUI.DefaultFolder. I don't believe having subfolders removes the problem.

But from observing the various threads on locations for storage folders which allow convenient user access it may be that you may need to use GetSafeDirDefaultExternal, although with Android's changing permissions some more Forum searching may be required to determine if this is still appropriate.

 

[Sergio Haurat]

I have made all these changes..., the problem exists only when compiling the application in release mode

- Some code segments were changed for better interpretation.
- SaveXLSXAs is fired by the _TabClick event of AS TabMenuAdvanced
Nomal B4X Page

Private Sub btnTabMenu_TabClick (Index As Int)
    Select Case btnTabMenu.GetTab(Index).xTab.Tag
        Case "fed"
            btnTabMenu.GetTab(1).xTab.Enabled = False
            btnTabMenu.GetTab(2).xTab.Enabled = False
            btnTabMenu.Index = 0
            btnTabMenu.Refresh
            cmn_lst_federaciones 'Loads all federations and shows them
        Case "club"
            btnTabMenu.GetTab(2).xTab.Enabled = False
            btnTabMenu.Index = 1
            btnTabMenu.Refresh
            cmn_lst_clubes 'Loads all the clubs of the selected federation and shows them
        Case "people"
            btnTabMenu.Index = 2
            btnTabMenu.Refresh
            cmn_lst_personas 'Loads all members of that federation from that club and lists them in the B4XTable
        Case "saveToFile"
            SaveXLSXAs
    End Select
End Sub

Private Sub SaveXLSXAs()
    Dim strFile As String = "PSAHB.xlsx"
    Wait For (mdlFunctions.WriteTableToExcel("Players.xlsx", dgvView, strFile)) Complete (Success As Boolean)
    If Success Then
        Try
            Dim saveFile As SaveAsDialog
            saveFile.Initialize
            Wait For (saveFile.SaveAs(File.OpenInput(File.DirInternal, strFile), "application/octet-stream", Application.LabelName)) Complete (Success As Boolean)
            ToastMessageShow("File saved", False)
        Catch
            ToastMessageShow(LastException, True)
            Log(LastException)
        End Try
    End If
End Sub

Sub Process_Globals
    Private xls As XSSFWorkbook
End Sub
Public Sub WriteTableToExcel(xlsBase As String, objTbl As B4XTable, strOutFile As String) As ResumableSub
    Try
        File.Copy(File.DirAssets, xlsBase, File.DirInternal, xlsBase)
        xls.Initialize("", File.Combine(File.DirInternal, xlsBase))
        Dim sheet As XLSSheet = xls.getSheetAt(0)
        Dim row As XSSFRow
        Dim mapRow As Map
        Dim intCnt As Int = 1
        Dim cell As XSSFCell
        Dim tblCol As B4XTableColumn
        row = sheet.createRow(1)
        For intCol = 0 To objTbl.Columns.Size - 1
            cell = row.createCell(intCol)
            tblCol = objTbl.Columns.Get(intCol)
            cell.StringCellValue = tblCol.Id
        Next
        For intRow = 1 To objTbl.Size
            row = sheet.createRow(intRow + 1)
            mapRow = objTbl.GetRow(intRow)
            intCnt = 0
            For Each strKey As String In mapRow.Keys
                cell = row.createCell(intCnt)
                cell.StringCellValue = mapRow.Get(Starter.loc.Localize(strKey))
                intCnt = intCnt + 1
            Next
        Next
        xls.write2(File.DirInternal, strOutFile)
        Return True
    Catch
        Return False
    End Try
End Sub

Sub Class_Globals
    Private ion As Object
End Sub

Public Sub Initialize
 
End Sub

Public Sub SaveAs (Source As InputStream, MimeType As String, Title As String) As ResumableSub
    Dim intent As Intent
    intent.Initialize("android.intent.action.CREATE_DOCUMENT", "")
    intent.AddCategory("android.intent.category.OPENABLE")
    intent.PutExtra("android.intent.extra.TITLE", Title)
    intent.SetType(MimeType)
    StartActivityForResult(intent)
    Wait For ion_Event (MethodName As String, Args() As Object)
    If -1 = Args(0) Then 'resultCode = RESULT_OK
        Dim result As Intent = Args(1)
        Dim jo As JavaObject = result
        Dim ctxt As JavaObject
        Dim out As OutputStream = ctxt.InitializeContext.RunMethodJO("getContentResolver", Null).RunMethod("openOutputStream", Array(jo.RunMethod("getData", Null)))
        File.Copy2(Source, out)
        out.Close
        Return True
    End If
    Return False
End Sub

Private Sub StartActivityForResult(i As Intent)
    Dim jo As JavaObject = GetBA
    ion = jo.CreateEvent("anywheresoftware.b4a.IOnActivityResult", "ion", Null)
    jo.RunMethod("startActivityForResult", Array(ion, i))
End Sub

Private Sub GetBA As Object
    Dim jo As JavaObject = Me
    Return jo.RunMethod("getBA", Null)
End Sub

Logger connected to: motorola motorola edge 30 pro
--------- beginning of main
*** Service (starter) Create ***
Device locale: es
Found 66 strings.
** Service (starter) Start **
** Activity (main) Create (first time) **
Found 66 strings.
2400
true
2400
*** mainpage: B4XPage_Created
*** mainpage: B4XPage_Appear
** Activity (main) Resume **
true
*** Receiver (httputils2service) Receive (first time) ***
*** frmxfilter: B4XPage_Created [mainpage]
*** mainpage: B4XPage_Disappear [mainpage]
*** frmxfilter: B4XPage_Appear [mainpage]
false
true
java.lang.Exception: Sub _written was not found.
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:227)
at anywheresoftware.b4a.BA$2.run(BA.java:395)
at android.os.Handler.handleCallback(Handler.java:984)
at android.os.Handler.dispatchMessage(Handler.java:104)
at android.os.Looper.loopOnce(Looper.java:238)
at android.os.Looper.loop(Looper.java:357)
at android.app.ActivityThread.main(ActivityThread.java:8118)
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:957)

Attache the output file in debug mode. I have commented out the line to save the file and in release mode and it keeps crashing

 

[Hamied Abou Hulaikah]

Sub _written was not found.

 

[Sergio Haurat]

Sub _written was not found.

I do not have a Sub called like this in all the code, for me it is inside aPOI and I get to capture the error because I am executing inside a Try

In the original example it talks about saving a stream with the function .Write(stream, file) or something like that. In the reality of aPOI, there is only the Write2 function that you must indicate the directory and the file.

Version 08 has .Write, version 11 only Write2.

 

[Sergio Haurat]

Problem solved. Something is not right in version 11, I have replaced with 08 and made the same changes to my code as inside the example.

@DonManfred The problem is in the aPOI library version 11