B4J Question [SOLVED] jPOI strange error

[Emme Developer]

Hi all, i'm trying to make a simple software that trasform my database in an excel file, but i got this strange error

Error occurred on line: 50 (Main)
java.lang.IllegalArgumentException: Attempting to write a row[1] in the range [0,592] that is already written to disk.
    at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:115)
    at anywheresoftware.b4j.objects.PoiSheetWrapper.CreateRow(PoiSheetWrapper.java:57)
    at b4j.example.main._appstart(main.java:170)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:614)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:231)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at b4j.example.main.start(main.java:38)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)

I want to get the second row that i initialize using sh.createrow. I don't understand how i got uninizialized row if i try to get it, but if i re create it is already written in disk

    If sh.GetRow(i).IsInitialized = False Then
            Dim row As PoiRow = sh.CreateRow(i)
'            Log(ss)
        Else
            row = sh.GetRow(i)
        End If

Thanks!

 

[Erel]

Can you upload a small project that demonstrates this issue?

 

[Emme Developer]

Hi @Erel, i don't know why, i'm not able to export as zip (i got "size is 0, but i aspected 12754"). By the way, is a very simple program.
This is all code

Spoiler: Code4

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
#AdditionalJar: sqlite-jdbc-3.7.2

#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
   
    Dim PoiWorkbook As PoiWorkbook
    PoiWorkbook.InitializeNew(True)
    Dim sh As PoiSheet = PoiWorkbook.AddSheet("Lang",0)
   
    Dim s As SQL
    File.Copy(File.DirAssets,"trad.db",File.DirApp,"trad.db")
    s.InitializeSQLite(File.DirApp,"trad.db",True)
   
    Dim i As Int = 0
    Dim j As Int = 0
   
   
    Dim la As List
    la.Initialize
   
'    Dim row As PoiRow
    Dim row As PoiRow = sh.CreateRow(0)
    row.CreateCellString(0,"Key")
    row.CreateCellString(1,"EN")
    row.CreateCellString(2,"IT")
    row.CreateCellString(3,"BR")
   
    Dim rs As ResultSet = s.ExecQuery("Select * from data order by lang, key asc")
    Do While rs.NextRow
        Dim ss As String = rs.GetString("lang")
        If la.IndexOf(ss) = -1 Then
            j = j+1
            i = 1
            la.Add(ss)
        End If
       
        If sh.GetRow(i).IsInitialized = False Then
            Dim row As PoiRow = sh.CreateRow(i)
        Else
            row = sh.GetRow(i)
        End If

        row.CreateCellString(0,rs.GetString("key"))
        i = i+1
        row.CreateCellString(j,rs.GetString("value"))       
    Loop
    PoiWorkbook.Save(File.DirApp,"lang.xlsx")
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

I attach the db used in files

 

[Erel]

The problem is related to the way the data is written. It is a streaming algorithm with a cache of 100 rows. When it is filled the rows are written to the disk.

You need to collect all the data and then write each row. You cannot go back.