CODEWITHSUNDEEP
powershell
chandu
chandu

Reputation: 35

Filling Color to the first row in excell sheet using PS script

I wrote a script where in it will export all the SSL certificate details to an excel sheet, but i wanted a help to fill the color to the first row of the sheet.

Please help me in writing the script.

My Script

Clear-Host
$threshold = 300   #Number of days to look for expiring certificates 

$deadline = (Get-Date).AddDays($threshold)   #Set deadline date

Invoke-Command -ComputerName 'AAA', 'BBB' { 
    Get-ChildItem -Path 'Cert:\LocalMachine\My' -Recurse | 
    Select-Object -Property Issuer, Subject, NotAfter, 
    @{Label = 'ServerName';Expression = {$env:COMPUTERNAME}}, 
    @{Label='Expires In (Days)';Expression = {(New-TimeSpan -Start (Get-Date) -End $PSitem.NotAfter).Days}} 
} | Export-Csv -Path C:\users\$env:username\documents\WorkingScript.csv -NoTypeInformation -Force

Thanks in Advance.

Upvotes: 0

Views: 2105

Answers (1)

user2517266
user2517266

Reputation: 365

You are creating a CSV file which does not hold formatting. Instead you could interact with Excel directly and create formatting that way.

As Olaf mentioned you could import a module to do this, or use the Excel com object.

Example using the COM object below

# Creating COM object to interact with excel
$excel = New-Object -ComObject Excel.Application

# set to false to hide the application
$excel.visible = $true 

# Add a workbook to the application
$workbook = $excel.Workbooks.Add()

# Adding a workbook automatically adds a sheet.
# We select it and then name it
$worksheetOne = $workbook.Worksheets.Item(1)
$worksheetOne.Name = 'Data'

# Setting the text in two different cells
$worksheetOne.Cells.Item(1, 1) = 'Column One Text'
$worksheetOne.Cells.Item(1, 2) = 'Column Two Text'

# Selecting the EntireRow of the cell "1,1" and setting it to a color
$worksheetOne.Cells.Item(1, 1).EntireRow.Interior.ColorIndex = 4

# Setting the same row to bold
$worksheetOne.Cells.Item(1, 1).EntireRow.Font.Bold = $true

# Option autofit all columns
$worksheetOne.UsedRange.EntireColumn.AutoFit() | Out-Null

# Save the file
$excel.ActiveWorkbook.SaveAs('C:\Users\Username\example.xlsx')

You can see some of the colors below.

https://learn.microsoft.com/en-us/office/vba/api/excel.colorindex

Upvotes: 1

Related Questions