locked
Test Again !!! 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
     }
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    

    Santosh Kumar Nair
    Thursday, June 10, 2010 6:50 PM

All replies

  • #--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    #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
    
     }
    
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    
    

    Santosh Kumar Nair


    Santosh Kumar Nair
    Thursday, June 10, 2010 7:00 PM