Applying Cells.Style to .xls template creates diagonal borders

Aspose.Cells Product Family
1

I just found that when using an .xls (Excel 97 - 2003) format template applying a Style object to an existing sheet creates diagonal borders by default. This does not happen when using an .xlsx (Excel 2007) template.

To avoid having diagonal border “X”'s through the cells where a Style is applied, explicitly set the diagonal borders to none.

Dim _cellStyleIndex As Int32 = wbAspose.Styles.Add()
Dim style As Style = wbAspose.Styles(_cellStyleIndex)
Dim _cell As Cell = sheet.Cells(r, c)
style.VerticalAlignment = TextAlignmentType.Center
style.HorizontalAlignment = TextAlignmentType.Center
style.IsTextWrapped = True
style.Font.IsBold = True
style.Font.Name = “Arial”
If r = 0 Then style.Font.Size = 12 Else style.Font.Size = 10
style.Pattern = BackgroundType.Solid
style.Borders.SetColor(System.Drawing.Color.FromArgb(150, 150, 150))
style.Borders.SetStyle(CellBorderType.Thin)
style.Borders.DiagonalStyle = CellBorderType.None
_cell.SetStyle(style)

Hope this helps someone else.

Jeff

(using Aspose.Cells 4.8.2.9)

2

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id: CELLSNET-15165.

Thank You & Best Regards,

3

Hi,

Please try the attached fix, we have resolved your issue.

Thank you.

4

The issue is still present using version 4.8.2.11. If a border style CellborderType property is set this causes diagonal borders to be added to the style object. The goal of this code is to get thin gray outside cell borders on the target cells.

Stepping through the code this is what happens

Dim _cellStyleIndex As Int32 = wbAspose.Styles.Add()
Dim style As Style = wbAspose.Styles(_cellStyleIndex)
at this point the Style object has only these 2 properties
DiagonalColor = "{Name=Black, ARGB=(255, 0, 0, 0)}"
DiagonalStyle = None {0}
When the color property is set
style.Borders.SetColor(System.Drawing.Color.FromArgb(150, 150, 150))
the Style object color property changes and the border is still “None”
DiagonalColor "{Name=969696, ARGB=(0, 150, 150, 150)}"
DiagonalStyle None {0}
When this is set
style.Borders.SetStyle(CellBorderType.Thin)
the style properties change to
DiagonalColor
"{Name=969696, ARGB=(0, 150, 150, 150)}"
DiagonalStyle Thin {1}
even though I still have not explicitly set any diagonal border properties. If the workbook is saved at this point the cells have unwanted diagonal gray crosses through them.
.
Finally, this must still be explicitly set
style.Borders.DiagonalStyle = CellBorderType.None
to change the object property back to
DiagonalStyle None {0}
and cells have only the desired outer borders.

If only the color property is set without setting either of the CellBorderType values, then the cell end up with no borders at all.

Thanks,
Jeff
5

Hi,

Thanks for your feedback giving us the steps involved.

We will further look into your issue and get back to you soon.

Thank you.

6

Hi,

“When this is set

style.Borders.SetStyle(CellBorderType.Thin)
the style properties change to
DiagonalColor
“{Name=969696, ARGB=(0, 150, 150, 150)}”
DiagonalStyle Thin {1}”


Well, when you use style.Borders.SetStyle(CellBorderType.Thin), we actually set all the borders including diagonal borders too. Please do not use style.Borders.SetStyle(CellBorderType.Thin), alternatively, you may set the top, bottom, left and right borders separately (e.g

'Setting the line style of the bottom border


style.Borders(BorderType.BottomBorder).LineStyle
= CellBorderType.Thin

, see the document: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/adding-borders-to-cells.html) instead of using the single line i.e.
style.Borders.SetStyle(CellBorderType.Thin) that is used to set all lines (including diagonoal borders) style as thin.

Thank y