B4J Tutorial jPOI Library - Automatic column width.

Discussion in 'B4J Tutorials' started by mark35at, Sep 10, 2019.

Tags:
  1. mark35at

    mark35at Well-Known Member Licensed User

    I was trying to get to grips with setting the column width in my Excel table. According to the help I tried:

    number of characters x 256, but all my columns dissappeared!

    upload_2019-9-10_13-37-13.png

    Setting a value of 2560 gives a width in Excel of 9.29 or 70 Pixels, enough for 9 characters. 2560/9.29=275 which I rounded to 285

    I finally got this code which will adjust according to the length of the data.
    It may not be pretty but it works!

    Hope it helps someone.

    Code:
    'Put the values from Data into a new Excel worksheet
    Sub SendDataToExcel
        
    Dim wb As PoiWorkbook
        wb.InitializeNew(
    True'create new xlsx workbook
        Dim sheet1 As PoiSheet = wb.AddSheet("Flug Buch"0)
        
    Dim Columnwidth(23),Cw As Int
      
        
    For i=0 To CurrentLine
            
    Dim row As PoiRow = sheet1.CreateRow(i)
          
            
    For j=0 To 20
                row.CreateCellString(j,data(i,j))
                Cw=(data(i,j).Length+
    1)*285                        '<= Calculated column width based on length of string
                If Cw>Columnwidth(j) Then Columnwidth(j)=Cw        'store new column width if it is higher
                sheet1.SetColumnWidth(j,Columnwidth(j))            'Set new width
            Next
        
    Next
        
        ExcelFilename=txtExcelFile.Text
              
        wb.Save(lblDirectory.Text , ExcelFilename)
        wb.Close
      
        
    If File.Exists(lblDirectory.Text , ExcelFilename) Then
            fx.Msgbox(MainForm, 
    "Excel file has been created""Done!")
        
    Else
            fx.Msgbox(MainForm, 
    "Please run 'run_debug.bat and send the error log to developer'""ERROR")
        
    End If
      
    End Sub
     
    Mashiane and Erel 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