PowerShell – get data from xls

This is not a fairy tale at all 🙂 just think on the regional settings…

Issue: Get data from a cell

First of all for this exmample an excel application should be installed to the machine. So here are some lines to get the data from a sheet (A1 cell)

$strExcelFile = “d:\data.xls”
$objExcel = New-Object -comobject Excel.Application
$objWorkBook = $objExcel.Workbooks.Open($strExcelFile)

write-host “number of Worksheets: ” $objWorkbook.Sheets.Count
$sheet = 1
$objSheet = $objWorkbook.Sheets.Item($sheet)
$objSheet.Cells.Item(1,1).Text

So from here you can start a “for cycle”. To get the number of columns at a given sheet you can use these lines:

$col = 1
$numCols = 0
while ($objSheet.Cells.Item(1,$col).Text -ne “”)
{$col +=1; $numCols +=1}

And here we are at the freaky part: To be able to get data from the xls the regional seetings of the Powershell env and the excel application should be the same.

function Import-Excel($name = $(Throw “‘name’ parameter is obligatory”), $sheet = 1){

$xl = new-object -com Excel.Application

# The locale could cause problems, so let’s set it to en-US
# details: http://support.microsoft.com/kb/320369
$ci = [System.Globalization.CultureInfo]’en-us’
$wb = $xl.workbooks.psbase.gettype().InvokeMember(“Open”,[Reflection.BindingFlags]::InvokeMethod, $null, $xl.workbooks, ($name,$false,$true), $ci)

# worksheet
$sh = $wb.sheets.item($sheet)

# column headers
$head = $sh.range($sh.range(“A1”), $sh.range(“A1”).end(-4161))

set-variable $ImportExcelCount -scope global -value ($sh.range(“A1”).end(-4121).row – 1)

# read each row as a associative array
#$sh.range($sh.range(“A2”), $sh.range(“A1”).end(-4121)).rows | foreach{ $row=$_; $out=@{}; $head | foreach{ $out[$_.formulalocal]=$row.range($_.addresslocal()).formulalocal}; $out }
$sh.range($sh.range(“A2”), $sh.range(“A4”)).rows | foreach{ $row=$_; $out=@{}; $head | foreach{ $out[$_.formulalocal]=$row.range($_.addresslocal()).formulalocal}; $out }

$null = $wb.psbase.gettype().InvokeMember(“Close”,[Reflection.BindingFlags]::InvokeMethod, $null, $wb, $false, $ci)
$null = $xl.quit()

#unload from memory
# see: http://www.microsoft.com/technet/scriptcenter/resources/pstips/nov07/pstip1130.mspx
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
}

This method is slow but it works at last 🙂

After importing xls you canrefer to the columns by their names:

Import-Excel $recipientXlsPath | foreach {
write-progress -act “Sending e-mails” -stat “$($i+1) / $ImportExcelCount” -percent ((100 * $i++ / $ImportExcelCount)%100)
if ($projcode_coloumnheader -ne $null) { $footer = $_[$idColumnHeader] + ‘             Questionarie: ‘ + $doctype + ‘     Project-Code: ‘ + $_[$projcode_coloumnheader]}
else { $footer = $_[$idColumnHeader] + ‘             Questionarie: ‘ + $doctype}
set-wdfooter -doc $tempfile -text $footer -pass $password -word $word

$msg = new-object Net.Mail.MailMessage
$att = new-object Net.Mail.Attachment($tempfile)

$msg.From = $from
$msg.To.Add($_[$mailAddressColumnHeader])
$msg.Subject = $subject
$msg.Body = $body
$msg.Attachments.Add($att)

$smtp.Send($msg)
$att.Dispose()

}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s