Board logo

标题: [原创代码] powershell生成Excel表格,自动填充,可以设置颜色 [打印本页]

作者: gflrlm    时间: 2020-1-4 23:03     标题: powershell生成Excel表格,自动填充,可以设置颜色

如题, powershell修改Excel,设置颜色,内容, 等。不知道跟vba相比,优缺点如何,有待高人解答。
  1. $processes=Get-Process
  2. $excel = New-Object -ComObject Excel.Application
  3. $excel.Visible = $true
  4. $workbook = $excel.Workbooks.add()
  5. $sheet = $workbook.worksheets.Item(1)
  6. #$workbook.Worksheets.item(3).delete()
  7. $workbook.Worksheets.item(2).delete()
  8. $workbook.Worksheets.item(1).name="Processes"
  9. $sheet = $workbook.WorkSheets.Item("Processes")
  10. $x = 2
  11. $lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
  12. $colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
  13. $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
  14. $chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
  15. for($b = 1 ; $b -le 2 ; $b++)
  16. {
  17. $sheet.cells.item(1,$b).font.bold = $true
  18. $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
  19. $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
  20. # $sheet.cells.item(1,$b).borders.Weight = $borderWeight::xlMedium
  21. }
  22. $sheet.cells.item(1,1) = "Name of Process"
  23. $sheet.cells.item(1,2) = "Working Set Size"
  24. foreach($process in $processes)
  25. {
  26. $sheet.cells.item($x, 1) = $process.name
  27. $sheet.cells.item($x, 1).font.ColorIndex = 52 #只有这个起作用,
  28. # $sheet.cells.item($x, 1).font.Color = 40
  29.     #$sheet.cells.item($x, 1).interior.color = 22
  30.   $sheet.cells.item($x, 1).interior.colorindex = 20#只有这个起作用,
  31. $sheet.cells.item($x,2) = $process.workingSet
  32. $x++
  33. if ($x -gt 10) {
  34. break
  35. }
  36. } #end foreach
  37. $range = $sheet.usedRange
  38. $MergeCells = $sheet.Range("A2:A5")
  39. $MergeCells.Select()
  40. $MergeCells.MergeCells = $true
  41. $range.EntireColumn.AutoFit() | out-null
  42. $ma = $sheet.range("A2").mergeArea()
  43. write-host $ma.count()
  44. write-host "merge aaa= " $sheet.cells.item(2,2).text -ForegroundColor red
  45. for ($i=0;$i -lt $ma.count();$i++) {
  46.    write-host "merge aera for A2= " $sheet.cells.item(2+$i,2).text -ForegroundColor yellow
  47. }
复制代码





欢迎光临 批处理之家 (http://bbs.bathome.net/) Powered by Discuz! 7.2