B4J Library MashPOI - Import & Export 2 Excel

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.

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

 

[Mashiane]

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

MashPOI

Author: Anele Mashy Mbanga [email protected]
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]

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.

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

'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...

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...

Type ExcelColumnTypeObj(xlString As String, xlNumeric As String, xlFormula As String, xlBoolean As String, xlDate As String)

and this results with..

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]

@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".

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]

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

'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]

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

'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

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