You’ve probably heard people complaining about how Microsoft’s PowerShell is not compatible with Excel. If you’re wondering why that might be, consider the story of a company who had to manage its data in two separate spreadsheets and realized they were losing money because it was impossible to share work between them.
The “powershell excel worksheet” is a PowerShell script that will enable users to import data from Excel into PowerShell. This allows for easy automation when working with large amounts of data.
Microsoft Excel is one of those products that most of us can’t live without, no matter how hard we try. Excel is used by many IT experts as a small database for storing large amounts of data in different automation algorithms. What is the best automation and Excel scenario? PowerShell!
Scripting and automating Excel spreadsheets has always been a challenge. Excel workbooks aren’t simply plain text files, unlike their less-featured (and easier) CSV counterparts. Excel workbooks needed PowerShell to operate complex Component Object Model (COM) objects, hence Excel was necessary. Not any longer.
Doug Finke, a wise member of the PowerShell community, designed a PowerShell module called ImportExcel for us mere mortals. All of that complexity is abstracted away by the ImportExcel module. It allows you to quickly manage Excel workbooks and get started with PowerShell scripting!
Let’s look at what you can accomplish with PowerShell and Excel using the ImportExcel module and some common use cases in this post.
Prerequisites
There are no additional requirements required to operate the ImportExcel module on a Windows machine. If you’re using macOS, you’ll need to use brew install mono-libgdiplus to install the mono-libgdiplus package. All of the examples in this tutorial will be created on macOS, although they should all function on any platform.
Before continue, make sure you restart your PowerShell session if you’re using macOS.
ImportExcel Module Installation
Begin by executing Install-Module ImportExcel -Scope CurrentUser from the PowerShell Gallery to download and install the module. You’ll be ready to leave in a few seconds.
Exporting to an Excel Worksheet using PowerShell
The standard PowerShell cmdlets Export-Csv and Import-Csv may be known to you. You may read and export PowerShell objects to CSV files using these cmdlets. Unfortunately, the Export-Excel and Import-Excel cmdlets aren’t available. You may, however, create your own functionality using the ImportExcel module.
Exporting PowerShell objects to an Excel spreadsheet is one of the most typical requests an administrator receives. You may simply do this using the ImportExcel module’s Export-Excel cmdlet.
For instance, you may need to locate certain processes running on your local computer and enter them into an Excel spreadsheet.
Export-Excel takes any object in the same manner as Export-Csv does. You may use this cmdlet to pipe any kind of object.
Use the Get-Process cmdlet to locate processes operating on a system using PowerShell. It provides each running process as well as different information about each process. Use the Export-Excel cmdlet to export the data to Excel, specifying the file path to the Excel workbook that will be produced. Below is an example of the command and a snapshot of the Excel file that was created.
Export-Excel -Path ‘./processes.xlsx’ Get-Process | Export-Excel -Path ‘./processes.xlsx’
Export-Excel cmdlet output
Congrats! You’ve now exported all of the data in the same way as Export-Csv did, but unlike Export-Csv, we can make this data much more fancy. Make sure that the worksheet is named Processes, that the data is in a table, and that the rows are auto-sized.
You can see what may be produced in the screenshot below by utilizing the AutoSize switch parameter to autosize all rows, TableName to indicate the name of the table that will contain all the data, and the WorksheetName parameter name of Processes.
Export-Excel -Path ‘./processes.xlsx’ Export-Excel -Path ‘./processes.xlsx’ Get-Process | Export-Excel -Path ‘./processes.xlsx’ -AutoSize -TableName Processes -WorksheetName Processes -AutoSize -AutoSize -AutoSize -AutoSize -AutoSize -AutoS
Result of the Autosize Switch Parameter
You may use the Export-Excel cmdlet with a variety of arguments to produce Excel workbooks of various types. Run Get-Help Export-Excel to get a complete list of what Export-Excel can perform.
Importing to Excel using PowerShell
So, in the last step, you exported some data to a file named processes.xlsx. You may now need to transfer this file to a different machine and import/read the data. It’s no issue. Import-Excel is a tool that you may use.
You simply need to specify the path to the Excel document/workbook using the Path option in its most basic form, as shown below. It reads the first worksheet, in this instance the Processes worksheet, and returns PowerShell objects, as you can see.
Import-Excel -Path ‘./processes.xlsx’ Import-Excel -Path ‘./processes.xlsx’ Import-Excel –
Parameter for the Path
Perhaps your Excel spreadsheet has many worksheets? The WorksheetName argument may be used to read a specific worksheet.
Import-Excel -Path ‘./processes.xlsx’ Import-Excel -Path ‘./processes.xlsx’ Import-Excel – -WorkSheetname SecondWorksheet
Do you simply need to read a few columns from an Excel worksheet? Only the parameters you want to read may be specified using the HeaderName parameter.
Import-Excel -Path ‘./processes.xlsx’ Import-Excel -Path ‘./processes.xlsx’ Import-Excel – -WorkSheetname Processes -HeaderName ‘CPU’,’Handle’
Other options in the Import-Excel cmdlet may be used to read Excel workbooks of various types. Run Get-Help Import-Excel for a complete list of what Import-Excel can accomplish.
Getting (and Setting) Excel Cell Values with PowerShell
You now know how to use PowerShell to read a whole Excel spreadsheet, but what if you just need one cell value? You could possibly use Import-Excel and Where-Object to select out the value you require, but it wouldn’t be very efficient.
Instead, you may “convert” an Excel workbook into a PowerShell object that can then be read and controlled using the Open-ExcelPackage cmdlet. To find a cell value, first bring the Excel workbook into memory by opening it.
$excel = Open-ExcelPackage -Path ‘./processes.xlsx’ $excel = Open-ExcelPackage -Path ‘./processes.xlsx’ $excel = Open-Ex
When dealing directly with COM objects, using Open-ExcelPackage is comparable to using New-Object -comobject excel.application.
After that, choose a worksheet from the workbook.
$worksheet = $excel.Workbook.Worksheets[‘Processes’] $worksheet = $excel.Workbook.Worksheets[‘Processes’] $worksheet = $excel
This method is equivalent to using excel.workbooks.open as a COM object to open workbooks.
You may now drill down to individual rows, columns, and cells after the worksheet has been assigned to a variable. You may need to locate all cell values in the A1 row. As seen below, you merely need to reference the Cells property with an index of A1.
$worksheet.Cells[‘A1’].Value
You may also modify the value of cells in a worksheet by changing the value of a variable, such as $worksheet. ‘differentvalue’ = Cells[‘A1’]
It’s critical to release the Excel package using the Close-ExcelPackage cmdlet after it’s been saved to memory.
$excel $excel $excel $excel $excel $excel $excel $excel $
Using PowerShell to Convert Excel to CSV Files
Once you’ve got the contents of an Excel worksheet represented as PowerShell objects, all you have to do now is submit those objects to the Export-Csv cmdlet to “convert” Excel workbooks to CSV.
Read the first worksheet in the processes.xlsx workbook, which converts all of the data into PowerShell objects, and then export those objects to CSV using the command below.
Export-Csv -Path ‘./processes.csv’ -NoTypeInformation | Import-Excel ‘./processes.xlsx’
You’ll see the same data on the Processes worksheet if you open the generated CSV file (in this example).
Multiple Worksheets to One
You may also produce a CSV file for each worksheet in an Excel workbook with several worksheets. The Get-ExcelSheetInfo cmdlet may be used to locate all the sheets in a workbook. Once you have the worksheet names, you may use the WorksheetName parameter to provide them to the WorksheetName parameter, and the sheet name to name the CSV file.
The required sample code may be seen below.
$sheets = (Get-ExcelSheetInfo -Path ‘./processes.xlsx’) ## Find each sheet in the workbook $sheets = (Get-ExcelSheetInfo -Path ‘./processes.xlsx’) foreach ($sheet in $sheets) ## read each sheet and produce a CSV file with the same name Import-Excel -WorksheetName $sheet -Path ‘./processes.xlsx’ | Export-Csv “./$sheet.csv” -NoTypeInformation Import-Excel -WorksheetName $sheet -Path ‘./processes.xlsx’
Conclusion
You can import, export, and manage data in Excel workbooks with the ImportExcel PowerShell module, much like CSVs, without needing to install Excel!
You learned the fundamentals of reading and writing data to an Excel workbook in this tutorial, but that’s just the beginning. You can make charts, pivot tables, and use other Excel capabilities using PowerShell with the ImportExcel module!
Microsoft’s PowerShell and Excel are two of the most popular tools in their respective fields. They work together, but sometimes you need to make sure that they’re working together correctly. To do this, you can use the “powershell create excel file without excel installed” command.
Frequently Asked Questions
Can PowerShell interact with Excel?
A: Yes, PowerShell can interact with Excel.
Can PowerShell output to Excel?
A: Yes, you can use the Export-CSV or Get-Content cmdlet to export data in a Microsoft Excel format.
What is Excel PowerShell?
A: Excel PowerShell is a cheat sheet for the Microsoft Office Suite. It allows users to locate commands quickly and easily while in Excel, Word, or PowerPoint.
Related Tags
- powershell write to excel
- powershell excel com object
- powershell excel | get cell value
- powershell import-excel file
- powershell output to excel in different columns
