I'm using Powershell to pull datetime values in Excel and next the script generates a PivotTable for each table.
(using the dfinke/ImportExcel module).

To format cells in hh:mm:ss format, I use PSExcel module.
For the plain tables the format works fine.
But the data in the PivotTables are not formatted as requested...

Format Data Worksheet

$Sheetnames = 'Min', 'Max', 'Average', 'Errors', 'ErrorTypes'

foreach ($sheet in $Sheetnames)
{
$Worksheet = $Excel | Get-Worksheet -Name $sheet
$Worksheet | Format-Cell -StartColumn 4 -EndColumn 4 -Autofit -NumberFormat 'hh:mm:ss'
}

Format Pivot Worksheet

$Sheetnames = 'MinPivotTable', 'MaxPivotTable', 'AveragePivotTable'

foreach ($sheet in $Sheetnames)
{
$Worksheet = $Excel | Get-Worksheet -Name $sheet
$Worksheet | Format-Cell -StartColumn 2 -EndColumn 8 -Autofit -NumberFormat 'hh:mm:ss'

Any suggestion how to get this working?

The following code does not work:

$xlDoc = New-Excel
Export-XLSX -Excel $xlDoc -InputObject $ppMembers -Table

Workaround:
Change to [CmdletBinding(DefaultParameterSetName='Excel-NoPivot')]
OR
Change the paramterset for table switch:
[parameter( ParameterSetName = 'Excel-NoPivot')]
[parameter( ParameterSetName = 'Path-NoPivot')]
[switch]$Table,
OR
Add a parameter switch for non-pivot:
[parameter( ParameterSetName = 'Excel-NoPivot')]
[parameter( ParameterSetName = 'Path-NoPivot')]
[switch]$NoPivot,

Hi! I really like this project, I think I will use it for most of our reporting because there is no easier way to automatically put the output of an SQL query into a nice usable Excel table and automatically email it out during the weekend!

Only issue is that I often need to add a sum. This is the most frequently used Excel function. For example if I make a query that lists sales per item, then of course I need to add the total sales in the bottom. For this the best thing would be a function that takes two parameters, a column and a kind of an offset, beginning and end, and puts a sum there. For example a SumThisColumn "D" 1 1 would mean that if 20 lines of data are put into a table and then you call this then it skips one line and puts into D22 a =SUM(D2:D20), taking the first parameter into D2 because it means skip the first line, that is a column header, and also skip the line it is put in, D22, and also one empty line before, that gives the second 1 so it would be D20.

Would this be possible? This would be very helpful as otherwise it is very hacky for me to put them in by code and I need to because the boss man wants automatic reports on Sunday so I can't just put it in manually.

Index operation failed; the array index evaluated to null.
At C:\Program Files\WindowsPowerShell\Modules\PSExcel\1.0.2\Export-XLSX.ps1:477 char:17

• ... $WorkSheet.Cells[$WorkSheet.Dimension.Address].AutoFitCol ...

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

  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : NullArrayIndex

Long story short, I have to create a report where I have a list of AD groups, the report follows the format AD Groupname as Sheetname, the groupmembers go into the sheets. Some groups are empty, and on those groups - this errors out.

Importing a file with the '-Text' parameter causes the value to be passed to [datetime]::FromOADate to be a string. This causes the catch block to get executed each time. I would think bracketing this with a check for the Text parameter could easily skip this step that will always fail.

I'm trying to copy a row to a different worksheet. I can't find an easy way of doing it. Is what I've written here the best way?

` $row = @(Get-CellValue -worksheet $Worksheet -Coordinates "A2:J2")

$DifferentWorksheet.SetValue($lastrow,1, $row.'#')

$DifferentWorksheet.SetValue($lastrow,2, $row.Action)

$DifferentWorksheet.SetValue($lastrow,3, $row.Project)

$DifferentWorksheet.SetValue($lastrow,4, $row.Context)

$DifferentWorksheet.SetValue($lastrow,5, $row.Area)

$DifferentWorksheet.SetValue($lastrow,6, $row.Time)

$DifferentWorksheet.SetValue($lastrow,7, $row.Due)

$DifferentWorksheet.SetValue($lastrow,8, $row.NA)

$DifferentWorksheet.SetValue($lastrow,9, $row.Created)

$DifferentWorksheet.SetValue($lastrow,10, $row.Finished)

$excel.save()`

Hello,

i am trying to use function in "readme" but without success.

Where is problem?

Thank you
Filip

Ran this:
Import-Module .\PSExcel.psm1

Got this in return:
`Add-Type : Could not load file or assembly 'file:///F:\PowerShell\ClassComp\PSExcel-master\PSExcel\lib\epplus.dll' or
one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)
At F:\PowerShell\ClassComp\PSExcel-master\PSExcel\PSExcel.psm1:9 char:26

• if( -not ($Library = Add-Type -path $BinaryPath -PassThru -ErrorAction stop) ...
  

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

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

Greetings,

We receive the following error when we use the Import-XLXS module to try to combine 2 excel files in PowerShell 5.

    PS C:\windows\system32> $L = Import-XLSX C:\Users\Public\KBs\SecurityUpdates.xlsx
    Import-XLSX : Failed to open 'C:\Users\Public\KBs\SecurityUpdates.xlsx':
    Cannot find type [OfficeOpenXml.ExcelPackage]: verify that the assembly containing this type is 
    loaded.
    At line:1 char:6
    + $L = Import-XLSX C:\Users\Public\KBs\SecurityUpdates.xlsx
   +      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : NotSpecified: (  [Write-Error], WriteErrorException
   + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Import-XLSX  

I look forward to hearing back from you soon concerning this issue.

It seems, that there is no function to merge cells. It would be a nice feature for one of the next versions :)

Hi!

I've needed to make a few PivotTables and associated graphs that need to use a summary function - for example, to count the number of occurrences of a single item.

The ImportExcel module handles this by accepting a variable-typed parameter for PivotData. It looks like to specify an alternate function, that module uses a hashtable instead of a String.

# Count occurrences of each process name
gps | Export-Excel @otherArguments -PivotData @{name='count'}

Since in this module, the PivotValues parameter in Add-PivotTable is defined as a String[], the same approach wouldn't work. Would a separate parameter be appropriate (something like PivotFunction)?

I'm happy to submit a pull request for this if you'd like; I'd just need to do it from home instead of on my work machine. :)

Thanks!
~replica

Hi...

Does anyone know what i am doing wrong ?

$Excel = New-Excel -Path "C:\temp\Demo.xlsx"
$Workbook = $Excel | Get-Workbook
$Worksheet = $Workbook | Get-Worksheet -Name "allData"
$Worksheet | Get-CellValue -Coordinates "B1:B1"

I simply want to readout cell value B1 in Excel Sheet called allData...

Best
Hansjörg

Could this be used to detect macro content in files (or especially extract the VPA code)? I'm looking for a faster method than COM or the python-based OleTools.

hi, I create some small script which can be useful for others.

content of file csv2xlsx.ps1:

function csv2xlsx {
Param(
[Parameter(Mandatory=$false,ValueFromPipeline=$true)]
[ValidateScript({Test-Path $_})]
[String]$CSVfile
)

Process {
if(-not($CSVfile)) { Throw "You must supply a value for -CSVfile" }

Import-Module PSExcel

$path = split-path $CSVfile -Parent
$file = split-path $CSVfile -Leaf

$infile = join-path $path $file
$outfile = join-path $path ($file.split('.')[-2]+".xlsx")

$CSV = Import-Csv $infile
$CSV | Export-XLSX -Path $outfile

$Excel = New-Excel -Path $outfile
$Worksheet = $Excel | Get-Worksheet
$Worksheet | Format-Cell -Autofit -StartColumn 1 -EndColumn 100
$Worksheet | Format-Cell -AutoFilter $True
$Excel | Close-Excel -Save
$outfile
}
}

csv2xlsx $args[0]

Ran into a scenario where more than one pivot table would be helpful. This appears to be broken, either on my end or in EPPlus (writing out .NET equivalent rather than abstraction shortly)

Example to reproduce this:

#Create xlsx
gci C:\ -file | Export-XLSX -Path $File -force

#Add first pivot
Add-PivotTable -Path $File -WorkSheetName 'Worksheet1' -PivotTableWorksheetName 'P1' -PivotRows Extension -PivotValues Length

#Add second pivot
Add-PivotTable -Path $File -WorkSheetName 'Worksheet1' -PivotTableWorksheetName 'P2' -PivotRows CreationTime -PivotValues Name

ii $file

Alternatively, you can use two worksheets worth of data:

#Create xlsx
gci C:\ -file | Export-XLSX -Path $File -force

#Add second sheet
gci C:\temp -file | Export-XLSX -Path $File -WorksheetName 'Worksheet2'

#Add first pivot
Add-PivotTable -Path $File -WorkSheetName 'Worksheet1' -PivotTableWorksheetName 'P1' -PivotRows Extension -PivotValues Length

#Add first pivot
Add-PivotTable -Path $File -WorkSheetName 'Worksheet2' -PivotTableWorksheetName 'P2' -PivotRows Extension -PivotValues Length

ii $file

The following error appears:

Clicking yes results in the following actions:

Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
Repaired Records: Workbook properties from /xl/workbook.xml part (Workbook)

Using the same data source (first example), everything will appear normal. Both pivot tables appear to work. Using separate data sources, we see something is off in the repaired version; both pivot tables appear to target the same data source, despite being pointed at different data sources.

Submitted a reply to an open issue on the Codeplex EPPlus project. I'll move my test cases to .NET to verify this isn't my abstraction, I suspect it may be a bug in the EPPlus library. Or it could be me : )

PSExcel is an amazing module!

Do you have any recommendations on how to deal with objects with embedded objects? For example, when doing the following command:
Get-ADUser username -Properties ProxyAddresses | Export-XLSX c:\test.xlsx

When opening Excel, under the ProxyAddresses column it says "Microsoft.ActiveDirectory.Management.ADPropertyValueCollection".

I could programmatically test each object to see if there is a collection then build a new string array with the data, but I'm wondering if there is an easier way? I was hoping there was an argument that would do the work for me, but couldn't find one :)

        if($PSBoundParameters.ContainsKey('Path'))
        {
            $Excel.SaveAs($Path)                
        }
        #Return object
        $Excel                  
}

}

This will make it possible to stream data into a mail attachment without writing to a tmp-file (which will be locked as long as PowerShell is running).

The function Join-Worksheet documents and accepts named parameter DestinationWorksheetName. However, it does not actually pass that to the Export-XLSX function.

The fix is to pass this parameter value as argument WorksheetName to the Export-XLSX function.

Hello,

When I try to export(sql query resultset) to xlsx from a datatable I see there few additional columns DataView, RowVersion, Row, IsNew, IsEdit are being added at the end of all rows.
Is there any way these columns can be ignored while exporting?

Below is the script cut

$dgvReportRsTable= New-Object System.Windows.Forms.DataGridView
$tempSQLDataPath = Join-Path $tempDir "\TempRQD.csv"
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
$objTable = $DataSet.Tables[0]
$objTable | Export-CSV $tempSQLDataPath
			
$array= new-object System.Collections.ArrayList
$data=@(Import-CSV $tempSQLDataPath)
$array.AddRange($data)
			
$arrayDT = ConvertTo-DataTable -InputObject $array
Load-DataGridView -DataGridView $dgvReportRsTable -Item $arrayDT
			
$dgvReportRsTable.ReadOnly = $true
$dgvReportRsTable.DefaultCellStyle.NullValue = "NULL"
$dgvReportRsTable.AllowUserToAddRows = $false
$dgvReportRsTable.Rows | select -expand DataBoundItem | Export-XLSX -Path $SaveFileDialog.FileName -AutoFit -Force -ClearSheet

Or am I missing anything? Appreciate you help.

Thanks,
Dinesh

Hi,

I'm running into issues trying to save an excelfile after editing it with PSExcel. I'm trying to get rid of the excess worksheet PSExcel generates when creating a graph, since I'll be using the same dataset to generate a couple of different graphs, and want to avoid having 5 identical worksheets in there.

The following works perfectly:

$Xl = New-Excel -Path C:\temp\report.xlsx 
$WorkBook = $Xl | get-workbook
$WorkSheet = $Workbook | Get-Worksheet -Name "PivotTable1"
$WorkSheet.Name = "DB In Use"
$Xl | save-Excel -close

My pivottable worksheet is renamed to the name I want it to have. But when I do the following, I get an error on saving the excel file:

$Xl = New-Excel -Path C:\temp\report.xlsx 
$WorkBook = $Xl | get-workbook
$WorkSheet = $Workbook | Get-Worksheet -Name "Data1"
$WorkBook.Worksheets.Delete($WorkSheet)
$Xl | save-Excel -close

The script fails on the last line:
`Save-Excel : Error saving file. Exception calling "Save" with "0" argument(s): "Error saving file C:\temp\demo.xlsx"
At R:_Scriptrepository\Under_Construction\Tondelje\ExchangeEnvironmentReport.ps1:158 char:17

• $Xl | save-Excel <<<< -close
  • CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
  • FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Save-Excel`

The issue also occurs when using $Workbook.Worksheets.Delete("Data1").

At first I thought this might've been an issue with EPPlus so I've updated to the last version but to no avail.

Thank you for your help!

The following codes works fine and results in a ForestGreen header

$DemoData = 1..10 | Foreach-Object{
    [PSCustomObject]@{
        Name       = "jsmith $_"
        EmployeeID = $EID
        Date       = $Date
    }
}

$File = "$env:TEMP\test.xlsx"
$DemoData | Export-XLSX -Path $File -WorksheetName 'Printers' -Table -AutoFit -Force
$Excel = New-Excel -Path $File
$Worksheet = $Excel | Get-Worksheet -Name Printers

$HeadParams = @{
    Header              = $True
    Bold                = $True
    Size                = '12'
    Color               = 'White'
    BackgroundColor     = 'ForestGreen' 
    HorizontalAlignment = 'Center'
    WrapText            = $True
}
$Worksheet | Format-Cell @HeadParams
$Worksheet | Set-FreezePane -Row 2
$Excel | Close-Excel -Save

However, when you change the input of Get-Worksheet to a non existing worksheet, no error is thrown and the formatting is not applied. It would be nice if it would throw an error when you specify a name for a worksheet and that worksheet name doesn't exist. So you know you made a mistake somewhere.

This code doesn't give a ForestGreen header and no error that the worksheet isn't found:

$File = "$env:TEMP\test.xlsx"
$DemoData | Export-XLSX -Path $File -WorksheetName 'Printers' -Table -AutoFit -Force
$Excel = New-Excel -Path $File
$Worksheet = $Excel | Get-Worksheet -Name UnexistingWorksheet

$HeadParams = @{
    Header              = $True
    Bold                = $True
    Size                = '12'
    Color               = 'White'
    BackgroundColor     = 'ForestGreen' 
    HorizontalAlignment = 'Center'
    WrapText            = $True
}
$Worksheet | Format-Cell @HeadParams
$Worksheet | Set-FreezePane -Row 2
$Excel | Close-Excel -Save

In the help doc:

    -Sheet <Object>
        Index or name of Worksheet to import

But it actually requires an index and does not support by name.

Per Filip Hasa's suggestion - Thanks Filip!

There seems to be an issue with

Format-Cell -Autofit -AutofitMinWidth -AutofitMxWidth -Append

it seems the autofit is applied to the data that is being appended and no longer honors larger cells that already existed. not sure if there is anything that can be done, I will be looking.

I posted this in the Technet forum but I was told to post it here instead:

I am trying to read an Excel spreadsheet that gets automatically generated every morning. It is a feed from Remedy. I can open the file manually with no problems, but I want to use the spreadsheet, which has user info in it, for getting said user info. Every time I use the PSExcel module and use Import-XLSX to read the file, I get the following error:

"Exception calling '.ctor' with '1' argument(s): 'Can not open the package. Package is an OLE compound document"

It's a head scratcher and the only thing I could find was a reference to C# code. I'm not sure what to do, but it's weird that I can open it manually but not using PS. I haven't tried the ImportExcel module but I'm guessing it would be the same result. Any suggestions would be greatly appreciated.
Brian

I Get a Removed Records: Named range from /xl/workbook.xml part (Workbook) Error When I open a Excel File after I apply a "Set-CellValue -WorkSheet $Worksheet -Coordinates 'C4:C4' -Value "Test5""
Then Do A "$Excel | Save-Excel -Close"
I am Runnin Excel 2013 15.0.4927.1000
Log File

-
error108080_01.xml

I couldn't find an Add-Worksheet cmdlet so I created this one. Please see attached and let me know what you think. I based it off the Get-Worksheet.

The idea behind Add-Worksheet was to Get-Worksheet -Name 'MyWorksheet' and create it if it does not exist. I added parameters for passing a HashTable of Header Column Names and the Row Number to put the header column names into. It also Freezes the Header and adds a Light Gray background.

It needs to reviewed for code correctness and style and could be improved by added Parameters for -Color and -BackgroundColor, and others.

Regards, Brooks
Add-Worksheet.ps1.txt

Hey Warren -

I ran into a slight issue with the Import-XLSX Script. I have a business XLSX file that has a top row that has some images embedded and custom formatting/branding for my company that is customer facing. My headers actually start on row 2. So I went in and modified the Import-XLSX function to support changing the RowStart and HeaderStart with a couple parameters. Here is the quick and dirty code I modified to do this..

function Import-XLSX {
    <#
    .SYNOPSIS
        Import data from Excel

    .DESCRIPTION
        Import data from Excel

    .PARAMETER Path
        Path to an xlsx file to import

    .PARAMETER Sheet
        Index or name of Worksheet to import

    .PARAMETER Header
        Replacement headers.  Must match order and count of your data's properties.

    .PARAMETER FirstRowIsData
        Indicates that the first row is data, not headers.  Must be used with -Header.

    .PARAMETER Text
        Extract cell text, rather than value.

        For example, if you have a cell with value 5:
            If the Number Format is '0', the text would be 5
            If the Number Format is 0.00, the text would be 5.00 

    .EXAMPLE
        Import-XLSX -Path "C:\Excel.xlsx"

        #Import data from C:\Excel.xlsx

    .EXAMPLE
        Import-XLSX -Path "C:\Excel.xlsx" -Header One, Two, Five

        # Import data from C:\Excel.xlsx
        # Replace headers with One, Two, Five

    .EXAMPLE
        Import-XLSX -Path "C:\Excel.xlsx" -Header One, Two, Five -FirstRowIsData -Sheet 2

        # Import data from C:\Excel.xlsx
        # Assume first row is data
        # Use headers One, Two, Five
        # Pull from sheet 2 (sheet 1 is default)

    .NOTES
        Thanks to Doug Finke for his example:
            https://github.com/dfinke/ImportExcel/blob/master/ImportExcel.psm1

        Thanks to Philip Thompson for an expansive set of examples on working with EPPlus in PowerShell:
            https://excelpslib.codeplex.com/

    .LINK
        https://github.com/RamblingCookieMonster/PSExcel

    .FUNCTIONALITY
        Excel
    #>
    [cmdletbinding()]
    param(
        [parameter( Mandatory=$true,
                    ValueFromPipeline=$true,
                    ValueFromPipelineByPropertyName=$true)]
        [validatescript({Test-Path $_})]
        [string[]]$Path,

        $Sheet = 1,

        [string[]]$Header,

        [switch]$FirstRowIsData,

        [switch]$Text,

        [int]$RowStart,

        [int]$HeaderStart

    )
    Process
    {
        foreach($file in $path)
        {
            #Resolve relative paths... Thanks Oisin! http://stackoverflow.com/a/3040982/3067642
            $file = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($file)

            write-verbose "target excel file $($file)"

            Try
            {
                $xl = New-Object OfficeOpenXml.ExcelPackage $file
                $workbook  = $xl.Workbook
            }
            Catch
            {
                Write-Error "Failed to open '$file':`n$_"
                continue
            }

            Try
            {
                if( @($workbook.Worksheets).count -eq 0)
                {
                    Throw "No worksheets found"
                }
                else
                {
                    $worksheet = $workbook.Worksheets[$Sheet]
                    $dimension = $worksheet.Dimension

                    $Rows = $dimension.Rows
                    $Columns = $dimension.Columns
                }

            }
            Catch
            {
                Write-Error "Failed to gather Worksheet '$Sheet' data for file '$file':`n$_"
                continue
            }
            if ($RowStart) {
                $RowStart = $RowStart
            }
            else {
                $RowStart = 2
            }
            if($Header -and $Header.count -gt 0)
            {
                if($Header.count -ne $Columns)
                {
                    Write-Error "Found '$columns' columns, provided $($header.count) headers.  You must provide a header for every column."
                }
                if($FirstRowIsData)
                {
                    $RowStart = 1
                }
            }
            else
            {
                if ($HeaderStart) {
                    $Header = @( foreach ($Column in $HeaderStart..$Columns)
                    {
                        if($Text)
                        {
                            $worksheet.Cells.Item($HeaderStart,$Column).Text
                        }
                        else
                        {
                            $worksheet.Cells.Item($HeaderStart,$Column).Value
                        }
                    } )
                }
                else 
                {
                    $Header = @( foreach ($Column in 1..$Columns)
                    {
                        if($Text)
                        {
                            $worksheet.Cells.Item(1,$Column).Text
                        }
                        else
                        {
                            $worksheet.Cells.Item(1,$Column).Value
                        }
                    } )
                    }
            }

            [string[]]$SelectedHeaders = @( $Header | select -Unique )

            Write-Verbose "Found $(($RowStart..$Rows).count) rows, $Columns columns, with headers:`n$($Headers | Out-String)"

            foreach ($Row in $RowStart..$Rows)
            {
                $RowData = @{}

                if ($HeaderStart) {
                    $HeaderStartColumns = 0..($Columns - $HeaderStart)
                }
                else
                {
                    $HeaderStartColumns = 0..($Columns - 1)
                }

                foreach ($Column in $HeaderStartColumns)
                {
                    $Name  = $Header[$Column]
                    if($Text)
                    {
                        $Value = $worksheet.Cells.Item($Row, ($Column+1)).Text
                    }
                    else
                    {
                        $Value = $worksheet.Cells.Item($Row, ($Column+1)).Value
                    }

                    Write-Debug "Row: $Row, Column: $Column, Name: $Name, Value = $Value"

                    #Handle dates, they're too common to overlook... Could use help, not sure if this is the best regex to use?
                    $Format = $worksheet.Cells.Item($Row, ($Column+1)).style.numberformat.format
                    if($Format -match '\w{1,4}/\w{1,2}/\w{1,4}( \w{1,2}:\w{1,2})?')
                    {
                        Try
                        {
                            $Value = [datetime]::FromOADate($Value)
                        }
                        Catch
                        {
                            Write-Verbose "Error converting '$Value' to datetime"
                        }
                    }

                    if($RowData.ContainsKey($Name) )
                    {
                        Write-Warning "Duplicate header for '$Name' found, with value '$Value', in row $Row"
                    }
                    else
                    {
                        $RowData.Add($Name, $Value)
                    }
                }
                New-Object -TypeName PSObject -Property $RowData | Select -Property $SelectedHeaders
            }

            $xl.Dispose()
            $xl = $null
        }
    }
}

The following code works flawlessly when there is no file with the same name present and generates 2 different worksheets in one workbook:

$Object1 = [PSCustomObject]@{
    Name       = 'Bob'
    FamilyName = 'Smith'
}
$Object2 = [PSCustomObject]@{
    Year   = '2015'
    Result = 'Great!'
}
$ExcelFile = "$env:TEMP\Demo.xlsx"
$Object1 | Export-XLSX -Path $ExcelFile -WorksheetName 'People' -Table -AutoFit
$Object2 | Export-XLSX -Path $ExcelFile -WorksheetName 'Results' -Table -AutoFit
$Excel = New-Excel -Path $ExcelFile
$Excel | Close-Excel -Save

But when adding the switch '-ClearSheet' at the end of 'Export-XLSX', to overwrite an existing file, it's throwing the following error:

$Object1 | Export-XLSX -Path $ExcelFile -WorksheetName 'People' -Table -AutoFit -ClearSheet
$Object2 | Export-XLSX -Path $ExcelFile -WorksheetName 'Results' -Table -AutoFit -ClearSheet
# Error:
Exception calling "Add" with "2" argument(s): "Tablename is not unique"

When using the switch '-Force' it deletes both worksheets and only leaves the last worksheet. In this case the one named 'Results'. And throws no errors.

$Object1 | Export-XLSX -Path $ExcelFile -WorksheetName 'People' -Table -AutoFit -Force
$Object2 | Export-XLSX -Path $ExcelFile -WorksheetName 'Results' -Table -AutoFit -Force

The desired behavior when using '-Force' (I think) would be to only overwrite that specific worksheet and not to touch other worksheets in the workbook. Could this be corrected?

Hi, when I do Import-Module PSExcel -Force , it won't import the .dll. Here is the error I get.

Add-Type : Could not load file or assembly
'file:///C:\Users\nadvolodkin\Documents\WindowsPowerShell\Modules\PSExcel\lib\epplus.dll' or one of its
dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)

Had a request for querying specific cell ranges for values. Get-CellValue seems appropriate. Will need to add random headers, pull from the first row (potentially outside of specified range), or let the user specify their own.

When exporting data to a worksheet with a name longer then 31 characters you get this throw error:
At C:\Program Files\WindowsPowerShell\Modules\psexcel\1.0.2\Add-Table.ps1:151 char:13

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

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

  • CategoryInfo : OperationStopped: (Could not get w...th (parenthes
    :String) [], RuntimeException
  • FullyQualifiedErrorId : Could not get worksheets to search: Yet another tab with (parenthesis) could not be foun
    d. Valid worksheets:

Can be mitigated by adding
[ValidateLength(1,31)]
in line 206

Hi,

If I import a sheet, and the sheet has been edited (removed a lot of rows, now 30). The count of the rows stays the same as the original (600 lines). The lines are empty and give all sorts of errors.

$Users = Import-XLSX -Path "D:\ImportUsers\Import\ImportUsers.xlsx" -Header LastName, FirstName, DisplayName, UserName, UPN, Groups Write-Host "Number of users: $($Users.Count)" #(shows 600, should be 30)

I've tried to use a check for empty values, but this doesn't help.
$Usercount = 0 Foreach ($User in $Users) {If ($User.Username -or ($User.Username -ne "")) {$Usercount++}}

How can I show the correct number of rows. My workarround is to create a new sheet in the workbook, copy all the content I want and delete the old Sheet.

Thanks for the great efford, the module has saved us a lot of manual work and installations of Office on servers :)

I am trying to use PSExcel as a (much) faster way to pull data out of a shared workbook for use in my own analysis (PS) script. The problem is that someone always has the workbook open (for editing). Using the COM Object model, I was able to specify to open the workbook in read-only mode, so it didn't matter if someone else already had it open (in edit mode). Is there currently a way (or can a way be added) to open the workbook in read-only mode?

When use Export-xlsx to export to an existing workbook that has three worksheets. It will remove all other sheets except the "worksheet1". Any one has a fix for that?

Tested out code at zPSExcel.Intro.ps1 and added -ClearSheet after every Export-XLSX command.

However, when adding -ClearSheet after

Export-XLSX -Path C:\Temp\Files.xlsx -PivotRows Extension -PivotValues Length -ChartType Pie -ClearSheet

Following errors output:

Add-PivotTable : Skipping existing worksheet 'PivotTable1'
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\PSExcel\Export-XLSX.ps1:359 char:26

•             $Excel = Add-PivotTable @Params -Excel $Excel -WorkSheetName $Wo ...
  

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

  • CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
  • FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Add-PivotTable

You cannot call a method on a null-valued expression.
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\PSExcel\Export-XLSX.ps1:374 char:17

•             $Excel.SaveAs($Path)
  

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

  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

Whatever I try, I seem unable to suppress the Exporting verbose messages when importing the module.

$VerbosePreference = 'SilentlyContinue'
Import-Module PSExcel | Out-Null
VERBOSE: Exporting function 'Add-PivotChart'.
VERBOSE: Exporting function 'Add-PivotTable'.
VERBOSE: Exporting function 'Add-Table'.
VERBOSE: Exporting function 'Close-Excel'.
VERBOSE: Exporting function 'ConvertTo-ExcelCoordinate'.
VERBOSE: Exporting function 'Export-XLSX'.
VERBOSE: Exporting function 'Format-Cell'.
VERBOSE: Exporting function 'Get-CellValue'.
VERBOSE: Exporting function 'Get-Workbook'.
VERBOSE: Exporting function 'Get-Worksheet'.
VERBOSE: Exporting function 'Import-XLSX'.
VERBOSE: Exporting function 'Join-Object'.
VERBOSE: Exporting function 'Join-Worksheet'.
VERBOSE: Exporting function 'New-Excel'.
VERBOSE: Exporting function 'Save-Excel'.
VERBOSE: Exporting function 'Search-CellValue'.
VERBOSE: Exporting function 'Set-CellValue'.
VERBOSE: Exporting function 'Set-FreezePane'.

Would it be possible to add a param to specific the date format to use in Excel? The data I am passing to Export-XLSX is in UK format, but the produced XLSX file as the date format in US. Thanks

Due to line 132 in Import-XLSX.ps1
$RowEnd = $Rows + $RowStart - 1
when using -RowStart the $RowEnd variable gets incremented by the number of rows to skip.

If i have a total of 10 rows and want to skip the first 5 i will use -RowStart 6

But I still want to only import until row 10, so $RowEnd should still be 10 and not 15 (10 + 6 -1)

Otherwise I get my 5 rows as desired and then another 5 objects in the output array with every value as a $null.

Same deal with -ColStart

Updated code in Pull Request #51

First of all I would like to say that his is a really really cool module. I've tried first the one from dfinke but wanted some more flexibility so I switched to this one.

The problem I'm facing now is when using -AutoFit. I'm trying to achieve the following:

• Column 1, 2, 3 and 4 should have a fixed width of 30
• The other columns should be automatically fitted to their content
• Filter functionality should be on (so the little arrows are visible in the header)

The code I have now does all of that except for fitting the first 4 columns to a fixed size and the other columns to their content.

$DemoData = Get-Printer -ComputerName Server1 -Full

$DemoData | Export-XLSX -Path $File -WorksheetName 'Printers' -Table -Force -ClearSheet -AutoFit
$Excel = New-Excel -Path $File

$Worksheet = $Excel | Get-Worksheet -Name Printers
$Worksheet | Set-FreezePane -Row 2

$HeadParams = @{
    Header              = $True
    Bold                = $True
    Size                = '12'
    Color               = 'White'
    BackgroundColor     = 'ForestGreen' 
    HorizontalAlignment = 'Center'
    WrapText            = $True
}
$Worksheet | Format-Cell @HeadParams
$Worksheet | Format-Cell -StartColumn 1 -EndColumn 4 -AutofitMaxWidth 30 -AutofitMinWidth 30
$Excel | Close-Excel -Save

I've also tried by defining the other columns so they autofitted, but no luck:

$Worksheet | Format-Cell -StartColumn 5 -AutoFit

Thank you for your help.

Hi,
I am trying to use PSExcel for refreshing pivots present on my excel, but unable to achieve it. Basically my server will no more have MS Excel app on it and PSExcel was only option available to try out this.
It would be great, if anyone who has solution to it!
Thank you in advance!

Is it possible to add a parameter to name the worksheet where the PivotTable is produced?

In the module of dfinke they use the following::

Get-Process | Export-Excel $File  -WorkSheetname memory `
    -IncludePivotTable -PivotRows Company -PivotData PM `
    -IncludePivotChart -ChartType PieExploded3D -Show

In this example the Worksheet with the data is named 'memory' and the worksheet with the PivotTable is named 'memoryPivotTable'.

When we do the same with our function, the worksheet is called 'memory' but the PivotTable is called 'PivotTable1':

Get-Process | Export-XLSX -Path $File  -WorksheetName memory `
    -PivotRows Company -PivotValues PM `
    -ChartType PieExploded3D

It would be nice to have a parameter to choose a custom name for the worksheet of the PivotTable.

As an extra bonus it would also be great to have the worksheet with the 'PivotTable' as the active sheet, when you open the file for the first time.

Solution:
#initialize stuff
$RowIndex = 2
Try
{
if ($Excel -eq $Null) {
$Excel = New-Object OfficeOpenXml.ExcelPackage($Path) -ErrorAction Stop
}

Can we export data into already exists Excel template having formulas?

I'm a new PowerShell user, experimenting with porting some of my Perl utility scripts to PS. One such script reworks a busy CSV report to a well-formatted xlsx to be mailed to my office manager. Pretty fantastic use case for your module -- thanks for writing it!

I have almost everything going the way I want, with the notable exception of NumberFormat. It's a mileage report for reimbursement, and I have 5 columns in the header: Date, Begin, End, Mileage, Customer. Date obviously should contain datetime values (inbound format is mm/dd/yy); Begin and End are integers; Mileage is decimal. Customer can stay text.

My current formatting code looks like this:

$newCsv = Import-Csv $inputFilename -Delimiter ','
$newCsv | Export-XLSX -Path $fullOutputPath -AutoFit -WorksheetName "$monthName Mileage"
$excel = New-Excel -Path $fullOutputPath
$totalCell = Search-CellValue -Excel $excel { $_ -eq "Total" }
$excel | Get-Worksheet | Format-Cell -Header -Bold $true
$excel | Get-Worksheet | Format-Cell -StartRow $totalCell.Row -StartColumn $totalCell.Column -EndRow $totalCell.Row -EndColumn ($totalCell.Column + 1) -Bold $true -Underline $true
$excel | Get-Worksheet | Format-Cell -StartRow 2 -StartColumn 1 -NumberFormat "datetime"
$excel | Get-Worksheet | Format-Cell -StartRow 2 -StartColumn 2 -EndRow ($totalCell.Row - 1) -EndColumn ($totalCell.Column + 1) -NumberFormat "single"
$excel | Get-Worksheet | Format-Cell -StartRow $totalCell.Row -StartColumn ($totalCell.Column + 1) -NumberFormat "decimal"

In my searching around for ideas, I came across this Reddit post that deals with almost my exact use case. You responded, then the OP described the issue I'm seeing: The resulting file shows all rows as 'number stored as text'...until I go in and correct it as a number type, no numeric formatting applies.

I looked around at the code in the repo, but I'm not familiar enough to PS to know if I'm using the NumberFormat parameter correctly. I'd sure appreciate a little nudge in the right direction.

Hi,

I get the following error when I run PS workflow as an InlineScript within Azure Automation runbook. It works fine when run as a PS file: The usage is: $vmScheduleObject | select 'VM Name', 'ResourceGroup Name', 'Shutdown Schedule', 'Schedule Expires' | Export-XLSX -Path $blob -Table -AutoFit -Force

Exception calling "AutoFitColumns" with "0" argument(s): "A generic error occurred in GDI+."
At Get-VMOnDemandSchedule:3 char:3
+
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ExternalException

Any advise on this would be great.

When using a TimeSpan in an object property the following error is produced:

Exception calling "AutoFitColumns" with "0" argument(s): "Not a legal OleAut date."

Which originates from the function 'Export-XLSX' in almost the last line of the code:

if($AutoFit)
{
$WorkSheet.Cells[$WorkSheet.Dimension.Address].AutoFitColumns()
}

Reproducible as following:

$DemoData = [PSCustomObject] @{
     Name = "Problem object"
     NoProblem = "6.04:53:33.2200000"
     Problem = [TimeSpan]::FromSeconds(53971559/100)
 }
 $File = "\\server1\c$\temp\Demo.xlsx"
 $DemoData | Export-XLSX -Path $File -WorksheetName 'Printers' -Table -AutoFit -Force

I don't know if this is a bug in the code, but when using 'Export-Csv' this isn't a problem.

Hi,

Do you have a solution to create an external hyperlink with PSEXCEL ?

I tested :
$Worksheet.SetValue(1,1,'=LIEN_HYPERTEXTE("X:\XXX\XXX.docx";"TEXT")') --> OK, the expression writes the text but the hyperlink is not created.
$Worksheet.Hyperlinks.Add($Worksheet.Cells.Item(1,1),'"X:\XXX\XXX.docx","","","TEXT"') --> Nok

Have you got an idea ?

Thanks !

Let's pretend we have an array from Get-ADComputer. If the first object has a lastLogonTimestamp, but some other objects do not, we still loop through and add data, now with the incorrect column index given the differing property count.

Ideally, we should only see consistent input data, but, a user (like myself this morning) might run into a case where they see very strange results.

Potential solutions:

• Validate property count on every row. Ideally using a switch (off-by-default) given the performance impact to large data sets. Exclude rows that don't match (include their data in verbose/error output perhaps)
• Ignore. Users will investigate and hopefully normalize their data
• Normalize. If the first item has properties a,b,c,d, and the second item has a,b,d, normalize item 2 by including c with $null. This would be both expensive, and perhaps the user doesn't intend to have their data normalized.