Android Question Change and save Excel - or alternatives?

[matzeman]

Hello everybody,

this is my first thread here and first of all I want to thank you for this excellent software and all the tutorials.

With Excel VBA I gained much experience and now I discovered Basic4Android as a new hobby. I tried a lot of things (e.g. with the excel library) during the last days and studied the posts here. But I didn't find an answer to the following.

I would like to open an excel file (ok) and change an existing worksheet. After a user entered some data I would like to transfer this information to this excel-sheet and save the workbook. There is no need for showing the table on the phone - I only have to make a lot of complex calculations and then want to transfer the results back to the phone.

Perhaps another library (SQL?) can serve the same purpose, but I do not know what to look for. Could anybody help?

Thanks in advance!

 

[Erel]

The excel library allows you to make a copy of an existing worksheet, modify it and save it as a new file. You can then delete the original file and copy the new one over the old one.

 

[matzeman]

Thanks Erel for your help, but I still haven't got it.

Sub Globals
    Dim workbookold As ReadableWorkbook
    Dim worksheetold As ReadableSheet
    Dim workbooknew As WritableWorkbook
    Dim worksheetnew As WritableSheet
    Dim cell As WritableCell
End Sub

Sub Activity_Create(FirstTime As Boolean)
    loadtable(File.DirAssets, "Book1.xls")
End Sub

Sub loadtable(Dir As String, FileName As String)
    workbookold.Initialize(File.DirAssets, "Book1.xls")
    worksheetold = workbookold.GetSheet(0)
   
    workbooknew.Initialize2(File.DirInternal, "Book2.xls", workbookold)
    worksheetnew = workbooknew.AddSheet("Test",0)
   
    ' modify one cell
    cell.InitializeText(2,2, "Hello")
    worksheetnew.AddCell(cell)
   
    ' delete old sheet and save the new workbook
    workbooknew.RemoveSheet(1)
    workbooknew.write
    workbooknew.Close
   
    ' afterwards rename file
End Sub

To see the result on my computer (later I will have about 20 columns of data, so checking it on the emulator is no option), I tried to change the path, e.g. "D:\" instead of "File.DirInternal" or "File.DirRootExternal" but it didn't work. Is there any possibility?

Thanks!

 

[NJDude]

If you need to access the "D:" drive on your PC from your Android device you have to use SAMBA

 

[Erel]

You can only work with local files. As NJDude wrote you can use the SMB library to download the file, modify it and upload the modified file.

 

[matzeman]

Thank you very much. I will have a try.

 

[matzeman]

I am in despair. I don't know what I have to change that the code works:

Sub Process_Globals

End Sub

Sub Globals
    Dim wkb1 As ReadableWorkbook
    Dim wks1 As ReadableSheet
    Dim wkb2 As WritableWorkbook
    Dim wks2 As WritableSheet
    Dim zelle As WritableCell
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("startseite")
    If File.Exists(File.DirInternal, "test.xls") = False Then
        File.Copy(File.DirAssets, "test.xls", File.DirInternal, "test.xls")
    End If
   
    wkb1.Initialize(File.DirInternal, "test.xls")
    wks1 = wkb1.GetSheet(0)
   
    wkb2.Initialize2(File.DirInternal, "testneu.xls", wkb1)
    wks2 = wkb2.GetSheet(0)

    zelle.InitializeText(5, 5, "hallo")
    wks2.AddCell(zelle)
   
    wkb2.Write
End Sub

The last command "wkb2.write" doesn't work, "java.lang.NullPointerException" is the error message.

Could anyone help? Thanks in advance!

 

[Erel]

Can you post the full error message from the logs (right click on the logs)?

 

[matzeman]

Hi Erel,

here is the error message

---------------------------------------------------------------------------------
LogCat connected to: B4A-Bridge: samsung GT-I9000-354406041890477
--------- beginning of /dev/log/system
--------- beginning of /dev/log/main
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (main) Create, isFirst = false **
running waiting messages (1)
** Activity (main) Resume **
** Service (service1) Destroy **
** Activity (main) Pause, UserClosed = false **
** Activity (main) Create, isFirst = false **
** Activity (main) Resume **
** Service (service1) Create **
** Service (service1) Start **
Connected to B4A-Bridge (Wifi)
java.net.SocketException: Connection reset by peer
Connected to B4A-Bridge (Wifi)
** Activity (main) Pause, UserClosed = false **
Installing file.
PackageAdded: package:b4a.example
** Activity (main) Create, isFirst = true **
(Main, 28) FullScreen or IncludeTitle properties in layout file do not match the activity attributes settings. (warning #1004)
main_activity_create (B4A line: 42)
wkb2.Write
java.lang.NullPointerException
at jxl.biff.DVParser.<init>(DVParser.java:669)
at jxl.biff.BaseCellFeatures.getDVParser(BaseCellFeatures.java:501)
at jxl.write.biff.SheetWriter.writeDataValidation(SheetWriter.java:1137)
at jxl.write.biff.SheetWriter.write(SheetWriter.java:581)
at jxl.write.biff.WritableSheetImpl.write(WritableSheetImpl.java:1558)
at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:950)
at anywheresoftware.b4a.objects.WorkbookWrapper$WritableWorkbookWrapper.Write(WorkbookWrapper.java:150)
at b4a.example.main._activity_create(main.java:332)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at b4a.example.main.afterFirstLayout(main.java:98)
at b4a.example.main.access$100(main.java:16)
at b4a.example.main$WaitForLayout.run(main.java:76)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:4627)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:871)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:629)
at dalvik.system.NativeStart.main(Native Method)
---------------------------------------------------------------------------------

Thank you.

 

[Erel]

Can you upload your project (File - Export as zip)?

Maybe there is an unsupported feature in the existing XLS file.

 

[matzeman]

Sure (find attached).

First I thought perhaps the problem is a German Umlaut or something similar. That is why I cleared almost all contents in the excel file and tried it once more yesterday - it didn't work.

After reading your last post I tried it again with a new and empty xls.workbook - there was no problem. Afterwards I copied the values and formats from my original file to the new one and there was still no problem. Thanks to your help I get along with the problem at the moment.

Perhaps it would be interesting to know the cause to avoid it in the future. But don't waste your time. Thank you very much for your help, Erel.

 

[hibrid0]

I like to know if your problem was solved, I need the same solution, I cant write to 1 cell in a Sheet exist.

 

[matzeman]

After replacing the excel sheet (taking a new one) everything was fine.

 

[hibrid0]

Tajnks for your answer