[新手上路]批处理新手入门导读[视频教程]批处理基础视频教程[视频教程]VBS基础视频教程[批处理精品]批处理版照片整理器
[批处理精品]纯批处理备份&还原驱动[批处理精品]CMD命令50条不能说的秘密[在线下载]第三方命令行工具[在线帮助]VBScript / JScript 在线参考
返回列表 发帖

[问题求助] 使用powershell汇总excel表中的单元格数据

本帖最后由 5i365 于 2022-9-17 19:59 编辑

当前文件夹下有N多个xlsx文件, 只处理每个文件中第1个工作薄中的第1个工作表, 表的内容很简单,示例如下:
_____________________________________________________A.xlsx
姓名, 语文, 数学, 社会, 历史
张三, 35, 39, 88, 76

_____________________________________________________B.xlsx
姓名, 语文, 数学, 社会, 历史
李四, 36, 39, 89, 78

_____________________________________________________C.xlsx
姓名, 语文, 数学, 社会, 历史
张三, 45, 31, 72, 84





相同文件夹下,还有很多相同格式的文件...
__________________________________________________________________________


我想查询张三这个人的: 语文, 数学, 社会, 历史 成绩的总和, 实际上就是计算所有 姓名为 张三的 工作表的 B2  C2  D2  E2的和


但我不知道张三在哪几个xlsx文件的表中


上面的示例中, A.xlsx 和 C.xlsx 文件中有张三, 则最后的处理结果: 输出对应的两个表中 B2  C2  D2  E2的和, 即:


姓名, 语文, 数学, 社会, 历史
张三, 80, 70, 160, 160

求大佬帮忙, 多谢
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

  1. #@&cls&powershell "type '%~0'|out-string|iex"&pause&exit
  2. $a=new-object -com excel.application
  3. $f=$env:tmp+'\tmp.csv'
  4. $n='张三'
  5. dir *.xlsx|%{
  6. $b=$a.workbooks.open($_.fullname).sheets.item(1)
  7. $b.saveas($f,6)
  8. $b.close()
  9. ipcsv $f|?{$_.'姓名' -eq $n}|%{$c+=$_.'语文'+$_.'数学'+$_.'社会'+$_.'历史'}
  10. del $f
  11. }
  12. $a.quit()
  13. $c
复制代码

TOP

回复 2# idwma


多谢大侠帮忙,
想问一下, 怎样用B2 C2 D2 E2的坐标来取值, 因为有时表里面是不规则的, 用标头定位不准, 用字母+数字的坐标才准
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

回复 3# 5i365
  1. #@&cls&powershell "type '%~0'|out-string|iex"&pause&exit
  2. $a=new-object -com excel.application
  3. $f=$env:tmp+'\tmp.csv'
  4. $n='张三'
  5. dir *.xlsx|%{
  6. $b=$a.workbooks.open($_.fullname).sheets.item(1)
  7. $b.saveas($f,6)
  8. $b.close()
  9. ipcsv $f -header 'a2','b2','c2','d2','e2'|?{$_.'a2' -eq $n}|%{
  10. $b2+=$_.'b2'
  11. $c2+=$_.'c2'
  12. $d2+=$_.'d2'
  13. $e2+=$_.'e2'
  14. }
  15. del $f
  16. }
  17. $a.quit()
  18. '{0},{1},{2},{3}' -f $b2,$c2,$d2,$e2
复制代码

TOP

本帖最后由 5i365 于 2022-9-18 05:43 编辑

回复 4# idwma

多谢!
还是我没有描述清楚! 我的意思更简单的说: 每个表中

姓名的值 如果在 D5
语文的值 如果在 G19
数学的值 如果在 W20
社会的值 如果在 P21
历史的值 如果在 AG21

这种情况下,如何先取到值?
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

本帖最后由 5i365 于 2022-9-18 10:05 编辑

回复 4# idwma


   另外, 上面的代码执行时会报如下错误:
ERROR: Method invocation failed because [System.__ComObject] does not contain a method named 'close'.
A.ps1 (7, 2): ERROR: At Line: 7 char: 2
ERROR: +     $b.close()
ERROR: +     ~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (close:String) [], RuntimeException
ERROR:     + FullyQualifiedErrorId : MethodNotFound
ERROR:
ERROR: ipcsv : The member "����" is already present.


__________________________________________________________________________
还会弹一个对话框提示: 是否保存对 tmp.csv 的更改
__________________________________________________________________________
任务管理器里的 excel.exe  需要手动退出
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

本帖最后由 5i365 于 2022-9-18 07:26 编辑

回复 4# idwma


我搜索到了下面的贴子, 感觉应该能用上
http://www.bathome.net/viewthread.php?tid=63417&highlight=epplus
贴子中的dll过期了, 我搜索了一下, 下载下面的模块文件, 不用安装, 里面有就那个dll直接拖出来就能用

https://www.powershellgallery.com/packages/ImportExcel/7.8.1#manual-download

试了一下, 真不错, 使用下面的代码能取到姓名的值
Add-Type -Path ".\EPPlus.dll"
$ex = New-Object OfficeOpenXml.ExcelPackage(".\a.xlsx")
$ex.Workbook.Worksheets[1].Cells["A1"].Value
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

姓名和成绩在表格里斜着排是吗
还是说没有规律的就是要按单元格坐标来取值

TOP

本帖最后由 5i365 于 2022-9-18 12:34 编辑

回复 8# idwma


   没有规律的就是要按单元格坐标来取值+
把下面的值先整理规律后再处理也行
姓名的值 如果在 D5
语文的值 如果在 G19
数学的值 如果在 W20
社会的值 如果在 P21
历史的值 如果在 AG21
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

本帖最后由 idwma 于 2022-9-18 14:30 编辑

那要的结果是取值还是整理还是全都要
  1. #@&cls&powershell "type '%~0'|out-string|iex"&pause&exit
  2. $a=new-object -com excel.application
  3. $n='张三'
  4. $arr=('姓名','d5',''),('语文','g19',''),('数学','w20',''),('社会','p21',''),('历史','ag21','')
  5. dir *.xlsx|%{
  6.     $b=$a.workbooks.open($_.fullname)
  7.     $c=$b.sheets.item(1)
  8.     if($c.range($arr[0][1]).value() -eq $n){
  9.         $arr[1..$arr.count]|%{
  10.             [int]$_[2]+=$c.range($_[1]).value()
  11.         }
  12.     }
  13.     $b.close()
  14. }
  15. $a.quit()
  16. $arr|%{$_ -join ':'}
复制代码

TOP

本帖最后由 5i365 于 2022-9-18 15:38 编辑

回复 10# idwma

真遇到难题了,
__________________________________________________________________________________________________
难点1:

分数的所在的单元格不是固定的, 但是有个规律:
名字和分数都在 姓名和科目名的右边, 例如下面这样: 看来要先找到姓名和科目的文本, 取到它所在的单元格坐标, 然后再取到其右边一个单元格的值

姓名 | 张三

语文 | 86

数学 | 96

__________________________________________________________________________________________________

难点2:

学校的电脑里装的是WPS, 没有装excel , 不过, 我试了一下, 给wps装上VBA组件,  也可以用 com对象, 但是要给别的电脑上全装东西, 有点麻烦
所以目前看来用那个 epplus.dll 是最好了, 我试了一下, 取单元格的值, 就是用我上面的代码,
但是要查找文本, 不知道这个模块有没有查找文本函数, 这有点难度了, 不过那个模块里有很多示例文件
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

本帖最后由 5i365 于 2022-9-18 15:50 编辑

回复 10# idwma
输入:
$in = "张三"
___________________________________________________

输出 上面指定的姓名的, 各科目的和, 就行了, 例如下面这样:
张三是在两个xlsx文件中, 其语文,在A文件中得分35 , 在B文件中得分45, 那就直接显示其和 80 就行了, 其它科目也一样


姓名, 语文, 数学, 社会, 历史
张三, 80, 70, 160, 160
___________________________________________________

此题最大的难点: 查找姓名和科目文本
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

本帖最后由 5i365 于 2022-9-18 16:08 编辑

回复 10# idwma

在模块论坛找到了点查找替换的代码
大图链接:  i.ibb.co/MpSdHtC/err.png
网址: https://github.com/dfinke/ImportExcel/issues/565

   
  1. $xl = Open-ExcelPackage <<path>>
  2. $xl.<<sheet>>.Cells.where({$_.value -like "*find*"}).foreach({$_.value = $value -replace "find","Replace"})
  3. close-excelPackage $xl
复制代码


  1. #我必须使用以下格式来更改匹配单元格中的值:
  2. $xl.<<sheet>>.Cells.where({$_.value -like "*find*"}).foreach({$_.Value = "Replace"})
复制代码
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

那找到张三的位置他的成绩相对位置是横坚斜还是随机的?

TOP

回复 14# idwma

随机! 但都是名在左, 值在右
我用下面的代码, 找到了姓名的坐标, 看来有戏了! 其它逻辑的代码, 我还写不出来
________________________________________________________

Add-Type -Path ".\EPPlus.dll"

$ex = New-Object OfficeOpenXml.ExcelPackage(".\a.xlsx")

$ex.Workbook.Worksheets[1].Cells.where({ $_.value -like "姓名" }).Address
本人所发所有贴子或代码, 诸大侠若认为有改进之处,请不吝赐教,感激不尽!

TOP

返回列表