HorizontalAlignment Problem

Aspose.Cells Product Family
1

I have had a constant problem when using Aspose.Cells in regards to the alignment of columns. It seems that last column to be set on a worksheet overrides all the other columns in that row. So if my first column is left aligned, and the next 5 are right aligned, all of them end up being right aligned. Just for grins I moved the code to set the first column to the end, but the other 5 columns also become left aligned. Confusing.

Here is some relative code

'Create the Excel object
Dim _book As Workbook = New Workbook
'Set the default style
Me.SetDefaultStyle(_book)
'Grab the first worksheet
Dim _sheet As Worksheet = _book.Worksheets(0)
Dim _style As Aspose.Cells.Style = Me.SetHeaderStyle(_book)

m_Row = 0
With _sheet
'Set the headers & their style
For i As Integer = 0 To 4
.Cells(m_Row, i).Style = _style
Next

.Cells(m_Row, 0).PutValue("Path")
.Cells(m_Row, 0).Style.HorizontalAlignment = TextAlignmentType.Left
.Cells(m_Row, 1).PutValue("Size (KB)")
.Cells(m_Row, 1).Style.HorizontalAlignment = TextAlignmentType.Right
.Cells(m_Row, 2).PutValue("Last Accessed")
.Cells(m_Row, 2).Style.HorizontalAlignment = TextAlignmentType.Right
.Cells(m_Row, 3).PutValue("Last Modified")
.Cells(m_Row, 3).Style.HorizontalAlignment = TextAlignmentType.Right
.Cells(m_Row, 4).PutValue("Created")
.Cells(m_Row, 4).Style.HorizontalAlignment = TextAlignmentType.Right

<------- SNIP --------->

Private Sub SetDefaultStyle(ByRef _book As Aspose.Cells.Workbook)
Dim defaultStyle As Aspose.Cells.Style = _book.DefaultStyle
defaultStyle.Font.Name = "Verdana"
defaultStyle.Font.Size = 8
_book.DefaultStyle = defaultStyle
End Sub 'SetDefaultStyle

Private Function SetHeaderStyle(ByRef _book As Aspose.Cells.Workbook) As Aspose.Cells.Style
Dim styleIndex = _book.Styles.Add
Dim style As Aspose.Cells.Style = _book.Styles(styleIndex)
style.Font.Name = "Verdana"
style.Font.Size = 8
style.Font.Color = Color.Maroon
style.HorizontalAlignment = TextAlignmentType.Left

Return style
End Function 'SetHeaderStyle

2

Hi,

Thanks for considering Aspose.

Well, I have studied your code a bit. Since you have set the style of all the header cells (A1:E1) to the _style object (which is defined in the SetHeaderStyle() function) in the For...Next loop. And after the loop and before the ....<------- SNIP --------->, you are referring to the this style object and your ultimate change will override all the cell styles.

One option to solve it is to define another style object (which should set the horizontal alignment to right etc.) and then apply this style to B1:E1 cells only. I change your code a bit and add a method (SetMyStyle) to your code and it works fine, Kindly check it:

'Create the Excel object

Dim _book As Workbook = New Workbook()

'Set the default style

Me.SetDefaultStyle(_book)

'Grab the first worksheet

Dim _sheet As Worksheet = _book.Worksheets(0)

Dim _style As Aspose.Cells.Style = Me.SetHeaderStyle(_book)

Dim m_Row As Integer = 0

With _sheet

Dim i As Integer

'Set the headers & their style

For i = 0 To 4

.Cells(m_Row, i).Style = _style

Next

Dim _style1 As Aspose.Cells.Style = Me.SetMyStyle(_book)

.Cells(m_Row, 0).PutValue("Path")

.Cells(m_Row, 0).Style.HorizontalAlignment = TextAlignmentType.Left

.Cells(m_Row, 1).PutValue("Size (KB)")

.Cells(m_Row, 1).Style = _style1

.Cells(m_Row, 2).PutValue("Last Accessed")

.Cells(m_Row, 2).Style = _style1

.Cells(m_Row, 3).PutValue("Last Modified")

.Cells(m_Row, 3).Style = _style1

.Cells(m_Row, 4).PutValue("Created")

.Cells(m_Row, 4).Style = _style1

End With

_book.Save("d:\\test\\teststylealignment.xls")

End Sub

Private Sub SetDefaultStyle(ByRef _book As Aspose.Cells.Workbook)

Dim defaultStyle As Aspose.Cells.Style = _book.DefaultStyle

defaultStyle.Font.Name = "Verdana"

defaultStyle.Font.Size = 8

_book.DefaultStyle = defaultStyle

End Sub 'SetDefaultStyle

Private Function SetHeaderStyle(ByRef _book As Aspose.Cells.Workbook) As Aspose.Cells.Style

Dim styleIndex = _book.Styles.Add()

Dim style As Aspose.Cells.Style = _book.Styles(styleIndex)

style.Font.Name = "Verdana"

style.Font.Size = 8

style.Font.Color = Color.Maroon

style.HorizontalAlignment = TextAlignmentType.Left

Return style

End Function 'SetHeaderStyle

Private Function SetMyStyle(ByRef _book As Aspose.Cells.Workbook) As Aspose.Cells.Style

Dim styleIndex = _book.Styles.Add()

Dim style As Aspose.Cells.Style = _book.Styles(styleIndex)

style.Font.Name = "Verdana"

style.Font.Size = 8

style.Font.Color = Color.Maroon

style.HorizontalAlignment = TextAlignmentType.Right

Return style

End Function 'SetMyStyle

Thank you.

3

Thanks Amjad - that worked great! I didnt realize that setting the style overrode any manual changes.

Rob