This PowerShell Module wraps the .NET EPPlus DLL (included). Easily integrate reading and writing Excel spreadsheets into PowerShell, without launching Excel in the background. You can also automate the creation of Pivot Tables and Charts.
There are two ways to install this module. If you are running PowerShell V5
Install-Module -Name ImportExcel
Otherwise To install in your personal modules folder (e.g. ~\Documents\WindowsPowerShell\Modules), run:
iex (new-object System.Net.WebClient).DownloadString('https://raw.github.com/dfinke/ImportExcel/master/Install.ps1')
- Using
-IncludePivotTable
, if that pivot table name exists, you'll get an error.- Investigating a solution
- Workaround delete the Excel file first, then do the export
- Added
NoLegend
,Show-Category
,ShowPercent
for all charts including Pivot Charts - Updated PieChart, BarChart, ColumnChart and Line chart to work with the pipeline and added
NoLegend
,Show-Category
,ShowPercent
These new features open the door for really sophisticated work sheet creation.
Stay tuned for a blog post and examples.
Quick List
- StartRow, StartColumn for placing data anywhere in a sheet
- New-ExcelChart - Add charts to a sheet, multiple series for a chart, locate the chart anywhere on the sheet
- AutoNameRange, Use functions and/or calculations in a cell
- Quick charting using PieChart, BarChart, ColumnChart and more
Big bug fix for version 3.0 PowerShell folks!
This technique fails in 3.0 and works in 4.0 and later.
$m="substring"
"hello".$m(2,1)
Adding .invoke
works in 3.0 and later.
$m="substring"
"hello".$m.invoke(2,1)
A big thank you to DarkLite1 for adding the help to Export-Excel.
Added -HeaderRow
parameter. Sometimes the heading does not start in Row 1.
Fixes Export-Excel generates corrupt Excel file
Import-Excel
has a new parameter NoHeader
. If data in the sheet does not have headers and you don't want to supply your own, Import-Excel
will generate the property name.
Import-Excel
now returns .Value
rather than .Text
Merged ValidateSet for Encoding and Extension. Thank you Irwin Strachan.
Export-Excel can now handle data that is not an object
echo a b c 1 $true 2.1 1/1/2015 | Export-Excel c:\temp\test.xlsx -Show
Or
dir -Name | Export-Excel c:\temp\test.xlsx -Show
Hide worksheets Got a great request from forensicsguy20012004 to hide worksheets. You create a few pivotables, generate charts and then pivotable worksheets don't need to be visible.
Export-Excel
now has a -HideSheet
parameter that takes and array of worksheet names and hides them.
Here, you create four worksheets named PM
,Handles
,Services
and Files
.
The last line creates the Files
sheet and then hides the Handles
,Services
sheets.
$p = Get-Process
$p|select company, pm | Export-Excel $xlFile -WorkSheetname PM
$p|select company, handles| Export-Excel $xlFile -WorkSheetname Handles
Get-Service| Export-Excel $xlFile -WorkSheetname Services
dir -File | Export-Excel $xlFile -WorkSheetname Files -Show -HideSheet Handles, Services
Note There is a bug in EPPlus that does not let you hide the first worksheet created. Hopefully it'll resolved soon.
Added Conditional formatting. See TryConditional.ps1 as an example.
Or, check out the short "How To" video.
- Now import Excel sheets even if the file is open in Excel. Thank you Francois Lachance-Guillemette
- For -PivotRows you can pass a
hashtable
with the name of the property and the type of calculation.Sum
,Average
,Max
,Min
,Product
,StdDev
,StdDevp
,Var
,Varp
Get-Service |
Export-Excel "c:\temp\test.xlsx" `
-Show `
-IncludePivotTable `
-PivotRows status `
-PivotData @{status='count'}
6/16/2015 (Thanks Justin)
- Improvements to PivotTable overwriting
- Added two parameters to Export-Excel
- RangeName - Turns the data piped to Export-Excel into a named range.
- TableName - Turns the data piped to Export-Excel into an excel table.
Examples
Get-Process|Export-Excel foo.xlsx -Verbose -IncludePivotTable -TableName "Processes" -Show
Get-Process|Export-Excel foo.xlsx -Verbose -IncludePivotTable -RangeName "Processes" -Show
- Fixed null header problem
- Added three parameters:
- FreezeTopRow - Freezes the first row of the data
- AutoFilter - Enables filtering for the data in the sheet
- BoldTopRow - Bolds the top row of data, the column headers
Example
Get-CimInstance win32_service |
select state, accept*, start*, caption |
Export-Excel test.xlsx -Show -BoldTopRow -AutoFilter -FreezeTopRow -AutoSize
- Published to PowerShell Gallery. In PowerShell v5 use
Find-Module importexcel
thenFind-Module importexcel | Install-Module
- datetime properties were displaying as ints, now are formatted
- Now you can create multiple Pivot tables in one pass
- Thanks to pscookiemonster, he submitted a repro case to the EPPlus CodePlex project and got it fixed
$ps = ps
$ps |
Export-Excel .\testExport.xlsx -WorkSheetname memory `
-IncludePivotTable -PivotRows Company -PivotData PM `
-IncludePivotChart -ChartType PieExploded3D
$ps |
Export-Excel .\testExport.xlsx -WorkSheetname handles `
-IncludePivotTable -PivotRows Company -PivotData Handles `
-IncludePivotChart -ChartType PieExploded3D -Show
- Included and embellished Claus Nielsen function to take all sheets in an Excel file workbook and create a text file for each
ConvertFrom-ExcelSheet
- Renamed
Export-MultipleExcelSheets
toConvertFrom-ExcelSheet
- You can add a title to the Excel "Report"
Title
,TitleFillPattern
,TitleBold
,TitleSize
,TitleBackgroundColor
- Thanks to Irwin Strachan for this and other great suggestions, testing and more
- Renamed
AutoFitColumns
toAutoSize
- Implemented
Export-MultipleExcelSheets
- Implemented
-Password
for a worksheet - Replaced
-Force
switch with-NoClobber
switch - Added examples for
Get-Help
- If Pivot table is requested, that sheet becomes the tab selected
- Implemented exporting data to named sheets via the -WorkSheename parameter.
gsv | Export-Excel .\test.xlsx -WorkSheetname Services
dir -file | Export-Excel .\test.xlsx -WorkSheetname Files
ps | Export-Excel .\test.xlsx -WorkSheetname Processes -IncludePivotTable -Show -PivotRows Company -PivotData PM
Reads each sheet in TestSheets.xlsx and outputs it to the data directory as the sheet name with the extension .txt
ConvertFrom-ExcelSheet .\TestSheets.xlsx .\data
Reads and outputs sheets like Sheet10 and Sheet20 form TestSheets.xlsx and outputs it to the data directory as the sheet name with the extension .txt
ConvertFrom-ExcelSheet .\TestSheets.xlsx .\data sheet?0
You can set the pattern, size and of if the title is bold.
$p=@{
Title = "Process Report as of $(Get-Date)"
TitleFillPattern = "LightTrellis"
TitleSize = 18
TitleBold = $true
Path = "$pwd\testExport.xlsx"
Show = $true
AutoSize = $true
}
Get-Process |
Where Company | Select Company, PM |
Export-Excel @p
$p = Get-Process
$DataToGather = @{
PM = {$p|select company, pm}
Handles = {$p|select company, handles}
Services = {gsv}
Files = {dir -File}
Albums = {(Invoke-RestMethod http://www.dougfinke.com/powershellfordevelopers/albums.js)}
}
Export-MultipleExcelSheets -Show -AutoSize .\testExport.xlsx $DataToGather
NOTE If the sheet exists when using -WorkSheetname parameter, it will be deleted and then added with the new data.
Click on this image to watch the short video.
You can also find EPPLus on Nuget.