B4J Question jPoi 5 - working with csv files

[ilan]

hi

i need to import some data from an excel file. the file extension is csv. using it with jPoi 5 gives me an error:

Waiting for debugger to connect...
Program started.
Error occurred on line: 199 (XLUtils)
java.lang.reflect.InvocationTargetException
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:487)
at b4j.example.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:876)
at b4j.example.b4xpagesmanager._showpage(b4xpagesmanager.java:355)
at b4j.example.b4xpagesmanager._addpage(b4xpagesmanager.java:176)
at b4j.example.b4xpagesmanager._addpageandcreate(b4xpagesmanager.java:190)
at b4j.example.b4xpagesmanager._initialize(b4xpagesmanager.java:126)
at b4j.example.main._appstart(main.java:86)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
at b4j.example.main.start(main.java:38)
at javafx.graphics/com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$9(LauncherImpl.java:846)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runAndWait$12(PlatformImpl.java:455)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:134)
at anywheresoftware.b4a.debug.Debug.CallSubNew2(Debug.java:81)
... 35 more
Caused by: java.lang.reflect.InvocationTargetException
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:115)
... 36 more
Caused by: java.lang.RuntimeException: Object should first be initialized (PoiWorkbook).
at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:49)
at b4j.example.xlutils._rangestringornametorange(xlutils.java:462)
at b4j.example.xlreader._readranges(xlreader.java:593)
at b4j.example.xlreader._readrange(xlreader.java:58)
at b4j.example.b4xmainpage._loaddata(b4xmainpage.java:110)
at b4j.example.b4xmainpage._b4xpage_created(b4xmainpage.java:83)
... 41 more

if i open the file in excel and save it as .xlsx it is working fine.
is it possible to make it run using csv files?

thanx

 

[DonManfred]

if i open the file in excel and save it as .xlsx it is working fine.

Excel has probably 100mb of code to import an CSV. ?

You need to build the Excel file by yourself based on the CSV-Data.

All needed methods to do it are available in xlutils

XLUtils / jPOI 5 - Read and write MS Excel workbooks

As written here, I plan to make it easier to read and write Excel workbooks. The solution is based on three components: - Apache POI - https://poi.apache.org/ Large open source project that provides APIs for Microsoft documents. Note that the files are accessed directly, it doesn't depend on the...

www.b4x.com

I don´t know whether there is a csv-import-method though

 

[ilan]

Excel has probably 100mb of code to import an CSV. ?

thats not much, erel was 10 days in iceland he will do it in 10 minutes ?

 

[DonManfred]

he will do it in 10 minutes ?

right. But in less than 100 lines i guess

 

[Erel]

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private xl As XLUtils
End Sub

Public Sub Initialize
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    xl.Initialize
    Dim parser As CSVParser
    parser.Initialize
    Dim tr As TextReader
    tr.Initialize2(File.OpenInput("C:\Users\H\Downloads\countries-20140629.csv", ""), "Windows-1252")
    Dim input As String = tr.ReadAll
    Dim table As List = parser.Parse(input, ",", False)
    CreateWorkbook(table, "C:\Users\H\Downloads\test.xlsx")
End Sub

Private Sub CreateWorkbook (Table As List, FileName As String)
    Dim wb As XLWorkbookWriter = xl.CreateWriterBlank
    Dim sheet As XLSheetWriter = wb.CreateSheetWriterByName("Sheet1")
    Dim address As XLAddress = xl.AddressName("A1")
    For Each row() As String In Table
        address.Col0Based = 0
        For Each cell As String In row
            sheet.PutString(address, cell)
            address.Col0Based = address.Col0Based + 1
        Next
        address.Row0Based = address.Row0Based + 1
    Next
    Dim TitleStyle As XLStyle = wb.CreateStyle.FontBold(12).ForegroundColor(xl.COLOR_GREY_25_PERCENT)
    sheet.AddStylesToRange(xl.CreateXLRange(xl.AddressName("A1"), xl.AddressName("C1")), Array(TitleStyle))
    sheet.AutoSizeColumn(0)
    sheet.AutoSizeColumn(1)
    sheet.AutoSizeColumn(2)
    wb.SaveAs(FileName, "", False)
End Sub

CSV: http://blog.plsoucy.com/2012/04/iso-3166-country-code-list-csv-sql/
CSVParser: https://www.b4x.com/android/forum/threads/b4x-csvparser-csv-parser-and-generator.110901/#content

 

[DonManfred]

@ilan What did i say? Less than 100 lines ?

 

[ilan]

Erel you are the best! Thank you ? ? ?