How to apply Excel Table Style using Powershell

Question

I'm thinking about writing script that convert my existing CSV file to XLSX file so I been following this post https://code.adonline.id.au/csv-to-xlsx-powershell/

and it's working fine but I'm just wondering how can I format as a table and apply style while converting to XLSX file?

I'll be really appreciated if I can get any help or suggestion.

### Set input and output path
$inputCSV =  "C:\AuditLogSearch\Modified Audit-Log-Records.csv"
$outputXLSX = "C:\AuditLogSearch\output1.xlsx"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

@SantiagoSquarzon Hi again, can you please provide me an example that have import csv and export it to xlsx file with formated table style. — ziico
– ziico, Commented Dec 1, 2021 at 22:43

Santiago Squarzon · Accepted Answer · 2022-04-20 20:26:16Z

3

Assuming you want to try out the ImportExcel Module.

Install it first: Install-Module ImportExcel -Scope CurrentUser

Then the code would look like this:

$params = @{
    AutoSize      = $true
    TableName     = 'exampleTable'
    TableStyle    = 'Medium11' # => Here you can chosse the Style you like the most
    BoldTopRow    = $true
    WorksheetName = 'YourWorkSheetName'
    PassThru      = $true
    Path          = 'path/to/excel.xlsx' # => Define where to save it here!
}

$xlsx = Import-Csv path/to/csv.csv | Export-Excel @params
$ws   = $xlsx.Workbook.Worksheets[$params.Worksheetname]
$ws.View.ShowGridLines = $false # => This will hide the GridLines on your file
Close-ExcelPackage $xlsx

The author has a Youtube channel where he used to upload tutorials and there is also online Documentation over the internet if you want to learn more.

edited Apr 20, 2022 at 20:26

answered Dec 1, 2021 at 23:03

Santiago Squarzon

65.7k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to apply Excel Table Style using Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related