# Autofit Excel Rows using PowerShell

## Problem

In this blog post, we will show you how to autofit excel rows using PowerShell.

As the context, our original rows will look as follows:

And after autofitting the rows, it will look as follows:

## Using Excel Com Object

To autofit the rows using this approach, we can use `Autofit`

method . The rest is about how we define the range of the rows that will be autofitted.

Below example will autofit all the rows that contain data on it by using `UsedRange`

property to get the rows, then calling `Autofit`

method subsequently.

```
try {
# Create excel object
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")
# Autofit exel rows on active worksheet that contains data only (UsedRange)
$sheet = $workbook.ActiveSheet
$sheet.UsedRange.EntireRow.Autofit()
# Save excel file
$workbook.Save()
}
finally {
# Close excel file
$workbook.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
}
```

We enclose the code above with `try-finally`

block because we want to make sure it will release the resource after modifying Excel that is performed at `finally`

block.

Anyway, we can also specifically define the rows to be autofitted by using `Range`

method. In below example we specify the range from `A1`

to `A3`

.

```
$sheet = $workbook.ActiveSheet
$sheet.Range("A1:A3").EntireRow.Autofit()
```

If we want to autofit only one row, we can use `Rows`

collection and specify the index of the row as follows:

```
$sheet = $workbook.ActiveSheet
$sheet.Rows[1].Autofit()
$sheet.Rows[2].Autofit()
$sheet.Rows[3].Autofit()
```

Last, if we want to autofit entire worksheet’s rows, we should access `Cells`

property, then subsequently call `Autofit`

method as follows:

```
$sheet = $workbook.ActiveSheet
$sheet.Cells.EntireRow.Autofit()
```

## Using ImportExcel Module

`ImportExcel`

is an external module that is built based on EPPlus which is a very well-known library to work with Excel spreadsheets in .NET.

Before using `ImportExcel`

module, we have to install it.

```
Install-Module -Name ImportExcel
```

This solution follows the same pattern as in previous solution. We will autofit the rows within the range that contains data. To autofit the rows, we must set `CustomHeight`

property of row object to `false`

.

Unlike `Excel Com Object`

, in `ImportExcel`

module, we can only autofit by directly accessing the row object.

In this example, we will iterate through the rows that contain data. Then, we can find the start and the end row using `Dimension`

property of worksheet object.

```
try {
# Import the module
Import-Module ImportExcel
# Open excel file
$excel = Open-ExcelPackage -Path "C:\Scripts\Test.xlsx"
# Get active worksheet
$currentWorksheetIndex = $excel.Workbook.View.ActiveTab
$workSheet = $excel.Workbook.Worksheets[$currentWorksheetIndex + 1]
# Autofit all the rows within the range that contains data
$startRow = $workSheet.Dimension.Start.Row
$endRow = $workSheet.Dimension.End.Row
for ($i = $startRow; $i -le $endRow; $i++) {
$workSheet.Row($i).CustomHeight = $false
}
}
finally {
# Close excel file
Close-ExcelPackage $excel
}
```

Similar to previous solution, we enclose the script with `try-finally`

block to avoid memory leak.

## Using PSExcel Module

`PSExcel`

is another module based on EPPlus library. You can find all the examples in github repository.

Before using this module, we have to install it.

```
Install-Module -Name PSExcel
```

This solution is basically the same with previous solution since the module is based on `EPPlus`

as well.

In this example, we will autofit the rows within the range that contains data. To autofit the rows, we must set `CustomHeight`

property of row object to `false`

.

We can find the start and the end row using `Dimension`

property of worksheet object.

```
try {
# Import the module
Import-Module PSExcel
# Open excel file
$excel = New-Excel -Path 'C:\Scripts\Test.xlsx'
# Get active worksheet
$currentWorksheetIndex = $excel.Workbook.View.ActiveTab
$workSheet = $excel.Workbook.Worksheets[$currentWorksheetIndex + 1]
# Autofit all the rows within the range that contains data
$startRow = $workSheet.Dimension.Start.Row
$endRow = $workSheet.Dimension.End.Row
for ($i = $startRow; $i -le $endRow; $i++) {
$workSheet.Row($i).CustomHeight = $false
}
# Save excel file
$excel | Save-Excel
}
finally {
# Close excel file
$excel | Close-Excel
}
```

We also enclose the script with `try-finally`

block to avoid memory leak.

## Conclusion

To autofit Excel rows using PowerShell, we can use excel module/libraries.

We can use `Excel Com Object`

which is based on .NET Framework. We can also use PowerShell external modules like `PSExcel`

and `ImportExcel`

. `ImportExcel`

is an excellent module which is based on `EPPlus`

library, a popular C# libary for woking with Excel from .NET.

`PSExcel`

is the alternative of `ImportExcel`

module. `PSExcel`

is also based on `EPPlus`

but this module is no longer maintained as stated in its GitHub repository.