Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Come back after working on another project and interesting things have happened. First of all I want to thank everyone who participated in answering this question, especially pierre-yves samyn who's answer I went with. This was my first time posting a question to a forum, I'm normally able to find answers to whatever questions I have by googling, thanks again for your patience with me. What follows is just a bit of code to setup an example table, skip below it to find my take on pierre's answer.

load code into empty spreadsheet

sub createTable
    sheet = thiscomponent.sheets(0)

    'load some data
    data = array(array("Team","BOS","CHI","HOU","KC"),array("Boston","","CHI","HOU(1)","KC"),_
                 array("Chicago","CHI","","CHI","Tied"),array("Houston","HOU(1)","CHI","","Tied(1)"),_
                 array("Kansas City","KC","Tied","Tied(1)",""))
    sheet.getCellRangeByName("A1:E5").DataArray = data

    'make some ranges
    refPos = sheet.getCellRangeByName("A1").cellAddress
    addrs = sheet.getCellRangeByName("B2:E5").absoluteName
    if sheet.NamedRanges.hasByName("TeamTable") then sheet.NamedRanges.removeByName("TeamTable")
    sheet.NamedRanges.addNewByName("TeamTable", addrs, refPos, 0)
    addrs = sheet.getCellRangeByName("B1:E1").absoluteName
    if sheet.NamedRanges.hasByName("Teams") then sheet.NamedRanges.removeByName("Teams")
    sheet.NamedRanges.addNewByName("Teams", addrs, refPos, 0)

    'load some crosses
    border = createUnoStruct("com.sun.star.table.BorderLine")
    border.Color = RGB(0,0,128)     'blue
    border.OuterLineWidth = 26      '26=.75pt, 35=1.00pt
    table = sheet.getCellRangeByName("TeamTable")
    for pos = 0 to table.Rows.Count-1
        cell = table.getCellByPosition(pos, pos)
        cell.DiagonalTLBR = border : cell.DiagonalBLTR = border
    next

    'prettify stuff
    table.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    sheet.getCellRangeByName("Teams").HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    sheet.getCellRangeByName("A1:E1").CharWeight = com.sun.star.awt.FontWeight.BOLD

    'make some styles
    cellStyles = thiscomponent.StyleFamilies.getByName("CellStyles")
    styles = array(array("Positive", RGB(0,153,0)), array("Tied", RGB(128,0,128)),_
                   array("Negative", RGB(255,0,0)))
    for each style in styles
        if cellStyles.hasByName(style(0)) then cellStyles.removeByName(style(0))
        cs = thiscomponent.createInstance("com.sun.star.style.CellStyle")
        cellStyles.insertByName(style(0), cs) : cellStyle = cellStyles.getByName(style(0))
        cellStyle.ParentStyle = "Default" : cellStyle.CharColor = style(1)
    next
end sub

What follows is, largely, the code I implemented. This code creates mutliple conditions on a range.

sub formatTable
    'get stuff you just made
    sheet = ThisComponent.Sheets(0)
    table = sheet.getCellRangeByName("TeamTable") : teams = sheet.getCellRangeByName("Teams")
    tableColumns = table.Columns.Count-1    'columns rows doesn't matter, table is square

    dim empty() as new com.sun.star.beans.PropertyValue 'create an empty property
    for r = 0 to tableColumns
        row = table.getCellRangeByPosition(0,r,tableColumns,r)  'get row of table

        'creates a conditional range format and an empty entry format for the row range, 
        'clear is optional, it's just there so that this sub can run multiple times
        cf = row.ConditionalFormat : cf.clear : cf.addNew(empty) : row.ConditionalFormat = cf

        'get sheet conditional range format you just made, formats are added to a list, 
        'most recent format is at the bottom of the list. if you're adding a condition entry 
        'to an existing range you'll have to use pierre's absolute name range thing
        i = sheet.ConditionalFormats.Length-1
        cfs = sheet.ConditionalFormats.ConditionalFormats(i)

        'get the condition entry you just made and load your stuff
        cfe = cfs.getByIndex(cfs.Count-1)
        cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
        cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
        cfe.setPropertyValue("StyleName", "Positive")

        'create another entry, no need to re-get range since you haven't changed it
        cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
        cfe = cfs.getByIndex(cfs.Count-1)
        cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
        cfe.setPropertyValue("Formula1", """Tied""")
        cfe.setPropertyValue("StyleName", "Tied")

        'again only different
        cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
        cfe = cfs.getByIndex(cfs.Count-1)
        cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.NOT_CONTAINS)
        cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
        cfe.setPropertyValue("StyleName", "Negative")
    next
end sub

I found methods createByRange and createEntry in the ConditionalFormats, but was unable to made them work, so went with the old addNew method to create my formats, perhaps someone will come by and show us how it's supposed to be done. Anyway, I'm marking this question SOLVED, thanks again you all.

Come back after working on another project and interesting things have happened. First of all I want to thank everyone who participated in answering this question, especially pierre-yves samyn who's answer I went with. This was my first time posting a question to a forum, I'm normally able to find answers to whatever questions I have by googling, thanks again for your patience with me. What follows is just a bit of code to setup an example table, skip below it to find my take on pierre's answer.

load code into empty spreadsheet

sub createTable
    sheet = thiscomponent.sheets(0)

    'load some data
    data = array(array("Team","BOS","CHI","HOU","KC"),array("Boston","","CHI","HOU(1)","KC"),_
                 array("Chicago","CHI","","CHI","Tied"),array("Houston","HOU(1)","CHI","","Tied(1)"),_
                 array("Kansas City","KC","Tied","Tied(1)",""))
    sheet.getCellRangeByName("A1:E5").DataArray = data

    'make some ranges
    refPos = sheet.getCellRangeByName("A1").cellAddress
    addrs = sheet.getCellRangeByName("B2:E5").absoluteName
    if sheet.NamedRanges.hasByName("TeamTable") then sheet.NamedRanges.removeByName("TeamTable")
    sheet.NamedRanges.addNewByName("TeamTable", addrs, refPos, 0)
    addrs = sheet.getCellRangeByName("B1:E1").absoluteName
    if sheet.NamedRanges.hasByName("Teams") then sheet.NamedRanges.removeByName("Teams")
    sheet.NamedRanges.addNewByName("Teams", addrs, refPos, 0)

    'load some crosses
    border = createUnoStruct("com.sun.star.table.BorderLine")
    border.Color = RGB(0,0,128)     'blue
    border.OuterLineWidth = 26      '26=.75pt, 35=1.00pt
    table = sheet.getCellRangeByName("TeamTable")
    for pos = 0 to table.Rows.Count-1
        cell = table.getCellByPosition(pos, pos)
        cell.DiagonalTLBR = border : cell.DiagonalBLTR = border
    next

    'prettify stuff
    table.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    sheet.getCellRangeByName("Teams").HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    sheet.getCellRangeByName("A1:E1").CharWeight = com.sun.star.awt.FontWeight.BOLD

    'make some styles
    cellStyles = thiscomponent.StyleFamilies.getByName("CellStyles")
    styles = array(array("Positive", RGB(0,153,0)), array("Tied", RGB(128,0,128)),_
                   array("Negative", RGB(255,0,0)))
    for each style in styles
        if cellStyles.hasByName(style(0)) then cellStyles.removeByName(style(0))
        cs = thiscomponent.createInstance("com.sun.star.style.CellStyle")
        cellStyles.insertByName(style(0), cs) : cellStyle = cellStyles.getByName(style(0))
        cellStyle.ParentStyle = "Default" : cellStyle.CharColor = style(1)
    next
end sub

What follows is, largely, the code I implemented. This code creates mutliple conditions on a range.

sub formatTable
    'get stuff you just made
    sheet = ThisComponent.Sheets(0)
    table = sheet.getCellRangeByName("TeamTable") : teams = sheet.getCellRangeByName("Teams")
    tableColumns = table.Columns.Count-1    'columns rows doesn't matter, table is square

    dim empty() as new com.sun.star.beans.PropertyValue 'create an empty property
    for r = 0 to tableColumns
        row = table.getCellRangeByPosition(0,r,tableColumns,r)  'get row of table

        'creates a conditional range format and an empty entry format for the row range, 
        'clear is optional, it's just there so that this sub can run multiple times
        cf = row.ConditionalFormat : cf.clear : cf.addNew(empty) : row.ConditionalFormat = cf

        'get sheet conditional range format you just made, formats are added to a list, 
        'most recent format is at the bottom of the list. if you're adding a condition entry 
        'to an existing range you'll have to use pierre's absolute name range thing
        i = sheet.ConditionalFormats.Length-1
        cfs = sheet.ConditionalFormats.ConditionalFormats(i)

        'get the condition entry you just made and load your stuff
        cfe = cfs.getByIndex(cfs.Count-1)
        cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
        cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
        cfe.setPropertyValue("StyleName", "Positive")

        'create another entry, no need to re-get range since you haven't changed it
        cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
        cfe = cfs.getByIndex(cfs.Count-1)
        cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
        cfe.setPropertyValue("Formula1", """Tied""")
        cfe.setPropertyValue("StyleName", "Tied")

        'again only different
        cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
        cfe = cfs.getByIndex(cfs.Count-1)
        cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.NOT_CONTAINS)
        cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
        cfe.setPropertyValue("StyleName", "Negative")
    next
end sub

I found methods createByRange and createEntry in the ConditionalFormats, but was unable to made them work, so I went with the old addNew method to create my formats, perhaps someone will come by and show us how it's supposed to be done. Anyway, I'm marking this question SOLVED, thanks again you all.