Android Question Writing to excel file and getting error

David Hawkins · May 11, 2023

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

Justcooldev · May 12, 2023

@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 · May 11, 2023

Hi ! Please send me the full project (if it is possible) ... So i can test it

David Hawkins · May 11, 2023

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

Justcooldev · May 11, 2023

David Hawkins said:
I can try to create a small project to send to you.

don't break your head

I have a question : in debug mode, wich line is crashing ?
Regards,

David Hawkins · May 11, 2023

In the code above it is Line 19

Justcooldev · May 11, 2023

The line is working fine for me, are you opening the file with excel,OpenOffice, etc.. ?

David Hawkins · May 11, 2023

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.

Justcooldev · May 11, 2023

have you tested it with an other phone ?

David Hawkins · May 11, 2023

I've tested it with a OnePlus 9 with OxygenOS version 13.0 and a Sumsung Galaxy Tab 6 Lite version 13

Justcooldev · May 11, 2023

and always the same error ?

David Hawkins · May 11, 2023

Yes always the same error

Justcooldev · May 12, 2023

Hi, i worked on your problem,

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

Regards

DonManfred · May 12, 2023

Justcooldev said:
i attach an example, it is working with me

It is as faulty as the original.

Code with File.DirRootExternal = broken code

Justcooldev · May 12, 2023

@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

David Hawkins · May 13, 2023

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

Justcooldev · May 13, 2023

Hi David ! Where did you placed the sub GetPermissions ?

David Hawkins · May 14, 2023

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

Justcooldev · May 14, 2023

Hi David, i'm happy for you,

David Hawkins said:
thank you so much for you help and patience.

No problem !
I'm at your service !

Android Question Writing to excel file and getting error

Active Member

Member

Active Member

Member

Active Member

Member

Active Member

Member

Active Member

Member

Active Member

Member

Attachments

Expert

Member

Attachments

Active Member

Member

Active Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency