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