PowerShell的excelAPI

在powershell中可以通过调用com来对excel等office产品进行操控，这里介绍一些excel的一些API

打开、保存、关闭

$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type] #设置excel文件类型
$objExcel = New-Object -ComObject excel.application #创建excel对象
$objExcel.visible = $false    #是否可见
$excel.displayAlerts = $false #是否显示对话框
$path = "C:\Users\Administrator\Desktop\test.xls" #待打开文件
$objExcel.workbooks.Add()#创建新的工作簿
$workbook = $objExcel.workbooks.open($path) #获取工作簿对象
$ws=$workbook.WorkSheets.item(1)#获取工作表对象，也可以通过item("Sheet1")
$S2 = $workbook.sheets | where {$_.name -eq "Sheet2"} #获取名为Sheet2的表
$workbook.activate()#激活工作簿
$ws.activate()#激活工作表
$workbook.save()#保存工作簿
$workbook.saveas("C:\Users\Administrator\desktop\save.xlsx")#另存工作簿
$workbook.close()#关闭工作簿
$objExcel.Quit()#退出Excel程序
$workbook.workSheets.item(3).delete() #删除工作表
# 释放进程
$excel = $null
[GC]::Collect()

表格属性

$workbook.author = "Thomas Lee - tfl@psp.co.uk"             #工作簿作者
$workbook.title = "Excel and PowerShell rock!"
$workbook.subject = "Demonstrating the Power of PowerShell"

定位区域、单元格

$ws.cells.item(1,2)#行列索引只能为整数
$ws.range("b2")#选择单个单元格
$ws.range("b2","d5")#选择从B2到D5的一块区域
$ws.range("1:1").select()#选择第一行
$ws.range("b:d").select()#选择第二列到第四列
$ws.rows.item(3).select()#选择第三行
$ws.columns.item(3).select()#选择第三列
$ws.Columns(1).EntireColumn.Delete() #删除第一列

单元格操作

$ws.cells.item(2,1) = "hello"#设置值
$ws.cells.item(2,1).value2 = "hello"#设置值
$ws.cells.item(2,1).select()#选中单元格
$ws.cells.item(2,1).copy()#复制单元格
$ws.cells.item(2,1).pastespecial(-4163)#粘贴单元格
$r1=$ws.range("b2","d5").find('hello')#区域搜索包含hello的单元格
$row=$r1.row                     #搜索到的单元格的行
$column=$r1.column               #搜索到的单元格的列
$max_line_num = $worksheet_data.Range("A1").CurrentRegion.Rows.Count   #返回表格中最后一行的行号

格式

$ws.cells.item(2,1).font.Size = 9#设置字体大小
$ws.cells.item(2,1).Name = "Times New Roman"#设置字体名称
$ws.cells.item(2,1).borders.LineStyle = $LineStyle::xlContinuous#设置单元格边框
$ws.cells.item(2,1).font.bold = "true"#设置加粗
$ws.cells.item(2,1).font.italic = $true#设置倾斜
$ws.cells.item(2,1).NumberFormat = "#,##0.00"#设置数字格式

创建四个枚举类型。枚举类型用于告诉 Excel 允许将哪些值填入特定的选项类型。例如，xlLineStyle 枚举用于确定所绘制线条的类型：双线条、虚线等，针对将使用的每个枚举类型创建一个快捷别名。实际上，我们将把代表枚举名称的字符串转换成 [type]

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
for($b = 1 ; $b -le 2 ; $b++)
{
 $sheet.cells.item(1,$b).font.bold = $true
 $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
 $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
 $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}

调整行高

$worksheet.Rows(4).RowHeight = 20
$worksheet.Rows(5).RowHeight = 20

自动调整行高和列宽

$range = $sheet.usedRange  #已经有内容的区域，等价于ctrl+a
$range.EntireColumn.AutoFit() | out-null

定义网格线种类

$LineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]

指定网格线种类

$sheet.cells.item(2,3).borders.LineStyle = $LineStyle::xlDashDotDot
网线种类：

• xlContinuous 实线
• xlDashDot 一点划线
• xlDashDotDot 二点划线
• xlDouble 双线
• xlSlantDashDot 斜线
• xlDash 虚线

• xlLineStyleNone 没有线

  定义网线的宽度

  $Weight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]

  指定网格线的宽度

  $sheet.cells.item(2,3).borders.Weight = $Weight::xlMedium
  网线宽度种类：

• xlHairlin极细

• xlThin 细
• xlMedium 中
• xlThick 粗

$worksheet.Range("A1","I1").Merge()                                   #合并单元格
$worksheet.Range("A1","I1").font.bold = $true                         #字体加粗
$selection1 = $worksheet.Range("A1","I1")
$selection1.HorizontalAlignment = -4108                               #居中
$worksheet.Rows(1).RowHeight = 50                                     #设置行高
$selection1.Font.Size = 14                                            #字体大小
$selection1.Interior.ColorIndex = 2                                   #单元格颜色为白色

复制粘贴

$worksheet_data.Range("A1","AD"+$max_line_num).copy()               #复制区域
$work_raw_data.Range("A1","AD"+$max_line_num).PasteSpecial(-4163)   #粘贴为数值

XlPasteType常量（粘贴的格式）

xlPasteAll -4104 全部（默认值）
xlPasteAllExceptBorders 7 边框除外
xlPasteColumnWidths 8 列宽
xlPasteComments -4144 批注
xlPasteFormats -4122 格式
xlPasteFormulas -4123 公式
xlPasteFormulasAndNumberFormats 11 公式和数字格式
xlPasteValidation 6 有效性验证
xlPasteValues -4163 数值
xlPasteValuesAndNumberFormats 12 值和数字格式

XlPasteSpecialOperation常量（粘贴的操作）

xlPasteSpecialOperationNone -4142 无（默认值）
xlPasteSpecialOperationAdd 2 加
xlPasteSpecialOperationSubtract 3 减
xlPasteSpecialOperationMultiply 4 乘
xlPasteSpecialOperationDivide 5 除

查找替换

$sh_data.Range("G:H").replace("-", "/")  #替换

筛选

$range = $ws.UsedRange           # 选中所有内容
$range.EntireColumn.AutoFilter() # 开启筛选模式
$range.AutoFilter(1, "A")        # 决定要筛选哪列的哪些内容

公式

单元格输入公式

$worksheet.Range("H4").formula = '=SUMIFS(数据源!E:E,数据源!G:G,">=2021/9/1",数据源!G:G,"<=2021/9/30",数据源!U:U,"=")/10000'

图表

$ch = $excel.Charts.Add()                   # 初始化画布
$ch.ChartTitle.Text = 'memory ocuppation'   # 图表标题
$ch.chartType = 70                          # 图表类型
$ch.setSourceData($sht.Range("A1:B11"), 2)  # 数据源
$ch.ApplyDataLabels(3)                      # 数据标签

chartType常量