如何使用Powershell将CSV导出到Excel

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将完整的CSV导出到Excel。我坚持使用静态列名称。但是,如果我的CSV具有通用的未知标头名称,则无效。

Steps to reproduce

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

打开PowerShell ISE并复制并粘贴以下独立代码。使用F5“C:\ Windows \ system32 \ WindowsPowerShell \ v1.0 \ powershell_ise.exe”运行它

Get-Process | Export-Csv -Path $env:temp\process.csv -NoTypeInformation

$processes = Import-Csv -Path $env:temp\process.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:temp\process.csv
$Excel.visible = $true

What it does

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

该脚本将所有活动进程的列表作为CSV导出到临时文件夹。此文件仅适用于我们的示例。它可以是包含任何数据的任何CSV

2. It reads in the newly created CSV and saves it under the $processes variable

它读入新创建的CSV并将其保存在$ processes变量下

3. It creates a new and empty Excel workbook where we can write data

它创建了一个新的空Excel工作簿,我们可以在其中编写数据

4. It iterates through all rows (?) and writes all values from the name and vm column to Excel

它遍历所有行(?)并将name和vm列中的所有值写入Excel

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?

如果我不知道列标题怎么办? (在我们的示例名称和vm中)。如何解决我不知道其标题名称的值?

• How do I count how many columns a CSV has? (after reading it with Import-Csv)

如何计算CSV的列数? (用Import-Csv读取后)

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

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

6 个解决方案

#1

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

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

Gimmicks are

• 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.

将所有CSV值保留为纯文本,例如= B1 + B2或0000001.您没有看到#Name或类似的东西。没有进行自动格式化。

• Automatically chooses the right delimiter (comma or semicolon) according to your regional setting

根据您的区域设置自动选择正确的分隔符(逗号或分号)

• Autofit columns

PowerShell Code

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

#2

Why would you bother? Load your CSV into Excel like this:

你为什么要打扰?将CSV加载到Excel中,如下所示:

$csv = Join-Path $env:TEMP "process.csv"
$xls = Join-Path $env:TEMP "process.xlsx"

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.OpenText($csv)

$wb.SaveAs($xls, 51)

You just need to make sure that the CSV export uses the delimiter defined in your regional settings. Override with -Delimiter if need be.

您只需确保CSV导出使用区域设置中定义的分隔符。如果需要,用-Delimiter覆盖。

---

Edit: A more general solution that should preserve the values from the CSV as plain text. Code for iterating over the CSV columns taken from here.

编辑:一种更通用的解决方案,应将CSV中的值保留为纯文本。用于迭代从此处获取的CSV列的代码。

$csv = Join-Path $env:TEMP "input.csv"
$xls = Join-Path $env:TEMP "output.xlsx"

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = "@"

$i = 1
Import-Csv $csv | % {
  $j = 1
  foreach ($prop in $_.PSObject.Properties) {
    if ($i -eq 1) {
      $ws.Cells.Item($i, $j++).Value = $prop.Name
    } else {
      $ws.Cells.Item($i, $j++).Value = $prop.Value
    }
  }
  $i++
}

$wb.SaveAs($xls, 51)
$wb.Close()

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

#3

This topic really helped me, so I'd like to share my improvements. All credits go to the nixda, this is based on his answer.

这个话题对我很有帮助,所以我想分享一些改进。所有学分都归入nixda,这是基于他的回答。

For those who need to convert multiple csv's in a folder, just modify the directory. Outputfilenames will be identical to input, just with another extension.

对于那些需要在文件夹中转换多个csv的人,只需修改目录即可。 Outputfilenames与输入相同,只是与另一个扩展名相同。

Take care of the cleanup in the end, if you like to keep the original csv's you might not want to remove these.

最后要注意清理,如果你想保留原来的csv,你可能不想删除它们。

Can be easily modifed to save the xlsx in another directory.

可以轻松修改以将xlsx保存在另一个目录中。

$workingdir = "C:\data\*.csv"
$csv = dir -path $workingdir
foreach($inputCSV in $csv){
$outputXLSX = $inputCSV.DirectoryName + "\" + $inputCSV.Basename + ".xlsx"
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$excel.DisplayAlerts = $False
$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
### $Excel.Application.International(3) = ,
### $Excel.Application.International(5) = ;
$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()
}
## To exclude an item, use the '-exclude' parameter (wildcards if needed)
remove-item -path $workingdir -exclude *Crab4dq.csv

#4

If you want to convert CSV to Excel without Excel being installed, you can use the great .NET library EPPlus (under LGPL license) to create and modify Excel Sheets and also convert CSV to Excel really fast!

如果您想在不安装Excel的情况下将CSV转换为Excel,您可以使用优秀的.NET库EPPlus(在LGPL许可下)来创建和修改Excel表格,并且还可以非常快速地将CSV转换为Excel!

Preparation

1. Download the latest stable EPPlus version

下载最新稳定的EPPlus版本

2. Extract EPPlus to your preferred location (e.g. to $HOME\Documents\WindowsPowerShell\Modules\EPPlus)

将EPPlus提取到您的首选位置(例如,到$ HOME \ Documents \ WindowsPowerShell \ Modules \ EPPlus)

3. Right Click EPPlus.dll, select Properties and at the bottom of the General Tab click "Unblock" to allow loading of this dll. If you don't have the rights to do this, try [Reflection.Assembly]::UnsafeLoadFrom($DLLPath) | Out-Null

右键单击EPPlus.dll,选择“属性”,然后在“常规”选项卡底部单击“取消阻止”以允许加载此dll。如果您无权执行此操作,请尝试[Reflection.Assembly] :: UnsafeLoadFrom($ DLLPath)|外空

Detailed Powershell Commands to import CSV to Excel

# Create temporary CSV and Excel file names
$FileNameCSV = "$HOME\Downloads\test.csv"
$FileNameExcel = "$HOME\Downloads\test.xlsx"

# Create CSV File (with first line containing type information and empty last line)
Get-Process | Export-Csv -Delimiter ';' -Encoding UTF8 -Path $FileNameCSV

# Load EPPlus
$DLLPath = "$HOME\Documents\WindowsPowerShell\Modules\EPPlus\EPPlus.dll"
[Reflection.Assembly]::LoadFile($DLLPath) | Out-Null

# Set CSV Format
$Format = New-object -TypeName OfficeOpenXml.ExcelTextFormat
$Format.Delimiter = ";"
# use Text Qualifier if your CSV entries are quoted, e.g. "Cell1","Cell2"
$Format.TextQualifier = '"'
$Format.Encoding = [System.Text.Encoding]::UTF8
$Format.SkipLinesBeginning = '1'
$Format.SkipLinesEnd = '1'

# Set Preferred Table Style
$TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1

# Create Excel File
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage 
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add("FromCSV")

# Load CSV File with first row as heads using a table style
$null=$Worksheet.Cells.LoadFromText((Get-Item $FileNameCSV),$Format,$TableStyle,$true) 

# Load CSV File without table style
#$null=$Worksheet.Cells.LoadFromText($file,$format) 

# Fit Column Size to Size of Content
$Worksheet.Cells[$Worksheet.Dimension.Address].AutoFitColumns()

# Save Excel File
$ExcelPackage.SaveAs($FileNameExcel) 

Write-Host "CSV File $FileNameCSV converted to Excel file $FileNameExcel"

#5

This is a slight variation that worked better for me.

这是一个稍微变化,对我来说效果更好。

$csv = Join-Path $env:TEMP "input.csv"
$xls = Join-Path $env:TEMP "output.xlsx"

$xl = new-object -comobject excel.application
$xl.visible = $false
$Workbook = $xl.workbooks.open($CSV)
$Worksheets = $Workbooks.worksheets

$Workbook.SaveAs($XLS,1)
$Workbook.Saved = $True

$xl.Quit()

#6

I had some problem getting the other examples to work.

我在使其他示例工作时遇到了一些问题。

EPPlus and other libraries produces OpenDocument Xml format, which is not the same as you get when you save from Excel as xlsx.

EPPlus和其他库生成OpenDocument Xml格式,这与您从Excel保存为xlsx时所获得的格式不同。

macks example with open CSV and just re-saving didn't work, I never managed to get the ',' delimiter to be used correctly.

打开CSV的macks示例只是重新保存不起作用,我从来没有设法正确使用','分隔符。

Ansgar Wiechers example has some slight error which I found the answer for in the commencts.

Ansgar Wiechers的例子有一些轻微的错误,我在评论中找到了答案。

Anyway, this is a complete working example. Save this in a File CsvToExcel.ps1

无论如何,这是一个完整的工作示例。将其保存在文件CsvToExcel.ps1中

param (
[Parameter(Mandatory=$true)][string]$inputfile,
[Parameter(Mandatory=$true)][string]$outputfile
)

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

$wb = $excel.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = "@"

write-output "Opening $inputfile"

$i = 1
Import-Csv $inputfile | Foreach-Object { 
    $j = 1
    foreach ($prop in $_.PSObject.Properties)
    {
        if ($i -eq 1) {
            $ws.Cells.Item($i, $j) = $prop.Name
        } else {
            $ws.Cells.Item($i, $j) = $prop.Value
        }
        $j++
    }
    $i++
}

$wb.SaveAs($outputfile,51)
$wb.Close()
$excel.Quit()
write-output "Success"

Execute with:

.\CsvToExcel.ps1 -inputfile "C:\Temp\X\data.csv" -outputfile "C:\Temp\X\data.xlsx"