如何使用Powershell将CSV导出为Excel

Question

如何使用Powershell将CSV导出为Excel

excelpowershellwindows-7export-to-exceloffice-2013

10

我正在尝试使用Powershell将完整的CSV导出为Excel。我卡在了使用静态列名的点上。但是，如果我的CSV具有通用的未知标题名称，则此方法将不起作用。

复现步骤：

打开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

它的作用

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

我的问题

如果我不知道列标题怎么办？（在我们的示例中为name和vm）。如何处理我不知道它们的标题名称的值？
如何计算CSV文件具有多少列？（在使用Import-Csv读取后）

我只想用PowerShell将整个CSV文件写入Excel

- nixda

8个回答

16

我正在使用excelcnv.exe将csv转换为xlsx，看起来工作正常。您需要将目录更改为excelcnv所在的位置。如果是32位，则进入Program Files（x86）

Start-Process -FilePath 'C:\Program Files\Microsoft Office\root\Office16\excelcnv.exe' -ArgumentList "-nme -oice ""$xlsFilePath"" ""$xlsToxlsxPath"""

- WinstonKyu

2

为什么这个没有任何赞？这绝对是将 .csv 文件转换为 .xlsx 的最简单方法。感谢提供如此简单的解决方案！ - Lews Therin

这似乎有效，但如果您的分隔符与指定给您的本地化不同，则此命令将无法将数据转换为列。我还没有找到是否可以指定分隔符字符的方法。 - Stuggi

“-nme”开关是用来做什么的？ - Markus Nißl

我无法让它正常工作。excelcnv.exe 总是返回 0 的退出代码，并且没有提供任何反馈或输出。 - Tyler Montney

8

为什么要这么麻烦呢？您可以按照以下方法将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)

您只需要确保CSV导出使用与您的区域设置中定义的分隔符相同的分隔符。如果需要，可以使用-Delimiter进行覆盖。

编辑：一种更通用的解决方案，应该将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 | ForEach-Object {
  $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)

显然，这种第二种方法的性能不会太好，因为它是逐个处理每个单元格。

- Ansgar Wiechers

我忘了提到我不能使用OpenText方法。这个方法与双击CSV并使用Excel打开它相同。但是，这种方法有其缺陷：您将失去二进制值的前导零，因为Excel自动检测到的列格式。以= 或-开头的值被误解为公式。您知道一种避免这种情况的PowerShell方法吗？ - nixda

在你的情况下，这是什么问题？Get-Process 的输出没有看起来像公式的值，并且您可以通过格式化列以所需的外观强制使用前导零。 - Ansgar Wiechers

正如我在问题中提到的“此文件仅供我们示例使用”。假设我们有一个带有前导零和等号的CSV文件，并且我们想避免Excel自动格式检测。所有值都应被视为纯文本。 - nixda

"$ws.Cells.Item($i, $j++).Value = $prop.Name" 和 "$ws.Cells.Item($i, $j++).Value = $prop.Value" 导致了错误。我可以回显 Excel 单元格的值和 CSV 值，但是我无法设置 Excel 单元格的值。（我使用的是您提供的代码，没有进行任何修改。当然，我首先创建了一个输入 CSV 文件） - nixda

在下面的代码行中，删除“Value”并运行脚本。 $ws.Cells.Item($i, $j++).Value = $prop.Name 例如：$ws.Cells.Item($i, $j++) = $prop.Name - user2844885

显示剩余3条评论

6

此话题对我有很大帮助，因此我想分享我的改进。所有功劳归功于 nixda，这是基于他的答案。

如果需要转换文件夹中的多个 csv，请修改目录。输出文件名将与输入文件名相同，只是扩展名不同。

最后一定要处理好清理工作，如果您想保留原始的 csv，则可能不想删除它们。

可以轻松修改以将 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

- obiwankoban

你好，你的代码正是我所需要的，但是有没有办法将“更改工作表名称”添加到代码中定义的某个地方？谢谢，我知道这是一个较旧的帖子，希望有人能够帮忙！ - Defca Trick

@DefcaTrick 请看这里：https://stackoverflow.com/a/25881123/6774278 一开始，只需执行以下操作： $worksheet = $workbook.worksheets.Item(1) $worksheet.name = "指定您自己的自定义工作表名称" 现在工作表已重命名，其余部分相同。 - obiwankoban

谢谢，这起作用了。有一些关于从Tableau生成CSV的导出的解决方法。 - junketsu

2

如果您想在没有安装Excel的情况下将CSV转换为Excel，则可以使用出色的.NET库EPPlus（根据LGPL许可证）快速创建和修改Excel表格，并将CSV转换为Excel！

准备工作

下载最新稳定版EPPlus
将EPPlus提取到首选位置（例如，$HOME\Documents\WindowsPowerShell\Modules\EPPlus）
右键单击EPPlus.dll，选择属性，在常规选项卡底部单击“取消锁定”，以允许加载此dll。如果您没有执行此操作的权限，请尝试[Reflection.Assembly] :: UnsafeLoadFrom（$DLLPath）| Out-Null

导入CSV到Excel的详细PowerShell命令

# 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"

- Florian Feldhaus

运行您的示例时，我遇到了一个错误：“SaveAs”存在多个模糊重载，并且参数计数为“1”。看起来非常有前途，只是想提醒一下，以防有人运行并遇到相同的错误。 - sheldonhull

1

这是一个稍微不同的变化，对我来说效果更好。

$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()

- mack

你的1在$XLS之后代表哪种文件格式？我在MSDN概述中找不到它。通常我会使用51，因为这代表xlWorkbookDefault。另外，$Worksheets = $Workbooks.worksheets似乎不相关。 - nixda

使用1适用于.xls文件扩展名。当我使用51来打开.xls扩展名的文件时，Excel会出现“您尝试打开的文件与文件扩展名指定的格式不同”的错误。使用51适用于.xlsx扩展名。 - mack

1

我在尝试其他示例时遇到了一些问题。

EPPlus和其他库生成的是OpenDocument Xml格式，与从Excel另存为xlsx时得到的格式不同。

macks使用打开CSV并重新保存的示例没有起作用，我无法正确使用','分隔符。

Ansgar Wiechers的示例有一些小错误，我在评论中找到了答案。

无论如何，这是一个完整的工作示例。将其保存在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"

使用以下命令执行：

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

- Gil Roitto

0

我在寻找如何将一组csv文件编译成一个单独的Excel文档，并将工作表（选项卡）命名为csv文件时，偶然发现了这个函数。它是一个很好的功能。可惜，我无法在我的网络上运行它们:(所以我不知道它的运行效果如何。

Function Release-Ref ($ref)
{
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    [System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
    }
    Function ConvertCSV-ToExcel
    {
    <#
    .SYNOPSIS
    Converts     one or more CSV files into an excel file.
    
    .DESCRIPTION
    Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the
    file being the name of the worksheet.
        
    .PARAMETER inputfile
    Name of the CSV file being converted
    
    .PARAMETER output
    Name of the converted excel file
    
    .EXAMPLE
    Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘report.xlsx’
    
    .EXAMPLE
    ConvertCSV-ToExcel -inputfile ‘file.csv’ -output ‘report.xlsx’
    
    .EXAMPLE
    ConvertCSV-ToExcel -inputfile @(“test1.csv”,”test2.csv”) -output ‘report.xlsx’
    
    .NOTES
    Author:     Boe Prox
    Date Created: 01SEPT210
    Last Modified:
    
    #>
    
    #Requires -version 2.0
    [CmdletBinding(
    SupportsShouldProcess = $True,
    ConfirmImpact = ‘low’,
    DefaultParameterSetName = ‘file’
    )]
    Param (
    [Parameter(
    ValueFromPipeline=$True,
    Position=0,
    Mandatory=$True,
    HelpMessage=”Name of CSV/s to import”)]
    [ValidateNotNullOrEmpty()]
    [array]$inputfile,
    [Parameter(
    ValueFromPipeline=$False,
    Position=1,
    Mandatory=$True,
    HelpMessage=”Name of excel file output”)]
    [ValidateNotNullOrEmpty()]
    [string]$output
    )
    
    Begin {
    #Configure regular expression to match full path of each file
    [regex]$regex = “^\w\:\\”
    
    #Find the number of CSVs being imported
    $count = ($inputfile.count -1)
    
    #Create Excel Com Object
    $excel = new-object -com excel.application
    
    #Disable alerts
    $excel.DisplayAlerts = $False
    
    #Show Excel application
    $excel.V    isible = $False
    
    #Add workbook
    $workbook = $excel.workbooks.Add()
    
    #Remove other worksheets
    $workbook.worksheets.Item(2).delete()
    #After the first worksheet is removed,the next one takes its place
    $workbook.worksheets.Item(2).delete()
    
    #Define initial worksheet number
    $i = 1
    }
    
    Process {
    ForEach ($input in $inputfile) {
    #If more than one file, create another worksheet for each file
    If ($i -gt 1) {
    $workbook.worksheets.Add() | Out-Null
    }
    #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
    $worksheet = $workbook.worksheets.Item(1)
    #Add name of CSV as worksheet name
    $worksheet.name = “$((GCI $input).basename)”
    
    #Open the CSV file in Excel, must be converted into complete path if no already done
    If ($regex.ismatch($input)) {
    $tempcsv = $excel.Workbooks.Open($input)
    }
    ElseIf ($regex.ismatch(“$($input.fullname)”)) {
    $tempcsv = $excel.Workbooks.Open(“$($input.fullname)”)
    }
    Else {
    $tempcsv = $excel.Workbooks.Open(“$($pwd)\$input”)
    }
    $tempsheet = $tempcsv.Worksheets.Item(1)
    #Copy contents of the CSV file
    $tempSheet.UsedRange.Copy() | Out-Null
    #Paste contents of CSV into existing workbook
    $worksheet.Paste()
    
    #Close temp workbook
    $tempcsv.close()
    
    #Select all used cells
    $range = $worksheet.UsedRange
    
    #Autofit the columns
    $range.EntireColumn.Autofit() | out-null
    $i++
    }
    }
    
    End {
    #Save spreadsheet
    $workbook.saveas(“$pwd\$output”)
    
    Write-Host -Fore Green “File saved to $pwd\$output”
    
    #Close Excel
    $excel.quit()
    
    #Release processes for Excel
    $a = Release-Ref($range)
    }
}

- Sh3pard

1

我尝试清理了一下格式，但还是很乱。请考虑重新排版你的代码。 - General Grievance

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- nixda · Accepted Answer

哎呀，我完全忘记了这个问题。与此同时，我找到了一个解决方案。
这个PowerShell脚本可以将CSV转换为XLSX格式并在后台运行

特点是

保留所有CSV值为纯文本，如=B1+B2或0000001。
你看不到#名称或任何类似的东西，也没有自动格式化。
根据您的地区设置自动选择正确的分隔符（逗号或分号）
自动适应列宽

PowerShell 代码

### 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()