locked
Test 4 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
    }
    #---------------------------------------------------------------------------------------------------------------------------------------------------------
    Forums Tester
    Thursday, June 10, 2010 1:07 AM