Awesome
PowerShell and Excel
Has the ImportExcel module helped you?
- Made you look good to the boss?
- Saved you time?
- Made you more productive?
Consider donating. Thank you!
<br>
<br>
<a href="https://www.paypal.com/paypalme/DougCharlesFinke"><img src="https://img.shields.io/badge/Donate-PayPal-green.svg" alt="Donate" height="28"></a>
<br>
<br>
<br>
<br>
<a href="https://www.paypal.com/paypalme/DougCharlesFinke"><img src="https://img.shields.io/badge/Donate-PayPal-green.svg" alt="Donate"></a>
Overview
Automate Excel with PowerShell without having Excel installed. Works on Windows, Linux and Mac. Creating Tables, Pivot Tables, Charts and much more just got a lot easier.
Examples ✨
Check out the more than 100 examples on ways to create amazing reports as well as make you more productive with PowerShell and Excel.
Basic Usage
Installation
Install-Module -Name ImportExcel
Create a spreadsheet
Here is a quick example that will create spreadsheet file from CSV data. Works with JSON, Databases, and more.
$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@
$data | Export-Excel .\salesData.xlsx
Read a spreadsheet
Quickly read a spreadsheet document into a PowerShell array.
$data = Import-Excel .\salesData.xlsx
$data
Region State Units Price
------ ----- ----- -----
West Texas 927 923.71
North Tennessee 466 770.67
East Florida 520 458.68
East Maine 828 661.24
West Virginia 465 053.58
North Missouri 436 235.67
South Kansas 214 992.47
North North Dakota 789 640.72
South Delaware 712 508.55
Add a chart to spreadsheet
Chart generation is as easy as 123. Building charts based on data in your worksheet doesn't get any easier.
Plus, it is automated and repeatable.
$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@
$chart = New-ExcelChartDefinition -XRange State -YRange Units -Title "Units by State" -NoLegend
$data | Export-Excel .\salesData.xlsx -AutoNameRange -ExcelChartDefinition $chart -Show
Add a pivot table to spreadsheet
Categorize, sort, filter, and summarize any amount data with pivot tables. Then add charts.
$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@
$data | Export-Excel .\salesData.xlsx -AutoNameRange -Show -PivotRows Region -PivotData @{'Units'='sum'} -PivotChartType PieExploded3D
Convert Excel data to other formats
Create a separate CSV file for each Excel sheet
Do you have an Excel file with multiple sheets and you need to convert each sheet to CSV file?
Problem Solved
The yearlyRetailSales.xlsx has 12 sheets of retail data for the year.
This single line of PowerShell converts any number of sheets in an Excel workbook to separate CSV files.
(Import-Excel .\yearlyRetailSales.xlsx *).GetEnumerator() |
ForEach-Object { $_.Value | Export-Csv ($_.key + '.csv') }
Additional Resources
Videos
- Export-Excel Hello World
- Make Excel Data Pop
- Slice And Dice Data
- Lightning talk - PowerShell Excel Module
More Videos
- Look smarter: deliver your work in Excel - James O'Neill @jamesoneill
- Module Monday: ImportExcel - Adam Driscoll @adamdriscoll
- Tutorials Excel Module Part 1
- Tutorials Excel Module Part 2
- Tutorials Excel Module Part 3
- PowerShell Excel - Invoke-ExcelQuery
- Powershell Excel - Data Validation
- Creating Dashboards xPlatform
Articles
Contributing
Contributions are welcome! Open a pull request to fix a bug, or open an issue to discuss a new feature or change.
Original README.md