操作excel自动化的方式很多今天我们看看如何用电脑自带的PowerShell来干这件事储备知识：工作簿工作表，要知道这俩是啥玩意注：#开头的是注释信息操作的时候可以不写一创建一个带有学生成绩的excel文件准备工作：打开电脑上的PowerShell软件所有指令都需要在此软件中运行1既然是操作excel首先需要一个excel的变量$excel=New-Object-ComObjectExcel.A
操作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()
