如何使用 Powershell 将 CSV 导出到 Excel [英] How to export a CSV to Excel using Powershell

问题描述

我正在尝试使用 Powershell 将完整的 CSV 导出到 Excel.我停留在使用静态列名的地方.但是，如果我的 CSV 具有通用的未知标题名称，这将不起作用.

I'm trying to export a complete CSV to Excel by using Powershell. I stuck at a point where static column names are used. But this doesn't work if my CSV has generic unknown header names.

打开您的 PowerShell ISE 并复制 &粘贴以下独立代码.使用 F5
运行它^{"C:Windowssystem32WindowsPowerShellv1.0powershell_ise.exe"}

Open your PowerShell ISE and copy & paste the following standalone code. Run it with F5
^{"C:Windowssystem32WindowsPowerShellv1.0powershell_ise.exe"}

Get-Process | Export-Csv -Path $env:tempprocess.csv -NoTypeInformation

$processes = Import-Csv -Path $env:tempprocess.csv 
$Excel = New-Object -ComObject excel.application 
$workbook = $Excel.workbooks.add() 

$i = 1 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,1) = $process.name
 $excel.cells.item($i,2) = $process.vm
 $i++ 
} 
Remove-Item $env:tempprocess.csv
$Excel.visible = $true

它的作用

1. 该脚本会将所有活动进程的列表以 CSV 格式导出到您的临时文件夹.此文件仅用于我们的示例.它可以是包含任何数据的任何 CSV 文件
2. 它读入新创建的 CSV 并将其保存在 $processes 变量下
3. 它会创建一个新的空 Excel 工作簿，我们可以在其中写入数据
4. 它遍历所有行 (?) 并将 name 和 vm 列中的所有值写入 Excel

1. The script will export a list of all active processes as a CSV to your temp folder. This file is only for our example. It could be any CSV with any data
2. It reads in the newly created CSV and saves it under the $processes variable
3. It creates a new and empty Excel workbook where we can write data
4. It iterates through all rows (?) and writes all values from the name and vm column to Excel

我的问题

• 如果我不知道列标题怎么办?(在我们的示例中，name 和 vm).我如何处理不知道标头名称的值?
• 如何计算 CSV 有多少列?(使用Import-Csv阅读后)

My questions

• What if I don't know the column headers? (In our example name and vm). How do I address values where I don't know their header names?
• How do I count how many columns a CSV has? (after reading it with Import-Csv)

我只想用 Powershell 将整个 CSV 写入 Excel

I just want to write an entire CSV to Excel with Powershell

推荐答案

糟糕，我完全忘记了这个问题.与此同时，我得到了一个解决方案.
此 Powershell 脚本在后台将 CSV 转换为 XLSX

Ups, I entirely forgot this question. In the meantime I got a solution.
This Powershell script converts a CSV to XLSX in the background

• 将所有 CSV 值保留为纯文本，如 =B1+B2 或 0000001.
  你看不到 #Name 或类似的东西.没有自动格式化.
• 根据您的区域设置自动选择正确的分隔符(逗号或分号)
• 自动调整列
• Preserves all CSV values as plain text like =B1+B2 or 0000001.
  You don't see #Name or anything like that. No autoformating is done.
• Automatically chooses the right delimiter (comma or semicolon) according to your regional setting
• Autofit columns

### Set input and output path
$inputCSV = "C:somefolderinput.csv"
$outputXLSX = "C:somefolderoutput.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()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

这篇关于如何使用 Powershell 将 CSV 导出到 Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！