excel - PowerShell | OLEDB | Excel |无法从第三方应用程序创建的 XLSX 读取数据

Question

**更新 (2012.12.13) - 添加示例代码、输入格式、输出示例

我已经为此奋斗了几天，现在我已经没有想法了。我已经成功针对多个 XLSX 文件测试了一个脚本(并且可以在将其放在我面前后上传)。我已经有了连接字符串功能，可以解析我需要的数据，等等...

问题是，当我尝试处理输入文件(从第三方报告应用程序生成)时，不会从工作表中读取数据。

如果我在 Excel 中打开并保存输入文件(没有格式更改、没有数据输入/删除、根本没有修改)，输入文件将处理和解析所有数据。

我尝试在连接字符串中使用多个“扩展属性”设置，但无济于事，包括 HDR=Yes/No 和 IMEX=1。

有人曾经见过这样的事情吗？

<小时/>

#inputFile_original.xlsx will not parse the data from the worksheet
#inputFile_original_reSaved.xlsx parses the data without any issues

$fileName  = "inputFile_original.xlsx"
#$fileName = "inputFile_original_reSaved.xlsx"
$filePath  = ".\OLEDB\test\"

#Build the connection string
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
$ConnectionString += (Join-Path -Path $filePath -ChildPath $fileName)
$ConnectionString += ";OLE DB Services=-4;Extended Properties="
$ConnectionString += '"Excel 12.0 Xml;HDR=YES;IMEX=1";'

$conn   = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$conn.Open()

$tables = $conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)

$cmd    = New-Object System.Data.OleDb.OleDbCommand("Select * FROM [$($tables.rows[0].TABLE_NAME)]",$conn)

$da     = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
$ds     = New-Object System.Data.DataSet
$da.Fill($ds)

#Output the data to the console
$ds.tables

<小时/>

此外，输入文件的格式并不是真正易于使用的布局。同样，由于是由第三方应用程序生成的。

存在空行，并且标题行不是从第 1 行开始。

      A           B           C           D           E           F   --------------------------------------------------------------------------01 | ReportTitle02 |03 | ColHeader1  ColHeader2  ColHeader3  ColHeader4  ColHeader5  ColHeader604 | Data        Data        Data        Data        Data        Data05 | Data        Data        Data        Data        Data        Data06 | Data        Data        Data        Data        Data        Data07 | Data        Data        Data        Data        Data        Data08 | Data        Data        Data        Data        Data        Data09 |10 | Total: 5

我收到的输出如下所示。
(一)原始文件

Report Title :F2           :F3           :F4           :F5           :F6           :Report Title : ColHeader1F2           : F3           : F4           : F5           : F6           :

(B) 重新保存文件

Report Title :F2           :F3           :F4           :F5           :F6           :Report Title : ColHeader1F2           : ColHeader2F3           : ColHeader3F4           : ColHeader4F5           : ColHeader5F6           : ColHeader6Report Title : DataF2           : DataF3           : DataF4           : DataF5           : DataF6           : DataReport Title : DataF2           : DataF3           : DataF4           : DataF5           : DataF6           : DataReport Title : DataF2           : DataF3           : DataF4           : DataF5           : DataF6           : DataReport Title : DataF2           : DataF3           : DataF4           : DataF5           : DataF6           : DataReport Title : DataF2           : DataF3           : DataF4           : DataF5           : DataF6           : DataReport Title :F2           :F3           :F4           :F5           :F6           :Report Title : Total: 5F2           :F3           :F4           :F5           :F6           :

Answer 1

您可以直接从 Powershell 在 Excel 中打开它们，而不是使用 OLEDB 打开 Excel 文件...这是一个仅打印工作表大小的快速示例。我从中了解到的唯一问题是，每次都必须完全退出 Excel 并清除其中的任何残余内容(最后的 GC 内容)。

foreach($File in $excelFiles)
{
   $excel = New-Object -comobject Excel.Application
   $excel.visible = $false
   $workbook = $excel.Workbooks.Open($File)

   write-host "There are $($workbook.Sheets.count) sheets in $File"
   For($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
   {
     $worksheet = $workbook.sheets.item($i)
     $rowMax = ($worksheet.usedRange.rows).count
     $columnMax = ($worksheet.usedRange.columns).count
     write-host "Sheet $($i) ($($worksheet.Name)) has dimensions $($rowMax) x $($columnMax)"

   $worksheet = $rowMax = $columnMax = $null
   } #end for
$workbook.close($false)
$workbook = $null
$excel.quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

} #end foreach