James O'Neill's Blog

December 12, 2017

Using the import Excel Module: Part 3, Pivots and charts, data and calculations

Filed under: Uncategorized — jamesone111 @ 4:43 pm

In the previous post I showed how you could export data to an XLSx file using the Export-Excel command in Doug Finke’s ImportExcel module (Install it from the PowerShell gallery!). The command supports the creation of Pivot tables and Pivot charts. Picking up from where part 2 left off, I can get data about running processes, export them to a worksheet and then set up a pivot table

$mydata = Get-Process | Select-Object -Property Name, WS, CPU, Description, Company, StartTime
$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
   -IncludePivotTable -PivotRows "Company" -PivotData @{"WS"="Sum"} -show

clip_image002

To add a pivot chart the command line becomes
$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
    
-IncludePivotTable -PivotRows "Company" -PivotData @{"WS"="Sum"} `
    
-IncludePivotChart -ChartType Pie -ShowPercent -show

clip_image004

The chart types are also suggested by intellisense, note that some of them don’t support -ShowPercent or ‑ShowCategory options and “bad” combinations will result in an error on opening Excel. Re-creating existing Pivot charts can cause an error as well.

There is an alternative way of creating Pivot tables and charts –which is particularly useful when we want more than one in the same workbook

del .\demo.xlsx

$xl = $mydata | Export-Excel -Path .\demo.xlsx -WorkSheetname "Processes" -PassThru

$Pt1 = New-PivotTableDefinition -PivotTableName "WS"  -PivotData @{"WS" ="Sum"} -SourceWorkSheet "Processes" `
          -PivotRows Company -IncludePivotChart -ChartType ColumnClustered -NoLegend
$Pt2 = New-PivotTableDefinition -PivotTableName "CPU" -PivotData @{"CPU"="Sum"} -SourceWorkSheet "Processes" `
          -PivotRows Company -IncludePivotChart -ChartType ColumnClustered -NoLegend

$xl = Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -PivotTableDefinition $Pt1 -PassThru
Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -PivotTableDefinition $Pt2 -Show

clip_image006

New-PivotTableDefinition builds the table definition as a hash table – we could equally well write a large hash table with multiple pivots defined in it, like this

del .\demo.xlsx

$mydata | Export-Excel -Path .\demo.xlsx -WorkSheetname "Processes" -Show -PivotTableDefinition @{
    "WS" = @{"SourceWorkSheet"   = "Processes"      ;
             "PivotRows"         = "Company"        ;
             "PivotData"         = @{"WS"="Sum"}    ;
             "IncludePivotChart" = $true            ;
             "ChartType"         = "ColumnClustered";
             "NoLegend"          = $true};
   "CPU" = @{"SourceWorkSheet"   = "Processes"      ;
             "PivotRows"         = "Company"        ;
             "PivotData"         = @{"CPU"="Sum"}   ;
             "IncludePivotChart" = $true            ;
             "ChartType"         = "ColumnClustered";
             "NoLegend"          = $true }
}

Export-Excel allows [non-pivot] charts to be defined and passed as a parameter in similar same way -in the following example we’re going to query a database for a list of the most successful racing drivers, the SQL for the query looks like this:
$Sql = "SELECT TOP 25 WinningDriver, Count(RaceDate) AS Wins
        FROM   races 
        GROUP  BY WinningDriver  
        ORDER  BY count(raceDate) DESC"

Then we define the chart and feed the result of the query into Export-Excel (I’m using my GetSQL module from the PowerShell Gallery for this but there are multiple ways )
$chartDef = New-ExcelChart -Title "Race Wins" -ChartType ColumnClustered
               -XRange WinningDriver -YRange Wins -Width 1500 -NoLegend -Column 3

Get-SQL $Sql | Select-Object -property winningDriver, Wins |
  Export-Excel -path .\demo2.xlsx -AutoSize -AutoNameRange -ExcelChartDefinition $chartDef -Show

The important thing here is that the chart definition refers to named ranges in the spreadsheet – “Winning Driver” and “Wins” and the Export-Excel command is run with –AutoNameRanges so the first column of is a range named “Winning Driver” and the second “Wins” – you can see in the screen shot “Wins” has been selected in the “Name” box (underneath the File menu) and the data in the Wins column is selected. The chart doesn’t need a legend and positioned the right of column 3

clip_image008

I found that he EEPLUS object which Doug uses can insert a Data table object directly into a worksheet, which should be more efficient, and it also saves using a select-object command to remove the database housekeeping properties which are in every row of data as I had to do in the example above. It didn’t take much to a command to Doug’s module to put SQL data into a spreadsheet without having to pipe the data into Export-Excel from another command. And I cheated by passing the resulting object through to Export-Excel so that I could use parameters found in Export-Excel and pass the Worksheet object and parameters on and get Export-Excel to finish the job so I write something like this:
Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo2.xlsx -WorkSheetname "Winners" `
        -AutoSize  -AutoNameRange -ExcelChartDefinition $chartDef -Show
  

In this example I use an existing session with a database – the online help shows you how to use different connection strings with ODBC or the SQL Server native client. 

I also added commands to set values along a row or down a column – for an example we can expand the racing data to not just how many wins, but also how many fastest laps and how many pole positions, export this data and use the -Passthrough switch to get an Excel Package object back
$SQL = "SELECT top 25 DriverName,         Count(RaceDate) as Races ,
                      Count(Win) as Wins, Count(Pole)     as Poles,
                      Count(FastestLap) as Fastlaps
        FROM  Results
        GROUP BY DriverName
        ORDER BY (count(win)) desc"

$Excel = Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo3.xlsx `
            -WorkSheetname "Winners" -AutoSize -AutoNameRange -Passthru

Having done this, we can add columns to calculate the ratios of two pairs of existing columns

$ws = $Excel.Workbook.Worksheets["Winners"]
Set-Row    -Worksheet $ws -Heading "Average"     -Value {"=Average($columnName`2:$columnName$endrow)"}
`
              -NumberFormat "0.0" -Bold
Set-Column -Worksheet $ws -Heading "WinsToPoles" -Value {"=D$row/C$row"} -Column 6 -AutoSize -AutoNameRange
Set-Column -Worksheet $ws -Heading "WinsToFast"  -Value {"=E$row/C$row"} -Column 7 -AutoSize -AutoNameRange
Set-Format -WorkSheet $ws -Range "F2:G50" -NumberFormat "0.0%"

In the examples above the value parameter is a Script block, when this is evaluated $row and $column are available so if the value is being inserted in row 5, {"=E$row/C$row"} becomes =E5/C5
The script block can use $row , $column (current row and column numbers) $columnName (current column letter), $StartRow/$EndRow $StartColumn/$EndColumn (column and row numbers)

If the value begins with “=” it is treated as a formula rather than a value; we don’t normally want to put in a fixed formula – without the “=” the value inserted down the column or across the row will be a constant

The set-Column command supports range naming, and both commands support formatting – or we can use the ‑PassThru switch and pipe the results of setting the column into Set-Format. There seems to be a bug in the underlying library where applying number formatting to column after formatting a row applies the same formatting to the column and to the row from the previous operation. So, the example above uses a third way to apply the format which is to specify the range of cells in Set-Format.
Finally we can output this data, and make use of the names given to the newly added columns in a new chart.

$chart = New-ExcelChart -NoLegend -ChartType XYScatter -XRange WinsToFast -YRange WinsToPoles `
           -Column 7 -Width 2000 -Height 700

Export-Excel -ExcelPackage $Excel -WorkSheetname "Winners"-Show -AutoSize -AutoNameRange `
         -ExcelChartDefinition $chart

clip_image010

So there you have it; PowerShell objects or SQL data goes in – possibly over multiple sheets; headings and filters get added, panes arranged: extra calculated rows and columns are inserted , and formatting applied, pivot tables and charts inserted – and if Excel itself is available you can export them. No doubt someone will ask before too long if I get the the charts out of Excel and into PowerPoint slides ready for a management meeting … And since the all of this only works with XLSX files, not legacy XLS ones there might me another post soon about reading those files.

Advertisements

December 11, 2017

Using the Import Excel module part 2: putting data into .XLSx files

Filed under: Office,Powershell — jamesone111 @ 3:55 pm

This is third of a series of posts on Excel and PowerShell – the first on getting parts of an Excel file out as images wasn’t particularly tied to the ImportExcel Module, but the last one, this one and next one are.  I started with the Import Command – which seemed logical given the name of the module; the Export command is more complicated, because we may want to control the layout and formatting of the data, add titles, include pivot tables and draw charts;. so I have split it into two posts. At its simplest the command looks like this :

Get-Process | Export-Excel -Path .\demo.xlsx -Show

This gets a list of processes, and exports them to an Excel file; the -Show switch tells the command to try to open the file using Excel after saving it. I should be clear here that import and export don’t need Excel to be installed and one of the main uses is to get things into Excel format with all the extras like calculations, formatting and charts on a computer where you don’t want to install desktop apps; so –Show won’t work in those environments.  If no –WorksheetName parameter is give the command will use “Sheet1”.

Each process object has 67 properties and in the example above they would all become columns in the worksheet, we can make things more compact and efficient by using Select-Object in the command to filter down to just the things we need:

Get-Process | Select-Object -Property Name,WS,CPU,Description,StartTime |
Export-Excel -Path .\demo.xls -Show
 

Failed exporting worksheet 'Sheet1' to 'demo.xls':
Exception calling ".ctor" with "1" argument(s):
"The process cannot access the file 'demo.xls' because it is being used by another process."

This often happens when you look at the file and go back to change the command and forget to close it – we can either close the file from Excel, or use the -KillExcel switch in Export‑Excel – from now on I’ll use data from a variable

$mydata = Get-Process | Select-Object -Property Name, WS, CPU, Description, Company, StartTime
$mydata | Export-Excel -KillExcel -Path .\demo.xlsx -Show

This works, but Export-Excel modifies the existing file and doesn’t remove the old data – it takes the properties of the first item that is piped into it and makes them column headings, and writes each item as a row in the spreadsheet with those properties. (If different items have different properties there is a function Update-FirstObjectProperties to ensure the first row has every property used in any row). If we are re-writing an existing sheet, and the new data doesn’t completely cover the old we may be left with “ghost” data. To ensure this doesn’t happen, we can use the ‑ClearSheet option

$mydata | Export-Excel -KillExcel -Path .\demo.xlsx -ClearSheet -Show

clip_image002

Sometimes you don’t want to clear the sheet but to add to the end of it, and one of the first changes I gave Doug for the module was to support a –Append switch, swiftly followed by a change to make sure that the command wasn’t trying to clear and append to the same sheet.

We could make this a nicer spreadsheet – we could make it clear the column headings look like headings, and even make them filters, we can also size the columns to fit…

$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
             -BoldTopRow
-AutoSize -Title "My Processes" -TitleBold -TitleSize 20 -FreezePane 3 -AutoFilter -Show

clip_image004

The screen shot above shows the headings are now in bold and the columns have been auto sized to fit. A title has been added in bold, 20-point type; and the panes have been frozen above row 3. (There are options for freezing the top row or the left column or both, as well as the option used here –FreezePane row [column]) and filtering has been turned on.

Another way to present tabular data nicely is to use the -Table option

$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet –BoldTopRow    -AutoSize `
       -TableName table -TableStyle Medium6 -FreezeTopRow -show

clip_image006

“Medium6” is the default table style but there are plenty of others to choose from, and intellisense will suggest them

clip_image008

Sometimes it is helpful NOT to show the sheet immediately, and one of the first things I wanted to add to the module was the ability to pass on an object representing the current state of the workbook to a further command, which makes the following possible:

$xl = $mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" `
    
-ClearSheet -AutoSize -AutoFilter -BoldTopRow –FreezeTopRow -PassThru

$ws = $xl.Workbook.Worksheets["Processes"]

Set-Format -WorkSheet $ws -Range "b:b" -NumberFormat "#,###"   -AutoFit
Set-Format -WorkSheet $ws -Range "C:C" -NumberFormat "#,##0.00" -AutoFit
Set-Format -WorkSheet $ws -Range "F:F" -NumberFormat "dd MMMM HH:mm:ss" -AutoFit

The first line creates a spreadsheet much like the ones above, and passes on the Excel Package object which provides the reference to the workbook and in turn to the worksheets inside it.
The example selected three columns from the worksheet and applied different formatting to each. The module even supports conditional formatting, for example we could add these lines into the sequence above

Add-ConditionalFormatting -WorkSheet $ws -Range "c2:c1000" -DataBarColor Blue
Add-ConditionalFormatting -WorkSheet $ws -Range "b2:B1000" -RuleType GreaterThan
`
           
-ConditionValue '104857600'  -ForeGroundColor "Red" -Bold

The first draws data bars so we can see at glance what is using CPU time and the second makes anything using over 100MB of memory stand out.

Finally, a call to Export-Excel will normally apply changes to the workbook and save the file, but there don’t need to any changes – if you pass it a package object and don’t specify passthrough it will save your work, so “Save and Open in Excel” is done like this once we have put the data in a formatted it the way we want.

Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -Show

clip_image002[1]

In the next post I’ll look at charts and Pivots, and the quick way to get SQL data into Excel

December 5, 2017

Using the Import-Excel module: Part 1 Importing

Filed under: Office,Powershell — jamesone111 @ 9:15 am

The “EEPLus” project provides .NET classes to read and write XLSx files without the need to use the Excel object model or even have Excel installed on the computer (XLSx files, like the other  Office Open XML Format are actually .ZIP format files, containing XML files describing different aspects of the document – they were designed to make that sort of thing easier than the “binary” formats which went before.)   Doug Finke, who is well known in the PowerShell community, used EEPlus to build a PowerShell module named ImportExcel which is on GitHub and can be downloaded from the PowerShell gallery (by running Install-Module ImportExcel on PowerShell 5 or PS4 with the Package Management addition installed) As of version 4.0.4 his module contains some of my contributions. This post is to act as an introduction to the Export parts of the module that I contributed to; there are some additional scripts bundled into the module which do require Excel itself but the core Import / Export functions do not. This gives a useful way to get data on a server into Excel format, or to provide users with a work book to enter data in an easy to use way and process that data on the server – without needing to install Microsoft Office or translate to and from formats like .CSV.

The Import-Excel command reads data from a worksheet in an XLSx file. By default, it assumes the data has headers and starts with the first header in Cell A1 and the first row of data in row 2. It will skip columns which don’t have a header and but will include empty rows. If no worksheet name is specified it will use the first one in the work book, so at its simplest the command looks like :
Import-Excel -Path .\demo.xlsx  

It’s possible that the worksheet isn’t the first sheet in the workbook and/or has a title above the data, so we can specify the start point explicitly
Import-Excel -Path .\demo.xlsx -WorkSheetname winners -StartRow 2  

We can say the first row does not contain headers and either have each property (column) named P1, P2, P3 etc, by using the ‑NoHeader switch or specify header names with the -HeaderName parameter like this

Import-Excel -Path .\demo.xlsx -StartRow 3 -HeaderName “Name”,"How Many"

The module also provides a ConvertFrom-ExcelSheet command which takes -Encoding and -Delimiter parameters and sends the data to Export-CSV with those parameters, and a ConvertFrom-ExcelToSQLInsert command which turns each row into a SQL statement: this command in turn uses a command ConvertFrom-ExcelData, which calls Import-Excel and then runs a script block which takes two parameters PropertyNames and Record.

Because this script block can do more than convert data, I added an alias “Use-ExcelData” which is now  part of the module and can be used like this
Use-ExcelData -Path .\NewUsers.xlsx -HeaderRow 2 -scriptBlock $sb

If I define the script block as below, each column becomes a parameter for the New-AdUser command which is run for each row

$sb = {
  param($propertyNames, $record)
  $propertyNames | foreach-object -Begin {$h = @{} }  -Process {
      if ($null -ne $record.$_) {$h[$_] = $record.$_}
  } -end {New-AdUser @h -verbose}
}

The script block gets a list of property names and a row of data: the script block gets called for each row and creates a hash table, adds an entry for each property and finally Splats the parameters into a command. It can be any command in the end block provided that the column names in Excel match its Parameters , I’m sure you can come up with your own use cases.

Blog at WordPress.com.