by oising
21. November 2007 22:51
Well, this is not exactly rocket science, but it helped me out today with some spreadsheets tomfoolery. It generates PSCustomObjects with properties named after the header row in the Excel sheet (you need a header row for this to work), just like Import-Csv. The parameters should be self-explanatory: filename and worksheet.
param (
[
string]$filename = $(throw "need a filename, e.g. c:\temp\test.xls"),
[string]$worksheet
)
if
(-not (Test-Path $filename)) {
throw "Path '$filename' does not exist."
exit
}
if
(-not $worksheet) {
Write-Warning "Defaulting to Sheet1 in workbook."
$worksheet = "Sheet1"
}
# resolve relative paths
$filename = Resolve-Path $filename
# assume header row (HDR=YES)
$connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data" +
"Source=${filename};Extended Properties=`"Excel 8.0;HDR=YES`"";
$connection
= New-Object data.OleDb.OleDbConnection $connectionString;
$connection.Open();
$command = New-Object data.OleDb.OleDbCommand "select * from [$worksheet`$]"
$command
.connection = $connection
$reader = $command.ExecuteReader("CloseConnection")
if
($reader.HasRows) {
# cache field names
$fields = @()
$count = $reader.FieldCount
for ($i = 0; $i -lt $count; $i++) {
$fields += $reader.GetName($i)
}
while ($reader.read()) {
trap [exception] {
Write-Warning "Error building row."
break;
}
# needs to match field count
$values = New-Object object[] $count
# cache row values
$reader.GetValues($values)
$row = New-Object psobject
$fields | foreach-object -begin {$i = 0} -process {
$row | Add-Member -MemberType noteproperty -Name $fields[$i]
-Value $values[$i]; $i++
}
$row # emit row
}
}