powershell生成Excel表格，自动填充，可以设置颜色

gflrlm

如题， powershell修改Excel，设置颜色，内容， 等。不知道跟vba相比，优缺点如何，有待高人解答。

1. 
   
2. $processes=Get-Process
   
3. $excel = New-Object -ComObject Excel.Application
   
4. $excel.Visible = $true
   
5. $workbook = $excel.Workbooks.add()
   
6. $sheet = $workbook.worksheets.Item(1)
   
7. #$workbook.Worksheets.item(3).delete()
   
8. $workbook.Worksheets.item(2).delete()
   
9. $workbook.Worksheets.item(1).name="Processes"
   
10. $sheet = $workbook.WorkSheets.Item("Processes")
    
11. $x = 2
    
12. 
    
13. $lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
    
14. $colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
    
15. $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
    
16. $chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
    
17. 
    
18. for($b = 1 ; $b -le 2 ; $b++)
    
19. {
    
20. $sheet.cells.item(1,$b).font.bold = $true
    
21. $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
    
22. $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
    
23. # $sheet.cells.item(1,$b).borders.Weight = $borderWeight::xlMedium
    
24. }
    
25. 
    
26. $sheet.cells.item(1,1) = "Name of Process"
    
27. $sheet.cells.item(1,2) = "Working Set Size"
    
28. 
    
29. foreach($process in $processes)
    
30. {
    
31. $sheet.cells.item($x, 1) = $process.name
    
32. $sheet.cells.item($x, 1).font.ColorIndex = 52 #只有这个起作用，
    
33. # $sheet.cells.item($x, 1).font.Color = 40
    
34.     #$sheet.cells.item($x, 1).interior.color = 22
    
35.   $sheet.cells.item($x, 1).interior.colorindex = 20#只有这个起作用，
    
36. $sheet.cells.item($x,2) = $process.workingSet
    
37. $x++
    
38. if ($x -gt 10) {
    
39. break
    
40. }
    
41. } #end foreach
    
42. 
    
43. $range = $sheet.usedRange
    
44. 
    
45. $MergeCells = $sheet.Range("A2:A5")
    
46. $MergeCells.Select()
    
47. $MergeCells.MergeCells = $true
    
48. 
    
49. 
    
50. $range.EntireColumn.AutoFit() | out-null
    
51. 
    
52. $ma = $sheet.range("A2").mergeArea()
    
53. write-host $ma.count()
    
54. write-host "merge aaa= " $sheet.cells.item(2,2).text -ForegroundColor red
    
55. 
    
56. for ($i=0;$i -lt $ma.count();$i++) {
    
57.    write-host "merge aera for A2= " $sheet.cells.item(2+$i,2).text -ForegroundColor yellow
    
58. }

复制代码