Free Support Forum - aspose.com

Cell formatting not being applied by rows or columns

The Style.VerticalAlignment property is not being effected when applied by row or column. The sheet properties seem to only be effected if I loop through every individual cell and apply the settings.

Style.HorizontalAlignment settings are effective when applied to entire columns, the VerticalAlignment is not. The output is saved to Excel 2007 .xlsx and Excel shows the vertical text alignment as “bottom” in those rows where it should be “top”.

I am already applying individual IsTextWrapped cell formatting based on length of the cell contents. But I expect the code will run faster if I am able to apply more changes by column or row rather than per cell.

Here is code snippet of a larger class:

For c = 0 To maxColumn
'sheet.Cells.Columns©.Style.VerticalAlignment = TextAlignmentType.Top ’ No effect when applied to entire columns
If sheet.Cells(2, c).Type = CellValueType.IsString Then ’ The first 2 rows on the sheet are headers so I am checking the content of the first data row (third sheet row) to see if it is numeric or not.
sheet.Cells.Columns©.Style.HorizontalAlignment = TextAlignmentType.Left ’ This works OK
Else
sheet.Cells.Columns©.Style.HorizontalAlignment = TextAlignmentType.Right ’ This works OK
End If
For r = 2 To maxFormatRow
If (sheet.Cells(r, c).StringValue.Length() > 70) Then
sheet.Cells(r, c).Style.IsTextWrapped = True
End If
'sheet.Cells.Rows®.Style.VerticalAlignment = TextAlignmentType.Top ’ No effect when applied to entire rows
sheet.Cells(r, c).Style.VerticalAlignment = TextAlignmentType.Top ’ Vertical alignment is only effected when I apply to individual cells
Try
sheet.AutoFitRow®
Catch
End Try
Next
Next



Thank you for any input,

Jeff

Hi,

This is the not the better way to format a row or column using the code, e.g
sheet.Cells.Columns©.Style or sheet.Cells.Rows©.Style… you are using Style property for Column or Row objects, this won’t work as we have restricted it for performance accounts in the new versions of the product. The best way to format a column or row is: create a style object, specify your desired alignment or other attributes, create styleflag object, make your desired attributes on, now finally use Row/Column.ApplyStyle() method to provide your style and styleflag objects. This would give you better performance, it is efficient approach and will work surely.

See the following sample code, I have tested it and it works fine. Please adjust my sample code accordingly as it is just a hint how you will apply formatting (it may not fit in your scenario, so you need to update/adjust it accordingly):

Sample code:

Dim workbook As New Workbook()
workbook.Open(“e:\test\Book1.xlsx”)
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim c, r As Integer
Dim maxColumn As Integer = 10
Dim maxFormatRow As Integer = 30

'Adding a new Style to the styles collection of the Excel object
Dim index As Integer = workbook.Styles.Add()
'Accessing the newly added Style to the Excel object
Dim style As Style = workbook.Styles(index)
'Setting the vertical alignment of the text
style.VerticalAlignment = TextAlignmentType.Top
'Setting the horizontal alignment of the text
style.HorizontalAlignment = TextAlignmentType.Right
'Creating Style Flag
Dim styleFlag As StyleFlag = New StyleFlag()
styleFlag.HorizontalAlignment = True
styleFlag.VerticalAlignment = True


'Adding a new Style to the styles collection of the Excel object
index = workbook.Styles.Add()
'Accessing the newly added Style to the Excel object
Dim style2 As Style = workbook.Styles(index)
'Setting the vertical alignment of the text
style2.VerticalAlignment = TextAlignmentType.Top
'Setting the horizontal alignment of the text
style2.HorizontalAlignment = TextAlignmentType.Left
'Creating Style Flag
Dim styleFlag2 As StyleFlag = New StyleFlag()
styleFlag2.HorizontalAlignment = True
styleFlag2.VerticalAlignment = True



For c = 0 To maxColumn
sheet.Cells.Columns©.ApplyStyle(style, styleFlag)
If sheet.Cells(2, c).Type = CellValueType.IsString Then
sheet.Cells.Columns©.ApplyStyle(style2, styleFlag2)
End If
Next

workbook.Save(“e:\test\output.xlsx”)


For further reference, please see the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/formatting-rows-columns.html

Thank you.