New-ExcelWorkbook.ps1

1. <# 
2. .SYNOPSIS 
3.     This script creates an Excel workbook using PowerShell 
4. .DESCRIPTION 
5.     This script demonstrates manipulating Excell with PowerShell 
6.     and the Excel.Application COM object. 
7. .NOTES 
8.     File Name  : New-ExcelWorkbook.ps1 
9.     Author     : Thomas Lee - tfl@psp.co.uk 
10.     Requires   : PowerShell Version 2.0 
11. .LINK 
12.     This script posted to: 
13.         http://pshscripts.blogspot.com/2010/03/new-excelworkbookps1.html
14.     MSDN Sample posted at: 
15.         http://msdn.microsoft.com/en-us/library/bb211359.aspx  
16. .EXAMPLE
17.     Run it and see one! 
18. #> 
19. ##  
20. # Start of Script 
21. ## 
22. # Then we create and save a sample worksheet 
23. # Create Excel object 
24. $excel = new-object -comobject Excel.Application 
25.     
26. # Make Excel visible 
27. $excel.visible = $true 
28.    
29. # Create a new workbook 
30. $workbook = $excel.workbooks.add() 
31.  
32. # The default workbook has three sheets, remove 2 
33. $S2 = $workbook.sheets | where {$_.name -eq "Sheet2"} 
34. $s3 = $workbook.sheets | where {$_.name -eq "Sheet3"} 
35. $s2.delete() 
36. $s3.delete() 
38. # Get sheet and update sheet name 
39. $s1 = $workbook.sheets | where {$_.name -eq 'Sheet1'} 
40. $s1.name = "PowerShell Sample" 
41.    
42. # Update workook properties 
43. $workbook.author = "Thomas Lee - tfl@psp.co.uk" 
44. $workbook.title = "Excel and PowerShell rock!" 
45. $workbook.subject = "Demonstrating the Power of PowerShell with Excel" 
46.    
47. # Next update some cells in the worksheet 'PowerShell Sample' 
48. $s1.range("A1:A1").cells="Cell a1" 
49. $s1.range("A2:A2").cells="A2" 
50. $s1.range("b1:b1").cells="Cell B1" 
51. $s1.range("b2:b2").cells="b2" 
52.  
53. # now make a sum 
54. $s1.range("E1:E2").cells="Widgets" 
55. $s1.range("E2:E2").cells=2 
56. $s1.range("E3:E3").cells=2 
57. $s1.range("E4:E4").cells=38 
58. $s1.range("D5:D5").cells="Total" 
59. $s1.range("E5:E5").cells.formula = "=sum(e2,E4)" 
60.    
61. # And save it away: 
62. $s1.saveas("c:\foo\xlsx3.xlsx") 
63. # end of script 

Technorati Tags: Excel,PowerShell,Scripts,Excel.Application