Insert Excel Columns Using PowerShell

Tagged: PowerShell

Problem#

In this article, we will show you how to insert columns to Excel using PowerShell.

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

And after insert a new column at position 5, it will look as follows:

Using Excel Com Object#

After creating the object, we will open the Excel file to get workbook object we want to manipulate.

Then, we use Insert method from column object, within the data being used (UsedRange), to insert a new column at position 5. It will automatically shift the original column to the right. Then, we fill the cells value on the new column.

This approach only allows us to insert the column one by one in case we want to insert multiple columns. The better approach is using importexcel module which is based on EPPlus. It has method to automatically insert more than one column.

try {
    # Open excel file
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Open("C:\Scripts\Test.xlsx") 
    
    # Get active worksheet
    $sheet = $workbook.ActiveSheet

    # Insert columns
    $columnIndexToInsert = 5
    $columnRange = $sheet.UsedRange.Columns($columnIndexToInsert)
    $columnRange.Insert()
    $sheet.UsedRange.Cells.Item(1, $columnIndexToInsert) = "Address"
    $sheet.UsedRange.Cells.Item(2, $columnIndexToInsert) = "1254 Main Street, Kissimmee, FL 34758"
    $sheet.UsedRange.Cells.Item(3, $columnIndexToInsert) = "333 North Bergen Lane #3F, Clifton, NJ 07011"

    # 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

This approach has the same pattern as in previous solution where we have to create Excel object first before inserting a new column using InsertColumn from worksheet object.

This method also allows us to insert multiple columns by changing the value of variable $numberOfColumnsInsert below with value greater than 1.

You can see in the documentation the list of methods that can be used to insert the columns and its descriptions.

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]

    # Insert columns
    $columnIndexToInsert = 5
    $numberOfColumnsInsert = 1
    $workSheet.InsertColumn($columnIndexToInsert, $numberOfColumnsInsert)
    $worksheet.Cells[1, $columnIndexToInsert].Value = "Address"
    $worksheet.Cells[2, $columnIndexToInsert].Value = "1254 Main Street, Kissimmee, FL 34758"
    $worksheet.Cells[3, $columnIndexToInsert].Value = "333 North Bergen Lane #3F, Clifton, NJ 07011"    
}
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

Similar to previous implementation, 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 insert the column using InsertColumn method from worksheet object.

Since it is based on EPPlus just like previous module, we can apply the same logic including if we want to insert multiple columns by changing the value of variable $numberOfColumnsInsert to be greater than 1.

You can see from the documentation the list of methods that can be used to insert the columns and its descriptions.

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]

    # Insert columns
    $columnIndexToInsert = 5
    $numberOfColumnsInsert = 1
    $workSheet.InsertColumn($columnIndexToInsert, $numberOfColumnsInsert)
    $worksheet.Cells[1, $columnIndexToInsert].Value = "Address"
    $worksheet.Cells[2, $columnIndexToInsert].Value = "1254 Main Street, Kissimmee, FL 34758"
    $worksheet.Cells[3, $columnIndexToInsert].Value = "333 North Bergen Lane #3F, Clifton, NJ 07011"  
    
    # 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 insert new columns to Excel using PowerShell, we can use excel module/libraries to manipulate excel.

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.