Product Company Support Download Purchase Blog Reviews
   
  Download Eval Now
divider
 
OfficeWriter Blogs
 

OfficeWriter for the IT Pro: Automated Dell Warranty Lookup using Powershell and ExcelTemplate

Submitted by Jims on Thu, 12/22/2011 - 10:39

OfficeWriter for the IT Pro posts are aimed at exploring ways to extend the use of OfficeWriter to the IT work space.

This script will dynamically query Dell's Warranty web-service (http://xserv.dell.com/services/assetservice.asmx) via PowerShell and export the results to an Excel (xlsx) file using OfficeWriter's ExcelTemplate object. I've added colored conditional formatting depending on how many days are left before the warranty expires.

In the script, we leverage two external community provided PowerShell functions, Out-DataTable and Get-DellWarranty. Get-DellWarranty accepts a computer name then returns the results as a PowerShell object. The ExcelTemplate object will not bind a PowerShell object so we use Out-DataTable to convert the object into a .NET DataTable.

You will need proper permissions and PowerShell access to run the script against remote servers. You will need to modify the $myComputerList variable to include the computers that you want to query. You will need to download the resources.zip file attached to this post. It contains the required PowerShell modules, DellWarrantyExporttoExcel script, DellWarrantyLook.xlsx excel template, and a sample excel output file (output.xlsx). The final requirement to run the script is a copy of OfficeWriter Standard. You can download an evaluation from http://www.officewriter.com/officewriter-free-evaluation.aspx.

Pay special attention to where you unzip the resources folder and the location of your OfficeWriter dll because the script looks for specific paths.

DellWarrantyExportToExcel.ps1
###############################################################################################
##
## Dell Warranty Export to Excel
##
## by Jim Stallings ([http://www.officewriter.com])
##
###############################################################################################
 
# Load the dell-warranty powershell module
Import-Module 'C:\modules\DellWarrantyInfo\DellWarrantyInfoFunction.psm1'
 
# Load the out-Datatable powershell module
Import-Module 'C:\modules\OutTable\OutDataTable.psm1'
 
# Add the OfficeWriter assembly
Add-Type -Path 'C:\Program Files\SoftArtisans\OfficeWriter\bin\dotnet\SoftArtisans.OfficeWriter.ExcelWriter.dll'
 
# Create a array to hold all the computers you want to query
$myComputerList = 'hvint01','tyche','eudora'
 
# Create a new ExcelApplication object
$xlt = New-Object "SoftArtisans.OfficeWriter.ExcelWriter.ExcelTemplate"
 
# Open the template file
$xlt.Open("C:\templates\DellWarrantyLookup.xlsx")
 
# Create a DataBindingProperties object. A DataBindingProperties is a
# required parameter in all ExcelTemplate data binding methods
$dataProps = $xlt.CreateDataBindingProperties()
 
# Get the computer(s) warranty info
 
for($i = 0; $i -le $myComputerList.Count - 1; $i++)
{
 
if($i -eq 0){
 
# Retrieve the Dell Warranty Information for the first computer in our myComputerList array
$computer = get-DellWarranty $myComputerList[$i] | Out-DataTable
 
}
 
else{
 
# Add the remaining computers to the data table
$nextcomputer = get-DellWarranty $MyComputerList[$i]
$workRow = $computer.NewRow()
$workRow['ComputerName'] = $nextcomputer.ComputerName
$workRow['ServiceTag'] = $nextcomputer.ServiceTag
$workRow['SystemType'] = $nextcomputer.SystemType
$workRow['EndDate'] = $nextcomputer.EndDate
$workRow['DaysLeft'] = $nextcomputer.DaysLeft
 
# Add the new row to the data table created earlier
$computer.Rows.Add($workRow)
 
}
 
}
 
# Bind the data to the data sets
$xlt.BindData($computer, 'Computer', $dataProps)
 
# Import the data into the template
$xlt.Process()
 
# Save the output
$xlt.Save('C:\output\Output.xlsx')

 

Here is a look at the Excel template:

 

Here is the sample output file:

 

 

 

Future Updates

  1. Error Handling
  2. Relative Paths
  3. Automated computer look up (list, network lookup)
  4. More ExcelTemplate eye candy

 

 

Resources

Tags: 
powershell
officewriter
exceltemplate
dell warrenty web services
Category: 
OfficeWriter Tutorials
footer
Follow SoftArtisans on Facebook! Follow SoftArtisans OfficeWriteron Twitter! Follow SoftArtisans on LinkedIn! Follow SoftArtisans Blogs! Follow SoftArtisans on YouTube!
divider
Copyright 2013 © SoftArtisans, Inc. All Rights Reserved.
1.877.SOFTART or +1.617.607.8800 | 3 Brook Street Watertown, MA, USA 02472 | E: contact@softartisans.com | Contact Us