none
test2 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:15 PM
    Moderator