B4J Question jpoi : Using different cellstyle

Discussion in 'B4J Questions' started by thierry6031, Jan 9, 2019.

  1. thierry6031

    thierry6031 Member Licensed User

    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
    Code:
    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(
    0256 * 30)
        sheet1.AddMergedRegion(
    1020)
        sheet1.AddMergedRegion(
    3040)
        sheet1.AddMergedRegion(
    5060)
        sheet1.AddMergedRegion(
    7080)
        
        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(
    1640)
        sheet1.SetColumnWidth(
    3640)
        sheet1.SetColumnWidth(
    5640)
        sheet1.SetColumnWidth(
    7640)
        
        sheet1.SetColumnWidth(
    21592)
        sheet1.SetColumnWidth(
    41592)
        sheet1.SetColumnWidth(
    61592)
        sheet1.SetColumnWidth(
    81592)
        
        
    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

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

    thierry6031 Member Licensed User

    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
     

    Attached Files:

  4. Erel

    Erel Administrator Staff Member Licensed User

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

    thierry6031 Member Licensed User

    Thanks Erel, I'll give it a try

    Thierry
     
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