# Insert Rows to Excel Using PowerShell

## Problem

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

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

And after insert a new row between the second and third row, 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 row object, within the data being used (`UsedRange`

), to insert a new row at position `3`

. It will automatically shift down the existing row at that position. Then, we fill the cells value on the new row.

This approach only allows us to insert the row one by one in case we want to insert multiple rows. The better approach is using `importexcel`

module which is based on EPPlus. It has `method`

to automatically insert more than one rows.

```
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 rows
$rowIndexToInsert = 3
$sheet.UsedRange.Rows($rowIndexToInsert).Insert()
$sheet.UsedRange.Cells.Item($rowIndexToInsert, 1) = "Austin"
$sheet.UsedRange.Cells.Item($rowIndexToInsert, 2) = "Reeves"
$sheet.UsedRange.Cells.Item($rowIndexToInsert, 3) = "[email protected]"
$sheet.UsedRange.Cells.Item($rowIndexToInsert, 4) = "California"
$sheet.UsedRange.Cells.Item($rowIndexToInsert, 5) = "Male"
# 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 row using `InsertRow`

from worksheet object.

This method also allows us to insert multiple rows by changing the value of variable `$numberOfRowsInsert`

below with value greater than 1.

You can see in the `documentation`

the list of methods that can be used to insert the rows and the 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 rows
$rowIndexToInsert = 3
$numberOfRowsInsert = 1
$copyStyles = $true
$workSheet.InsertRow($rowIndexToInsert, $numberOfRowsInsert, $copyStyles)
$worksheet.Cells[$rowIndexToInsert, 1].Value = "Austin"
$worksheet.Cells[$rowIndexToInsert, 2].Value = "Reeves"
$worksheet.Cells[$rowIndexToInsert, 3].Value = "[email protected]"
$worksheet.Cells[$rowIndexToInsert, 4].Value = "California"
$worksheet.Cells[$rowIndexToInsert, 5].Value = "Male"
}
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 row using `InsertRow`

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 rows by changing the value of variable `$numberOfRowsInsert`

to be greater than 1.

You can see from the `documentation`

the list of methods that can be used to insert the rows and the 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 rows
$rowIndexToInsert = 3
$numberOfRowsInsert = 1
$copyStyles = $true
$workSheet.InsertRow($rowIndexToInsert, $numberOfRowsInsert, $copyStyles)
$worksheet.Cells[$rowIndexToInsert, 1].Value = "Austin"
$worksheet.Cells[$rowIndexToInsert, 2].Value = "Reeves"
$worksheet.Cells[$rowIndexToInsert, 3].Value = "[email protected]"
$worksheet.Cells[$rowIndexToInsert, 4].Value = "California"
$worksheet.Cells[$rowIndexToInsert, 5].Value = "Male"
# 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 rows 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.