powerhell excel访问而不安装Excel [英] powershell excel access without installing Excel

问题描述

我需要从Powershell中读取现有的（密码保护）Excel电子表格（.xlsx文件） - 但是我不想安装Excel。我发现每种方法都假定Excel安装在脚本运行的工作站上。

我已经尝试过Excel查看器，但似乎没有工作;它不会正常调用。我看过stackoverflow上的其他解决方案，但是他们似乎都想要更新excel电子表格，我希望我不用那么远。

解决方案

请参阅脚本专家中的详细文章。您必须在Powershell脚本中使用经典的COM ADO。

嘿，脚本专家！如何从Excel中读取而不使用Excel？

相关Powershell片段：

  $ strFileName =C：\Data\scriptingGuys\Servers.xls
 $ strSheetName ='ServerList $'
 $ strProvider = Provider = Microsoft.Jet.OLEDB.4.0
 $ strDataSource =Data Source = $ strFileName
 $ strExtend =扩展属性= Excel 8.0
 $ strQuery =Select * from [$ strSheetName]
 
 $ objConn =新对象System.Data.OleDb.OleDbConnection（$ strProvider; $ strDataSource; $ strExtend）
 $ sqlCommand =新对象系统。 Data.OleDb.OleDbCommand（$ strQuery）
 $ sqlCommand.Connection = $ objConn 
 $ objConn.open（）
 $ DataReader = $ sqlCommand.ExecuteReader（）
 
 $（$ DataReader.read（））
 {
 $ ComputerName = $ DataReader [0] .Tostring（）
查询$ computerName ...
 Get-WmiObject - 类Win32_Bios -computername $ ComputerName 
} 
 $ dataReader.close（）
 $ objConn.close（）
  

表示您已经表示您的Excel文件受密码保护。

根据此Microsoft支持文章< a>，您无法使用OLEDB连接打开受密码保护的Excel文件。

从文章：

在连接选项卡上，浏览到您的工作簿文件。忽略用户
ID和密码条目，因为这些不适用于Excel
连接。 （您不能打开一个受密码保护的Excel文件作为数据
源。有关此主题的更多信息，稍后在
文章。）

I need to be able to read an existing (password protected) Excel spreadsheet (an .xlsx file) from Powershell - but I don't want to install Excel. Every approach I've found assumes that Excel is installed on the workstation where the script is running.

I've tried the Excel viewer, but it doesn't seem to work; it won't invoke properly. I've looked at other solutions on stackoverflow, but all of them seem to want to update the excel spreadsheet, and I'm hoping I don't have to go that far.

Am I missing something obvious?

解决方案

See the Detailed Article from Scripting Guy here. You have to use classic COM ADO in your Powershell Script.

Hey, Scripting Guy! How Can I Read from Excel Without Using Excel?

Relevant Powershell Snippet:

$strFileName = "C:\Data\scriptingGuys\Servers.xls"
$strSheetName = 'ServerList$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"
$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$DataReader = $sqlCommand.ExecuteReader()

While($DataReader.read())
{
 $ComputerName = $DataReader[0].Tostring() 
 "Querying $computerName ..."
 Get-WmiObject -Class Win32_Bios -computername $ComputerName
}  
$dataReader.close()
$objConn.close()

That said, you have stated that your Excel file is password protected.

According to this Microsoft Support article, you cannot open password protected Excel files using OLEDB Connections.

From the Article:

On the Connection tab, browse to your workbook file. Ignore the "User ID" and "Password" entries, because these do not apply to an Excel connection. (You cannot open a password-protected Excel file as a data source. There is more information on this topic later in this article.)

这篇关于powerhell excel访问而不安装Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！