Apply alignment formatting and set column width in Excel worksheet .NET

I’m having a couple of issues.

1. I can’t get data in the columns to align. I need to align all data in a column based on the column header. This is the code I am using for that:

Dim x As Integer
Dim i As Integer = wb.Styles.Add()
Dim style As Style = wb.Styles(i)
style.HorizontalAlignment = TextAlignmentType.Right
Dim styleFlag As StyleFlag = New StyleFlag()
styleFlag.HorizontalAlignment = True
For x = 1 To colcount Step 1
  If sh.Cells(0, x).Value = "EFFECTIVE" Then
     sh.Cells(5, x).SetStyle(style, styleFlag)
  End If

Or, in this case I’m just testing by setting 4th row to align right. It hits the code and performs, but does nothing. In fact it does this with any style/style flag I set during a loop through data rows. The only styles I can get to work is when I create a range and set borders. It’s as if it just isn’t saving the change I made. But there is no ApplyStyle method for this.

2. Is there a way to auto expand the columns to a certain width? AutoFit doesn’t do what I need. What I need is: 3 columns = 33% on each column, 4 = 25% etc. So that when you open the spreadsheet it’s always (as much as can be, as 30 columns could be selected in the report) showing data full width. I see no way to input percentages for calculations or anything like that.



  1. I have tested your issue with our latest version/fix: Aspose.Cells for .NET v8.3.2.x, it works fine with it. I am using the following sample code and please find attached the output file. The B6 cell value is right aligned which it should in the output file.


Sample code:

Dim wb As New Workbook()

Dim sh As Worksheet = wb.Worksheets(0)






Dim colcount As Integer = 5

Dim x As Integer = 0

'int i = wb.Styles.Add();

Dim style As Style = wb.CreateStyle()

style.HorizontalAlignment = TextAlignmentType.Right

Dim styleFlag As New StyleFlag()

styleFlag.HorizontalAlignment = True

For x = 1 To colcount

If sh.Cells(0, x).StringValue = “EFFECTIVE” Then

sh.Cells(5, x).SetStyle(style, styleFlag) 'B6 cell value is right aligned -Ok

End If

Next x


  1. If you do not want to use AutoFit Rows/Cols height/width, then you may try using our other methods (e.g Cells.SetColumnWidth, Cells.SetColumnWidthInch, Cells.SetColumnWidthPixel etc.) to set the column width, see the document for your reference:

Adjusting Row Height and Column Width

Thank you.

Issue #1 is resolved. Thanks. It was due to overwriting styles. I didn’t realize I couldn’t set a another style to a cell without losing the previous style info.

#2 - I understand I can set column widths, but it doesn’t work for percentages. There is no automated way to simply expand data columns in equal sizes across the sheet? I have to make up some sort of weird calculation to determine the size of the columns based on the number that have data in them then apply that?


1) Good to know that your issue is sorted out now.

2) Well, yes, you cannot specify the columns’ width as percentage values, you can only set the column’s width using the provided methods (as per my previous post) via Aspose.Cells APIs. I think MS Excel also does not provide any means to specify the width of column as percentage. Aspose.Cells follows MS Excel standards. If you know how to do it in Ms Excel manually, let us know with details, we will check it soon.

Thank you.