B4J Question Problems with jPOI library

[strupp01]

Try the Libraray jPOI all day long. But do not come to a satisfactory result. I do not find examples with different fonts and font heights.
I would like to do the following:
It is an excel spreadsheet with about 60 lines needed, which makes each line a different font, font height and possibly line height required. The contents of the cells (4) are also different per line. Example:

grün vorne hoch teilen
blau hinten niedrig versenden
rot hinten hoch halten

Does anyone have an example that reflects these functions approximately or can me with some lines represent this ?

 

[Erel]

The simplest way to do it is by creating a template with the styles already set and then load it, fill the cells and save it to a different workbook.

 

[strupp01]

I once wrote a mini-program.
It generates an Excel file "1.xlsx" with the data in rows and columns. The line height and line width are also converted correctly.
What does not work is the font and font height. I would like to Use the font "Arial" for lines 1 and 3 and use "Courier" for line 2.
What am I doing wrong ?

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
   
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
       

    Dim wb As PoiWorkbook
    wb.InitializeNew(True) 'create new xlsx workbook

    Dim sheet1 As PoiSheet = wb.AddSheet("Aufmaß", 0)
   
    sheet1.SetColumnWidth(0, 256 * 61)
    sheet1.SetColumnWidth(1, 256 * 6)
    sheet1.SetColumnWidth(2, 256 * 5)
    sheet1.SetColumnWidth(3, 256 * 15)


    Dim font As PoiFont
    font.Initialize(wb)
    font.Bold = True
    font.Underline = False
    font.Name = "Arial"
    font.Size = 10
    font.SetColor(fx.Colors.Black)


    Dim cs As PoiCellStyle
    cs.Initialize(wb)
    cs.HorizontalAlignment = cs.HORIZONTAL_CENTER
    cs.SetFont(font)


    'Zeile1
    Dim row As PoiRow = sheet1.CreateRow(0)
    row.RowStyle = cs    'Schriftart und Schriftgröße
    row.Height = 20    'zeilenhöhe
   
    row.CreateCellString(0, "grün")
   
    row.CreateCellString(1, "vorne")

    row.CreateCellString(2, "hoch")

    row.CreateCellString(3, "teilen")

   
    'Zeile2
    Dim row As PoiRow = sheet1.CreateRow(1)
   
    row.Height = 30    'zeilenhöhe
    row.CreateCellString(0, "blau")
   
    row.CreateCellString(1, "hinten")

    row.CreateCellString(2, "niedrig")

    row.CreateCellString(3, "versenden")


    'Zeile3
    Dim row As PoiRow = sheet1.CreateRow(2)
   
    row.Height = 20    'zeilenhöhe
    row.CreateCellString(0, "rot")
   
    row.CreateCellString(1, "hinten")

    row.CreateCellString(2, "hoch")

    row.CreateCellString(3, "halten")

   
    wb.Save(File.DirApp, "1.xlsx")
    wb.Close

    MainForm.Close

End Sub

 

[strupp01]

I tried further and found a solution.
I like to accept improvements, as I believe that there is a better solution.

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
    

    Dim wb As PoiWorkbook
    wb.InitializeNew(True) 'create new xlsx workbook

    Dim sheet1 As PoiSheet = wb.AddSheet("Aufmaß", 0)
    
    sheet1.SetColumnWidth(0, 256 * 61)
    sheet1.SetColumnWidth(1, 256 * 6)
    sheet1.SetColumnWidth(2, 256 * 5)
    sheet1.SetColumnWidth(3, 256 * 15)
    
    Dim font As PoiFont
    font.Initialize(wb)
    font.Bold = True
    font.Underline = False
'    font.Name = "Palatino Linotype"
    font.Name = "Arial"
    font.Size = 10
    font.SetColor(fx.Colors.Black)

    Dim font1 As PoiFont
    font1.Initialize(wb)
    font1.Bold = True
    font1.Underline = False
'    font.Name = "Palatino Linotype"
    font1.Name = "Courier"
    font1.Size = 15
    font1.SetColor(fx.Colors.Black)

    Dim font2 As PoiFont
    font2.Initialize(wb)
    font2.Bold = True
    font2.Underline = False
'    font.Name = "Palatino Linotype"
    font2.Name = "Arial"
    font2.Size = 20
    font2.SetColor(fx.Colors.Black)

    Dim pc As PoiCellStyle
    pc.Initialize(wb)
    pc.SetFont(font)

    Dim pc1 As PoiCellStyle
    pc1.Initialize(wb)
    pc1.SetFont(font1)

    Dim pc2 As PoiCellStyle
    pc2.Initialize(wb)
    pc2.SetFont(font2)
    
    'Zeile1
    Dim row As PoiRow = sheet1.CreateRow(0)
    row.Height = 20    'zeilenhöhe
    pc.SetFont(font)

    
    row.CreateCellString(0, "grün")
    row.CreateCellString(1, "vorne")

    row.CreateCellString(2, "hoch")

    row.CreateCellString(3, "teilen")
    
    SetStyleToRowCells(row, pc)

    
    'Zeile2
    Dim row As PoiRow = sheet1.CreateRow(1)
    
    row.Height = 20    'zeilenhöhe
    pc1.SetFont(font1)

    row.CreateCellString(0, "blau")
    
    row.CreateCellString(1, "hinten")

    row.CreateCellString(2, "niedrig")

    row.CreateCellString(3, "versenden")
    SetStyleToRowCells(row, pc1)


    'Zeile3
    Dim row As PoiRow = sheet1.CreateRow(2)
    
    row.Height = 20    'zeilenhöhe
    pc2.SetFont(font2)

    row.CreateCellString(0, "rot")
    
    row.CreateCellString(1, "hinten")

    row.CreateCellString(2, "hoch")

    row.CreateCellString(3, "halten")
    SetStyleToRowCells(row, pc2)

    
    wb.Save(File.DirApp, "1.xlsx")
    wb.Close

    MainForm.Close

End Sub

Private Sub SetStyleToRowCells(row1 As PoiRow, style1 As PoiCellStyle)
    For Each cell As PoiCell In row1.Cells
        cell.CellStyle = style1
    Next
End Sub