[转载代码] [PowerShell每日技巧]导出数据到Excel(20140311)

DAIC

You can easily convert object results to CSV files in PowerShell. This generates a CSV report of current processes:

Get-Process | Export-Csv $env:temp\report.csv -UseCulture -Encoding UTF8 -NoTypeInformationCOPY

To open the CSV file in Microsoft Excel, you could use Invoke-Item to open the file, but this would only work if the file extension CSV is actually associated with the Excel application.

This script will open the CSV file always in Microsoft Excel. It illustrates a way to find out the actual path to your Excel application (it assumes it is installed and does not check if it is missing altogether though):

$report = "$env:temp\report.csv"
$ExcelPath = 'C:\Program Files*\Microsoft Office\OFFICE*\EXCEL.EXE'
$RealExcelPath = Resolve-Path -Path $ExcelPath | Select-Object -First 1 -ExpandProperty Path
& $RealExcelPath $reportCOPY

http://powershell.com/cs/blogs/tips/archive/2014/03/11/exporting-data-to-excel.aspx

DAIC

This tip provides a nice demonstration of using wildcards to resolve the path to a file or executable. Another method to open a CSV file in Excel takes advantage of the Start-Process cmdlet and the fact that Excel is registered in the App Paths key in the registry. In fact, any application that's registered in App Paths can be lauched using Start-Process in the same manner.

$report = "$env:temp\report.csv"
Start-Process Excel -ArgumentList $reportCOPY

Note that if the path to the CSV file includes spaces, it must be passed as a quoted string, so the following would work.

Start-Process Excel -ArgumentList '"C:\Users\Some User\Documents\Import File.csv"'COPY

This won't work, however, if you require variable expansion. Instead, use:

Start Start-Process Excel -ArgumentList """$report"""COPY

You can use the following command to determine if Excel is registered in App Paths.

Test-Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe'COPY

To list all applications registered in App Paths, use:

Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths'COPY