How to Auto Resize Row and Column

Resizing rows and columns in spreadsheet can save a lot of space and making it more readable. IronXL C# library have the functionality to auto resize rows and columns. Since it is done in C#, the resize methods can be called upon all the existing rows and columns. Hence, automating the manual task in spreadsheet.

How to Auto Resize Row and Column

Download C# library to auto resize row and column in Excel
Import the target Excel file
Utilize AutoSizeRow method to apply auto resize on rows
Use AutoSizeColumn method to resize column based on its content width
Export the edited spreadsheet to various formats

Auto Resize Rows Example

The AutoSizeRow method to automatically resize the height of specified row based on its value height.

:path=/static-assets/excel/content-code-examples/how-to/autosize-rows-columns-rows.cs

using IronXL;

// Load existing spreadsheet
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Apply auto resize on row 2
workSheet.AutoSizeRow(1);

workBook.SaveAs("autoResize.xlsx");

Imports IronXL

' Load existing spreadsheet
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Apply auto resize on row 2
workSheet.AutoSizeRow(1)

workBook.SaveAs("autoResize.xlsx")

VB C#

Demonstration

Auto Resize Columns Example

Use AutoSizeColumn method to resize the width of column(s) based on its value length.

:path=/static-assets/excel/content-code-examples/how-to/autosize-rows-columns-columns.cs

using IronXL;

// Load existing spreadsheet
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Apply auto resize on column A
workSheet.AutoSizeColumn(0);

workBook.SaveAs("autoResizeColumn.xlsx");

Imports IronXL

' Load existing spreadsheet
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Apply auto resize on column A
workSheet.AutoSizeColumn(0)

workBook.SaveAs("autoResizeColumn.xlsx")

VB C#

Demonstration

Please note

All the row and column index position follows zero-based indexing.

Advance Auto Resize Rows Example

Another overload of AutoSizeRow takes the second parameter as Boolean value. When set to true, the height of merged cells will also be considered by taking height of the upper-left Cell's value in the merge region and divide by the number of rows that are in merged region. This is of course in the condition of the merged cell's content has the highest height relative the rest of the rows in the merged region.

:path=/static-assets/excel/content-code-examples/how-to/autosize-rows-columns-advance-rows.cs

using IronXL;

// Load existing spreadsheet
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Apply auto resize to rows individually
workSheet.AutoSizeRow(0, true);
workSheet.AutoSizeRow(1, true);
workSheet.AutoSizeRow(2, true);

workBook.SaveAs("advanceAutoResizeRow.xlsx");

Imports IronXL

' Load existing spreadsheet
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Apply auto resize to rows individually
workSheet.AutoSizeRow(0, True)
workSheet.AutoSizeRow(1, True)
workSheet.AutoSizeRow(2, True)

workBook.SaveAs("advanceAutoResizeRow.xlsx")

VB C#

Example

For example, the height of the content is 192 pixels and it is located in the merged region that is 3 rows tall. Then applying autosize on any of these rows will take the 192 px height and divide it by 3, resulting in 64 px height for each row. The AutoSizeRow has to be applied to each row individually.

What if the value is set to false?

When the value is set to false, the AutoSizeRow method would simply adjust the height based on the cell's content with highest height. In Microsoft Excel, when a range of cells get merged it keeps only the upper-left value and erase the rest. However, IronXL apply Merge while NOT erasing value of other cells in the merged region.

:path=/static-assets/excel/content-code-examples/how-to/autosize-rows-columns-advance-rows-false.cs

using IronXL;

workSheet.Merge("A1:A3");

workSheet.AutoSizeRow(0, false);
workSheet.AutoSizeRow(1, false);
workSheet.AutoSizeRow(2, false);

Imports IronXL

workSheet.Merge("A1:A3")

workSheet.AutoSizeRow(0, False)
workSheet.AutoSizeRow(1, False)
workSheet.AutoSizeRow(2, False)

VB C#

For demonstration purposes, I have manually resized the row height and not using Excel autofit row height function since it would add a noticeable top and bottom padding. The merged cell height was not being prioritized. Hence, there are no changes in height because each row's height was calculated based on it's content height.

Advance Auto Resize Columns Example

Similar to AutoSizeRow, it is also possible to make the resizing of column consider the width of merged cell. When set to true, the width of merged cells will also be considered. If the merged cell has the longest content, the width of resized column will be the merged cell's width divide by the number of columns that are in merged region.

:path=/static-assets/excel/content-code-examples/how-to/autosize-rows-columns-advance-columns.cs

using IronXL;

// Load existing spreadsheet
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Apply auto resize to columns individually
workSheet.AutoSizeColumn(0, true);
workSheet.AutoSizeColumn(1, true);
workSheet.AutoSizeColumn(2, true);

workBook.SaveAs("advanceAutoResizeColumn.xlsx");

Imports IronXL

' Load existing spreadsheet
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Apply auto resize to columns individually
workSheet.AutoSizeColumn(0, True)
workSheet.AutoSizeColumn(1, True)
workSheet.AutoSizeColumn(2, True)

workBook.SaveAs("advanceAutoResizeColumn.xlsx")

VB C#

Example

For example, the width of the content is 117 pixels and it is located in the merged region that is 2 columns wide. Then applying autosize on any of these columns will take the 117 px width and divide it by 2, resulting in 59 px height for each column. The AutoSizeColumn has to be applied to each column individually.

What if the value is set to false?

With false value, the AutoSizeColumn method adjust the width based on the cell's content with longest width. Microsoft Excel keeps only the upper-left value of merged region and erase the rest. On the other hand, IronXL Merge method does NOT erase value of other cells in the merged region.

:path=/static-assets/excel/content-code-examples/how-to/autosize-rows-columns-advance-columns-false.cs

workSheet.Merge("A1:B1");

workSheet.AutoSizeColumn(0, false);
workSheet.AutoSizeColumn(1, false);

workSheet.Merge("A1:B1")

workSheet.AutoSizeColumn(0, False)
workSheet.AutoSizeColumn(1, False)

VB C#

The merged cell width was not being prioritized. Hence, there are no changes in width because each column's width was calculated based on it's content width.

The Differences of Excel and IronXL Auto Resize

Rows

Excel autofit row height function for row apply a noticeable top and bottom padding to the cell.

Columns

Excel autofit column width function for column also apply a left and right padding to the cell, but less noticeable.

Chaknith Bin

Software Engineer

Chaknith is the Sherlock Holmes of developers. It first occurred to him he might have a future in software engineering, when he was doing code challenges for fun. His focus is on IronXL and IronBarcode, but he takes pride in helping customers with every product. Chaknith leverages his knowledge from talking directly with customers, to help further improve the products themselves. His anecdotal feedback goes beyond Jira tickets and supports product development, documentation and marketing, to improve customer’s overall experience.When he isn’t in the office, he can be found learning about machine learning, coding and hiking.

How to Auto Resize Row and Column