Powershell: Function: Convert CSV into auto-filtered XLSX files

The purpose of this powershell function is to be used as a means of within script generating report like files.

This function has been generalized for use under a variety of situations, but expect that so long as your data is broken out within the input CSV the result XLSX file should have:

- Top row - to last filled column is assumed to be a header row.
  - this row gets bolded, a filled in color, and the filters are set at this layer.

System executing a script that contains this function needs to have excel installed and working.

**********************script begin***************************************

Function CSVtoXLSX
{
    [cmdletbinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$arg1,
       
        [Parameter(Mandatory=$true, Position=1)]
        [string]$arg2
    )
    $XL = new-object -comobject Excel.application
    $XL.visible=$false
    $XL.displayalerts=$false
    $XL.workbooks.open("$arg1").SaveAs("$arg2",51)
    $XL.quit()
    #
    $XL2=new-object -comobject Excel.application
    $XL2.visible=$false
    $XL2.displayalerts=$false
    $WB=$XL2.workbooks.open("$arg2")
    $ws1=$wb.worksheets.item(1)
    $ws1.activate()
    #
    # Find the column count, to define our working-range.
    #
    $findrange=$ws1.usedrange.cells
    $colcount=$findrange.columns.count
    $workrange=$ws1.range($ws1.cells.item(1, 1), $ws1.cells.item(1, $colcount))
    #
    # Adjust the first row into a Header type Row
    #
    $workrange.font.bold=$true
    $workrange.interior.colorindex=15
    #
    # Set autofilter for all columns, and then autofit all columns.
    #
    $xl2.selection.autofilter() | out-null
    [void]$ws1.cells.entirecolumn.autofit()
    #
    # Save changes and close out.
    #
    $wb.saveas("$arg2")
    $wb.close()
    $XL2.quit()
}

*********************************end script********************

Use case:

$input="c:\testfolder\testfile.csv"
$output="c:\testfolder\convertedreport.csv"

CSVtoXLSX $input $output

Note:

The default sheet name given to the spreadsheet created will be the name of the original CSV file.

Random Notes:

I have for many years now, leveraged a VBS script to perform the same tasks I wrote this powershell function for. It still works wonderfully, I just wanted to see if I could do this conversion within powershell itself vs calling a seperate script.

There may be additional edits to this one adding features going forward, but the base code as it is solid and meant to be slapped into any existing powershell script.