I've been using your module since May and it's been working fine. I noticed that there had been a lot of recent updates so I downloaded and installed the latest version of the module and now Export-Excel is not working at all. Whenever I try to use the module I get the following error. For example:

PS F:\PowerShellScripts> Get-Service | Export-Excel services.xlsx
invoke member w/ expression name
At line:1 char:1
+ Get-Service | Export-Excel services.xlsx
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], NotImplementedExceptio
    + FullyQualifiedErrorId : System.NotImplementedException

I cannot figure this out and don't understand what is happening. The previous version (from 27th May '15) worked fine. It seems to be an issue with the Export-Excel.ps1 file, which didn't even exist in the previous version. I also noticed that parameter completion is no longer working for the Export-Excel cmdlet.

I am using PowerShell 3.0 on Windows 2008 R2. I also tested on Windows 7 with PowerShell 3 - same issue. Reverting back to the previous version works fine.

Hope you can help!

Hey Doug,

Cool stuff! I had a few suggestions/questions I wanted to share:

1. Instead of -AutoFitColumns, how about using -AutoSize (like Format-Table) for consistency?
2. Are -IncludePivotTable/Chart parameters necessary? Because it seems like they could be inferred by the use of -Pivot* parameters and -ChartType parameters, respectively.

Just some food for thought.

Kirk out.

Hi,
Thanks for this excellent module. Is there any option to have percentage in chart.

I was able to import the module from PowerShell 2.0 previously (once you made the changes in 8ca0249 to Add =$true to Parameters to support downlevel versions). However now with the latest commit of the module I am no longer able to import the module under PowerShell 2.0. Here's the error:

Import-Module : The 'C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ImportExcel\ImportExcel.psd1' module cannot be imported because its manifest contains one or more members that are not valid. The valid manifest members are ('ModuleToProcess', 'NestedModules', 'GUID', 'Author', 'CompanyName', 'Copyright', 'ModuleVersion', 'Description', 'PowerShellVersion', 'PowerShellHostName', 'PowerShell
HostVersion', 'CLRVersion', 'DotNetFrameworkVersion', 'ProcessorArchitecture', 'RequiredModules', 'TypesToProcess', 'FormatsToProcess', 'ScriptsToProcess', 'PrivateData', 'RequiredAssemblies', 'ModuleList', 'FileList', 'FunctionsToExport', 'VariablesToExport', 'AliasesToExport', 'CmdletsToExport'). Remove the members that are not valid ('RootModule'), then try to import the module again.
At line:1 char:14

• Import-Module <<<< "ImportExcel"
  • CategoryInfo : InvalidData: (C:\Windows\syst...mportExcel.psd1:String) [Import-Module], InvalidOperationException
  • FullyQualifiedErrorId : Modules_InvalidManifestMember,Microsoft.PowerShell.Commands.ImportModuleCommand

This is occurring on a Windows 2008 R2 SP1 server running PowerShell 2.0. I can import and use the module without issue on another server which is running PowerShell 3.0 but unfortunately we can't upgrade all of our servers to 3.0 at the current time. Any chance you can support 2.0?

Doug, thank you for a wonderful module. I've got it working perfectly in our environment. Automated Excel reports with pivot tables are being email to the boss everyday :) I have 1 small feature request
though.

Add -PivotDataCount

For example...

Get-Process | Export-Excel .\Test.xlsx -WorkSheetname ProcessList -IncludePivotTable -PivotRows ProcessName -PivotDataCount ProcessName

This parameter should count the values in ProcessName which would give you a broken down view of how many times a process is running.

In Excel, by default, when you drag a column down into the "Values" area, it does the count automatically.

I thought of using a Group-Object before passing the object to Export-Excel but then all the raw data is stripped out and I can't manipulate the pivot table nicely in Excel.

hello,
do you think it'll be possible to make a cell or a text cell different color if there is something in the text that set the color.

i mean it'll be really great that some some defined reason, one cell will be in a different color.

exemple : Normal text will produce

Red text :

or Red Cell :

i though for example if there is a tag in text like : "RED::my text" will produce a red "my text" for the cell.

it'll be usefull for testing repport. to see what is good or not

i hope you understand what i mean ..
best regards
Chris

Very good job !!!
I am wondering if I can export something like intranet timeline chart
see here: http://www.zakon.org/robert/internet/timeline/Count_Network.gif
something like the above. maybe with lines to connect each tick group!

Is there any way , also, to set button with functionality in the cells?
something like a button to make visible and not visible some cells.
or a button to make visible or not visible some charts.

thank you very much in advance!!

I am trying to do the following but the highlighting does not work...

$High = @('1'..'35')

Get-Process | Export-Excel -Path document.xlsx -ConditionalText $(
New-ConditionalText -Text $High
)

If I swap $High for text such as '32' it will highlight correctly.
Also I notice that if I do highlight '32' it will trigger on cells containing 32 such as '725454232846574'

I guess I could do with the use of comparison operators as it seems that currently it's doing -like '32'.

Really love this module by the way, keep up the great work.

When I try some of the examples from you video demonstration, the excel workbook that is created will not open and I get the error "There was a problem sending the command to the program." This is on Windows 8.1 x64 and I grabbed the latest version of your module from the PSGallery.

PS C:\Windows\system32> install-module -name importexcel
install-module : The term 'install-module' is not recognized as the
name of a cmdlet, function, script file, or operable program. Check
the spelling of the name, or if a path was included, verify that the
path is correct and try again.
At line:1 char:1

• install-module -name importexcel

• - CategoryInfo          : ObjectNotFound: (install-module:String
    ) [], CommandNotFoundException
  - FullyQualifiedErrorId : CommandNotFoundException
  
  

PS C:\Windows\system32> $psversiontable.psversion

Major Minor Build Revision

3 0 -1 -1

PS C:\Windows\system32>

some how this is not working, i cant see to figure out why --------------------------------------------------------------------------------------------
$hotfixdetails = get-hotfix

$hotfixdetails |
Export-Excel .\mycomputer.xlsx -WorkSheetname PatchingData -IncludePivotTable -PivotRows installedby -PivotData 'hotfixid'
-IncludePivotChart -ChartType PieExploded3D

I have an application that exports data to an excel workbook. Currently I have to go in and remove the first row of data before importing using ImportExcel. This is because the "header" row is on the 2nd row instead of the first, as expected. Therefore, I think it would be nice, and there may be other uses if we could set a variable that begins importing data at the nth row instead of always starting from the first row,
I have included an example of the export from this application.

I have an existing xlsx file that contains various pivot tables and charts based on data that I would like to update using Export-Excel. The various tables are on a worksheet labeled "Tables" and the charts are on a "Charts" worksheet. I'm using this file as a template file of sorts; it contains the complex tables/charts based off of data in the Chassis worksheet and I don't have to try and recreate/organize the tables/charts every time - only refresh the data.

While trying to export an array of chassis information to this xlsx file, I receive the following error:

$chassisArray  | Export-Excel -Path $existingFile -worksheetname Chassis -BoldTopRow -AutoSize -Debug
Exception calling "Save" with "0" argument(s): "Error saving file C:\temp\test.xlsx"
At line:120 char:13
+             $pkg.Save()
+             ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

If I remove the worksheet that contains the pivot tables, the export is successful, however this defeats the purpose of leveraging a template file.

Hi,
Trying to Export-Excel and receiving below message:
Export-Excel : Unable to find type [http://OfficeOpenXml.Style .ExcelFillStyle].

Using Powershell v 5.0 and Office 2016.

Thank You

I'm trying to pipe an array to Export-Excel, but the strings are not available in the worksheet. Only a number indicating the length of the string/

"test", "E:\HOME\creissfe\Backup 27.5.2015\Docs\K LAUFWERK\Praktikum HC 2011\1_Data Internship Christoph Reissfelder\1_Data Internship Christoph Reissfelder\CSR\EU CSR\EU Documents" | Export-Excel -Path $ExcelFile -WorkSheetname 'Paths' -Title 'Paths'

I tried by converting it first to a string with the help of 'Out-String', but no success. Maybe I'm missing something obvious here...

When I export data that contains a date, it gets converted to an integer.
get-adcomputer Somecomputer -properties lastlogondate |export-excel "Adcomputer.xlsx"

LastLogonDate Name ObjectClass
42114.61478 Somecomputer computer

there are two places in Export-excel (line 257 and 297) where US dates are hard coded. I have replace the "m/d/yy h:mm" in mine so that it uses local format.

switch ($TargetData.$Name) {
{$_ -is [datetime]} {$targetCell.Style.Numberformat.Format = [cultureinfo]::CurrentCulture.DateTimeFormat.ShortDatePattern }
}

Good morning Doug, I'm still an active user of your function (and still a big fan :) ) but I wanted to warn you about a small bug I recently encountered.

This code works flawlessly and generates an Excel sheet with a title:

$ExcelParams = @{
    Path = 'S:\Folder\File.xlsx'
    AutoSize = $true
    FreezeTopRow = $true
    TableName = 'Test'
    WorkSheetname = 'Test sheet'
    Title = 'My title'
}
Get-Process | where Name -EQ 'NonExisting' | Export-Excel @ExcelParams

This code, without the Title parameter also generates an Excel sheet but when opening it it prompts to inform the user something is wrong:

$ExcelParams = @{
    Path = 'S:\Folder\File.xlsx'
    AutoSize = $true
    FreezeTopRow = $true
    TableName = 'Test'
    WorkSheetname = 'Test sheet'
}
Get-Process | where Name -EQ 'NonExisting' | Export-Excel @ExcelParams

In my script I have a lot of stuff to export that can be $Null or really have some content.
Example

$ExcelParams = @{
    Path         = $ExcelFile
    AutoSize     = $true
    FreezeTopRow = $true
}
$LogonScriptNotExisting | Export-Excel @ExcelParams -TableName 'Users with non existing logon script' -WorkSheetname 'Users with non existing logon script' -Title 'Users with non existing logon script'
$LogonScriptBlank | Export-Excel @ExcelParams -TableName 'Logon script blank' -WorkSheetname 'Logon script blank'
$TypeOfAcccountBlank | Export-Excel @ExcelParams -TableName 'Type of account blank' -WorkSheetname 'Type of account blank'
$TSUserProfileNotExisting | Export-Excel @ExcelParams -TableName 'TS Profile non existing' -WorkSheetname 'TS Profile non existing'
$DisplayNameStandard | Export-Excel @ExcelParams -TableName 'DisplayName standard not followed' -WorkSheetname 'DisplayName standard not followed'
$DisplayNameCnName | Export-Excel @ExcelParams -TableName 'DisplayName NE CommonName' -WorkSheetname 'DisplayName NE CommonName'
$Description | Export-Excel @ExcelParams -TableName 'Description empty' -WorkSheetname 'Description empty'
$HomeFolderGrouphc | Export-Excel @ExcelParams -TableName 'Grouphc home folder' -WorkSheetname 'Grouphc home folder'
$TSUserProfileGrouphc | Export-Excel @ExcelParams -TableName 'Grouphc TS Profile' -WorkSheetname 'Grouphc TS Profile'
$TSHomeDirectoryGrouphc | Export-Excel @ExcelParams -TableName 'Grouphc TS Home drive' -WorkSheetname 'Grouphc TS Home drive'
$InactiveUser | Export-Excel @ExcelParams -TableName 'Inactive users' -WorkSheetname 'Inactive users'
$InactiveComputer | Export-Excel @ExcelParams -TableName 'Inactive computers' -WorkSheetname 'Inactive computers'
$GroupsNoPlaceholder | Export-Excel @ExcelParams -TableName 'ROL Groups without belsrvc' -WorkSheetname 'ROL Groups without belsrvc'
$UsersWithoutOCS | Export-Excel @ExcelParams -TableName 'Users without OCS' -WorkSheetname 'Users without OCS'

As an extra bonus, it would be great if the function just didn't generate a worksheet at all when there is no content. And also didn't throw an error, because some of these variables can be empty. Otherwise we have to do the following for every line above, which makes the script unnecessary long :

if ($UsersWithoutOCS) {
    $UsersWithoutOCS | Export-Excel @ExcelParams -TableName 'Users without OCS' -WorkSheetname 'Users without OCS'
}
#...

This also generates an corrupt Excel file with mixed up headers:

$ExcelParams = @{
    Path = $ExcelFile
    AutoSize = $true
    FreezeTopRow = $true
    TableName = 'Test'
    WorkSheetname = 'Test sheet'
    Title = 'My title'
}
Get-Process | where Name -EQ 'explorer' | Export-Excel @ExcelParams

Thank you for your help and time. It's still one of my favorite functions.

Hi Doug, I would like to say a big thank you again for your hard work on this function. I can't say it enough, it's an awesome function!

What I'm now trying to do is have an Excel sheet that contains multiple object properties in one cell.

Some code to clarify. This is the workflow I use to collect all the needed information about users home drives on servers:

Workflow Get-HomeDrivesHC {
    Param (
        [String[]]$ComputerName,
        [PSCredential]$Credentials,
        [Int]$OlderThanDays
    )

    Foreach -parallel ($C in $ComputerName) {
        Write-Verbose "Check ComputerName: $C"
        Sequence {
            $Drives = InlineScript {
                if (Test-Path -Path 'E:\HOME') {
                    Get-ChildItem -Path 'E:\HOME' -Force | where { $_.PSIsContainer }
                }
            } -PSComputerName $C -PSCredential $Credentials -PSConnectionRetryIntervalSec 3 -PSConnectionRetryCount 3

            Foreach -parallel ($D in $Drives) {
                Write-Verbose "Check drive: $C - $($D.FullName)"
                InlineScript {
                    Filter Select-FilesHC {
                        if ($_.LastWriteTime -lt (Get-Date).AddDays(-$Using:OlderThanDays)) {
                            Write-Output $_
                        }
                    }

                    $PathTooLong = $OldPST = $UsedPST = @()

                    $D = $Using:D
                    $Content  = Get-ChildItem -Path $D.FullName -Recurse -Force -EV +e -EA SilentlyContinue

                    $PSTfiles = $Content | where {$_.Extension -eq '.pst'}
                    $PSTsize  = $PSTfiles |  Measure-Object -Property length -sum -EA SilentlyContinue

                    foreach ($P in $PSTfiles) {
                        if ($P | Select-FilesHC) {
                            $OldPST += $P
                        }
                        else {
                            $UsedPST += $P
                        }
                    }

                    $FolderSize     = $Content | 
                                            Measure-Object -Property length -sum -EA SilentlyContinue
                    $TotalFileCount = $Content | 
                                            Measure-Object
                    $FilesPicture   = $Content | where {
                                        '.jpg', '.jpeg', '.png', '.bmp', '.jpg' -eq $_.Extension} | 
                                            Measure-Object -Property length -sum -EA SilentlyContinue
                    $FilesAudio     = $Content | where {
                                        '.mp3', '.wav', '.aac', '.ogg', '.wma', '.flac' -eq $_.Extension} | 
                                            Measure-Object -Property length -sum -EA SilentlyContinue
                    $FilesVideo     = $Content | where {
                                        '.mpeg', '.mpg', '.vob', '.flv', '.wmv', '.asf', 'mp4', 'm4v', '3gp' -eq $_.Extension} | 
                                            Measure-Object -Property length -sum -EA SilentlyContinue

                    $LastModTime    = $Content | Sort-Object LastWriteTime | Select -ExpandProperty LastWriteTime -Last 1

                    $D | Add-Member -MemberType noteproperty -Name FolderSize -Value $FolderSize.Sum
                    $D | Add-Member -MemberType noteproperty -Name TotalFileCount -Value $TotalFileCount.Count
                    $D | Add-Member -MemberType noteproperty -Name FilesPictureSize -Value $FilesPicture.Sum
                    $D | Add-Member -MemberType noteproperty -Name FilesPictureCount -Value $FilesPicture.Count
                    $D | Add-Member -MemberType noteproperty -Name FilesPSTsize -Value $PSTsize.Sum
                    $D | Add-Member -MemberType noteproperty -Name FilesPSTcount -Value $PSTsize.Count
                    $D | Add-Member -MemberType noteproperty -Name FilesAudioSize -Value $FilesAudio.Sum
                    $D | Add-Member -MemberType noteproperty -Name FilesAudioCount -Value $FilesAudio.Count
                    $D | Add-Member -MemberType noteproperty -Name FilesVideoSize -Value $FilesVideo.Sum
                    $D | Add-Member -MemberType noteproperty -Name FilesVideoCount -Value $FilesVideo.Count
                    $D | Add-Member -MemberType noteproperty -Name LastModTime -Value $LastModTime
                    $D | Add-Member -MemberType noteproperty -Name OldPST -Value $OldPST
                    $D | Add-Member -MemberType noteproperty -Name UsedPST -Value $UsedPST

                    $D

                    if ($e) {
                        # Report all paths that are too long
                        $e | where {$_.Exception -like 'System.IO.PathTooLongException*'} | foreach {
                            $PathTooLong += $_.TargetObject
                        }
                        New-Object PSObject -Property @{
                            PathTooLong = $PathTooLong
                        }
                    }
                } -PSComputerName $C -PSCredential $Credentials -PSConnectionRetryIntervalSec 3 -PSConnectionRetryCount 3
                Write-Verbose "Details retrieved for drive: $C - $($D.FullName)"
            }
            Write-Verbose "Details retrieved for computername: $C"
        }
    }
}

$Result = Get-HomeDrivesHC -ComputerName 'SERVER' -Credentials $Credentials -OlderThanDays '40'

This code works just fine and collects all the needed stuff in the variable $Result.

The desired result should look like this:

So one line for multipe 'OldPSTFullName' files. I tried to do this as following:

$Result | Where Name | select Name, FullName, Exists, 
    @{N='OldPSTFullName';E={ if ($_.OldPst) { $_ | Select -ExpandProperty OldPst | Select -ExpandProperty FullName }}},
    @{N='OldPSTDate';E={ if ($_.OldPst) { $_ | Select -ExpandProperty OldPst | Select -ExpandProperty LastWriteTime }}} |
        Export-Excel -Path $ExcelFile -WorkSheetname 'HomeDrives' -TableName 'HomeDrives' -AutoSize -FreezeTopRow

The code above generates the text 'System.Object' in a cell. So I tried the following to avoid this:

$Result | Where Name | select Name, FullName, Exists, 
    @{N='OldPSTFullName';E={ if ($_.OldPst) { $_ | Select -ExpandProperty OldPst | Select -ExpandProperty FullName| Out-String}}},
    @{N='OldPSTDate';E={ if ($_.OldPst) { $_ | Select -ExpandProperty OldPst | Select -ExpandProperty LastWriteTime| Out-String}}} |
        Export-Excel -Path $ExcelFile -WorkSheetname 'HomeDrives' -TableName 'HomeDrives' -AutoSize -FreezeTopRow

The problem with the last one is that it's all on one row, and I would like to have it split. Maybe I'm doing something wrong on PowerShell level and it has less to do with your function. But I can't really find a way to figure this out.

Any help or tips would be greatly appreciated, thank you.

Replace it with -NoClobber

Doug -

Ran into an issue when importing an RVTools spreadsheet with Import-Excel. The headers were there but the rest of the sheet didnt have any values. I was getting 1 null set of objects and one set where the headers were populated as objects.

I took the simple approach and just did an if statement where if the $rows variable is equal to 1 then make the object you return $null - if not populate with data.

if ($rows -eq 1)
{
    [PSCustomObject]$h = $null
}
else 
{           
    foreach ($Row in ($HeaderRow+1)..$Rows) {
        $h=[Ordered]@{}
        foreach ($Column in 0..($Columns-1)) {
            if($Header[$Column].Length -gt 0) {
                $Name    = $Header[$Column]
                $h.$Name = $worksheet.Cells[$Row,($Column+1)].Value
            }
        }
        [PSCustomObject]$h
    }
}

Hopefully this helps someone.

$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

When I run this, I get this error:

ScriptHalted
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\ImportExcel.psm1:186 char:17

•             throw $Error[0].Exception.Message
  

•             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OperationStopped: (:) [], RuntimeException
  • FullyQualifiedErrorId : ScriptHalted

Other examples without titles work fine for me.

Win 8.1 Pro PS ISE 5.0.10018.0 and loaded importexcel module 1.5

hello, i have an issue (or not, you'll tell me). i have a gui with a button to export something, the name of the file is define with a date.
So each time you click on the button the name should change.. but in real, the name (the variable) change but it still write to the first file. i'm not able to write to a new named file.
i hope it's clear.. 8-(

As requested, ideas for potential enhancements:

1. Auto-collapse pivot tables/data...would require C# re-code
2. Placement of multiple pivot graphs/images on a single tab/worksheet
3. Idea you mentioned to me...just so we have it down..."loading the the Excel sheet all in memory and then go at it like that"
   4.Include "slicers" so an end user can select from a base set...example..list all customers in a combo-drop-down-box and which ever customer is selected will be reflected in the pivots tables and pivot graphics/images
4. Also have the ability to place multiple pivot tables, not the graphs/images, on a single tab/worksheet
   6.Give the ability to hang/include VBA code...have a parameter off the main Export-Excel that would allow for dev to pass in VBA and specify which tab/worksheet(s) that VBA applies....really start to leverage some serious functionality
5. Give the overall feel of an fully interactive "dashboard" to the end-user. As it stands the module itself is brilliant, as well as its development....but getting feedback from my users and they still "feel" they are in Excel and not a "dashboard," something that would be built via Tableau for instance

Export-Excel : A parameter cannot be found that matches parameter name 'AutoFitColumns'.
At line:1 char:24

• $vmlist | Export-Excel @p -IncludePivotTable

•                    ~~
  

  • CategoryInfo : InvalidArgument: (:) [Export-Excel], ParameterBindingException
  • FullyQualifiedErrorId : NamedParameterNotFound,Export-Excel

Import-Module fails on the PSM1 file with:
The "=" operator is missing after a named argument.
In the functions Import-Excel and Export-MultipleExcelSheets the parameter list specifies Mandatory instead of Mandatory=$true
I am using PowerShell v2.0

This is not an issue by and far. I wanted to post something about a potential new feature request. So, I was thinking if possible, there could be a parameter that would except some form of VBA . I can understand the perils that could be involved with this; as a VBA module would have to be created and then exported with the Excel document. Just something to think about, I would completely understand if this would be "down the road" idea. Again keep of the great work, the team is awesome.

Thanks,
Forensicsguy20012004

added the module to the modules folder.
even when I run as administrator I still get this:

Add-Type -Path "$($PSScriptRoot)\EPPlus.dll" fails. ERROR message:
Add-Type : Could not load file or assembly 'file:///C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ImportExcel\EPPlus.dll' or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ImportExcel\ImportExcel.psm1:1 char:1

• Add-Type -Path "$($PSScriptRoot)\EPPlus.dll"

• - CategoryInfo : NotSpecified: (:) [Add-Type], FileLoadException
  - FullyQualifiedErrorId : System.IO.FileLoadException,Microsoft.PowerShell.Commands.AddTypeCommand
  

I was trying the following Export-Excel example:
Get-Service | Export-Excel "c:\projects\temp\test.xlsx" -Show -IncludePivotTable -PivotRows status -PivotData @{status='count'}

And it throws the following error:
Export-Excel : Unable to find type [OfficeOpenXml.Style.ExcelFillStyle]. Make sure that the assembly that contains this type is loaded.

I believe that there is a problem with the calls to Resolve-Path in Export-ExcelSheet.ps1 and ImportExcel.psm1 (twice)
For a remote path Resolve-Path returns PowerShell's internal path which starts with Microsoft.PowerShell.Core\FileSystem::
This is not a valid path because the colon character is only allowed as the second character in a path, for example: C:\folder1\folder2

This is the fragment that does not work with UNC paths:
$Path = (Resolve-Path $Path).Path
This does work with UNC paths:
$Path = (Resolve-Path $Path).ProviderPath

Thank you Doug, this is really a wonderfull function!

$File = 'C:\test.xlsx' 
1..10 | ForEach-Object {
    [PSCustomObject]@{
        Name = "John $_"
        FamilyName = 'Looooooooooooooooooooooooooooooooooooooooooooooooooong name'
        Date = Get-Date
    }
} | Export-Excel -Path $File -WorkSheetname 'My sheet' -AutoSize

If you try this you'll see that the date is shown as '##########', all other fields are properly fitted.

Hi Doug!

Noticed your note on multiple PivotTables - ran into the same issue trying to come up with a fun demo for my boss.

Added a repro to an issue on codeplex - They released the fix last night, pulled 4.0.4 down, and it seems to be working now.

Cheers!

Add-Format.txt

I've added 4 functions to the module
Add-ConditionalFormatting
Set-Format
Set-ColumnWidth
Set-RowHeight

So I can have code like this
$excel = $invData | [snip] | Export-Excel -WorksheetName "Server Anti-Virus" -AutoSize -FreezeTopRow -AutoFilter -PassThru -Path $FilePath
$wb = $excel.Workbook.Worksheets | Where-Object -Property name -EQ "Server Anti-Virus"
Set-format $wb.Column(7), $wb.Column(8), $wb.Column(12) -HorizontalAlignment Right
Set-format $wb.Row(1) -HorizontalAlignment Left -Bold
$wb.Cells["D1:K1048576"].Style.Numberformat.Format = [cultureinfo]::CurrentCulture.DateTimeFormat.ShortDatePattern
Add-ConditionalFormatting -WorkSheet $wb -Address "c1:c1048576" -BackGroundColor "Yellow" -RuleType Expression -ConditionValue 'AND(FIND("2008 R2",$B1),$C1<>"Service Pack 1")'
$excel.Save() ; $excel.Dispose() ;

My guess is that other people may be looking at something similar, but just adding a contribution to the pot.

It would be nice if Export-Excel could delete and create a new file when it already exists when using the '-Force' switch. For the moment the '-Force' switch is not recognized and doesn't do anything.

When using 'Export-Excel' without the '-Force' switch, it just overwrites the existing worksheets but not the whole workbook. This default behavior is logical to me, but it should be possible to also overwrite the whole workbook, that's where the '-Force' switch could come into play.

Looks great. I've taken several cheap shots at this over the years and every one of them felt hacky. (My favorite hack was using ConvertTo-Html and making Excel open it as a spreadsheet!). Happy to have something that reads/writes .xlsx natively.

Noticed right off the bat though that it doesn't like unresolved PS paths like ~\Desktop\Spreadsheet.xlsx as it's passing it to the managed code directly. If I figure out how to contribute (I am a Git noob) I can fix and do a pull request.

Your module will be really useful for streamlining and improving our reporting. Being able to export data from different cmdlets to different worksheets within the same workbook is amazing. It would be really great if we could make a few other changes to the way the data is displayed to make the reports easier to use/read. For example is there any way that the following features could be implemented in the module:

Ability to set the View \ Freeze Panes \ Freeze Top Row function?
Ability to bold the text of the first row on each worksheet (the column headers)?
Ability to enable Data \ Filter to automatically enabling filtering in each worksheet?

Many thanks,

Stuart

Sheet1 already exists.
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ImportExcel\ImportExcel.psm1:155 char:17

throw "$WorkSheetname already exists."

Hey Doug,

first of all thanks a lot for this feature-rich module. I've followed its development for some time now but just today had the chance to actually use it for a report.

I ran into a problem with the Export-Excel function that prevents me from working with it - it creates corrupted excel documents all the time.

Setup

PS v4.0
Win7 Pro SP1 (German)
Office 2010 ProPlus x86 (German)

This is the error I get:

it says unreadable contents have been found and asks me if I'd like to recover them

Code examples:

My first attempt

$file = 'D:\temp\myexcelreport.xlsx'
$excelParams = @{AutoSize = $true; AutoFilter = $true; TitleBold = $true; FreezeTopRow = $true}
gsv | Export-Excel @excelParams -Path $file -WorkSheetname 'Service stuff' -TableName 'ExtServices' -Title 'My services:' 

Second attempt trying to narrow it down but even this gives me a corrupted excel file.
gsv | Export-Excel -Path D:\temp\myexcelreport.xlsx

Using 2b7ffe0 as head revision.

I've been using an Export-Xlsx function from Luc Dekens for a while and haven't had any of these problems.

Get-NetIPAddress -AddressFamily IPv4 | Select-Object IPAddress,InterfaceAlias,ifIndex,PrefixLength| Export-Excel -Path 'C:\Temp\IPAddresses.xlsx' -WorkSheetname 'IPAddresses' -Show

If you try this you'll see that the ip-address is shown as number

Hi

Get the following error if -Table is used, I suspect the issue is with the dash (Space isn't the issue)

Fails:
$Input | Group-Object ShortName |%{$.group | Export-Xlsx -Path $env:USERPROFILE
Documents\CiscoAdapter-$TestScenario-$StartTime.xlsx -WorksheetName $.Name -Tab
le -Autofit }
Could not get worksheets to search: UCS Sandbox - sys/chassis-1 could not be
found. Valid worksheets:
UCS Sandbox - sys chassis-1
At C:\Users\xxxx\Documents\WindowsPowerShell\Modules\PSEXCEL\Add-Table.ps
1:151 char:13

•         Throw "Could not get worksheets to search: $_"
  

•         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OperationStopped: (Could not get w...sys chassis
    -1
    :String) [], RuntimeException
  • FullyQualifiedErrorId : Could not get worksheets to search: UCS Sandbox
  • sys/chassis-1 could not be found. Valid worksheets:
    UCS Sandbox - sys chassis-1

Works:
$Input | Group-Object ShortName |%{$.group | Export-Xlsx -Path $env:USERPROFILE
Documents\CiscoAdapter-$TestScenario-$StartTime.xlsx -WorksheetName $.Name -Aut
ofit }

The name of the sheet is "UCS Sandbox - sys chassis-1"

When exporting phone numbers from active directory user objects into an Excel sheet, the formatting completely disappears. In AD we have phone numbers like '+32 4 4444 444'. But when pass them on to Export-Excel it shows us '3244444444' and the plus sign disappears.

Example code:

$Users = Get-ADUser -SearchBase 'OU=Users,DC=domain,DC=net' -Filter * -Properties MobilePhone, Fax, OfficePhone, HomePhone

$Users | Export-Excel -Path S:\Test\Out_Test\ADList.xlsx -WorkSheetname 'AD' -TableName 'AD' -FreezeTopRow

Is there a way to have Excel show the number as a string, like the original? I tried the following without success:

$Users | select *, @{label='Tel';Expression={' ' + $_.'Telephone number'}} -ExcludeProperty 'Telephone number' |

 Export-Excel -Path S:\Test\Out_Test\ADList.xlsx -WorkSheetname 'AD' -TableName 'AD' -FreezeTopRow

Thank you for your help.

Currently Export-Excel ends like this
$pkg.Save()
$pkg.Dispose()
if($Show) {Invoke-Item $Path}

I have modified mine so that it ends like this
if ($PassThru) { return $pkg }
else {
$pkg.Save()
$pkg.Dispose()
if($Show) {Invoke-Item $Path}
}

This means I can do this sort of thing
$x = $avdata |export-excel -Path "$pwd\test2.xlsx" -TitleBold -AutoSize -FreezeTopRow -AutoFilter -PassThru

Add-ConditionalFormatting -WorkSheet $x.Workbook.Worksheets[1] -Address "b1:b1048576" -ForeGroundColor "RED" -RuleType ContainsText -ConditionValue "2003"
$x.Workbook.Worksheets[1].Cells["D1:G1048576"].Style.Numberformat.Format = [cultureinfo]::CurrentCulture.DateTimeFormat.ShortDatePattern
$x.Workbook.Worksheets[1].Row(1).style.font.bold = $true
$x.Save() ; $x.Dispose() ; start .\test2.xlsx

(add-conditionalFormatting is another function I added)

Just simple DRY.

I am doing everything in Posh, systematically.

Below is my example; the "usage" and "domain" tabs are the raw data sets or the "-IncludePivotTable." However both of those two tabs/worksheets are the same data, the two pivot tables are different. One pivot table is a count of usage and the other pivot table is counts based on domain assets. I would like to "hide" the domain tab as it is just a duplication of the usage tab.

When numbers with leading zeros (for example, 0123) are written to an Excel file, Excel treats them as numbers & the leading zeros are dropped (0123 becomes 123). This may present problems in situations where leading zeros are truly part of the data, such as US ZIP (postal) codes for parts of New York, New Jersey & Massachusetts.

Example to reproduce the issue:

"a,b,c\rn01,002,3rn00u812,05150,abc,rn123,456,098rn0123,456,098"|ConvertFrom-Csv|Export-Excel .\test.xlsx -Show

This was originally observed in an export of SQL Server data (via Invoke-SQLCmd) where the fields were otherwise identified as strings.

Hi,
It would be nice if export-excel function can create a Index page in sheet 1(Ascending order) with links to other excel sheets of the same workbook.

Thanks.

Hello Doug.
thanks a lot for the great work.
when trying to use the new feature conditional text it gives me this error

new-ConditionalText : The term 'new-ConditionalText' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that
the path is correct and try again.

This could be another enhancement if not already available and if so, my fault. I was thinking about basic charting. Essentially something along the plotting disk growth over time. Say have a variable that allows a hashtable to be passed; and in that hashtable one could pass the fields for the X and Y coordinates. I might already be there and I have not see it or missed it, totally my fault. As I have said before the module is AWESOME, keep up the good work.

Thanks,
Forensicsguy20012004

\Documents\WindowsPowerShell\Modules\EPPlus\EPPlus.dll' or one of its dependencies. Operation is not supported