excel的powerquery使用（用电脑自带的PowerShell操作excel）

操作excel自动化的方式很多 今天我们看看如何用电脑自带的PowerShell来干这件事

储备知识：工作簿 工作表 ，要知道这俩是啥玩意

注：#开头的是注释信息 操作的时候可以不写

一 创建一个带有学生成绩的excel文件

准备工作：打开电脑上的PowerShell软件 所有指令都需要在此软件中运行

1 既然是操作excel 首先需要一个excel的变量

$excel = New-Object -ComObject Excel.Application #不显示excel对话框 没有这行的话 在保存的时候可能会弹出对话框 $excel.DisplayAlerts =$false

2 此时的excel只是个空壳子 接下来我们需要创建一个工作簿

$workbook = $excel.Workbooks.Add()

3 此时已经有一个工作簿 接下来我们需要获取工作簿中的第一个工作表

$sheet1 = $workbook.Sheets.Item(1)

4 此时工作表只是空表格 我们写入在第一行写入表头(姓名 数学 语文 外语 总分)

Cells[1,1]：第一行第一列

$sheet1.Cells[1,1].Value2 = "姓名" $sheet1.Cells[1,2].Value2 = "数学" $sheet1.Cells[1,3].Value2 = "语文" $sheet1.Cells[1,4].Value2 = "外语" $sheet1.Cells[1,5].Value2 = "总分"

5 有了表头 接下来需要填充一些数据做演示

从第二行开始 一直到第十行

for($i = 2;$i -le 10;$i ) { #写入姓名列 $sheet1.Cells[$i,1].Value2 = ("张三" $i).ToString() #写入数学列 $sheet1.Cells[$i,2].Value2 = (80 $i).ToString() #写入语文列 $sheet1.Cells[$i,3].Value2 = (81 $i).ToString() #写入外语列 $sheet1.Cells[$i,4].Value2 = (82 $i).ToString() }

6 现在我们已经有了 2-10行 九条成绩数据 保存工作簿并退出

#保存工作簿到D盘下 $workbook.SaveAs("D:\成绩.xlsx") #退出excel $excel.Quit() #清空excel变量 $excel = $null #清理excel 没有这一步任务管理器可能还会有excel进程残留 [GC]::Collect()

7 现在我们已经有了一个 成绩.xlsx 的文件 内容如下

二 接下来汇总总分

1 跟上面第一步一样 我们需要一个操作excel的变量

$excel = New-Object -ComObject Excel.Application #不显示excel对话框 $excel.DisplayAlerts =$false

2 读取上面创建的excel文件

$workbook = $excel.Workbooks.Open("D:\成绩.xlsx")

3 读取文件后 我们需要拿到刚才的工作表也就是第一个工作表

$sheet1 = $workbook.Sheets.Item(1)

4 拿到了工作表 接下里就是读取2-10行的各科成绩汇总总分

for($i = 2;$i -le 10;$i ) { #数学分数 $cell2 = $sheet1.Cells[$i,2].Value2 #语文分数 $cell3 = $sheet1.Cells[$i,3].Value2 #外语分数 $cell4 = $sheet1.Cells[$i,4].Value2 #总分在第五列 等于语数外三列的和 $sheet1.Cells[$i,5].Value2 =($cell2 $cell3 $cell4).ToString() }

5 分数汇总后保存并退出

#因为是打开的现有工作簿 只需保存就行 不用再指定名字 $workbook.Save() $excel.Quit() $excel = $null [GC]::Collect()

6 现在总分已经汇总完成 如下

三 上述PowerShell指令汇总

# 一 创建excel #创建$excel变量 $excel = New-Object -ComObject Excel.Application #不弹出对话框 $excel.DisplayAlerts =$false #新建工作簿 $workbook = $excel.Workbooks.Add() #获取第一个表 $sheet1 = $workbook.Sheets.Item(1) #表头：姓名 第一行第一列 $sheet1.Cells[1,1].Value2 = "姓名" #表头：数学 第一行第二列 $sheet1.Cells[1,2].Value2 = "数学" #表头：语文 第一行第三列 $sheet1.Cells[1,3].Value2 = "语文" #表头：外语 第一行第四列 $sheet1.Cells[1,4].Value2 = "外语" #表头：总分 第一行第五列 $sheet1.Cells[1,5].Value2 = "总分" #第二行到第十行 需要填充一些数据做演示 for($i = 2;$i -le 10;$i ) { #写入姓名列 $sheet1.Cells[$i,1].Value2 = ("张三" $i).ToString() #写入数学列 $sheet1.Cells[$i,2].Value2 = (80 $i).ToString() #写入语文列 $sheet1.Cells[$i,3].Value2 = (81 $i).ToString() #写入外语列 $sheet1.Cells[$i,4].Value2 = (82 $i).ToString() } #保存到D:\成绩.xlsx $workbook.SaveAs("D:\成绩.xlsx") #退出 $excel.Quit() $excel = $null [GC]::Collect() # 二 读取上面创建的excel #创建$excel变量 $excel = New-Object -ComObject Excel.Application #不弹出对话框 $excel.DisplayAlerts =$false #打开工作簿 $workbook = $excel.Workbooks.Open("D:\成绩.xlsx") #获取第一个表 $sheet1 = $workbook.Sheets.Item(1) #汇总2-10行的总分 for($i = 2;$i -le 10;$i ) { #数学分数 $cell2 = $sheet1.Cells[$i,2].Value2 #语文分数 $cell3 = $sheet1.Cells[$i,3].Value2 #外语分数 $cell4 = $sheet1.Cells[$i,4].Value2 #总分在第五列 等于语数外三列的和 $sheet1.Cells[$i,5].Value2 =($cell2 $cell3 $cell4).ToString() } #保存 $workbook.Save() #退出 $excel.Quit() $excel = $null [GC]::Collect()

,