ConvertCSV2XL.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

<#PSScriptInfo
 
.VERSION 1.0.0.1
 
.GUID 2888473e-7597-49bd-a226-1d6400806108
 
.AUTHOR Sukhija Vikas (http://SysCloudPro.com)
 
.COMPANYNAME
 
.COPYRIGHT
 
.TAGS
 
.LICENSEURI
 
.PROJECTURI
 
.ICONURI
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
Excel should be installed on the machine from which it is executed.
 
.RELEASENOTES
https://syscloudpro.com/2018/07/06/convert-any-csv-to-formatted-excel-file/
 
Excel should be installed on the machine from which it is executed.
 
Example: .\ConvertCSV2XL.ps1 -CSVPath C:\ConvertCSV2XL\Test.csv -Exceloutputpath C:\ConvertCSV2XL\Test.xlsx
 
.PRIVATEDATA
 
#>


<#
 
.DESCRIPTION
 This Script will take CSV file as its parameter & convert it to formatted XLSX
 
#>
 
[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() 
 
#######################################################################