How to Auto Resize Rows and Columns

by Chaknith Bin

Resizing rows and columns in a spreadsheet can save a lot of space and make it more readable. The IronXL C# library provides the functionality to automatically resize rows and columns. Since it is done in C#, the resize methods can be called for all the existing rows and columns, automating the manual task in the spreadsheet.

Install with NuGet

Install-Package IronXL.Excel

nuget.org/packages/IronXL.Excel/

or

Download DLL

Download DLL

Manually install into your project

Auto Resize Rows Example

The AutoSizeRow method automatically resize the height of a specified row(s) based on its content.

: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")

Demonstration

Auto Resize Columns Example

Use the AutoSizeColumn method to resize the width of column(s) based on its content 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")

Demonstration

Advanced Auto Resize Rows Example

Another overload of the AutoSizeRow method takes a second parameter as a Boolean value. When set to true, the height of merged cells will also be considered by taking the height of the upper-left cell's value in the merge region and dividing it by the number of rows in the merged region. This condition applies when the content of the merged cell has the highest height relative to 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")

Example

For instance, suppose the content has a height of 192 pixels and is located in a merged region that spans 3 rows. When applying autosize to any of these rows, the algorithm divides the content's height of 192 px by 3, resulting in a height of 64 px for each row. Remember that the AutoSizeRow method needs 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 will adjust the row height based on the content of the cell with the highest height. In Microsoft Excel, when a range of cells is merged, it retains only the value of the upper-left cell and clears the rest. However, with IronXL, the Merge operation is performed without erasing the values 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)

For demonstration purposes, I manually adjusted the row height instead of using the Excel autofit row height function. This was done to avoid adding noticeable top and bottom padding.

With the useMergedCells parameter set to false, the height of merged cells is not being taken into consideration. As a result, there are no changes in height, and each row's height is calculated solely based on its content.

Advanced Auto Resize Columns Example

Similar to AutoSizeRow, you can also make the resizing of columns consider the width of merged cells. When set to true, the width of merged cells will be taken into account. If the merged cell has the longest content, the width of the resized column will be the merged cell's width divided by the number of columns in the 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")

Example

For instance, if the content has a width of 117 pixels and is located in a merged region that spans 2 columns, applying autosize on any of these columns will result in the width of 59 pixels for each column. The AutoSizeColumn method needs to be applied to each column individually.

What if the value is set to false?

When the value is set to false, the AutoSizeColumn method adjusts the width based on the cell's content with the longest width. In Microsoft Excel, when a range of cells is merged, it keeps only the upper-left value and erases the rest. However, the IronXL Merge method does NOT erase the 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)

The width of merged cells was not being prioritized, resulting in no changes in width because each column's width was calculated based on its content width.

The Differences between Excel and IronXL Auto Resize

Rows

The Excel autofit row height function applies a noticeable top and bottom padding to the cell.

Columns

The Excel autofit column width function also applies a left and right padding to the cell, but it is less noticeable.

Manual Resizing of Height and Width Example

In addition to relying on the AutoSizeRow and AutoSizeColumn methods to calculate and adjust the width and height of the content, you also have the flexibility to manually adjust the width and height of columns and rows to meet your specific requirements. This allows you to set them to a certain value that suits your needs.

• Adjust the height: set the Height property of the RangeRow.
• Adjust the width: set the Width property of the RangeColumn.

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

using IronXL;

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

RangeRow row = workSheet.GetRow(0);
row.Height = 10; // Set height

RangeColumn col = workSheet.GetColumn(0);
col.Width = 10; // Set width

workBook.SaveAs("manualHeightAndWidth.xlsx");

IRON VB CONVERTER ERROR developers@ironsoftware.com

Height and Width Units in Excel

The measurement units for height and width in Excel differ from each other. Specifically, the height is measured in 1/20 of a point, while the width is determined based on the number of "0"s that can fit in a cell using the font specified in the "Normal" style.

To simplify the conversion of units, let's the value in IronXL, which corresponds to the display pixels in Microsoft Excel. It is important to note that a pixel does not represent a physical point on the screen; rather, it is defined as 1/96 of an inch, regardless of the screen's actual resolution. Excel itself is designed to be resolution-independent and does not rely on physical pixels.

To establish a relationship between IronXL and Excel's height and width measurements, you can consider the following approximations:

• Height: RangeRow.Height = 10 will result in an approximate height of 1 pixel.
• Width: RangeColumn.Width = 23.255 will correspond to 1 pixel.