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.

PS-2-Excel

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

Annotations

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

Export-Xlsx

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.

export-xlsx-1

Now lets try to add a chart.

The result looks like this

export-xlsx-2

You can also use multiple values in the chart.

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

export-xlsx-3b

And on a separate worksheet the chart.

export-xlsx-3

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.

export-xlsx-ordered

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

Enjoy !

33 Comments

    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.

      LucD

      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!

    Kelley

    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.

      Kelley

      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.

        LucD

        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)
    C:\Windows\System32\config\systemprofile\Desktop

    (64Bit)
    C:\Windows\SysWOW64\config\systemprofile\Desktop

    Here’s the thread where I found this little nugget:
    http://social.technet.microsoft.com/Forums/windowsserver/en-US/aede572b-4c1f-4729-bc9d-899fed5fad02/run-powershell-script-as-scheduled-task-that-uses-excel-com-object

      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?

    Thanks!

      LucD

      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
        $ws.Application.ActiveWindow.FreezePanes=$true
        }

        ### 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
        $wb.SaveAs($Path,51,$Password)

        Hope those are handy for someone. Thanks again LucD!

          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=$ws.range(“A:A”)
    $CurrentColumn.NumberFormat = “0”

    Kenneth Jensen

    Hi,
    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

    Jon

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

    Reza

    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?

    Thanks,

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

    Andy

    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?

    Valentin

    Hello ,

    I have a problem with Excel System is:
    Win2k8R2
    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
    loaded.
    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
    Valentin

    Sean McCown

    Luc,
    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?

      LucD

      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.

    JB

    Luc,

    Trying to combine, your code from:
    http://communities.vmware.com/thread/271902?start=0&tstart=0

    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 ?

      admin

      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.

    Steve

    @LucD
    Thanks sir that works like a charm!!!

    Steve

    @Steve
    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.

      LucD

      @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

    Steve

    @Jules

    @LucD
    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!!!

      LucD

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

    Jules

    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.

    eg.:

    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

      LucD

      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 *

*
*

Buy the Book