Add a solid border to an Excel data bar range

PowerShell Help
1

Hi,

I’m pulling my hair out with this one.

I’m creating an excel spreadsheet using IMPORTEXCEL module. I’m adding a data bar on a column but I cannot find the correct syntax to apply a “solid border” (and ideally, the colour of the solid border).

I’ve searched everywhere and can’t find a command that works.

I’ve tried:

$rule.DataBar.BarBorder.BorderType = [OfficeOpenXml.ConditionalFormatting.ExcelDataBarBorderType]::Solid
$rule.DataBar.BarBorder.Color.Color = [System.Drawing.Color]::Black

I’ve tried:

$rule.Databar.Border.BorderType = "Solid" # Equivalent to Excel's Solid Border
$rule.Databar.Border.Color.Color = [System.Drawing.Color]::Black # Set the border color to Black (or any other color)

Nothing seems to work. Could you offer any remedy to my issue?.

Below is a simple script to output a list of svchost processes to an Excel workbook with green data bars applied to column B, which I’ve been using as a test:

cls
Import-Module ImportExcel   # running version 7.8.10

$ExportFilePath="C:\temp\DataBarTestFile1.xlsx"
If(Test-path $ExportFilePath){Remove-Item $ExportFilePath}

$processes = get-process | where-object {$_.ProcessName -eq "svchost"}  | Select-Object Name, Handles
write-host ("There were "+$processes.count+ " svchost processes found")

write-host "Exporting data to Excel workbook, please wait..." 
$excel = $Processes | Export-Excel -Path $ExportFilePath -WorksheetName "svchost processes" -AutoSize -BoldTopRow -FreezeTopRow -AutoFilter -TableStyle Medium2 -append -PassThru

$Range=("B2:B"+($Processes.count+1))

$rule=Add-ConditionalFormatting -WorkSheet $excel.Workbook.Worksheets["svchost processes"] -Range $Range -DataBarColor Green

$excel.Save()     # Save the final changes to the workbook

Close-ExcelPackage $excel
2

ImportExcel uses EPPlus.dll version 4.5.x which is the last version to be released that doesn’t require a commercial licence.

Unfortunately, support for advanced formatting of databars wasn’t added until version 7 of EPPlus.

If you have Excel installed you could use the COM object to apply the formatting:

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$wb = $xl.Workbooks.Open('E:\Temp\DataBarTestFile1.xlsx')
$ws = $wb.Worksheets('svchost processes')
$lastRow = $ws.UsedRange.Rows.Count
$range = $ws.Range("B2:`$B$lastRow")
$dataBar = $range.FormatConditions.AddDatabar()
$dataBar.BarColor.Color = 0x008000
$dataBar.BarBorder.Type = 1
$dataBar.BarBorder.Color.Color = 0x000000
$wb.Save()
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Note: this won’t reformat an existing rule, so don’t apply the rule with ImportExcel.

3

Thanks for the reply.

That at least explains why I’ve had so much trouble finding a working solution.

I’ve so far managed to avoid using a ComObject, and using ImportExcel instead for flexibility of use but thanks for the alternative method. It may now come to that.

4

Could you tell me what I need to add to your ComObject script if I wish to apply a minimum number value of 2017 and a maximum number value of 2023 to the conditional formatting rather than the default of minimum automatic and maximum automatic.

I’d also like to wrap an IF statement around it to detect whether Excel is installed before executing the code but I don’t know if that is possible.

5

Try this:

$dataBar.MinPoint.Modify(0,2017)
$dataBar.MaxPoint.Modify(0,2023)

Rather than an if statement, wrap it in a try/catch block and handle the exception.

try {
    New-Object -ComObject Excel.Application
}
catch [System.Runtime.InteropServices.COMException] {
    # Error handling.
}
6

Many thanks. That is a much better approach.

7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.