批处理之家 - Powered by Discuz! Board

标题: [转载代码] [PowerShell每日技巧]导出数据到Excel(20140311) [打印本页]

作者: DAIC 时间: 2014-3-12 15:20 标题: [PowerShell每日技巧]导出数据到Excel(20140311)

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 -NoTypeInformation
复制代码

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 $report
复制代码

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

作者: DAIC 时间: 2014-3-12 15:23

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 $report
复制代码

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"'
复制代码

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

Start Start-Process Excel -ArgumentList """$report"""
复制代码

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'
复制代码

To list all applications registered in App Paths, use:

Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths'
复制代码

欢迎光临批处理之家 (http://bbs.bathome.net/)