Change Font Size and Autofit rows

Aspose.Cells Product Family
1

I am trying to change the font size of the entire worksheet, is there a way of doing this?


I am having trouble changing the font size of some cells and then calling autofitrows. The row heights don’t seem to change. How can this be done?

Style.Font.Name = "Arial"
Style.Font.Size = 9
styleFlag.FontName = True
styleFlag.FontSize = True
Range = worksheet.Cells.CreateRange(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1)
Range.ApplyStyle(Style, styleFlag)
…
worksheet.AutoFitRows()

Thanks.
2

Hi,

I don't find the problem implementing your scenario a bit. Following is my test code and attached is my generated file. You can see the font size of the cells in the worksheet is changed to "Arial 9". Also, the autofit rows works fine.

Sample code:

'Instantiate a new Workbook.

Dim workbook As New Workbook()

Dim worksheet As Worksheet = workbook.Worksheets(0)

'Get the first Worksheet Cells.

Dim cells As Cells = workbook.Worksheets(0).Cells

'Fill some sample data into the cells.

For i As Integer = 0 To 9

For j As Integer = 0 To 9

cells(i, j).PutValue("This is my test data : " & i.ToString() & " , " & j.ToString())

Next j

Next i

'Create a style object.

Dim style As Style

style = workbook.Styles(workbook.Styles.Add())

style.Font.Name = "Arial"

style.Font.Size = 9

style.IsTextWrapped = True

Dim styleFlag As New StyleFlag()

styleFlag.FontName = True

styleFlag.FontSize = True

styleFlag.WrapText = True

Dim range As Range = cells.CreateRange(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1)

range.ApplyStyle(style, styleFlag)

workbook.Worksheets(0).AutoFitRows()

workbook.Save("f:\test\autofitplusfontsize.xls")

If you still could not figure it out, kindly post your input and output file here, we will check it soon.

And by the way which version of the component you are using? I am using latest v4.7.1.

Thank you.

3

Thanks for your reply. I am also using version 4.7.1. The problem is that the row height don’t shrink. Here is my sample code.


Dim workbook As New Workbook()
'Get the first Worksheet Cells.
Dim cells As Cells = workbook.Worksheets(0).Cells
'Fill some sample data into the cells.
For i As Integer = 0 To 9
For j As Integer = 0 To 2
cells(i, j).PutValue("This is my test data : " & i.ToString() & " , " & j.ToString())
Next j
Next i

workbook.Worksheets(0).Cells.SetColumnWidth(0, 49)
workbook.Worksheets(0).Cells.SetColumnWidth(1, 30)
workbook.Worksheets(0).Cells.SetColumnWidth(2, 30)

'Create a style object.
Dim style As Style
style = workbook.Styles(workbook.Styles.Add())
style.Font.Name = “Arial”
style.Font.Size = 9
style.IsTextWrapped = True
Dim styleFlag As New StyleFlag()
styleFlag.FontName = True
styleFlag.FontSize = True
styleFlag.WrapText = True

Dim range As Range = cells.CreateRange(0, 0, workbook.Worksheets(0).Cells.MaxRow + 1, workbook.Worksheets(0).Cells.MaxColumn + 1)
range.ApplyStyle(style, styleFlag)
workbook.Worksheets(0).AutoFitRow(0, workbook.Worksheets(0).Cells.MaxRow, 0, ldColIndex.ldColClientName)

workbook.Save(“c:\autofitplusfontsize.xls”)
4

Hi,

Thank you for considering Aspose.

Well, I checked your code and it works fine with the latest version of Aspose.Cells attached. Please try the attached version and let us know if it works fine for you. If you still face any problem, please share your generated excel file with us and we will check it soon.

Thank You & Best Regards,

5

Hi,

Thanks for providing us sample code.

"The problem is that the row height don't shrink. Here is my sample code."

Well, what do you mean by that? I mean the row heights are already shrinked and auto-fitted as per your code (You can manually check in MS Excel opening the generated file in it). Do you mean to say the column widths are not shrinked enough related to the data in the cells, well, you need to use Worksheet.AutoFitColumns() to perform the task. e.g

workbook.Worksheets(0).AutoFitColumns()

If you want the rows should display the wrapped text (similar to the file that I attached in my previous post), then you need to minimize the widths of the three columns (currently the column widths are greater than the actual text in the cells) or you can simply comment out the three lines, i.e..,

'workbook.Worksheets(0).Cells.SetColumnWidth(0, 49)

'workbook.Worksheets(0).Cells.SetColumnWidth(1, 30)

'workbook.Worksheets(0).Cells.SetColumnWidth(2, 30)
Kindly let us know if you still have some confusion.
Thank you.
6

I have attached the output file of the code and the row height of the first 10 rows is 24.75. If I autofit the rows in Excel, the row height shrinks to 12.75 and that’s what I wanted it to do.


Thanks.
7

Hi,

Well, I used your code (given below) and it works fine. The first 10 rows height is 12.75 in the generated file. Attached is the generated file.

Please use the latest fix/version that Nausherwan has provided you.

Sample code:

Dim workbook As New Workbook()

'Get the first Worksheet Cells.

Dim cells As Cells = workbook.Worksheets(0).Cells

'Fill some sample data into the cells.

For i As Integer = 0 To 9

For j As Integer = 0 To 2

cells(i, j).PutValue("This is my test data : " & i.ToString() & " , " & j.ToString())

Next j

Next i

workbook.Worksheets(0).Cells.SetColumnWidth(0, 49)

workbook.Worksheets(0).Cells.SetColumnWidth(1, 30)

workbook.Worksheets(0).Cells.SetColumnWidth(2, 30)

'Create a style object.

Dim style As Style = Nothing

style = workbook.Styles(workbook.Styles.Add())

style.Font.Name = "Arial"

style.Font.Size = 9

style.IsTextWrapped = True

Dim styleFlag As New StyleFlag()

styleFlag.FontName = True

styleFlag.FontSize = True

styleFlag.WrapText = True

Dim range As Range = cells.CreateRange(0, 0, workbook.Worksheets(0).Cells.MaxRow + 1, workbook.Worksheets(0).Cells.MaxColumn + 1)

range.ApplyStyle(style, styleFlag)

workbook.Worksheets(0).AutoFitRow(0, workbook.Worksheets(0).Cells.MaxRow, 0, workbook.Worksheets(0).Cells.MaxColumn)

workbook.Save("c:\autofitplusfontsize.xls")

Thank you.

8

Is that version good for .net 2.0?

9

Hi,

Thank you for considering Aspose.

Yes, you can use the previously attached version for .NET 2.0 on 32 bit machine. If you have a 64 bit machine, you can try the attached version (NET 2.0).

Thank You & Best Regards,

10

Thank you very much for your prompt response. The new version did change the row height to 12.75.

My next problem is that if I changed the font size to 9 for the entire row and the autofit rows, the row height changes to 12.75. If I try doing it in Excel, the row height changes to 12. I used the code below and have attached the output file. Any idea why? Also, is there a way of changing the font name/size for the entire worksheet without looping through the rows/columns?

Dim workbook As New Aspose.Cells.Workbook()
'Get the first Worksheet Cells.
Dim cells As Aspose.Cells.Cells = workbook.Worksheets(0).Cells
'Fill some sample data into the cells.
For i As Integer = 0 To 9
For j As Integer = 0 To 2
cells(i, j).PutValue("This is my test data : " & i.ToString() & " , " & j.ToString())
Next j
Next i

cells.SetColumnWidth(0, 49)
cells.SetColumnWidth(1, 30)
cells.SetColumnWidth(2, 30)

'Create a style object.
Dim style As Aspose.Cells.Style
Dim styleflag As New Aspose.Cells.StyleFlag
style = workbook.Styles(workbook.Styles.Add())
style.Font.Size = 9
style.Font.Name = “Arial”
styleFlag.FontSize = True
styleFlag.FontName = True

For i = 0 To cells.MaxRow
cells.Rows(i).ApplyStyle(style, styleflag)
Next
WorkSheet.AutoFitRows()

workbook.Save(“c:\autofitplusfontsize.xls”)

Thanks.

11

Hi,

Thank you for considering Aspose.

We have found your mentioned issue after an initial test. We will look into it and get back to you soon.

Thank You & Best Regards,

12

Hi,

Please try the attached version, now, the rows height would be 12 instead of 12.75.

Moreover, the Worksheet.AutoFitRow(s) is not a precise method. So, it might possible that the auto- fit row height will be slightly greater than the row height generated by MS Excel. There are certain users who demand the auto-fit rows height should be greater than what MS Excel sets. Anyways we always try to match up with MS Excel standards (if possible).

Thank you.

13

This is working fine now. Thank you.