Last week I have introduced you to ImportExcel PowerShell module and its capability to manipulate the worksheets and create pivot tables and pivot charts. This week let’s jump on some other features: conditional formatting and charts.

## ImportExcel – how to?

If you have not installed the module before, use the below code to do so and move on to the examples.

# get the module Set-ExecutionPolicy Bypass -Scope Process Install-Module -Name ImportExcel Import-Module -Name ImportExcel

### ImportExcel – how to do conditional formatting?

I have a dataset with three columns and would like to add the conditional formatting on each of the columns separately. As a base will use Events exported to the worksheet and later will use the **Export-Excel **function with **ConditionalFormat** parameter:

# cleanup any previous files $excelFile = "$env:TEMP\MikeyEvents.xlsx" Remove-Item $excelFile -ErrorAction SilentlyContinue # create a new file in TEMP location - clean worksheet $events = Get-EventLog -After (Get-Date -Format 'yyyy-MM-dd') -LogName system | SELECT EventID, Category, EntryType $events | Export-Excel -WorksheetName Events -TableName Events -Path $excelFile -KillExcel

That will produce spreadsheet like this:

Now, will add another worksheet with the Conditional Formatting:

# define conditional formatting for each column in a new worksheet # these are random formats, just to show the capability $ConditionalFormat =$( New-ConditionalText -ConditionalType AboveAverage -Range 'A:A' -BackgroundColor Red -ConditionalTextColor Black New-ConditionalText -ConditionalType DuplicateValues -Range 'B:B' -BackgroundColor Orange -ConditionalTextColor Black New-ConditionalText -Text Information -Range 'C:C' -BackgroundColor Blue -ConditionalTextColor Yellow ) # add the new worksheet with ConditionalFormat. $Events | Export-Excel -WorksheetName EventsConditional -TableName EventsConditional -Path $excelFile -ConditionalFormat $ConditionalFormat -Show -Activate -KillExcel

That is how to the other worksheet looks like one below:

We have gotten some colors. It is also possible to apply the formatting to the existing worksheet. For that scenario I will define the formatting first (keeping in mind I will place the second table on the side of existing one).

# prepare formatting for the second table on the same worksheet - shifted two columns to the right # columns A,B,C will become E,F,G $ConditionalFormat2 =$( New-ConditionalText -conditionalType AboveAverage -Range 'E:E' -BackgroundColor Red -ConditionalTextColor Black New-ConditionalText -conditionalType DuplicateValues -Range 'F:F' -BackgroundColor Orange -ConditionalTextColor Black New-ConditionalText -Text 'Information' -Range 'G:G' -BackgroundColor Blue -ConditionalTextColor Yellow ) # now to create the new table in the existing worksheet # note the -StartColumn switch $Events | Export-Excel -WorksheetName Events -TableName EventsConditional2 -Path $excelFile -ConditionalFormat $ConditionalFormat2 -Show -Activate -KillExcel -StartColumn 5

We are back on the first worksheet with to tables (first without, second with the conditional formatting applied).

On a separate note we can add conditional formatting using dedicated function called **Add-ConditionalFormatting**.

# load the spreadsheet $excel = Open-ExcelPackage -Path $excelFile -KillExcel # add new formatting for column G (second table) # note how I am referring the worksheet Add-ConditionalFormatting -Worksheet $excel.Events -RuleType Equal 'Warning' -Address 'G:G' -BackgroundColor Green # save it to the file Close-ExcelPackage $excel -Show

That is right. We have changed background for all the ‘warning’ values.

Finally, there is one interesting formatting using icon sets. The ImportExcel module can do it too:

# define conditional format for the IconSet using Quarters $ConditionalFormat3 =$( New-ConditionalFormattingIconSet -Range A2:A6 -ConditionalFormat FiveIconSet -IconType Quarters ) # add a new table "L",1,2,3,4,5 | Export-Excel -WorksheetName EventsIcons -TableName EventsConditional3 -Path $excelFile -ConditionalFormat $ConditionalFormat3 -Show -Activate -KillExcel

All the quarters are there! I will stop playing with the conditional formatting at this point. If you have other examples or scenarios, please comment below.

### ImportExcel – how to create charts?

Last week we’ve seen how to add pivot charts, but ImportExcel module can work with regular charts as well.

As an example, I am collecting some performance counters and going to draw a chart.

# cleanup any previous files $excelFile = "$env:TEMP\MikeyEvents.xlsx" Remove-Item $excelFile -ErrorAction SilentlyContinue # select your favourite counter and collect data # this will run for 30 seconds $counter = "\Processor(_Total)\% Processor Time" $data = Get-Counter $counter -SampleInterval 1 -MaxSamples 30 # create a new file in TEMP location $dataCooked = $data.CounterSamples | SELECT Path, TimeStamp, CookedValue $chartDef = New-ExcelChartDefinition -XRange Path,TimeStamp -YRange CookedValue -ChartType Line $dataCooked | Export-Excel -WorksheetName Events -TableName Events $excelFile -ExcelChartDefinition $chartDef -AutoNameRange -Show -KillExcel

That is extremely basic chart that would need some names defined (like Title, or Series). Adding multi-series chart is also not a problem. Have a look:

# cleanup any previous files $excelFile = "$env:TEMP\MikeyEvents.xlsx" Remove-Item $excelFile -ErrorAction SilentlyContinue # create an array with the data $math = @() for ($i=0;$i -lt 361; $i++) { $r = $i/180*3.14 $math += [pscustomobject]@{ Angle = $i; Sin = [math]::Sin($r); Cos = [math]::Cos($r) } } # export data first $math | Export-Excel -Path $excelFile -WorksheetName Math -AutoSize -TableName Math -KillExcel # define the chart $chartDef2 = New-ExcelChartDefinition -Title 'Sin(x)/Cos(x)' ` -ChartType Line ` -XRange "Math[Angle]" ` -YRange @("Math[Cos]","Math[Sin]") ` -SeriesHeader 'Cos(x)','Sin(x)'` -Row 0 -Column 0 # add the chart to another worksheet Export-Excel -Path $excelFile -WorksheetName MathChart -ExcelChartDefinition $chartDef2 -Activate -Show

The first worksheet (Math) has the data and the other one gets the beautiful sine waves.

There is a way to add the chart separately, using Add-ExcelChart.

# cleanup any previous files $excelFile = "$env:TEMP\MikeyEvents.xlsx" Remove-Item $excelFile -ErrorAction SilentlyContinue # define data $data = @" Surface,Value Right wall,1 Floor,1 Left wall,1 "@ # export data to the excel worksheet $data | ConvertFrom-Csv | Export-Excel -Path $excelFile -AutoFilter -WorksheetName Pie -AutoNameRange -AutoSize -Show -KillExcel # using Open/Close ExcelPackage add the new chart $excel = Open-ExcelPackage $excelFile Add-ExcelChart -Worksheet $excel.Pie -ChartType Pie -Title Pie -XRange Surface -YRange Value Close-ExcelPackage $excel -Show

## Summary

I hope you enjoyed this post and you are a bit more confident using the module. There are endless possibilities and scenarios.

Thank you,

Mikey

Permalink