B4J Tutorial jPOI Library - Automatic column width.

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

  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!


    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.

    'Put the values from Data into a new Excel worksheet
    Sub SendDataToExcel
    Dim wb As PoiWorkbook
    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
    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
        wb.Save(lblDirectory.Text , ExcelFilename)
    If File.Exists(lblDirectory.Text , ExcelFilename) Then
    "Excel file has been created""Done!")
    "Please run 'run_debug.bat and send the error log to developer'""ERROR")
    End If
    End Sub
    Mashiane and Erel like this.
  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