Insert Table to Excel Using PowerShell
Problem
In this article, we will show you how to insert table to Excel using PowerShell.
As the context, we will insert a table to empty worksheet, after which it will look as follows:
Using Excel Com Object
To create table using this approach, we must use Add method from ListObjects as defined in the documentation. We also need to define the range of the table and pass it to Add method.
If you use Windows PowerShell, you don’t need the first part of the script where it tries to search Microsoft.Office.Interop.Excel.dll assembly in windows GAC (Global Assembly Cache).
But, if you use PowerShell Core, you must add that code because PowerShell Core is built based on .NET Core and there is no GAC concept in .NET Core. Thus, either you manually copy the dll to your script folder then add the type or you add that code to automate searching for the dll before adding the type.
There is a better solution if you don’t want to bother with dll issue above. You can use importexcel module which is based on EPPlus. All you need is only installing the module.
# 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 {
# Open excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\Scripts\Book1.xlsx")
# Get active worksheet
$worksheet = $workbook.ActiveSheet
# Define table range
$range = $worksheet.Range("A1:B3")
# Create the table
$table = $worksheet.ListOBjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $range, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
# Set table style
$table.TableStyle = "TableStyleDark10"
# Fill the table
$range.Cells.Item(1, 1) = "Name"
$range.Cells.Item(1, 2) = "Age"
$range.Cells.Item(2, 1) = "John"
$range.Cells.Item(2, 2) = "22"
$range.Cells.Item(3, 1) = "Michael"
$range.Cells.Item(3, 2) = "25"
# 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
To add the table, we need to use Add method from Tables object. As the arguments, we need to pass the range of the table and table’s name. Optionally, we can also set table style. Then, we fill table’s data.
You can find the documentation for the list of methods you can use.
using namespace OfficeOpenXml.Table
try {
# Import the module
Import-Module ImportExcel
# Open excel file
$excel = Open-ExcelPackage -Path "C:\Scripts\Book1.xlsx"
# Get active worksheet
$currentWorksheetIndex = $excel.Workbook.View.ActiveTab
$workSheet = $excel.Workbook.Worksheets[$currentWorksheetIndex + 1]
# Define table range
$range = $worksheet.Cells["A1:B3"]
# Create the table and set table style
$table = $workSheet.Tables.Add($range, "Person")
$table.TableStyle = [TableStyles]::Dark10
# Fill the table
$workSheet.Cells[1, 1].Value = "Name"
$workSheet.Cells[1, 2].Value = "Age"
$workSheet.Cells[2, 1].Value = "John"
$workSheet.Cells[2, 2].Value = "22"
$workSheet.Cells[3, 1].Value = "Michael"
$workSheet.Cells[3, 2].Value = "25"
}
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 is similar to previous solutions since it is build based on EPPlus library. To add the table, we need to use Add method from Tables object. As the arguments, we need to pass the range of the table and table’s name. Optionally, we can also set table style. Then, we fill table’s data.
You can find the documentation for the list of methods you can use.
using namespace OfficeOpenXml.Table
try {
# Import the module
Import-Module PSExcel
# Open excel file
$excel = New-Excel -Path 'C:\Scripts\Book1.xlsx'
# Get active worksheet
$currentWorksheetIndex = $excel.Workbook.View.ActiveTab
$workSheet = $excel.Workbook.Worksheets[$currentWorksheetIndex + 1]
# Define table range
$range = $worksheet.Cells["A1:B3"]
# Create the table and set table style
$table = $workSheet.Tables.Add($range, "Person")
$table.TableStyle = [TableStyles]::Dark10
# Fill the table
$workSheet.Cells[1, 1].Value = "Name"
$workSheet.Cells[1, 2].Value = "Age"
$workSheet.Cells[2, 1].Value = "John"
$workSheet.Cells[2, 2].Value = "22"
$workSheet.Cells[3, 1].Value = "Michael"
$workSheet.Cells[3, 2].Value = "25"
# 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 table 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.
Anyway, using ImportExcel is more recommended than using Excel Com Object if you don’t want to bother with Microsoft.Office.Interop.Excel.dll assembly.