Aggregating styles

Aspose.Cells Product Family
1

I have problems with styles.

What I would like to do is to define some styles with ForeGroundColor, Font and Borders to apply to some cells much like this:

Dim style As Aspose.Cells.Style = Nothing
For i As Integer = 0 To 50
Dim index As Integer = pWorkbook.Styles.Add()
style = pWorkbook.Styles(index)
style.Name = "Custom_Style" + (CType((i + 1), Integer)).ToString()
style.ForegroundColor = Color.White
style.Pattern = BackgroundType.Solid
style.HorizontalAlignment = TextAlignmentType.Left
style.VerticalAlignment = TextAlignmentType.Center
style.Font.Name = "Arial"
style.Font.Size = 10
Next

'Table Footer
style = pWorkbook.Styles(enuStyle.TableFooter)
With style
.Font.IsBold = True
.Font.Size = 12
.ForegroundColor = Color.FromArgb(255, 255, 153) 'Light Yellow
.Pattern = BackgroundType.Solid
.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
End With

Later, when I fill cells, I want to apply a style to a column and modify the Custom property like this:

With objSheet.Cells(intRow, 2)
.PutValue(Convert.ToDecimal(drFund.Item("ClosingCapitalConverted")))
.Style = objWorkbook.Styles(enuStyle.TableFooter)
.Style.Number = 3 'Decimal #,##0
End With

The cell is formated with a percent sign and has 2 decimals like this:

259754357255.41%

If I don't set the style first (.Style = objWorkbook.Styles(enuStyle.TableFooter)), it is working correctly.

2

Hi,
Thanks for considering Aspose.

Well, I think you may try to modify your code a bit:

With objSheet.Cells(intRow, 2)
.PutValue(Convert.ToDecimal(drFund.Item("ClosingCapitalConverted")))
.Style = Nothing
.Style.Copy(style)
.Style.Number = 3 'Decimal #,##0
End With


If you still find any problem, create a test code and paste it completely here with the template excel file attached.

Thank you.

3

Thank you for your answer. It is working.

Is it possible to define partial style and aggregate 2 (or more) styles to a same cell like this:

.Style = Nothing

.Style.Copy(objWorkbook.Styles(enuStyle.TableFooter))

.Style.Copy(objWorkbook.Styles(enuStyle.Percent))

4

Hi,

Well, I am not sure about your need. But I think the last line of your code will overwrite the previous style, so it is recommended that you should create your desired Style object with all the attributes and flavours set, copy it to your desired cell's style and then modify it a bit.

Thank you.

5

>>But I think the last line of your code will overwrite the previous style

yes it does.

>>Well, I am not sure about your need.

What I would like to do is define the basic style settings for a cell

-for example BaseCell = Arial 10 + thin borders

Then, I would like to define another cell style settings that would be the BaseCell + something else

-for example PercentCell = BaseCell + right align + number 10

Then, another one should be

-TotalPercentCell = PercentCell + bold + yellow foregroundcolor

>>so it is recommended that you should create your desired Style object with all the attributes and flavours set

Are we limited on the number of styles?

6

Hi,

I think you may try:

  • Create a BaseCellStyle with Arial 10 and thin borders.
  • Add a new style i.e. PercentCellStyle, Copy the BaseCellStyle to it (i.e., PercentCellStyle.Copy(BaseCellStyle)), now set the style's right alignment and number 10.
  • Add another style i.e., TotalPercentCell, Copy the PercentCellStyle to it (i.e., TotalPercentCellStyle.Copy(PercentCellStyle)), set bold + yellow background etc.
  • Now you may apply the style to your desired cell(s) for your need.

Thank you.

7

That way, if I decide to change the BaseCellStyle font from Arial to Verdana, I would have to modify all the other one. This is what I try not to do!

Are we limited in the number of styles?

8

Hi,

No, there is no limitation although creating lots of styles may utilize your recourses.

Thank you.