Sort Excel Columns using PowerShell

Tagged: PowerShell

Problem#

In this article, I will show you how to sort Excel columns using PowerShell.

As the context, our worksheet will look as follows:

And we will sort column A (FirstName) using PowerShell.

Using Excel Com Object#

If you use Windows PowerShell you can omit the first part of below script which is to import Microsoft.Office.Interop.Excel namespace so that the classes of this namespace can be used.

To sort the the columns, you can use Sort property of worksheet object that has some methods and properties that will affect sorting behavior.

If you want to sort in ascending or descending order, you need to set xlSortOrder enum. Also If you want to skip sorting the header, you can set XlYesNoGuess enum to xlYes.

# If you use Windows PowerShell, you can omit this part of the script 
# which is to search and add the class from Global Assembly Cache (GAC)
$assemblyFile = (get-childitem $env:windir\assembly -Recurse 'Microsoft.Office.Interop.Excel.dll' 
    | Select-Object -First 1).FullName
Add-Type -Path $assemblyFile

try {
    # Create excel object
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")
    
    # Perform sorting based on some settings like the range, order, header, etc.
    $sheet = $workbook.ActiveSheet    
    $rangeToSort = $sheet.Range("A1")
    $order = [Microsoft.Office.Interop.Excel.xlSortOrder]::xlAscending
    $sortOn = [Microsoft.Office.Interop.Excel.XlSortOn]::SortOnValues
    $sortData = [Microsoft.Office.Interop.Excel.XlSortDataOption]::xlSortNormal
    $header = [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes
    
    $sheet.Sort.SortFields.Clear()
    $sheet.Sort.SortFields.Add($rangeToSort, $sortOn, $order, $sortData)
    $sheet.sort.setRange($sheet.UsedRange)  
    $sheet.sort.header = $header
    $sheet.sort.apply()

    # Save excel file
    $workbook.Save()    
}
finally {
    # Close excel file
    $workbook.Close()

    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)    
}

The setRange method is used to define the range of data where some of the columns will be sorted. To specify the column that will be sorted (in this case column A), we need to define the range, then this range will be passed as the first argument of Add method of SortFields property.

Last, 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.

Using ImportExcel Module#

ImportExcel is an external module that is built based on EPPlus which is a popular library to work with Excel spreadsheets in .NET.

Before using ImportExcel module, we have to install it.

Install-Module -Name ImportExcel

Unlike using Excel Com Object, ImportExcel doesn’t have property that can be used to exclude the header. Thus, we have to get the dimension of the data in order to get the start row.

Then, the start row will be added by 1 which means the range of data that can be sorted is below the header.

using namespace OfficeOpenXml

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
    $sheet = $excel.Workbook.Worksheets[$currentWorksheetIndex + 1]
    
    # Sort the columns
    $startRow = $sheet.Dimension.Start.Row
    $startColumn = $sheet.Dimension.Start.Column    
    $endRow = $sheet.Dimension.End.Row
    $endColumn = $sheet.Dimension.End.Column

    $rangeAddress = [ExcelCellBase]::GetAddress($startRow + 1, $startColumn, $endRow, $endColumn)
    $sheet.Cells[$rangeAddress].Sort(1)

}
finally {
    # Close excel file
    Close-ExcelPackage $excel    
}

In we want to sort in descending order, we can use the overload of Sort method, for example Sort(1, $true). The second parameter is optional which specifies the order. If we supply and the value is true, then we sort in descending order. Otherwise, the order will be ascending.

Similar to previous solution, we enclose the script with try-finally block to avoid memory leak.

Conclusion#

To sort Excel columns 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 ImportExcel. ImportExcel is an excellent module which is based on EPPlus library, a popular C# libary for woking with Excel from .NET.