Autofit Excel Rows using PowerShell

Tagged: 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.