Export-Xlsx, the sequel, and ordered data

Some time ago I published the Export-Xls function to the world in my Beyond Export-Csv: Export-Xls post. And it has been one of the more popular functions on my blog.


Just before Christmas 2012 I got a comment from Gilbert where he informed me that he had written an Export-Xlsx function and had borrowed some of the parameters that were on the original Export-Xls function. We exchanged a couple of emails with suggestions and ideas, and yesterday the finalised Export-Xlsx function was posted on Gilbert’s ITPilgrims blog (which you should follow btw) in his Export-Xlsx post.

In this post I will publish this new Export-Xlsx function with some annotations, and I will also show how you can now export your data in the order you want, provided you’re using PowerShell v3.

The script


Line 76-80: Parameter validation code block that checks if the requested file is of the type XLS or XLSX

Line 81: The default save location is in the user’s home drive and the default filename is Export.Xslx

Line 82: The default worksheetname is the name of the file

Line 84-102: The enumerated accepted charttypes.

Line 117-128: An internal helper function that converts a number to an Excel column name. For example the number 28 is converted to the column AB.

Line 133-135: Copy the data that was passed to the function to the internal WorkingData array. Note that the function can be used in a pipeline, so the Process block will collect all data passed through the pipeline into this WorkingData array.

Line 138-163: The input data is collected into a 2-dimensional array.

Line 169-175: If the function discovers that the local Excel installation is not Excel 2010, then the filetype will be changed to XLS.

Line 178-184: This part of the code configures the requested location of the worksheet. The accepted values are begin and end.

Line 186-188: If the Force switch was not used in the call and the requested file already exists, the function will add a timestamp to the name, to guarantee a unique filename.

Line 201-224: Handles the worksheet Title and the Header Colour.

Line 228-231: Handles the Borders switch.

Line 233: Handles the AutoFilter switch

Line 235: Handles the AutoFit switch

Line 237-248: Creates a chart, if requested, and handles the ChartOnNewSheet switch.

Line 254-259: This avoids having to kill the Excel process by clearing all the allocated COM object

Sample Usage


The Export-Xlsx function provides several of the parameters that were there in the Export-Xls function, and adds a couple of new ones. Let’s explore.

This produces an XLSX file with 1 worksheet, notice the default worksheet name and how Autofit, Autofilter and Borders are selected by default.


Now lets try to add a chart.

The result looks like this


You can also use multiple values in the chart.

And this produces an XLSX file with on one worksheet the data


And on a separate worksheet the chart.


Ordered data

With PowerShell v3 the [ordered] cast was made available as an accelerator. The functionality that this brings was something that was asked by many users in the previous PowerShell versions.

To use this accelerator is quite easy. The following code snippet will show you how it is done and what the effect on the results is.

Both XLSX files next to each other show the difference, without the [ordered] cast the order in which the properties appear is not controllable.


With the [ordered] cast, the properties appear in the same order as we specified them in the script. A great improvement !

Enjoy !



    Need your advice on this.
    I have a XLSX file with table, and a bar chart within a sheet.
    How should i export the both table & barchart into HTML format and email out?

    I have done on CSV files with table on COM objects to HTML without issues, but stuck with XLSX.


      I’m not sure if that is possible at all, especially the charts.
      For the data you can read the XLSX worksheet into an array, and then convert that array to a HTML table.


    Great Work!
    Question: Is it possible for this to work on a scheduled task?
    The same script I run mannualy and it works, but when it runs on a scheduled task, it doesn’t.


      Hi Everton,
      Since I wrote that post, there has been a new module, that does working with Excel files a lot better, and has more options.
      Have a look at the ImportExcel module.
      One of the big advantages in this module is that it doesn’t need Excel to be installed on the station where you use the module.

      Also for your scheduled task, I’m sure the ImportExcel module will do a much better job than my function.

    Mike Cooper

    This is fantastic, I have an old VB script that I can replace and it is 300% faster in powershell. The only problem that I have is that a couple of my columns have leading zeros and it is stripping out those 0 (zeros). Any suggestions.


      Not sure if it’s a PowerShell thing or Excel’s default cell formatting.
      Did you already try casting those values explicitly to [string] before you do the Export-Xlsx ?

    Jonathan Sinden

    Splendid. Just plain splendid! Thank you very much for this!


    Is there any way to set the header row or column headers to auto wrap or insert a break as you can do with an Alt-Enter. I’d like to shrink the column width to get more data on the screen without side to side scrolling. The actual data in the column takes up much less space that the header.


      I found a solution to splitting the header row. It not solved in the export-xlsx script but in the script that generates the data that gets piped to this great script. All I had to do to break the heading into more than one rows was insert n. So to break the text "Total Mailbox Size" I changed it to "TotalnMailbox`nSize”. Worked great.

      I’m sure everyone else in the world already knows this trick but it took a bit for me to find it.


        Thanks for sharing that Kelley, that is a great tip.

    Luis Esteves

    Hey, I wanted to add that if anyone is having trouble with Export-Xls running from Task Scheduler as a logged out user, it’s a bug in the Excel COM object but it is easily fixed by creating a couple of folders:

    (32Bit, always)


    Here’s the thread where I found this little nugget:

      Jonathan Sinden

      Excellent! Spent the last 2 hours trying to fix this issue! Ty very much!

    Luis Esteves

    Hi! This script is amazing and has let me automate a ton of reports I used to have to do manually. Thank you! I even added a parameter to pass a password to $wb.SaveAs. 🙂

    I am having one minor issue with the new version that I can’t figure out. All of my reports include the rows RowError, RowState,Table, ItemArray and HasErrors. I can’t figure out where these are coming from. They didn’t happen with the previous version of this script. Any hints?



      Hi Luis,
      Glad you like the function.

      Is the data you are exporting by any chance coming from a SQL query ?
      Those properties seem to get added in that case.
      With an ExcludeProperty you can easily remove those properties.

      $array | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | Export-Xlsx

      Does that fix the issue ?

        Luis Esteves

        It did work! Thanks for that tip, that was perfect. Thank you! 🙂

        A couple of mods that I did to the script that may be handy for others are:

        ### Freeze the top header row:

        # Added to the parameters list
        [Switch] $FreezeTopRow = $True

        # Inserted at line 240
        If ($FreezeTopRow)
        $ws.Application.ActiveWindow.SplitColumn = 0
        $ws.Application.ActiveWindow.SplitRow = 1

        ### Password Protect the Spreadsheet

        # Added to the parameters list
        [string] $Password

        # Replaced the $wb.SaveAs line with
        # Forced to XLSX Format (type 51) as having a variable here causes an error and I’m not sure why yet

        Hope those are handy for someone. Thanks again LucD!


          Glad it fixed the issue.

          Thanks for the mods, they are great.
          I will update the function.

    Kenneth Jensen

    I had a problem where one column of the Excel file I created had the scientific notation (7,31776E+12), because of long numbers.

    To avoid this, I inserted this code, just before line 252 (just before saving)

    This is hard coded to one column, but can be easily changed to be a parameter instead – I just don’t have the need for that – yet 🙂

    #Set column a to number format:
    $CurrentColumn.NumberFormat = “0”

    Kenneth Jensen

    GREAT script, which I have used bofore, but now I have a problem with it.
    I extract an itemnumber list, and because of the long numbers, they are written as scientific in the excel(e.g. 7,31776E+12)

    I would like it to be written as text in excel. How can I do this?

    Best regards
    Kenneth Jensen


    Hi Luc, this is very useful, would there be anyway to have the Excel start row as a parameter? e.g import from A3?


    Hi LucD,

    Thanks for the script it looks like it can be very handy. Though I’m having trouble plotting some data to a graph using the following. The output of this graphs both Name and Count, when I only want count. I tried casting Name to a string but that didn’t work. Any tips?


    (Get-CIVM | group cpucount) | select Name, Count | export-xlsx -path c:\tmp\foo12.xlsx -AppendWorksheet:$true -WorksheetName “CPU Count” -ChartType xlColumnClustered -force:$true


    Great script! I have lots of csv files that I run a convert to xlsx on every day and this has replaced those jobs.
    I am in need for the script to overwrite the original *.xlsx file everyday with the ‘AppendWorksheet’ switch applied and the ‘Force’ switch does not apply when using the ‘AppendWorksheet’ switch.
    Is there a way to overcome this?


    Hello ,

    I have a problem with Excel System is:
    Excel 2010 14.0.4760.1000
    When I execute:
    > Get-Process |
    >> Select Name,Id,Handles |
    >> Export-Xlsx -Path C:\report.xlsx

    I’m getting this error:
    Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat]: make sure that the assembly containing this type is
    At line:158 char:67
    + $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat] <<<< ::xlWorkbookNormal
    + CategoryInfo : InvalidOperation: (Microsoft.Offic…el.XLFileFormat:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

    The good news are that it is working on my Win 7 64bit system with Excel 2010 14.0.6129.5000 but i want to make it work on my LAB system on which i will test my reports

    Can you tell me what are the software requirements to make this work.

    I have the same issue with Export-Xls command.

    Best Regards

    Sean McCown

    Actually, I figured out how to add a worksheet, so my bad…
    but is there a way to add data to a worksheet already created?


      Hi Sean, I’m afraid that feature is not available (yet).
      As a bypass, for now, you could use Gilbert’s Import-Xlsx function to read the XLSX file, make your changes to 1 or more worksheets, and then use the Export-Xlsx to write the new data to a new XLSX file.
      Cumbersome I know, but that’s for now the only way I’m afraid.

    Sean McCown

    Hey Luc… first, thanks so much for this. I was staring down the barrel of a long assignment until this came along. And like everyone else it seems it’s almost there, but I’ve got some basic issues.
    1. I had to go to this current version to get the cols ordered the way I need them. Doing it with the select-object didn’t work for me at all with the previous version. I don’t know why, but it didn’t. That’s fixed now though.

    2. Now I have a hard time adding worksheets. I don’t get an error, but before I could just keep writing worksheets to the same file and name them all whatever I wanted. Now I can only use -AppendWorksheet and it doesn’t allow me to give it a name. And if I try to just write the worksheet without that param it creates a new dated file. That’s not what I need at all. I need to run several queries and put the results into different worksheets. And it would be really nice to be able to add rows to a worksheet I’ve already created.

    Again, this is excellent work, but if you could just merge the 2 versions you’d really have something here.



    Trying to combine, your code from:

    But i’m always stuck on a null-valued-expression, while the individual stats give value’s back.
    It’s seems that when putting data into the excel it fails.
    Can you help me pinpoint the problem ?


      Hi JB, it looks as if you might be suffering from the problem described in MS KB320369.
      Can you check ?
      Btw, your script runs fine in my test environment.


    Thanks sir that works like a charm!!!


    Correction to my previous comments. This creates a new spreadsheet for each data collection I feed to the Export-Xlsx function rather than creating a new worksheet within the base spreadsheet. My guess is that something is amiss with the [Switch] $AppendWorksheet routine but it is strictly a guess at this time.


      @Steve, you should be using the AppendWorksheet switch if you want to create multiple worksheets in the same XLSX file.
      See this example

      Get-Process |
      Select ProcessName,Description,Path |
      Export-Xlsx -Path D:\report.xlsx -WorksheetName Sheet1 -AppendWorksheet

      Get-Process |
      Select ProcessName,VirtualMemorySize,WorkingSet |
      Export-Xlsx -Path D:\report.xlsx -WorksheetName Sheet2 -AppendWorksheet -SheetPosition end -ChartType xl3DColumnClustered -ChartOnNewSheet



    Luc, This certainly is presenting my data in the columnar order i desired however it is not creating a new worksheet for each data collection I am creating as the old Function Export-Xls did. Sorry to be such a pain but this is SO close to being perfect!!! Thanks for all the help!!!


      @Steve, can you send me the script you are using, so I can have a look ?
      Send it to lucd (at) lucd (dot) info

    Travis Quinnelly

    Thanks Luc and Gilbert. I’ve got some usage for this coming up soon.


    A note on the [ordered] cast. If you are, for any reason, not able to use PowerShell v3, you could still pipe through Select-Object to set the column order.


    Get-Process | %{
    $object = @{
    Name = $_.Name
    Handles = $_.Handles
    Id = $_.Id
    ProcessName = $_.ProcessName
    New-Object PSObject -Property $object
    } |
    Select-Object Name,Handles,Id,ProcessName |
    Export-Xlsx -Path C:\report-is-now-ordered.xlsx


      Thanks, that is indeed the fix/bypass one had to use in pre-v3 PowerShell.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.