Convert any CSV to Formatted Excel file

Many of my scripts I created in the past required to have a Excel report instead of plain CSV, as these reports are for different Technical/Management/Leadership teams.

Today We got one such requirement where excel report is required therefore to make it easy for future I wrote a powershell code that takes CSV as input & end result is

formatted Excel file :).

Download the Script from below and use below syntax

https://github.com/VikasSukhija/Downloads/blob/master/ConvertCSV2XL.ps1

First parameter is always CsvPath

Convertcsv2xl.ps1 press tab, parameter will auto appear

.\ConvertCSV2XL.ps1 -CSVPath C:\ConvertCSV2XL\Test.csv -Exceloutputpath C:\ConvertCSV2XL\Test.xlsx

Input CSV file:

Output Excel file:

Note: As script is calling excel com object, so excel should be installed on the machine from which you are running it.

Let me know if you think any modification is required to make it more useful.(I have already started dot sourcing it in my other scripts)

PowerShell

<#     
    .NOTES 
    =========================================================================== 
     Created on:       07/04/2018  
     Created by:       Vikas Sukhija (http://SysCloudPro.com) 
     Organization:      
     Filename:         ConvertCSVTOXL.ps1 
    =========================================================================== 
    .DESCRIPTION 
        This will take CSV file as its parameter & convert it to XLS 
#> 
[CmdletBinding()] 
Param( 
  [Parameter(Mandatory=$True,Position=1)] 
   [string]$CSVPath, 
     
   [Parameter(Mandatory=$True)] 
   [string]$Exceloutputpath 
) 
 
####### Borrowed function from Lloyd Watkinson from script gallery## 
Function Convert-NumberToA1 {  
 Param([parameter(Mandatory=$true)]  
          [int]$number)  
    
    $a1Value = $null  
    While ($number -gt 0) {  
      $multiplier = [int][system.math]::Floor(($number / 26))  
      $charNumber = $number - ($multiplier * 26)  
      If ($charNumber -eq 0) { $multiplier-- ; $charNumber = 26 }  
      $a1Value = [char]($charNumber + 64) + $a1Value  
      $number = $multiplier  
    }  
    Return $a1Value  
  } 
#############################Start converting excel####################### 
 
$importcsv = import-csv $CSVPath 
$countcolumns = ($importcsv | Get-Member | where{$_.membertype -eq "Noteproperty"}).count 
 
 
#################call Excel com object ############## 
 
$xl = new-object -comobject excel.application 
$xl.visible = $false 
$Workbook = $xl.workbooks.open($CSVPath) 
$Worksheets = $Workbooks.worksheets 
$Workbook.SaveAs($Exceloutputpath, 51) 
$Workbook.Saved = $True 
$xl.Quit() 
 
#############Now format the Excel################### 
timeout 10 
$xl = new-object -comobject excel.application 
$xl.visible = $false 
$Workbook = $xl.workbooks.open($Exceloutputpath) 
$worksheet1 = $workbook.worksheets.Item(1) 
for ($c = 1; $c -le $countcolumns; $c++) { 
    $worksheet1.Cells.Item(1, $c).Interior.ColorIndex = 39 
} 
$colvalue = (Convert-NumberToA1 $countcolumns) + "1" 
$headerRange = $worksheet1.Range("a1", $colvalue) 
$headerRange.AutoFilter() | Out-Null 
$headerRange.entirecolumn.AutoFit() | Out-Null 
$worksheet1.rows.item(1).Font.Bold = $True 
$workbook.Save() 
$workbook.Close() 
$xl.Quit() 
 
#######################################################################