B4J Library MashPOI - Import & Export 2 Excel

Mashiane

Expert
Licensed User
Hi there

MashPOI is a collection of some jPOI related methods that I have found from the forum and created a library. As much as there are a couple of functions in the modules, I also want to add functionality to easily add worksheets, add rows and style the various columns in Excel documents.

I started this as a means to achieve something else... :)

A working example of importing all excel sheet records to a database table is herein. I will link this to another app I'm finalising. It's an exciting one.

B4X:
Sub btnB4XGoodies_Click
    'select and parse the xls file with b4x goodies
    Dim ext As List
    ext.Initialize
    ext.Add("*.xls")
    ext.Add("*.xlsx")
    Dim goodFile As String = OpenSaveFileDialog(MainForm,True,"Select Excel File",File.DirApp,ext)
    If goodFile = "" Then Return
    'test of the file meets the b4x structure
    Dim wb As PoiWorkbook = MashPOI.PoiOpenFile("",goodFile,"")
    Dim ws As PoiSheet = MashPOI.poigetworksheet(wb,"B4Xgoodies")
    'define the record map to link excel columns to db record
    Dim rmap As Map
    rmap.Initialize
    rmap.Put("a","product")
    rmap.Put("b","atype")
    rmap.Put("c","category")
    rmap.Put("d","aname")
    rmap.Put("e","author")
    rmap.Put("f","latestversion")
    rmap.Put("g","latestversiondate")
    rmap.Put("h","license")
    rmap.Put("i","description")
    rmap.Put("j","url")
    rmap.Put("k","tags")
 
    'get all data from the excel sheet
    Dim allRows As List = MashPOI.PoiGetRows(ws)
    'close the workbook
    wb.Close
    ' start the import by extracting only the relavant fields to import, starting at row 1
    Dim records As List = MashPOI.Poi2Import(allRows,rmap,1)
 
    'import the data and exclude the first row
    DBUtils.TableClear(b4xgoodies,"b4xgoodies")
    'reset the counters
    DBUtils.SQLiteResetCounter(b4xgoodies,"b4xgoodies","id")
    'add records to the database
    DBUtils.InsertMaps(b4xgoodies,"b4xgoodies",records)
    ' how many records do we have
    Dim recCount As Int = DBUtils.RecordCount(b4xgoodies,"b4xgoodies","id")
    InformUser(MainForm,"B4X Goodies",recCount & " record(s) now exist in your records.")
    'load the goodies
    LoadAuthors
End Sub
 

Attachments

Last edited:

Mashiane

Expert
Licensed User
I guess it's high time I try the LibDoc thingy...

MashPOI

Author: Anele Mashy Mbanga anele@mbangas.com
Version: 1.01
  • MashPOI
    • Functions:
      • Poi2Import (allRows As List, structure As Map, startRow As Int) As List
        returns a list of maps based on an import structure
        the import structure should be the column name and the field name to set
      • PoiColumnNames As List
        define the column names as a list from a > zz
      • PoiCreateFile (dir As String, fil As String, pwd As String) As Boolean
        create a new Poi xlsx workbook, xlsx extension will be added
      • PoiCreateRow (ws As PoiSheet, rowData As List, rowPos As Int) As PoiRow
        add a row of string data to the worksheet
      • PoiCreateSheet (wb As PoiWorkbook, sheetName As String) As PoiSheet
        create a new worksheet if it does not exist or return it if it does
      • PoiDateFromExcelDate (sValue As String) As String
        convert date saved in excel sheet to poi format
      • PoiExportList (dir As String, fil As String, pwd As String, wsName As String, Title As String, colNames As List, TitleStyle As ReportHeading, HeadingStyle As ReportHeading, SetBorder As Boolean, records As List) As Boolean
        export a list of map records to an excel spreadsheet
        if border is set a thin border is used
      • PoiGetBorderBottom (ws As PoiSheet, colAlpha As String, rowPos As Int) As Int
        return the bottom border style for cell @ row
      • PoiGetBorderLeft (ws As PoiSheet, colAlpha As String, rowPos As Int) As Int
        return the left border style for cell @ row
      • PoiGetBorderRight (ws As PoiSheet, colAlpha As String, rowPos As Int) As Int
        return the right border style for cell @ row
      • PoiGetBorderTop (ws As PoiSheet, colAlpha As String, rowPos As Int) As Int
        return the top border style for cell @ row
      • PoiGetCell (cell As PoiCell) As String
        return the cell value
      • PoiGetCellHorizontalAlignment (ws As PoiSheet, colAlpha As String, rowPos As Int) As Int
        return horizontal alignment for cell @ row
      • PoiGetCellRotation (ws As PoiSheet, colAlpha As String, rowPos As Int) As Int
        return the cell rotation for cell @ row
      • PoiGetCellRowValue (ws As PoiSheet, colAlpha As String, rowPos As Int) As String
        return the cell row value
      • PoiGetCellShrinkToFit (ws As PoiSheet, colAlpha As String, rowPos As Int) As Boolean
        return the shrink to fit for cell @ row
      • PoiGetCellStyle (ws As PoiSheet, rowPos As Int, colAlpha As String) As PoiCellStyle
        return the cell style for cell @ row
      • PoiGetCellValue (row As PoiRow, colAlpha As String) As String
        return the cell value at RC
      • PoiGetCellVerticalAlignment (ws As PoiSheet, colAlpha As String, rowPos As Int) As Int
        return the vertical alignment for cell @ row
      • PoiGetColumnPos (colAlpha As String) As Int
        return the column position from (a > zz)
      • PoiGetColumnWidth (ws As PoiSheet, colalpha As String) As Double
        return the column width
      • PoiGetHeader (ws As PoiSheet) As JavaObject
        get the header of the worksheet
      • PoiGetIsColumnHidden (ws As PoiSheet, colalpha As String) As Boolean
        return column visibility for column
      • PoiGetMargin (Sheet As PoiSheet, Margin As Short) As Double
        Margin: 0 = Left, 1 = Right, 2 = Top, 3 = Bottom, 4 = Header, 5 = Footer
      • PoiGetPoiCell (ws As PoiSheet, rowPos As Int, colAlpha As String) As PoiCell
        get poi cell, use 1 index
      • PoiGetPoiRowPoiCell (row As PoiRow, colAlpha As String) As PoiCell
        return the PoiCell at PoiRow and colulmn
      • PoiGetRow (ws As PoiSheet, rowPos As Int) As PoiRow
        return the row at index
      • PoiGetRowCellValue (ws As PoiSheet, rowpos As Int, colAlpha As String) As String
        return the row cell value
      • PoiGetRows (ws As PoiSheet) As List
        return a list of excel rows as maps, each records key is the column name
        this reads all rows and columns in the excel sheet
      • PoiGetWorksheet (wb As PoiWorkbook, sheetName As String) As PoiSheet
        return a PoiSheet using the name
      • PoiGetWorkSheetIndex (wb As PoiWorkbook, sheetName As String) As Int
        return the index of the worksheet, -1 does not exist
      • PoiGetWrapText (ws As PoiSheet, colAlpha As String, rowPos As Int) As Boolean
        return the wrap text for cell @ row
      • PoiHideColumn (Sheet As PoiSheet, colPos As Int, bHide As Boolean) As String
        hide a particular column
      • PoiOpenFile (dir As String, fil As String, pwd As String) As PoiWorkbook
        open an existing excel file
      • PoiPhysicalRowsCount (ws As PoiSheet) As Int
        return number of physical rows
      • PoiProtectWorkSheet (wsData As PoiWorkbook, pwd As String) As String
        protect the worksheet
      • PoiRemoveWorkSheet (wb As PoiWorkbook, sheetName As String) As String
        remove a worksheet
      • PoiSetColumnVisible (ws As PoiSheet, colalpha As String, bHide As Boolean) As String
        set column visibility
      • PoiSetHeader (ws As PoiSheet, heading As String) As String
        set the header of your worksheet
      • PoiSetHeading (Bold As Boolean, Color As Paint, FontName As String, FontSize As Int, UnderLine As Boolean) As ReportHeading
        set the report heading style
      • PoiSetMargin (Sheet As PoiSheet, Margin As Short, Size As Double) As String
        Margin: 0 = Left, 1 = Right, 2 = Top, 3 = Bottom, 4 = Header, 5 = Footer
      • PoiSetRowCellStyle (ws As PoiSheet, rowPos As Int, colAlpha As String, style As PoiCellStyle) As String
        set the style of the rowcell using 1 index
      • PoiSetRowStyle (row As PoiRow, style As PoiCellStyle) As String
        set the style of the row: ONLY existing row
      • PoiShiftRows (Sheet As PoiSheet, StartRow As Int, EndRow As Int, DirectionAmt As Int) As String
        shift rows up or down
      • PoiWorkSheetExists (wb As PoiWorkbook, sheetName As String) As Boolean
        return worksheet existance using the name
      • Process_Globals As String
  • ReportHeading
    • Fields:
      • Bold As Boolean
      • Color As Paint
      • FontName As String
      • FontSize As Int
      • IsInitialized As Boolean
        Tests whether the object has been initialized.
      • UnderLine As Boolean
    • Functions:
      • Initialize
        Initializes the fields to their default value.
 

Mashiane

Expert
Licensed User
Example 1 of this post is about importing data from an excel spreadsheet into your database. This example is about exporting database records to an existing excel template.

Template.png


What we will do here is to execute a couple of lines against MashPOI to export database records to this template.

Things to note from the above template...

1. The starting row to export records to is 3
2. The starting column to export records to is 1
3. By default, the first worksheet is used to export records to.
4. Create your template and put it in File.DirApp

Here is the code

B4X:
'initialize the MashPOI library
MashPOI.Initialize
'prepare the reporting template
MashPOI.PrepareExistingReport("Human Resources",File.DirApp,"humanresources.xls",3,1,False,False,"",False)'
'for each of the columns from the db table to use, add them
MashPOI.AddReportColumn("fullname","Full Name",False,"",MashPOI.ExcelColumnType.xlString,True)
'get the records from the database
Dim Records as list = dbutils.ExecuteMaps(jSQL,"select * from people order by people",Null)
'Execute the report
Dim bReport As Boolean = MashPOI.ExecuteReport(File.DirApp,"Human Resources",Records)
The PrepareExistingReport method is useful to perform a little bit of things. You specify the template folder and file, the start row and start column, whether the worksheet needs to be protected and what is the password to use. Using a timestamp will add a time stamp to your file.

Each column has a type defined as...

B4X:
Type ExcelColumn(FieldName As String, Heading As String, ColumnType As String,HasFormulae As Boolean,DefaultValue As String,border As Boolean)
The fieldname as it appears in the database or map, the Heading (can be left blank as we are using a template), the column-type (see below the column types), whether the column content is a formulae, the column default value (used by .GetDefault) and whether the column has a border around.

The column types are...

B4X:
Type ExcelColumnTypeObj(xlString As String, xlNumeric As String, xlFormula As String, xlBoolean As String, xlDate As String)
and this results with..

B4X:
ExcelColumnType.Initialize
    ExcelColumnType.xlBoolean = "Boolean"
    ExcelColumnType.xlDate = "Date"
    ExcelColumnType.xlFormula = "Formula"
    ExcelColumnType.xlNumeric = "Numeric"
    ExcelColumnType.xlString = "String"
After all the columns are added, its time to call the method to execute the export, ExecuteReport. With this you need to pass the Folder and File to export to and the list of map records from your db.

Ta!
 

keirS

Well-Known Member
Licensed User
@Mashiane a possibly useful sub to add to your library. It takes a numeric column reference as a parameter and returns the letter reference. So pass it column 50 for example and it returns "AX".


B4X:
Sub PoiGetAlphaColumnRef(ColumnnNumericRef As Int)
Dim SB  As  StringBuilder
Dim JOSB As JavaObject
Dim ColumnNo As Int = ColumnnNumericRef - 1
SB.Initialize
Do While ColumnNo >= 0
   
    Dim ColumnLetterChar As Int = 65 + (ColumnNo Mod 26)
    SB.append(Chr(ColumnLetterChar))
    ColumnNo = (ColumnNo / 26) 
    ColumnNo = ColumnNo - 1
Loop
 JOSB = SB
 Log(JOSB.RunMethodJO("reverse",Null).RunMethod("toString",Null))       
End Sub
 

Mashiane

Expert
Licensed User
Thanks for int input @keirS, its already implemented both ways, however it does not use javaobject and kinda long... ;)

B4X:
'return the column position from (a > zz)
Sub PoiGetColumnPos(colAlpha As String) As Int
    Dim alphalist1 As List = CreateList(",","a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z")
    Dim alphalist2 As List = CreateList(",","a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z")
    Dim colmap As Map
    colmap.Initialize
    Dim l1Tot As Int = alphalist1.Size - 1
    Dim l2Tot As Int = alphalist2.Size - 1
    Dim l1cnt As Int
    Dim l2cnt As Int
    Dim colpos As Int = 0
    
    'first alphabets
    For l1cnt = 0 To l1Tot
        colpos = colpos + 1
        colmap.Put(alphalist1.Get(l1cnt),colpos)
    Next
    'rest of the alphabets
    For l1cnt = 0 To l1Tot
        For l2cnt = 0 To l2Tot
            colpos = colpos + 1
            Dim strKey As String = $"${alphalist1.Get(l1cnt)}${alphalist2.Get(l2cnt)}"$
            colmap.Put(strKey,colpos)
        Next
    Next
    Dim xpos As Int = colmap.getdefault(colAlpha.tolowercase,0)
    Return xpos
End Sub
 

keirS

Well-Known Member
Licensed User
Thanks for int input @keirS, its already implemented both ways, however it does not use javaobject and kinda long... ;)

B4X:
'return the column position from (a > zz)
Sub PoiGetColumnPos(colAlpha As String) As Int
    Dim alphalist1 As List = CreateList(",","a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z")
    Dim alphalist2 As List = CreateList(",","a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z")
    Dim colmap As Map
    colmap.Initialize
    Dim l1Tot As Int = alphalist1.Size - 1
    Dim l2Tot As Int = alphalist2.Size - 1
    Dim l1cnt As Int
    Dim l2cnt As Int
    Dim colpos As Int = 0
   
    'first alphabets
    For l1cnt = 0 To l1Tot
        colpos = colpos + 1
        colmap.Put(alphalist1.Get(l1cnt),colpos)
    Next
    'rest of the alphabets
    For l1cnt = 0 To l1Tot
        For l2cnt = 0 To l2Tot
            colpos = colpos + 1
            Dim strKey As String = $"${alphalist1.Get(l1cnt)}${alphalist2.Get(l2cnt)}"$
            colmap.Put(strKey,colpos)
        Next
    Next
    Dim xpos As Int = colmap.getdefault(colAlpha.tolowercase,0)
    Return xpos
End Sub
You can make that a lot shorter
B4X:
Sub PoiGetColumnPos(colAlpha As String) As Int
    Dim NumericColumn As Int= 0
    For cntr = 0 To colAlpha.Length - 1
        NumericColumn = (NumericColumn * 26)  + (Asc(colAlpha.SubString2(cntr,cntr+1)) - 64)
    Next
    Return NumericColumn
End Sub
 
Top