Android Question Writing to excel file and getting error

David Hawkins

Active Member
Licensed User
Longtime User
Hi I am trying to create an Excel file by using the following code

Export Excel file:
Public Sub ExportTableToExcel(Table As B4XTable)
    'Log("Exporting table.")
    
    Dim rp As RuntimePermissions
    rp.GetSafeDirDefaultExternal("Download")
    
    Dim Filename As String = "FieldInformation.xlsx"

    Dim Download_Path As String = File.DirRootExternal & "/Download"
    'Log($"File Dir: ${Download_Path}/${Filename}"$)
    
    If File.IsDirectory(File.DirRootExternal , "Download") = False Then
        File.MakeDir(File.DirRootExternal, "Download")
    End If
    
    'Log(File.IsDirectory(File.DirRootExternal , "Download"))

    Dim newWorkbook As WritableWorkbook
    newWorkbook.Initialize(Download_Path, Filename)
    
    Dim sheet1 As WritableSheet
    sheet1 = newWorkbook.AddSheet("Information", 0)

    Dim iColumn As Int = 0

    For Each Column As B4XTableColumn In Table.Columns
        Dim cell As WritableCell
        cell.InitializeText(iColumn, 0, Column.Title)
        sheet1.AddCell(cell)
        iColumn = iColumn + 1
    Next
  
    For i = 1 To Table.Size
        Dim cInt As Int = 0
        For Each Column As B4XTableColumn In Table.Columns
            Dim Row As Object = Table.GetRow(i).Get(Column.Title)
            Dim cell As WritableCell
            cell.InitializeText(cInt, i, Row)
            sheet1.AddCell(cell)
            cInt = cInt + 1
        Next
    Next
  
    'Must call write and close to save the data.
    newWorkbook.Write
    newWorkbook.Close
    xui.MsgboxAsync($"File Dir: ${Download_Path}/${Filename}"$, "Table Exported")
    'Log("Export sucessfull!...")
End Sub

and when I run the code I get the following error

java.lang.NoSuchMethodError: No static method getDirTemp()Ljava/lang/String; in class Lanywheresoftware/b4a/objects/streams/File; or its super classes (declaration of 'anywheresoftware.b4a.objects.streams.File' appears in /data/app/~~4Mv4cYbVASn_GNwvTCLEJQ==/FarmFields.B4A-xUwRPFUN3j8NnVXsAW2_YQ==/base.apk)
at anywheresoftware.b4a.objects.WorkbookWrapper.getDefaultSettings(WorkbookWrapper.java:108)
at anywheresoftware.b4a.objects.WorkbookWrapper$WritableWorkbookWrapper.Initialize(WorkbookWrapper.java:122)
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:157)
at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:262)
at FarmFields.B4A.fieldhistory._exporttabletoexcel(fieldhistory.java:551)
at FarmFields.B4A.fieldhistory._button1_click(fieldhistory.java:536)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:157)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:201)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
at android.view.View.performClick(View.java:7892)
at android.widget.TextView.performClick(TextView.java:16219)
at android.view.View.performClickInternal(View.java:7869)
at android.view.View.-$$Nest$mperformClickInternal(Unknown Source:0)
at android.view.View$PerformClick.run(View.java:30880)
at android.os.Handler.handleCallback(Handler.java:942)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loopOnce(Looper.java:226)
at android.os.Looper.loop(Looper.java:313)
at android.app.ActivityThread.main(ActivityThread.java:8757)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:571)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1067)
Error occurred on line: 367 (FieldHistory)

The manifest Editor is

AddManifestText(
<uses-sdk android:minSdkVersion="5" android:targetSdkVersion="31"/>
<supports-screens android:largeScreens="true"
android:normalScreens="true"
android:smallScreens="true"
android:anyDensity="true"
android:imeOptions="flagNoExtractUi|flagNoFullscreen" />)
SetApplicationAttribute(android:icon, "@drawable/icon")
SetApplicationAttribute(android:label, "$LABEL$")
SetApplicationAttribute(android:usesCleartextTraffic, "true")
CreateResourceFromFile(Macro, Themes.LightTheme)
'End of default text.
SetApplicationAttribute(android:theme, "@style/LightTheme")
SetActivityAttribute(Main, android:windowSoftInputMode, adjustResize|stateHidden)
AddManifestText(
<uses-permission
android:name="android.permission.WRITE_EXTERNAL_STORAGE"
android:maxSdkVersion="31" />
)

Could somebody assist with a resolution please

Many Regards

David
 
Solution
@David Hawkins , I think i have fixed your problem :
check the attached example
it depends on class ManagerExternalStorage and the lib runtimepermissions
You will see on line : 48 of the ManagerExternalStorage Class change b4a.example by your package

Regards

Justcooldev

Member
Licensed User
Hi ! Please send me the full project (if it is possible) ... So i can test it
 
Upvote 0

David Hawkins

Active Member
Licensed User
Longtime User
Hi Justcooldev that could be difficult, as I am reading data from a remote database and writing it into a B4XTable. The table is populating perfectly but as soon as I click on a button to execute the code above I get the error.

The 'Download' folder is being created and the "FieldInformation.xlsx" spreadsheet is being created but I am unable to write to any of the cells as system crashes at the point where 'newworkbook' is being initialized, I can try to create a small project to send to you.

Regards

David
 
Upvote 0

David Hawkins

Active Member
Licensed User
Longtime User
I'm not opening the file with excel or open office or anything else, I trying to create the file with data inside it, the app is crashing before i can fully create the file using B4A.
It appears to be creating the file during initialization but then it crashes, with the errors shown above.
 
Upvote 0

Justcooldev

Member
Licensed User
Hi, i worked on your problem,

i attach an example, it is working with me
test it

Regards
 

Attachments

  • EXCELERROR.zip
    10.8 KB · Views: 154
Upvote 0

Justcooldev

Member
Licensed User
@David Hawkins , I think i have fixed your problem :
check the attached example
it depends on class ManagerExternalStorage and the lib runtimepermissions
You will see on line : 48 of the ManagerExternalStorage Class change b4a.example by your package

Regards
 

Attachments

  • EXCELERRORFIXED.zip
    12.7 KB · Views: 161
Upvote 0
Solution

David Hawkins

Active Member
Licensed User
Longtime User
Hi Justcooldev,
I implemented the changes in my app and it is working OK but a couple of things I can't produce .xlsx files using the normal excel library and the app keeps asking me to approve access to all files and as far as I can see I have allowed that access.

Regards
David
 
Upvote 0

David Hawkins

Active Member
Licensed User
Longtime User
Hi Justcooldev, I placed it in the B4XPage_Appear sub routine and then realised my mistake and it is now in the B4XPage_Create sub routine and all is working great, thank you so much for you help and patience.

Many regards
David
 
Upvote 0
Top