如何通过PowerShell在CSV转XLSX时应用Excel表格样式

解决方案 #

你可以在原有脚本数据导入完成、删除导入查询的步骤之后、保存工作簿之前，新增表格转换与样式配置逻辑，即可实现需求。修改后的完整可运行代码如下：

### 设置输入输出路径
$inputCSV =  "C:\AuditLogSearch\Modified Audit-Log-Records.csv"
$outputXLSX = "C:\AuditLogSearch\output1.xlsx"

### 新建仅含一个工作表的Excel工作簿
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### 构建文本查询连接器，功能等价于Excel界面的「数据 > 自文本」导入
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### 按系统区域设置自动识别分隔符
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### 配置导入格式为分隔符分隔，所有列默认按文本格式导入避免自动转义
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### 执行导入并删除查询对象
$query.Refresh()
$query.Delete()

### ------------------- 新增表格格式化逻辑开始 -------------------
### 自动识别已导入数据的使用范围
$usedRange = $worksheet.UsedRange
### 将范围转换为Excel表格，默认第一行为表头
$table = $worksheet.ListObjects.Add(1, $usedRange, 1)
### 设置表格名称（可选）
$table.Name = "AuditLogTable"
### 应用表格样式，可替换为你需要的样式名
### 常用内置样式：TableStyleLight1-21、TableStyleMedium1-28、TableStyleDark1-11
$table.TableStyle = "TableStyleMedium2"
### 开启表头筛选（默认开启，不需要可设为$false）
$table.ShowAutoFilter = $true
### 自动调整列宽适配内容
$usedRange.EntireColumn.AutoFit()
### ------------------- 新增表格格式化逻辑结束 -------------------

### 保存文件并退出Excel进程
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()
### 清理COM对象避免残留进程
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel

关键配置说明 #

• 表格样式可根据你的需求自由替换，上述代码中给出了常用的内置样式命名规则，修改$table.TableStyle的赋值即可
• 如果你的CSV文件没有表头，只需将$worksheet.ListObjects.Add(1, $usedRange, 1)中的最后一个参数改为0，Excel会自动生成列1、列2格式的默认表头
• 末尾新增的COM对象清理逻辑可以避免Excel进程残留在后台占用资源，建议保留

内容的提问来源于stack exchange，提问作者ziico