用Powershell解析excel文档有没有更快的方法?
我有MS Excel
文档通过Powershell
接口。每个excel文件有大约1000行数据的可能性。用Powershell解析excel文档有没有更快的方法?
目前这个剧本似乎读取Excel
文件,并在每个0.6秒1个记录的速度值写入屏幕。乍一看,这似乎非常缓慢。
这是我第一次读取Excel
文件,Powershell
,这是正常的?有没有更快的方式来读取和解析Excel
数据?
这里是脚本输出(修剪可读性)
PS P:\Powershell\ExcelInterfaceTest> .\WRIRMPTruckInterface.ps1 test.xlsx
3/20/2013 4:46:01 PM
---------------------------
2 078110
3 078108
4 078107
5 078109
<SNIP>
242 078338
243 078344
244 078347
245 078350
3/20/2013 4:48:33 PM
---------------------------
PS P:\Powershell\ExcelInterfaceTest>
这里是Powershell
脚本:
########################################################################################################
# This is a common function I am using which will release excel objects
########################################################################################################
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
########################################################################################################
# Variables
########################################################################################################
########################################################################################################
# Creating excel object
########################################################################################################
$objExcel = new-object -comobject excel.application
# Set to false to not open the app on screen.
$objExcel.Visible = $False
########################################################################################################
# Directory location where we have our excel files
########################################################################################################
$ExcelFilesLocation = "C:/ShippingInterface/" + $args[0]
########################################################################################################
# Open our excel file
########################################################################################################
$UserWorkBook = $objExcel.Workbooks.Open($ExcelFilesLocation)
########################################################################################################
# Here Item(1) refers to sheet 1 of of the workbook. If we want to access sheet 10, we have to modify the code to Item(10)
########################################################################################################
$UserWorksheet = $UserWorkBook.Worksheets.Item(2)
########################################################################################################
# This is counter which will help to iterrate trough the loop. This is simply a row counter
# I am starting row count as 2, because the first row in my case is header. So we dont need to read the header data
########################################################################################################
$intRow = 2
$a = Get-Date
write-host $a
write-host "---------------------------"
Do {
# Reading the first column of the current row
$TicketNumber = $UserWorksheet.Cells.Item($intRow, 1).Value()
write-host $intRow " " $TicketNumber
$intRow++
} While ($UserWorksheet.Cells.Item($intRow,1).Value() -ne $null)
$a = Get-Date
write-host $a
write-host "---------------------------"
########################################################################################################
# Exiting the excel object
########################################################################################################
$objExcel.Quit()
########################################################################################################
#Release all the objects used above
########################################################################################################
$a = Release-Ref($UserWorksheet)
$a = Release-Ref($UserWorkBook)
$a = Release-Ref($objExcel)
如果数据是静态的(没有公式参与,只是在单元格数据)您可以作为ODBC数据源访问电子表格,并对其执行SQL(或至少类似SQL)查询。看看this reference设置你的ConnectionString(在工作簿将成为这项工作一“表”每个工作表),并使用System.Data
查询它和你一样将常规数据库(唐·琼斯为此写了一wrapper function其可能有帮助)。
这个应该比启动Excel &更快地通过细胞逐个挑选。
在他的博客中Speed Up Reading Excel Files in PowerShell,罗伯特·Toups,小解释说,虽然加载到PowerShell是快速,实际上读取Excel单元格很慢。在另一方面,PowerShell中可以读取的文本文件非常快,所以他的解决办法是加载在PowerShell中的电子表格,使用Excel自带的CSV出口过程中,将其保存为CSV文件,然后使用PowerShell的标准Import-Csv
cmdlet来超炫处理数据快速。他报道说这使他的进口过程快了20倍!
利用Toups’代码,我创建了一个Import-Excel
功能,您可以导入电子表格数据变得非常容易。 我的代码添加了在Excel工作簿中选择特定工作表的功能,而不是仅使用默认工作表(即保存文件时的活动工作表)。如果您省略–SheetName
参数,它将使用默认工作表。
function Import-Excel([string]$FilePath, [string]$SheetName = "")
{
$csvFile = Join-Path $env:temp ("{0}.csv" -f (Get-Item -path $FilePath).BaseName)
if (Test-Path -path $csvFile) { Remove-Item -path $csvFile }
# convert Excel file to CSV file
$xlCSVType = 6 # SEE: http://msdn.microsoft.com/en-us/library/bb241279.aspx
$excelObject = New-Object -ComObject Excel.Application
$excelObject.Visible = $false
$workbookObject = $excelObject.Workbooks.Open($FilePath)
SetActiveSheet $workbookObject $SheetName | Out-Null
$workbookObject.SaveAs($csvFile,$xlCSVType)
$workbookObject.Saved = $true
$workbookObject.Close()
# cleanup
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) |
Out-Null
$excelObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) |
Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
# now import and return the data
Import-Csv -path $csvFile
}
这些补充功能是使用进口的Excel:
function FindSheet([Object]$workbook, [string]$name)
{
$sheetNumber = 0
for ($i=1; $i -le $workbook.Sheets.Count; $i++) {
if ($name -eq $workbook.Sheets.Item($i).Name) { $sheetNumber = $i; break }
}
return $sheetNumber
}
function SetActiveSheet([Object]$workbook, [string]$name)
{
if (!$name) { return }
$sheetNumber = FindSheet $workbook $name
if ($sheetNumber -gt 0) { $workbook.Worksheets.Item($sheetNumber).Activate() }
return ($sheetNumber -gt 0)
}
'Import-CSV'能否让您选择特定的单元格和列数据? – ProfessionalAmateur 2013-03-21 17:13:23
命令行工具的最佳实践(回到Unix时代)是它应该做好一件事情。所以Import-Csv只是导入整个事情。但是,您只需应用PowerShell的强大功能,通常是“Where-Object”选择行,“Select-Object”选择列。 – 2013-03-21 18:45:28
呃我检查一下。因为excel数据的格式不一致,所以要坚持下去。柱状数据在整个一天都不统一。我会和它一起玩,看看。我可能需要将powershell保存为CSV文件,因为不会有任何用户交互获取这些文件。 – ProfessionalAmateur 2013-03-21 19:06:09
这是非常有趣的,我给它一个镜头的明天,看看它是如何执行。 – ProfessionalAmateur 2013-03-21 02:02:40
这太好了。疯狂地更快。 – ProfessionalAmateur 2013-03-22 18:24:44