We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

The method to set the Style has changed (again)


I just updated Aspose.Cells from version to and my setting are not set anymore!

I use something like this:

With pSheet.Cells(pRow, 0)


.SetStyle(InitStyle(enuStyle.DefaultStyle, _

pFontSize:=16, _

pFontBold:=True, _


End With

The InitStyle method is a helper method that returns a Style:

Private Function InitStyle(ByVal pStyle As enuStyle, _

ByVal pFontColor As System.Drawing.Color, _

ByVal pForegroundColor As Color, _

Optional ByVal pCustom As String = "", _

Optional ByVal pFontName As String = "Arial", _

Optional ByVal pFontSize As Integer = 10, _

Optional ByVal pFontBold As Boolean = False, _

Optional ByVal pFontUnderline As Aspose.Cells.FontUnderlineType = FontUnderlineType.None, _

Optional ByVal pAlignmentHorizontal As Aspose.Cells.TextAlignmentType = TextAlignmentType.Left, _

Optional ByVal pAlignmentVertical As Aspose.Cells.TextAlignmentType = TextAlignmentType.Center, _

Optional ByVal pBorderTop As Aspose.Cells.CellBorderType = CellBorderType.None, _

Optional ByVal pBorderLeft As Aspose.Cells.CellBorderType = CellBorderType.None, _

Optional ByVal pBorderRight As Aspose.Cells.CellBorderType = CellBorderType.None, _

Optional ByVal pBorderBottom As Aspose.Cells.CellBorderType = CellBorderType.None _

) As Aspose.Cells.Style

Dim objStyle As Aspose.Cells.Style = mWorkbook.Styles(0)

With objStyle

'Basic formatting

.ForegroundColor = pForegroundColor

.Pattern = BackgroundType.Solid

.HorizontalAlignment = pAlignmentHorizontal

.VerticalAlignment = pAlignmentVertical

With .Font

.Name = pFontName

.Size = pFontSize

.IsBold = pFontBold

.Underline = pFontUnderline

.Color = pFontColor

End With

.Borders(Aspose.Cells.BorderType.TopBorder).LineStyle = pBorderTop

.Borders(Aspose.Cells.BorderType.LeftBorder).LineStyle = pBorderLeft

.Borders(Aspose.Cells.BorderType.RightBorder).LineStyle = pBorderRight

.Borders(Aspose.Cells.BorderType.BottomBorder).LineStyle = pBorderBottom

.Custom = pCustom

Select Case pStyle

Case enuStyle.DefaultStyle

'Nothing more

Case enuStyle.TableDetail

'Nothing more

Case enuStyle.TableFooterPrograms

.Font.IsBold = True

.Font.Size = 12

.Borders(Aspose.Cells.BorderType.TopBorder).LineStyle = CellBorderType.Double

Case enuStyle.TableHeader

.HorizontalAlignment = TextAlignmentType.Center

.Font.IsBold = True

.Font.Size = 12

.ForegroundColor = Color.Silver

.Borders(Aspose.Cells.BorderType.TopBorder).LineStyle = CellBorderType.Thin

.Borders(Aspose.Cells.BorderType.LeftBorder).LineStyle = CellBorderType.Thin

.Borders(Aspose.Cells.BorderType.RightBorder).LineStyle = CellBorderType.Thin

.Borders(Aspose.Cells.BorderType.BottomBorder).LineStyle = CellBorderType.Thin

Case enuStyle.TableStrategy

.Font.IsBold = True

.ForegroundColor = Color.FromArgb(204, 255, 204) 'Light green

Case enuStyle.TableStrategyBorders

.HorizontalAlignment = TextAlignmentType.Center

.VerticalAlignment = TextAlignmentType.Center

.Font.IsBold = True

.Font.Size = 10

.ForegroundColor = Color.FromArgb(204, 255, 204) 'Light green

.Pattern = BackgroundType.Solid

.Borders(Aspose.Cells.BorderType.LeftBorder).LineStyle = CellBorderType.Thin

.Borders(Aspose.Cells.BorderType.RightBorder).LineStyle = CellBorderType.Thin

.Borders(Aspose.Cells.BorderType.TopBorder).LineStyle = CellBorderType.Thin

.Borders(Aspose.Cells.BorderType.BottomBorder).LineStyle = CellBorderType.Thin

Case enuStyle.TableStyle

.Font.IsBold = True

.ForegroundColor = Color.FromArgb(255, 255, 153) 'Light Yellow

Case Else


End Select

End With

Return objStyle

End Function

What has changed? How can I easily fix that code? It is the second time in a short period of time I modify the Style method.

In the documentation, at http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/aspose.cells.style.html, it is written:

The Style object contains all style attributes (font, number format, alignment, and so on) as properties. There are two methods to set a cell's style.

First method is a fast and efficient way to change several cell-formatting properties on multiple cells at the same time. Keep in mind, however, that changing a style property will affect all cells already formatted with that style. If you want to change a single cell's style properties, second method can be used. It will not interfere with each other.

It would be helpful to provide examples of each methods!


Thanks for considering Aspose.

Well, in the lastest versions, we add some APIs with all the important enhancements. In the newer versions, we add a new method Cells.ApplyStyle() to apply style to the whole worksheet cells which are initiated. You should create style object(s), set its attributes for your desired formattings, and apply this style to a cell using Cell.SetStyle(styleobject), to worksheet cells using Cells.ApplyStyle(styleobject, styleflag), to a row using Cells.ApplyRowStyle(), to a column using Cells.ApplyColumnStyle(), to apply style to range Range.ApplyStyle and to modify an existing style object using Style.Update() methods. All these are new enhancements.

After seeing your code, If you want to change an existing sytle, you should use Style.Update method for your requirement. The changes would be affected on the range of cells (on which you have applied some style) after calling the Style.Update method. I think you may add a line at the end (after you have changed an existing style):


And thanks for mentioning the missing codes in the Style Class API, we will add examples there soon.

Thank you.


Thanks for you your answer but that doesn't fit my requirements and you will surely hurt other programmers as well!

I cannot use the defined styles as I would requires thousands of them (almost every possible permutation of font name, font size, font bold, border, background colors, foreground colors, ...).

To fit my need, I define a single default style and I use my helper method (InitStyle) to customize the style to what my current cell requires.

Using objStyle.Update() in this helper method has for effect that every cell of the worksheet gets the last style used on that worksheet.

I can hardly use column/row style as I cannot always apply the same style to that range.

As for the ApplyRowStyle method, your documentation sucks again! What value do we have to pass to the StyleFlag parameter?

You should never break compatibility like you just did. It is the second time I need to review all the styling techniques in the last 2 months.

What do I have to do to retreive what was working correctly before?


Style.Update method is only used for named style.In Ms Excel ,there are two types of style(named style and cell style) .If you assign a named style to the cell, Ms Excel will created a cell style(which parent is named style) and assign it to cell. If you change the named style(Format->Style->Modify->...->Ok),the cell style (which parent style is this named style) will changed too. The named Style.Update method is used to change the cell style which parent style is this named style.

If mWorkbook.Styles(0) is a named style (Default style),so you will always change the default style and other cells will be changed.If you call Style.Update,all cells style (which parent is default style) will be changed together.

Please add a style as the following codes:

int index = workbook.Styles.Add();
Style objStyle = workbook.Styles[index];

The InitStyle method should change as InitStyle(ByVal pStyle As enuStyle, ByVal objStyle As Aspose.Cells.Style .....).

If you want to apply the style to a column/row, please use cells.ApplyRowStyle() or cells.ApplyColumnStyle(). please check the StyleFlag doc in http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/aspose.cells.styleflagmembers.html

The following codes shows apply all properties the objStyle object to the first row.

StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;

If you still get the problem,please post a simple project.We will check it soon.

Please add a style as the following codes:

int index = workbook.Styles.Add();
Style objStyle = workbook.Styles[index];

Are we limited in the number of styles we can add to a workbook?


No Abs. not. You can add as many style objects as you wish and also depending upon you system resources.

Thank you.


If you use Cell.SetStyle() method and style is cell style not named style, you will gather it into inner list (not Workboo.Styles)and only keep a index to the cell. So any changes with objStyle will effect the cell which has been set the style.

You only need to add the once at the start of the codes if the style is not a named style.