ImportExcel: A PowerShell repository from Claustn

PowerShell Import-Excel

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.

Know Issues

Using -IncludePivotTable, if that pivot table name exists, you'll get an error.
- Investigating a soloution
- Workaround delete the Excel file first, then do the export

What's new

5/4/2015

Published to PowerShell Gallery. In PowerShell v5 use Find-Module importexcel then Find-Module importexcel | Install-Module

4/27/2015

datetime properties were displaying as ints, now are formatted

4/25/2015

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

Example

$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

4/20/2015

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 to ConvertFrom-ExcelSheet

4/13/2015

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

4/10/2015

Renamed AutoFitColumns to AutoSize
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

4/8/2015

Implemented exporting data to named sheets via the -WorkSheename parameter.

Examples

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

Convert (All or Some) Excel Sheets to Text files

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

Example Adding a Title

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

Example Export-MultipleExcelSheets

$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.

Claustn/ImportExcel