Tag Archives: powershell

Powershell: Some basic XML handling with Powershell and .NET

Hi everybody,

here are some fundamentals to handle XML Files with powershell.

The following commands uses these XML File


<config description="Config file for testing">
    <system description="Document Management">
        <document authorFirstName="Kirk" authorSurname="Author1" date="20130503 01:15:43" description="Document Hammet" index="3">C:\temp\doc0001.txt</document>
        <document authorFirstName="Lars" authorSurname="Author5" date="20130503 10:05:42" description="Document Ulrich" index="4">C:\temp\doc0002.txt</document>
        <document authorFirstName="Cliff" authorSurname="Author5" date="20130612 11:54:33" description="Document Burton"  index="6">C:\temp\doc0051.txt</document>
        <document authorFirstName="Jason" authorSurname="Author2" date="20130806 04:02:41" description="Document Newsted"  index="1">C:\temp\doc0041.txt</document>
        <document authorFirstName="Robert" authorSurname="Smith" date="20131202 07:12:03" description="Document Trujillo"  index="2">C:\temp\doc0012.txt</document>
        <document authorFirstName="James" authorSurname="Smith" date="20130211 09:05:59" description="Document Hetfield"  index="5">C:\temp\doc0003.txt</document>
    </system>
</config>

Creating  a XML File, create a document root, create subelements, add and set attributes, save the file

# Create a new XML File with config root node
[System.XML.XMLDocument]$oXMLDocument=New-Object System.XML.XMLDocument
# New Node
[System.XML.XMLElement]$oXMLRoot=$oXMLDocument.CreateElement("config")
# Append as child to an existing node
$oXMLDocument.appendChild($oXMLRoot)
# Add a Attribute
$oXMLRoot.SetAttribute("description","Config file for testing")
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("system"))
$oXMLSystem.SetAttribute("description","Document Management")
# Save File
$oXMLDocument.Save("c:\temp\config.xml")

Loading an existing XML File, read an element, do some XPATH queries

Continue reading Powershell: Some basic XML handling with Powershell and .NET

Advertisment to support michlstechblog.info

Powershell: Control Excel remotely

Hi,

I often have to collect some data and fill out some excel lists with those. Here are a few examples how to control Excel using the COM object with powershell.


###############################################################################
# This script controls Excel remotely
# Author Michael Albert michlstechblog.info
# License: GPL v2
################################################################################
# File must exists
$sInputFile="D:\temp\exceldocument.xlsx"
# Get COM Object
$oExcel = New-Object -ComObject "Excel.Application"
# Set Excel visible
$oExcel.Visible = $true
# open excel file
$oExcelDoc = $oExcel.Workbooks.Open($sInputFile)
# Open 1st Worksheet
$oWorksheet = $oExcelDoc.Worksheets.item(1)
# Jump to worksheet (only GUI)
$oWorksheet.Activate()
# Get Rows and columns dimension
$iUsedRangeRow  = $oWorksheet.UsedRange.Rows.Count
$iUsedRangeColumns  = $oWorksheet.UsedRange.Columns.Count
# Auto adjust column with
$oWorksheet.UsedRange.EntireColumn.AutoFit()|out-null
# Read a cell A1 A=1 1=1, Row and columns startet by 1
write-host "Cell A1" $oWorksheet.Cells.Item(1,1).Text
# Write text
$oWorksheet.Cells.Item(2,1).value2="New Text"
# Activate cell (only GUI)
$oWorksheet.Cells.Item(1,1).Activate()|out-null
# Set Backgroundcolor of a cell
$oWorksheet.Cells.Item(1,1).Interior.ColorIndex = 4
# With RBG, first load assembly
[reflection.assembly]::LoadWithPartialName("System.Drawing")
# With .NET defined color constants
$oWorksheet.Cells.Item(2,1).Interior.Color=[System.Drawing.Color]::White.ToARgb()
# With  RBG Color
$oWorksheet.Cells.Item(2,1).Interior.Color=([System.Drawing.Color]::FromArgb(254,7,7)).ToArgb()
# Set Font Color
$oWorksheet.Cells.Item(2,1).Font.ColorIndex = 1
# Font Size
$oWorksheet.Cells.Item(2,1).Font.Size=12
# Rotate
$oWorksheet.Cells.Item(2,1).Orientation=45
# Bold
$oWorksheet.Cells.Item(2,1).Font.Bold = $true
# Strike through
$oWorksheet.Cells.Item(2,1).Font.Strikethrough=$true
# Write a comment to a cell
$oWorksheet.Cells.Item(2,1).AddComment("This is a comment")|Out-Null

# Format Cell Range
$oWorksheet.Range("A1:B3").Numberformat="00,0"
$oWorksheet.Cells.Item(2,1).NumberFormat="@"
$oWorksheet.Cells.Item(2,1).NumberFormatLocal="@"

# New Worksheet and get object
$oNewWorksheet=$oExcelDoc.Worksheets.add()
# Rename new Worksheet
$oNewWorksheet.Name="My new worksheet"

# Close Document and save
$oExcelDoc.Close($true)
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
$oWorksheet=$null
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
$oExcelDoc=$null
# Close Excel
$oExcel.Quit()
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
$oExcel=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

To be continued 🙂

Michael