Android Question B4A Excel Library

mike1967

Active Member
Licensed User
Longtime User
Hello, In order to use an excel xls file like template for report i will to know if is possible modify cell value without lost the format and the borders defined in the template. If i use:

cell.InitializeText(colonna, riga, row(r))
sheet1.AddCell(cell)

i lost the format of the cell.
Thanks in advance
 

Attachments

  • SamplePageReport.PNG
    SamplePageReport.PNG
    38.3 KB · Views: 271

Erel

B4X founder
Staff member
Licensed User
Longtime User
You can keep the cell style with these two methods:
B4X:
Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
End Sub

Public Sub Initialize
    
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    Dim rp As RuntimePermissions
    Dim wb As WritableWorkbook
    Dim template As ReadableWorkbook
    template.Initialize(File.DirAssets, "Book1.xls")
    rp.CheckAndRequest(rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
    Wait For B4XPage_PermissionResult (Permission As String, Result As Boolean)
    If Result Then
        wb.Initialize2(File.DirRootExternal, "test.xls", template)
        Dim sheet As WritableSheet = wb.GetSheet(0)
        SetCellString(sheet, 0, 0, "new text")
        wb.Write
        wb.Close
    End If
End Sub

Sub SetCellString(sheet As WritableSheet, Col As Int, Row As Int, Text As String)
    Dim jo As JavaObject = sheet
    jo.RunMethodJO("getCell", Array(Col, Row)).RunMethod("setString", Array(Text))
End Sub

Sub SetCellNumber(sheet As WritableSheet, Col As Int, Row As Int, Value As Double) 'ignore
    Dim jo As JavaObject = sheet
    jo.RunMethodJO("getCell", Array(Col, Row)).RunMethod("setValue", Array(Value))
End Sub

Manifest editor:
B4X:
SetApplicationAttribute(android:requestLegacyExternalStorage, true)
 
Upvote 0

mike1967

Active Member
Licensed User
Longtime User
You can keep the cell style with these two methods:
B4X:
Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
End Sub

Public Sub Initialize

End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    Dim rp As RuntimePermissions
    Dim wb As WritableWorkbook
    Dim template As ReadableWorkbook
    template.Initialize(File.DirAssets, "Book1.xls")
    rp.CheckAndRequest(rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
    Wait For B4XPage_PermissionResult (Permission As String, Result As Boolean)
    If Result Then
        wb.Initialize2(File.DirRootExternal, "test.xls", template)
        Dim sheet As WritableSheet = wb.GetSheet(0)
        SetCellString(sheet, 0, 0, "new text")
        wb.Write
        wb.Close
    End If
End Sub

Sub SetCellString(sheet As WritableSheet, Col As Int, Row As Int, Text As String)
    Dim jo As JavaObject = sheet
    jo.RunMethodJO("getCell", Array(Col, Row)).RunMethod("setString", Array(Text))
End Sub

Sub SetCellNumber(sheet As WritableSheet, Col As Int, Row As Int, Value As Double) 'ignore
    Dim jo As JavaObject = sheet
    jo.RunMethodJO("getCell", Array(Col, Row)).RunMethod("setValue", Array(Value))
End Sub

Manifest editor:
B4X:
SetApplicationAttribute(android:requestLegacyExternalStorage, true)

this is my code:
B4X:
#Region  Project Attributes
    #ApplicationLabel: RegAnt Print
    #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

Sub Process_Globals
End Sub

Sub Globals
    Dim rp As RuntimePermissions
    Dim fp As FilePickerDialog
    Dim sql As SQL
    Dim dbpath As String
    dbpath=File.DirRootExternal & "/smartlauncher/.project/Regant/"
    Dim r As Int=0
    Dim rr As Int =0
    Dim riga As Int=0
    Dim colonna As Int =0

End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("main")
    Dim props As DialogProperties
    Dim ext() As String
    ext=Array As String ("csv")
    props.Initialize("",File.DirRootExternal&"/Download",0,0,ext) 'Array As String(".jpg",".png",".pdf"))
    fp.Initialize("FilePicker")
    fp.Properties = props
    rp.CheckAndRequest(rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
    Wait For Activity_PermissionResult (Permission As String, Result As Boolean)
    If Result Then
        File.Copy(dbpath,"DEV_DB",File.DirRootExternal & "/Download","DEV_DB")
        fp.show
    End If


End Sub


Sub Activity_Resume


End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub



Sub FilePicker_onSelectedFilePaths(selected() As String)
    If selected.Length =1 Then
        Log($"FilePicker_onSelectedFilePaths(${selected.Length})"$)
     
            fp.dismiss
            Dim parser As CSVParser
            parser.Initialize
            Dim table As List = parser.Parse(File.ReadString("", selected(selected.Length-1)), ",", True)
            sql.Initialize(File.DirRootExternal & "/Download","DEV_DB",False)
            Dim workbook1 As ReadableWorkbook
            Dim Sheet As ReadableSheet
            Dim tr As Int=0
            For Each row() As String In table
                tr=row.Length
            Next
            If tr=12 Then 'addetti
            End If
            If tr=10 Then 'attrezzature
            End If
            If tr=14 Then 'controlli
             
                workbook1.Initialize(File.DirAssets,"rptcontrolli.xls")
                Sheet = workbook1.GetSheet(0)
                Dim newWorkbook As WritableWorkbook

                newWorkbook.Initialize2(File.DirRootExternal,"Download/rptcontrolli.xls",workbook1)
             
                Dim sheet1 As WritableSheet
                sheet1 = newWorkbook.GetSheet(0)
                Dim cell As WritableCell
                For Each row() As String In table
                    For r = 0 To 13
                        riga=rr+5
                        colonna=r
                        Dim value As String =row(r)
                    SetCellString(sheet1, colonna,riga ,value)
                    'cell.InitializeText(colonna, riga, row(r))
                    'sheet1.AddCell(cell)
                    'AddImage(sheet1, File.OpenInput(File.DirAssets, "addetti.png"), 12,7, 2, 1)
                 
                    Next
                    rr=rr+1
                Next
            End If
            newWorkbook.Write
            newWorkbook.Close
     
    Else
        fp.show
    End If


End Sub
Sub SetCellString(wb As WritableSheet, Col As Int, Row As Int, Text As String)
    Dim jo As JavaObject = wb
    jo.RunMethodJO("getCell", Array(Col, Row)).RunMethod("setString", Array(Text))
End Sub

Sub SetCellNumber(wb As WritableSheet, Col As Int, Row As Int, Value As Double) 'ignore
    Dim jo As JavaObject = wb
    jo.RunMethodJO("getCell", Array(Col, Row)).RunMethod("setValue", Array(Value))
End Sub

Sub AddImage(wb  As WritableSheet, bmp As InputStream, col As Double, row As Double, _
   width As Double, height As Double)
    Dim wi As JavaObject
    wi.InitializeNewInstance("jxl.write.WritableImage", Array(col, row, width, height, Bit.InputStreamToBytes(bmp)))
    Dim jo As JavaObject = wb
    jo.RunMethod("addImage", Array(wi))
End Sub

Your code return no error,but the result xls Is corrupted,my code
on sub SetCellString it return the error like in attached picture. What i Wrong ? Thanks in advance.edit:also your code return same error.
 

Attachments

  • error.png
    error.png
    20.9 KB · Views: 180
Last edited:
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
(I state that I have never used Excel with B4A).
Dim workbook1 As ReadableWorkbook
Dim Sheet As ReadableSheet
Readable? I suppose they must be writable.


EDIT:
Modifying an existing workbook

As noted above it is not possible to directly modify a workbook file. Instead you need to create a new file that will include a copy of the original workbook.
This is done by first loading the existing workbook with ReadableWorkbook.

Then you should create a WritableWorkbook and call Initialize2 instead of Initialize. Initialize2 expects a ReadableWorkbook as the last parameter. It creates a copy of the given ReadableWorkbook.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
In the old days of B4A, unhandled exceptions were caught and such dialog was displayed. This is bad as it is better to let the app crash and provide a full crash report / error in the logs.
For backward compatibility, the old dialog appears when there is no Application_Error sub in the starter service (or usually when the starter service is missing).
 
Upvote 0

mike1967

Active Member
Licensed User
Longtime User
In the old days of B4A, unhandled exceptions were caught and such dialog was displayed. This is bad as it is better to let the app crash and provide a full crash report / error in the logs.
For backward compatibility, the old dialog appears when there is no Application_Error sub in the starter service (or usually when the starter service is missing).
Sorry, i made that what you says, now this is the error:
lib:Raising.. filepicker_onselectedfilepaths()
FilePicker_onSelectedFilePaths(1)
Error occurred on line: 114 (Main)
java.lang.RuntimeException: Method: setString not found in: jxl.write.Blank
at anywheresoftware.b4j.object.JavaObject$MethodCache.getMethod(JavaObject.java:366)
at anywheresoftware.b4j.object.JavaObject.RunMethod(JavaObject.java:119)
at b4a.example.main._setcellstring(main.java:704)
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:348)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
at anywheresoftware.b4a.BA$2.run(BA.java:387)
at android.os.Handler.handleCallback(Handler.java:873)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6669)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
 
Last edited:
Upvote 0

mike1967

Active Member
Licensed User
Longtime User
Sorry, i made that what you says, now this is the error:
lib:Raising.. filepicker_onselectedfilepaths()
FilePicker_onSelectedFilePaths(1)
Error occurred on line: 114 (Main)
java.lang.RuntimeException: Method: setString not found in: jxl.write.Blank
at anywheresoftware.b4j.object.JavaObject$MethodCache.getMethod(JavaObject.java:366)
at anywheresoftware.b4j.object.JavaObject.RunMethod(JavaObject.java:119)
at b4a.example.main._setcellstring(main.java:704)
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:348)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
at anywheresoftware.b4a.BA$2.run(BA.java:387)
at android.os.Handler.handleCallback(Handler.java:873)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6669)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
I fondi this:
But i not know of to apply.
 
Upvote 0

mike1967

Active Member
Licensed User
Longtime User
This means that you are trying to write to a blank cell. Put some text in the cell (in the template file).
i solved with :
B4X:
            Dim cellformat As WritableCellFormat
            cellformat.Initialize2(cellformat.FONT_ARIAL, 7, False, False, False, cellformat.COLOR_BLACK)
            cellformat.HorizontalAlignment = cellformat.HALIGN_CENTRE
            cellformat.VertivalAlignment = cellformat.VALIGN_CENTRE
            cellformat.SetBorder(cellformat.BORDER_ALL, cellformat.BORDER_STYLE_THIN, cellformat.COLOR_BLACK)
            Dim refl As Reflector
            refl.Target = cellformat
            refl.RunMethod2("setWrap", True, "java.lang.boolean")

......

                    cell.InitializeText(colonna, riga, row(r))
                    cell.SetCellFormat(cellformat)
                    sheet1.AddCell(cell)
rebuilding the original format.

Now i have promlem with add image to sheet, i create a new Post
 
Last edited:
Upvote 0
Top