Powershell Outperforms Access Without Installing Excel

Question

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

I tried the Excel viewer, but it does not seem to work; he will not be referenced properly. I looked at other solutions in stackoverflow, but all of them seem to want to update the Excel spreadsheet, and I hope I don't need to go that far.

Am I missing something obvious?

Answer 1

. Scripting Guy. COM ADO Powershell Script.

, ! Excel Excel?

Powershell:

$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()

, , Excel .

Microsoft Excel OLEDB.

:

"" . ID "" Password ", Excel . ( Excel, , . . .)

Answer 2

Excel, EPPlus - , Excel PowerShell. , EPPlus PowerShell.

passwort, Get-Process, :

# 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

Excel . .