locked
Test New post RRS feed

  • Question

  • #--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
    #Author : Markus Vilcinskas
    
    
    
    #Date : 06/09/10
    
    
    
    #Purpose: This script creates a catlog page for the TechNet forum
    
    
    
    #--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     $curFolder = Split-Path -Parent $MyInvocation.MyCommand.Path
    
    
    
     if($args.count -ne 1) {throw "Workbook URL is missing!"}
    
    
    
     $dataFile = $args[0]
    
    
    
     $xsltFile = "$curFolder\$($dataFile.Substring($dataFile.LastIndexOf("/")+ 1) -replace(".xlsx", ".xslt"))"
    
    
    
    
    
    
    
     If((Test-Path $xsltFile) -eq $false) {Throw "XSLT File Not Found: $xsltFile"}
    
    
    
     $iMax = 5
    
    
    
     $iStep = 0
    
    
    
     
    
    
    
     $a = $iMax / $iStep
    
    
    
     
    
    
    
    #--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
    #Open workbook
    
    
    
    #--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     $iStep++ 
    
    
    
     Write-Progress -activity "Opening workbook" `
    
    
    
      -status "Please wait" -percentcomplete ([int]($iStep/$iMax * 100)) `
    
    
    
      -currentoperation "Processing step $iStep of $iMax"
    
    
    
    
    
    
    
     $oExcel = new-object -comobject excel.application 
    
    
    
     $oExcel.visible = $false 
    
    
    
     $oExcel.DisplayAlerts = $false
    
    
    
     $oWorkbook = $oExcel.Workbooks.Open($dataFile)
    
    
    
     $oSheet = $oWorkbook.ActiveSheet
    
    
    
     $oRange = $oSheet.UsedRange
    
    
    
     $oRange2 = $oExcel.Range("A1")
    
    
    
     $oRange.Sort($oRange2, 2, $null, $null,1) | Out-Null
    
    
    
    #--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
    #Read workbook data and put it into a XML file
    
    
    
    #--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     $iStep++ 
    
    
    
     Write-Progress -activity "Creating XML data file" `
    
    
    
      -status "Please wait" -percentcomplete ([int]($iStep/$iMax * 100)) `
    
    
    
      -currentoperation "Processing step $iStep of $iMax"
    
    
    
    
    
    
    
     [xml]$xmlDoc = "<items/>"
    
    
    
     $iRow = 2
    
    
    
     $colMax = 1
    
    
    
     While($oSheet.Cells.Item(1, $colMax).Text.Length -gt 0){$colMax++}
    
    
    
     While($oSheet.Cells.Item($iRow, 1).Text.Length -gt 0)
    
    
    
     {
    
    
    
     $newNode = $xmlDoc.CreateElement("item")
    
    
    
     $xmlDoc.DocumentElement.AppendChild($newNode) | Out-Null
    
    
    
     For($i = 1; $i -lt $colMax; $i++)
    
    
    
     {
    
    
    
     $x = $xmlDoc.CreateElement($oSheet.Cells.Item(1, $i).Text)
    
    
    
     $x.set_InnerText($($oSheet.Cells.Item($iRow, $i).Text)) | Out-Null
    
    
    
     $newNode.AppendChild($x) | Out-Null
    
    
    
     }
    
    
    
     $iRow++
    
    
    
     }
    
    
    
     $oExcel.ActiveWorkbook.Close() | Out-Null
    
    
    
     $oExcel.Quit()
    
    
    
     spps -n excel
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
    #Transform the XML data
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     $iStep++ 
    
    
    
     Write-Progress -activity "Transforming data" `
    
    
    
      -status "Please wait" -percentcomplete ([int]($iStep/$iMax * 100)) `
    
    
    
      -currentoperation "Processing step $iStep of $iMax"
    
    
    
    
    
    
    
     $memStream = New-Object System.IO.MemoryStream
    
    
    
     $xslt = New-Object System.Xml.Xsl.XslCompiledTransform
    
    
    
     $xslt.Load($xsltFile)
    
    
    
     $xslt.Transform($xmlDoc, $null, $memStream) | Out-Null
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
    #Store data in clipbaord
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     $iStep++ 
    
    
    
     Write-Progress -activity "Storing data in clipboard" `
    
    
    
      -status "Please wait" -percentcomplete ([int]($iStep/$iMax * 100)) `
    
    
    
      -currentoperation "Processing step $iStep of $iMax"
    
    
    
    
    
    
    
     $memStream.Position = 0
    
    
    
     $reader = New-Object System.IO.StreamReader $memStream
    
    
    
     $dataBuf = $reader.ReadToEnd() 
    
    
    
     $dataBuf | clip
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
    #Show data in IE
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     $iStep++ 
    
    
    
     Write-Progress -activity "Starting Inteernet Explorer" `
    
    
    
      -status "Please wait" -percentcomplete ([int]($iStep/$iMax * 100)) `
    
    
    
      -currentoperation "Processing step $iStep of $iMax"
    
    
    
    
    
    
    
     $ie = new-object -com "InternetExplorer.Application"
    
    
    
     $ie.ToolBar = 0
    
    
    
     $ie.StatusBar = 0
    
    
    
     $ie.AddressBar = 0
    
    
    
     $ie.navigate("about:blank")
    
    
    
     $ie.visible = $true
    
    
    
     $ie.document.body.innerHTML = $dataBuf
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     Clear-Host
    
    
    
     Write-Host "`nData copied to the clipboard"
    
    
    
     Write-Host "`nCommand completed successfully`n"
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
     Trap 
    
    
    
     { 
    
    
    
     Write-Host "`nError: $($_.Exception.Message)`n" -foregroundcolor white -backgroundcolor darkred
    
    
    
     
    
    
    
     $ErrorRecord=$Error[0]
    
    
    
     $ErrorRecord | Format-List * -Force
    
    
    
     $ErrorRecord.InvocationInfo |Format-List *
    
    
    
     $Exception = $ErrorRecord.Exception
    
    
    
     for ($i = 0; $Exception; $i++, ($Exception = $Exception.InnerException))
    
    
    
     {
    
    
    
     "$i" * 80
    
    
    
     $Exception |Format-List * -Force
    
    
    
     }
    
    
    
     
    
    
    
     Exit
    
    
    
     }
    
    
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    Wednesday, June 9, 2010 10:11 PM