B4J Question Write object value to Excel file

[bdunkleysmith]

I have a project which has a Tableview (tv1) which I originally populated this way

        If typ = "sub" Then 'Collect only substitution events
            Dim row(11) As Object
            row(0) = x
            row(1) = "Q" & Period & " " & gt           
            If sf.instring(desc, "'") > 0 Then 'Player name has apostrophe
                desc = sf.Stuff(desc, "'", "'")
            End If           
            row(2) = desc
            row(3) = s1 & ":" & s2
            row(4) = Lead
            If FinalPeriod < 5 Then 'Game time remaining in decimal minutes
                'Game has not gone into OT
                If sf.Right(gt,2) <> "00" Then 'Need to add decimal point if exact minute time
                    row(5) = NumberFormat((FinalPeriod - Period) * 10 + sf.Left(gt,2) + sf.Right(gt,2)/60,1,2)
                Else
                    row(5) = NumberFormat((FinalPeriod - Period) * 10 + sf.Left(gt,2) + sf.Right(gt,2)/60,1,2) & ".0"
                End If
            Else
                'Game has gone into OT
                If Period < 5 Then 'But we are collecting events from the first four quarters
                    If sf.Right(gt,2) <> "00" Then 'Need to add decimal point if exact minute time
                        row(5) = NumberFormat((4 - Period) * 10 + (FinalPeriod - 4) * 5 + sf.Left(gt,2) + sf.Right(gt,2)/60,1,2)
                    Else
                        row(5) = NumberFormat((4 - Period) * 10 + (FinalPeriod - 4) * 5 + sf.Left(gt,2) + sf.Right(gt,2)/60,1,2) & ".0"
                    End If   
                Else 'Or we are collectng events from OT
                    If sf.Right(gt,2) <> "00" Then 'Need to add decimal point if exact minute time
                        row(5) = NumberFormat((FinalPeriod - 5) * 5 + sf.Left(gt,2) + sf.Right(gt,2)/60,1,2)
                    Else
                        row(5) = NumberFormat((FinalPeriod - 5) * 5 + sf.Left(gt,2) + sf.Right(gt,2)/60,1,2) & ".0"
                    End If
                End If
            End If
            row(6) = ""
            row(7) = ""
            row(8) = ""   
            row(9) = ""
            row(10) = ""
            'Collect events for selected team
            If (HButton.Selected = True AND tno = "1") OR (AButton.Selected = True AND tno = "2") Then
                tv1.Items.Add(row)
                x = x + 1
            End If
        End If

But I wanted to format the alignment of the columns and so used this method

    For Each colpbp As Map In pbp
        s2  = colpbp.Get("s2")
        desc = colpbp.Get("desc")
        If sf.instring(desc, "'") > 0 Then 'Player name has apostrophe
            desc = sf.Stuff(desc, "'", "'")
        End If       
        s1 = colpbp.Get("s1")
        tno = colpbp.Get("tno")
        lead = colpbp.Get("lead")
        gt = colpbp.Get("gt")
        typ = colpbp.Get("typ")
        period = colpbp.Get("period")
        Scoring = colpbp.Get("scoring")       
        Dim lbl1, lbl2, lbl3, lbl4, lbl5 As Label
        lbl1.Initialize("")
        lbl2.Initialize("")
        lbl3.Initialize("")
        lbl4.Initialize("")
        lbl5.Initialize("")       
        lbl1.Text = "Q" & period & " " & gt
        lbl3.Text = s1 & ":" & s2
        lbl5.Text = lead
        If tno = "1" Then           
            lbl2.Text = desc
        Else
            lbl4.Text = desc
        End If
        If (desc.Contains("made") OR desc.Contains("dunk") OR desc.Contains("tip in")) AND ScoringOnlyButton.Selected = True Then 'Collect only scoring (made) events
            If AllButton.Selected = True Then 'All Quarters button selected & so collect events
                tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
            Else
                If Q1Button.Selected = True AND period = "1" Then 'Q1 button selected & so collect only period 1 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
                If Q2Button.Selected = True AND period = "2" Then 'Q2 button selected & so collect only period 2 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
                If Q3Button.Selected = True AND period = "3" Then 'Q3 button selected & so collect only period 3 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
                If Q4Button.Selected = True AND period = "4" Then 'Q4 button selected & so collect only period 4 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
            End If
        End If
        If ScoringOnlyButton.Selected = False Then 'Collect all events
            If AllButton.Selected = True Then 'All Quarters button selected & so collect events
                tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
            Else
                If Q1Button.Selected = True AND period = "1" Then 'Q1 button selected & so collect only period 1 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
                If Q2Button.Selected = True AND period = "2" Then 'Q2 button selected & so collect only period 2 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
                If Q3Button.Selected = True AND period = "3" Then 'Q3 button selected & so collect only period 3 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
                If Q4Button.Selected = True AND period = "4" Then 'Q4 button selected & so collect only period 4 events
                    'tv1.Items.Add(row)
                    tv1.Items.Add(Array As Object(WrapLabel(lbl1, "CENTER_LEFT") , WrapLabel(lbl2, "CENTER_LEFT") , WrapLabel(lbl3, "CENTER"), WrapLabel(lbl4, "CENTER_LEFT"), WrapLabel(lbl5, "CENTER")))
                End If
            End If
        End If
    Next


Sub WrapLabel(lbl As Label, Alignment As String) As Pane
   'For alignment of tbleview cells
   Dim pn1 As AnchorPane
   pn1.Initialize("")
   pn1.AddNode(lbl, 0, 0, -1, -1)
   pn1.FillHorizontally(lbl, 0, 0)
   Dim jo1 = lbl As JavaObject
   jo1.RunMethod("setAlignment", Array As Object(Alignment))
   Return pn1
End Sub

However now the code I used to write the Tableview data to an Excel file

    For Each row() As Object In tbl.Items
        colCounter = 0
        For Each value As Object In row
            Dim cell As WritableCell
            cell.InitializeText(colCounter, rowCounter + 1, value)
            cell.SetCellFormat(rowsFormat)
            sheet1.AddCell(cell)
            colCounter = colCounter + 1
        Next
        rowCounter = rowCounter + 1
    Next
    'Must call write and close to save the data.
    newWorkbook.Write

writes out the objects, eg.

AnchorPane@1d4baa6 AnchorPane@1320451 AnchorPane@39dc15 AnchorPane@10bdfd8 AnchorPane@795e23
AnchorPane@1e51ada AnchorPane@b3ed3 AnchorPane@23f8d AnchorPane@1ba466e AnchorPane@19dbdfe
AnchorPane@b935ed AnchorPane@125ae79 AnchorPane@9e3a7c AnchorPane@1687737 AnchorPane@7689ec

rather than the actual values shown in the Tableview.

How do I change the code to write out the value of the object in the Tableview cell?

 

[Erel]

Try this:

For Each value As Pane In row
 DIm lbl As Label = value.GetNode(0)
 ...
  cell.InitializeText(colCounter, rowCounter + 1, lbl.Text)

 

[bdunkleysmith]

Thanks Erel, your support is amazing. That works OK. So I can use a common Excel export subroutine I just need to test whether the Tableview contains just text or the panes, which I've initially done in a crude way! Thanks again.