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
Next
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.
Thanks!
Hi,
- 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.
e.g
Sample code:
Dim wb As New Workbook()
Dim sh As Worksheet = wb.Worksheets(0)
sh.Cells(“A1”).PutValue(“test”)
sh.Cells(“B1”).PutValue(“EFFECTIVE”)
sh.Cells(“C2”).PutValue(123)
sh.Cells(“B6”).PutValue(“New”)
sh.Cells(“C6”).PutValue(“asdfa1”)
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
wb.Save(“e:\test2\outalignment1.xlsx”)
- 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?