ByteInTheSky

Rename Excel Columns using PowerShell

Tagged: PowerShell

Problem

In this blog post, we will show you how to rename Excel columns using PowerShell.

As the context, our original Worksheet will contain columns as follows:

excel file

And we will rename the columns by inserting space before Name in each column so that it will become First Name and Last Name as follows:

Excel Worksheet After Renaming Columns

Using Excel Com Object

Since PowerShell is deeply integrated with .NET Framework, we can use ComObject to work with Excel files. After creating the object, we will open the Excel file to read the file and then rename the columns.


try {
    # Open excel file
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")
    
    # Get the first worksheet and modify the headers at A1 and B1
    $worksheet = $workbook.Worksheets.Item(1)
    $totalColumns = $worksheet.UsedRange.Columns.Count

    for ($col = 1; $col -le $totalColumns; $col++) {
        $header = $worksheet.Cells.Item(1, $col).Value()
        $worksheet.Cells.Item(1, $col) = $header.Substring(0, $header.Length - 4) + ' Name'
    }    

    # # Below is the alternative solution if the columns are static
    # $worksheet.Cells.Item(1, 1).Value = "First Name"
    # $worksheet.Cells.Item(1, 2).Value = "Last Name"
    
    # 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.

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

And then following previous solution pattern, we have to open the file first to read before renaming the columns.


# Import the module
Import-Module ImportExcel

# Open excel file
$excel = Open-ExcelPackage -Path "C:\Scripts\Test.xlsx"

# Get the first worksheet and modify the headers at A1 and B1
$workSheet = $excel.Workbook.Worksheets[1]
$startColumn = $workSheet.Dimension.Start.Column
$endColumn = $workSheet.Dimension.End.Column

for ($col = $startColumn; $col -le $endColumn; $col++) {
    $header = $workSheet.Cells[1, $col].Value
    $workSheet.Cells[1, $col].Value = $header.Substring(0, $header.Length - 4) + ' Name'
} 

# # Below is the alternative solution if the columns are static
# $workSheet.Cells[1, 1].Value = "First Name" # $workSheet.Cells["A1"]
# $workSheet.Cells[1, 2].Value = "Last Name"  # $workSheet.Cells["B1"]

# Close excel file
Close-ExcelPackage $excel

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

In order to use the object, we must import the module first. Then, we create Excel object by specifying the path of our Excel file. This object will be used to get the first worksheet whose columns will be renamed.


# Import the module
Import-Module PSExcel

# Open excel file
$excel = New-Excel -Path 'C:\Scripts\Test.xlsx'

# Get the first worksheet and modify the headers at A1 and B1
$workSheet = $excel.Workbook.Worksheets[1]
$startColumn = $workSheet.Dimension.Start.Column
$endColumn = $workSheet.Dimension.End.Column

for ($col = $startColumn; $col -le $endColumn; $col++) {
    $header = $workSheet.Cells[1, $col].Value
    $workSheet.Cells[1, $col].Value = $header.Substring(0, $header.Length - 4) + ' Name'
}

# # Below is the alternative solution if the columns are static
# $workSheet.Cells[1, 1].Value = "First Name" # $workSheet.Cells["A1"]
# $workSheet.Cells[1, 2].Value = "Last Name"  # $workSheet.Cells["B1"]

# Save excel file
$excel | Save-Excel

Conclusion

To rename Excel columns using PowerShell, 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 well-known 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. If it doesn’t provide functionality that you need, you have to contribute to the source code by yourself.

← Rename Excel Worksheet using PowerShell
Delete Excel Columns using PowerShell →