CODEWITHSUNDEEP
excelpowershell
Rodolfo Spier
Rodolfo Spier

Reputation: 188

How to change the background color of an Excel report

Basically, I am using this script to run through all the .csv files in a specific folder and merge them all together. But after the merge, I still want to change the background color of each .csv file.

The script that I got so far does not do that, can't figure out how to do it as I am really new in PowerShell.

# Get all the information from .csv files that are in the $IN_FILE_PATH skipping the first line:
$getFirstLine = $true

get-childItem "$IN_FILE_PATH\*.csv" | ForEach {
    $filePath = $_

    $lines = $lines = Get-Content $filePath
    $linesToWrite = switch ($getFirstLine) {
        $true { $lines }
        $false { $lines | Select -Skip 1 }
    }

    # Import all the information... and tranfer to the new workbook.
    $Report_name = $((get-date).ToString("yyyy.MM.dd-hh.mm"))

    $getFirstLine = $false
    Add-Content "$OUT_FILE_PATH\Report $Report_Name.csv" $linesToWrite
}

e.g. The .csv file has this pattern:

Name        Age

Richard     18
Carlos      20
Jonathan    43
Mathew      25

Making sure to understand that Richard (18 years old) and Carlos (20 years old) are from filenumber1.csv - Jonathan (43 years old) and Mathew (25 years old) are from filenumber2.csv

I want Carlos' and Richard's rows to be with a white background, whereas Jonathan's and Mathew's rows to be grey. So that repeats in white-grey-white-grey dividing it by each file. I am trying to make it more friendly to observe the report in the end - to make sure that you can this separation from file to file more clear.

Any ideas?

Upvotes: 2

Views: 2100

Answers (1)

Robert Dyjas
Robert Dyjas

Reputation: 5227

As Vivek Kumar Singh mentioned in comments, .csv doesn't contain any formatting options. It's recommended to work with Excel file instead. And for that purpose, the best module I know and use is ImportExcel.

The code to set formatting is as below (inspired by this thread):

$IN_FILE_PATH = "C:\SO\56870016"
# mkdir $IN_FILE_PATH
# cd $IN_FILE_PATH
# rm out.xlsx

# Define colors
$colors = "White", "Gray"

# Initialization
$colorsTable = @()
$data = @()
$n = 0

Get-ChildItem "$IN_FILE_PATH\*.csv"  | % {
  $part = Import-Csv $_
  $data += $part
  for ($i = 0; $i -lt ($part).Count; $i++) {
    $colorsTable += $colors[$n%2]
  }
  $n++
}


$j = 0
$data | Export-Excel .\out.xlsx -WorksheetName "Output" -Append -CellStyleSB {
  param(
    $workSheet,
    $totalRows,
    $lastColumn
  )        

  foreach($row in (2..$totalRows )) {
    # The magic happens here
    # We set colors based on the list which was created while importing
    Set-CellStyle $workSheet $row $LastColumn Solid $colorsTable[$j]
    $j++
  }        
}

Hopefully the comments in the code help you to better understanding of what's going on in the code.

Upvotes: 1

Related Questions