powershell excel access without installing Excel

Question

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?

score 6 · Accepted Answer · answered Nov 15 '13 at 22:16

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.)

score 4 · Answer 2 · edited May 23 '17 at 12:24

If you don't have Excel installed, EPPlus is the best solution I know of to access Excel files from PowerShell. Refer to my answer here to setup EPPlus for PowerShell.

The following code creates a passwort protected Excel file containing the output of Get-Process and then reads back the process information from the password protected file:

# Load EPPlus
$DLLPath = "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\EPPlus\EPPlus.dll"
[Reflection.Assembly]::LoadFile($DLLPath) | Out-Null

$FileName = "$HOME\Downloads\Processes.xlsx"
$Passwort = "Excel"

# Create Excel File with Passwort
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage 
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add("FromCSV")
$ProcessesString = Get-Process | ConvertTo-Csv -NoTypeInformation | Out-String
$Format = New-object -TypeName OfficeOpenXml.ExcelTextFormat -Property @{TextQualifier = '"'}
$null=$Worksheet.Cells.LoadFromText($ProcessesString,$Format)
$ExcelPackage.SaveAs($FileName,$Passwort)

# Open Excel File with Passwort
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $FileName,$Passwort
# Select First Worksheet
$Worksheet = $ExcelPackage.Workbook.Worksheets[1]
# Get Process data from Cells
$Processes = 0..$Worksheet.Dimension.Columns | % { 
    # Get all Cells in a row
    $Row = $Worksheet.Cells[($Worksheet.Dimension.Start.Row+$_),$Worksheet.Dimension.Start.Column,($Worksheet.Dimension.Start.Row+$_),$Worksheet.Dimension.End.Column]
    # Join values of all Cells in a row to a comma separated string
    ($Row | select -ExpandProperty Value) -join ','
} | ConvertFrom-Csv

Refer to my answer here for more options to protect Excel files.

powershell excel access without installing Excel

2 Answers2

Linked