B4J Question jpoi : Using different cellstyle

[thierry6031]

Hello,

I recently discovered the jpoi library, and that seems wonderfull.
I am facing a problem I really don't understand.

I need to create cells using different style

Sub Process_Globals
    Dim wb As PoiWorkbook
    Dim sheet1 As PoiSheet
    Dim Ter_number As String
    Dim Ter_Name As String
    Dim font As PoiFont
    Dim FirstRowStyle As PoiCellStyle
    Dim SecondRowStyle As PoiCellStyle
    Dim StandardStyle As PoiCellStyle
End Sub

Sub AppStart (Args() As String)
    wb.InitializeNew(True) 'create new xlsx workbook
    sheet1 = wb.AddSheet("Sheet 1", 0)
    InitializeFont(wb, "Arial", 8)
    InitializeFirstRowStyle(wb)
    InitializeSecondRowStyle(wb)
    InitializeStandardStyle(wb)
    createTitle(wb)
    ExitApplication
End Sub

Sub InitializeFirstRowStyle (CurrentWorkBook As PoiWorkbook)
    FirstRowStyle.Initialize(CurrentWorkBook)
    FirstRowStyle.SetFont(font)
    FirstRowStyle.BorderBottom = FirstRowStyle.BORDER_DOUBLE
    FirstRowStyle.BorderLeft = FirstRowStyle.BORDER_DOUBLE
    FirstRowStyle.BorderTop = FirstRowStyle.BORDER_DOUBLE
    FirstRowStyle.BorderRight = FirstRowStyle.BORDER_DOUBLE
    FirstRowStyle.HorizontalAlignment = FirstRowStyle.HORIZONTAL_CENTER
    FirstRowStyle.VerticalAlignment = FirstRowStyle.VERTICAL_CENTER
End Sub

Sub InitializeSecondRowStyle (CurrentWorkBook As PoiWorkbook)
    SecondRowStyle.Initialize(CurrentWorkBook)
    SecondRowStyle.SetFont(font)
    SecondRowStyle.BorderBottom = SecondRowStyle.BORDER_DOUBLE
    SecondRowStyle.BorderLeft = SecondRowStyle.BORDER_DOUBLE
    SecondRowStyle.BorderTop = SecondRowStyle.BORDER_DOUBLE
    SecondRowStyle.BorderRight = SecondRowStyle.BORDER_DOUBLE
    SecondRowStyle.HorizontalAlignment = SecondRowStyle.HORIZONTAL_CENTER
    SecondRowStyle.VerticalAlignment = SecondRowStyle.VERTICAL_CENTER
    SecondRowStyle.Rotation = 90
    SecondRowStyle.WrapText = True
End Sub

Sub InitializeStandardStyle (CurrentWorkBook As PoiWorkbook)
    StandardStyle.Initialize(CurrentWorkBook)
    StandardStyle.SetFont(font)
End Sub

private Sub CreateTitle ()
    Dim titleRow As PoiRow = sheet1.CreateRow(0)

    sheet1.SetColumnWidth(0, 256 * 30)
    sheet1.AddMergedRegion(1, 0, 2, 0)
    sheet1.AddMergedRegion(3, 0, 4, 0)
    sheet1.AddMergedRegion(5, 0, 6, 0)
    sheet1.AddMergedRegion(7, 0, 8, 0)
    
    titleRow.CreateCellString(0, "Text")
    titleRow.CreateCellString(1, "1")
    titleRow.CreateCellString(2, "")
    titleRow.CreateCellString(3, "2")
    titleRow.CreateCellString(4, "")
    titleRow.CreateCellString(5, "3")
    titleRow.CreateCellString(6, "")
    titleRow.CreateCellString(7, "4")
    titleRow.CreateCellString(8, "")
    sheet1.SetColumnWidth(1, 640)
    sheet1.SetColumnWidth(3, 640)
    sheet1.SetColumnWidth(5, 640)
    sheet1.SetColumnWidth(7, 640)
    
    sheet1.SetColumnWidth(2, 1592)
    sheet1.SetColumnWidth(4, 1592)
    sheet1.SetColumnWidth(6, 1592)
    sheet1.SetColumnWidth(8, 1592)
    
    Dim titleRow2 As PoiRow = sheet1.CreateRow(1)

    titleRow2.Height= 38.89
    titleRow2.CreateCellString(1, "Facut")
    titleRow2.CreateCellString(3, "Facut")
    titleRow2.CreateCellString(5, "Facut")
    titleRow2.CreateCellString(7, "Facut")
    titleRow2.CreateCellString(2, "Contact")
    titleRow2.CreateCellString(4, "Contact")
    titleRow2.CreateCellString(6, "Contact")
    titleRow2.CreateCellString(8, "Contact")
    
    Dim mycell As PoiCell
    For Each mycell As PoiCell In titleRow.Cells
        mycell.CellStyle = FirstRowStyle
    Next
    
    For Each mycell As PoiCell In titleRow2.Cells
        mycell.CellStyle = SecondRowStyle
    Next
End Sub

My problem is that any way I try to do it, I can't set the style to be different.
In the example above, I can't get the cells on row 2 to get a rotation angle of 90°
It seems only one cellstyle can exist.
What am I doing wrong?

Thanks
Thierry

 

[Erel]

Can you upload the project?

Note that the simplest way to manage the style is by loading an existing workbook where all the styles are already set.

 

[thierry6031]

Thanks Erel.
I noticed a post telling how to use an xls file as a template.
I'll give it a try.
But anyway, I'm curious.
I am uploading the B4J file, along with 2 xlxs files: the first one is the expected output, the second is the file produced by the project.
Thanks
Thierry

 

[Erel]

It is very simple. You are reusing the same style object multiple times.

Modifying the style of one of the cells is equivalent to modifying the style of all the cells that share the same style object.

Correct code:

1. Remove the global second row variable. It is not needed.

2.

Sub CreateSecondRowStyle (CurrentWorkBook As PoiWorkbook) As PoiCellStyle
   Dim s As PoiCellStyle
   s.Initialize(CurrentWorkBook)
   s.SetFont(font)
   s.BorderBottom = s.BORDER_DOUBLE
   s.BorderLeft = s.BORDER_DOUBLE
   s.BorderTop = s.BORDER_DOUBLE
   s.BorderRight = s.BORDER_DOUBLE
   s.HorizontalAlignment = s.HORIZONTAL_CENTER
   s.VerticalAlignment = s.VERTICAL_CENTER
   s.WrapText = True
   s.Rotation = 90
   Return s
End Sub


Dim SecondRowStyle As PoiCellStyle = CreateSecondRowStyle(wb)
For Each mycell As PoiCell In titleRow2.Cells
   mycell.CellStyle = SecondRowStyle
Next
titleRow2.GetCell(0).CellStyle = CreateSecondRowStyle(wb) 'create a unique style for the first cell
titleRow2.GetCell(0).CellStyle.Rotation = 0

 

[thierry6031]

Thanks Erel, I'll give it a try

Thierry