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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | ############################################################################### # 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