B4J Library MashPOI - Import & Export 2 Excel

Discussion in 'B4J Libraries & Classes' started by Mashiane, Apr 1, 2018.

  1. Mashiane

    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.

    Code:
    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
     

    Attached Files:

    Last edited: Apr 2, 2018
  2. Mashiane

    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.
     
    Don Oso, BPak, joulongleu and 2 others like this.
  3. Mashiane

    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

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

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

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

    Code:
    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!
     
    rboeck and Johan Hormaza like this.
  4. keirS

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


    Code:
    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 and inakigarm like this.
  5. Mashiane

    Mashiane Expert Licensed User

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

    Code:
    'return the column position from (a > zz)
    Sub PoiGetColumnPos(colAlpha As StringAs 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
     
    Johan Hormaza likes this.
  6. keirS

    keirS Well-Known Member Licensed User

    You can make that a lot shorter
    Code:
    Sub PoiGetColumnPos(colAlpha As StringAs 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
     
    rboeck and Mashiane like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice