Cell Format Overwrite?


I tried searching the forums to see if this had been posted before but could not find it…

It seems that when I apply a style it overwrites the cell format

Basically, I was just manipulating the demo sets that are given with the download to alternate foreground colors for cells so that every other row would be a different color. However, when I do this, it loses the pre-formatting that has been done to the worksheet.

Is this intentional? It seems like it should default to what the sheet has unless the attribute has been touched?




Which version are you using? The latest hotfix is

Could you send your designer spreadsheet to me and post your code here? Thus I can see what caused your problem.


'Add LightGray color to color palette
Excel.ChangePalette(Color.LightGray, 55)

Dim styles As styles = Excel.Styles
'Set CategoryName style
Dim styleIndex As Integer = styles.Add()
Dim styleCategoryName As Style = styles(styleIndex)
styleCategoryName.Font.Size = 10
styleCategoryName.ForegroundColor = Color.LightGray
styleCategoryName.Font.Name = “Arial”
styleCategoryName.HorizontalAlignment = TextAlignmentType.Center

Dim cells As cells = sheet.Cells

cells.ImportDataTable(Me.dataTable1, False, 6, 2)
Dim i As Integer
Dim j As Integer
For i = 0 To Me.dataTable1.Rows.Count - 1
cells(6 + i, 1).PutValue(i + 1)
'Alternate line coloring
If (i Mod 2 <> 1) Then
'Need the extra row as well
For j = 1 To Me.dataTable1.Columns.Count + 1
cells(6 + i, j).Style = styleCategoryName

End If

Here is the code where the problem lies… only the alternating lines cell formatting gets overwritten [if I don’t put center then it won’t be centered, and the $$ as well]
The designer is sheet 10 from your samples



Sorry for the delay.

There are two ways to set a cell format.

1. Create a new style and assign to a cell
cell.Style = newStyle

This is same as your case. In this case, the cell format in designer file is overwritten.
Why? To save memory and improve performance, we recommend to use this way to set same formats to many cells. Thus, we have to ignore the different previous format of each cell.

2. Directy set format to a single cell
cell.Style.Font.Size = 10
cell.ForegroundColor = Color.LightGray

In this way, cell format in designer file will not be overwritten.



also, small note if someone tries to implement this, laurence meant to put: cell.Style.ForegroundColor, not cell.ForegroundColor