How to Auto Resize Row and Column

by Chaknith Bin

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.

C# NuGet Library for Excel

Install with NuGet

Install-Package IronXL.Excel
or
C# Excel DLL

Download DLL

Download DLL

Manually install into your project

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 Row

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

Auto Resize Column

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.

Advance Auto Resize Row

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#
Advance Auto Resize Row

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.

Advance Auto Resize Column

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#
Advance Auto Resize Column

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.

Excel and IronXL Aute Resize Row comparison

Columns

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

Excel and IronXL Aute Resize Column comparison

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.