Working with styles

Display Style Names

Display style names and style names are sometimes different. Thus to get a list of 'display' style names:

function fnGetDisplayStyleNames(oDoc, sStyleFamily as string)
dim mApiNames, nStyles as integer, i as integer, oStyles as object
 
oStyles = oDoc.getStyleFamilies.getByName(sStyleFamily)
mApiNames = oStyles.getElementNames
nStyles = uBound(mApiNames)
dim mStyleNames(nStyles)
for i = 0 to nStyles
        mStyleNames(i) = oStyles.getByIndex(i).getPropertyValue("DisplayName")
next
subShellSort(mStyleNames())
fnGetDisplayStyleNames = mStyleNames()
end function

subShellSort is defined in sorting and searching.

An example of calling this function:

mStyleFamilyNames = thisComponent.getStyleFamilies.getElementNames
for i = 0 to uBound(mStyleFamilyNames)
        msgbox join(fnGetDisplayStyleNames(thisComponent, mStyleFamilyNames(i)), chr(13)), 0, mStyleFamilyNames(i)
next

Number formats

Number formats have an Id number. Here is a function for getting that number:

function fnGetNumberFormatId(oDoc, sNumberFormat)
sCharLocale = oDoc.getPropertyValue("CharLocale")
nFormatId = oDoc.getNumberFormats.queryKey(sNumberFormat, sCharLocale, false)
if nFormatId = -1 then  'Not yet defined
        nFormatId = oDoc.getNumberFormats.addNew(sNumberFormat, sCharLocale)
end if
fnGetNumberFormatId = nFormatId
end function

An example for directly applying a number format to a range of cells in a spreadsheet:

oCells = thisComponent.getSheets.getByIndex(0).getCellRangeByName("B2:B10")
nFourDP = fnGetNumberFormatId(thisComponent, "0.0000")
oCells.setPropertyValue("NumberFormat", nFourDp)

Get Style

The following function is for returning a style of a particular name, if the name doesn't already exist a style is created with that name.

function fnGetStyle(oDoc, sStyleFamily, sInstance, sStyleName)
oStyles = thisComponent.getStyleFamilies.getByName(sStyleFamily)
if not oStyles.hasByName(sStyleName) then
        oStyle = thisComponent.createInstance(sInstance)
        oStyles.insertByName(sStyleName, oStyle)
else
        oStyle = oStyles.getByName(sStyleName)
end if
fnGetStyle = oStyle
end function

(It would be good if the sInstance parameter could be removed, but I haven't worked out how to do that. If you work it out please edit this page!)

An example of calling the above function, it creates a cell style called FourDP if it doesn't already exist, sets its number format, and applys the cell format to a range of cells:

sStyle = "FourDP"
oStyle = fnGetStyle(thisComponent, "CellStyles", "com.sun.star.style.CellStyle", sStyle)
oStyle.setPropertyValue("NumberFormat", fnGetNumberFormatId(thisComponent, "0.0000")
oCells = thisComponent.getSheets.getByIndex(0).getCellRangeByName("B2:B10")
oCells.setPropertyValue("CellStyle", sStyle)

Find Style

To find an existing style that has the properties that you want:

function fnFindStyle(oDoc, sStyleFamily, mProperties)
oStyleFamilies = oDoc.StyleFamilies
oStyles = oStyleFamilies.getByName(sStyleFamily)
nProperties = ubound(mProperties)
For i = 0 To oStyles.Count - 1
   oStyle = oStyles(i)
   bFound = true
   for j = 0 to nProperties
      if oStyle.getPropertyValue(mProperties(j).name) <> mProperties(j).value then
         bFound = false
         exit for
      end if
   next
   if bFound then
      fnFindStyle = oStyle
      exit function
   end if
next
end function

An example of calling this function:

dim mProperties()
subAddProperty(mProperties(), "NumberFormat", fnGetNumberFormatId(thisComponent, "0.0000"))
oStyle = fnFindStyle(thisComponent, "CellStyles", mProperties())
if isNull(oStyle) then
   sStyle = "FourDP"
   oStyle = fnGetStyle(thisComponent, "CellStyles", "com.sun.star.style.CellStyle", sStyle)
   oStyle.setPropertyValues(mProperties())
else
   sStyle = oStyle.getName
end if
oCells = thisComponent.getSheets.getByIndex(0).getCellRangeByName("B2:B10")
oCells.setPropertyValue("CellStyle", sStyle)

This example calls subAddProperty which is defined in Working_with_properties#subAddProperty.