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 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 a free evaluation here .
Pay special attention to where you unzip the resources folder and the location of your OfficeWriter dll because the script looks for specific paths.
################################################################################################### Dell Warranty Export to Excel#### by Jim Stallings ([http://www.officewriter.com])################################################################################################# # Load the dell-warranty powershell moduleImport-Module 'C:\modules\DellWarrantyInfo\DellWarrantyInfoFunction.psm1'
# Load the out-Datatable powershell moduleImport-Module 'C:\modules\OutTable\OutDataTable.psm1'
# Add the OfficeWriter assemblyAdd-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 ExcelTemplate 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
- Error Handling
- Relative Paths
- Automated computer look up (list, network lookup)
- More ExcelTemplate eye candy
Resources
Share the post "OfficeWriter for the IT Pro: Automated Dell Warranty Lookup using Powershell and ExcelTemplate"
Follow