批处理之家 - Powered by Discuz! Board

标题: [问题求助] 使用powershell汇总excel表中的单元格数据 [打印本页]

作者: 5i365 时间: 2022-9-17 19:23 标题: 使用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

求大佬帮忙, 多谢

作者: idwma 时间: 2022-9-17 21:04

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

作者: 5i365 时间: 2022-9-17 21:09

回复 2# idwma

多谢大侠帮忙,
想问一下, 怎样用B2 C2 D2 E2的坐标来取值, 因为有时表里面是不规则的, 用标头定位不准, 用字母+数字的坐标才准

作者: idwma 时间: 2022-9-17 21:19

回复 3# 5i365

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

作者: 5i365 时间: 2022-9-18 05:41

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

回复 4# idwma

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

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

这种情况下,如何先取到值?

作者: 5i365 时间: 2022-9-18 06:09

本帖最后由 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  需要手动退出

作者: 5i365 时间: 2022-9-18 06:12

本帖最后由 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

作者: idwma 时间: 2022-9-18 12:10

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

作者: 5i365 时间: 2022-9-18 12:33

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

回复 8# idwma

没有规律的就是要按单元格坐标来取值+
把下面的值先整理规律后再处理也行
姓名的值如果在 D5
语文的值如果在 G19
数学的值如果在 W20
社会的值如果在 P21
历史的值如果在 AG21

作者: idwma 时间: 2022-9-18 14:10

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

那要的结果是取值还是整理还是全都要

#@&cls&powershell "type '%~0'|out-string|iex"&pause&exit
$a=new-object -com excel.application
$n='张三'
$arr=('姓名','d5',''),('语文','g19',''),('数学','w20',''),('社会','p21',''),('历史','ag21','')
dir *.xlsx|%{
    $b=$a.workbooks.open($_.fullname)
    $c=$b.sheets.item(1)
    if($c.range($arr[0][1]).value() -eq $n){
        $arr[1..$arr.count]|%{
            [int]$_[2]+=$c.range($_[1]).value()
        }
    }
    $b.close()
}
$a.quit()
$arr|%{$_ -join ':'}
复制代码

作者: 5i365 时间: 2022-9-18 15:36

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

回复 10# idwma

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

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

姓名 | 张三

语文 | 86

数学 | 96

__________________________________________________________________________________________________

难点2:

学校的电脑里装的是WPS, 没有装excel , 不过, 我试了一下, 给wps装上VBA组件, 也可以用 com对象, 但是要给别的电脑上全装东西, 有点麻烦
所以目前看来用那个 epplus.dll 是最好了, 我试了一下, 取单元格的值, 就是用我上面的代码,
但是要查找文本, 不知道这个模块有没有查找文本函数, 这有点难度了, 不过那个模块里有很多示例文件

作者: 5i365 时间: 2022-9-18 15:43

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

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

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

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

此题最大的难点: 查找姓名和科目文本

作者: 5i365 时间: 2022-9-18 16:06

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

回复 10# idwma

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

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

#我必须使用以下格式来更改匹配单元格中的值：
$xl.<<sheet>>.Cells.where({$_.value -like "*find*"}).foreach({$_.Value = "Replace"})
复制代码

作者: idwma 时间: 2022-9-18 16:50

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

作者: 5i365 时间: 2022-9-18 16:57

回复 14# idwma

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

________________________________________________________

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

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

$ex.Workbook.Worksheets[1].Cells.where({ $_.value -like "姓名" }).Address

作者: flashercs 时间: 2022-9-18 17:00

本帖最后由 flashercs 于 2022-9-19 19:54 编辑

Add-Type -LiteralPath .\EPPlus.dll -ErrorAction Stop
$fields = @('姓名:', '语文:', '数学:', '社会:', '历史:')
$keyField = '姓名:'
$groupPso = Get-ChildItem -Path .\*.xlsx -Filter *.xlsx | Where-Object { -not $_.PSIsContainer } | ForEach-Object {
  try {
    $pack = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $_.FullName
    $pso = New-Object psobject
    $a2d = $pack.Workbook.Worksheets[1].Cells.Value
    for ($x = $a2d.GetLowerBound(0); $x -le $a2d.GetUpperBound(0); $x++) {
      $propName = $null
      for ($y = $a2d.GetLowerBound(1); $y -le $a2d.GetUpperBound(1); $y++) {
        if ($null -eq $propName) {
          if ($fields -contains $a2d[$x, $y]) {
            $propName = $a2d[$x, $y]
          }
        } else {
          if ($null -ne $a2d[$x, $y]) {
            $pso | Add-Member -MemberType NoteProperty -Name $propName -Value $a2d[$x, $y]
            $propName = $null
          }
        }
      }
    }
    $pso
  } finally {
    if ($pack) {
      $pack.Dispose()
      $pack = $null
    }
  }
  trap {}
} | Group-Object -Property $keyField
function Get-StudentScore {
  param (
    [string[]]$UserName
  )
  $groupPso | Where-Object { $null -eq $UserName -or $UserName -contains '*' -or $UserName -contains $_.Name } | ForEach-Object {
    $pso = New-Object psobject -Property @{$keyField = $_.Name }
    $_.Group | Measure-Object -Property ($fields -ne $keyField) -Sum | ForEach-Object {
      $pso | Add-Member -MemberType NoteProperty -Name $_.Property -Value ([int]$_.Sum)
    }
    $pso
  }
}
# main
# search 佟玉
# Get-StudentScore -UserName 佟玉 | Format-Table -AutoSize
# search 张三,李四
# Get-StudentScore -UserName 张三, 李四 | Format-Table -AutoSize
# search 所有学生
# Get-StudentScore -UserName * | Format-Table -AutoSize
# search 所有学生
# Get-StudentScore | Format-Table -AutoSize
# 左对齐
Get-StudentScore | Out-GridView -Title 学生成绩汇总 -Wait
# 筛选姓名
Get-StudentScore | Out-GridView -Title 学生成绩汇总_多选 -OutputMode Multiple | Out-GridView -Title 学生成绩汇总_已选择 -Wait
复制代码

作者: 5i365 时间: 2022-9-18 17:26

本帖最后由 5i365 于 2022-9-18 17:28 编辑

回复 16# flashercs

感谢大侠支招, 输出为空,
姓名和科目字段的文本右边有个: 把这个:删了也没有输出
但有一点可以肯定, 这几个文本都是唯一的,

$fields = @('姓名:', '语文:', '数学:', '社会:', '历史:')
$keyField = '姓名:'

作者: flashercs 时间: 2022-9-18 21:26

回复 17# 5i365

有没有个样本.xlsx ?网盘?

作者: 5i365 时间: 2022-9-19 07:13

回复 18# flashercs

不好意思, 刚看到, 是我又大意了, 虽然眼看着是成绩在科目的右边, 实际中间却还有单元格, 感觉我的算法不灵了, 确实有难度了

我把要取的科目颜色设为了红色, 原本是黑色的,

现在唯一的规律就是: 成绩左边或右边的文本是固定的, 例如, 语文成绩的右边的文本肯定是物理: 但中间有没有合并单元格不好判断

https://t.wss.ink/f/9borrlf6ksr 复制链接到浏览器打开

作者: 5i365 时间: 2022-9-19 07:19

回复 14# idwma

不好意思, 我最前面总结的规律是错的, 实际情况是上面19楼的描述

作者: flashercs 时间: 2022-9-19 09:41

回复 19# 5i365

16楼已修改.

作者: 5i365 时间: 2022-9-19 13:35

回复 21# flashercs

多谢大侠, 稍后我多测试一下,

作者: 5i365 时间: 2022-9-19 13:43

本帖最后由 5i365 于 2022-9-19 13:44 编辑

回复 21# flashercs

试了几个, 感觉应该可以了,
请问, 怎样在上面的代码中把所有学生姓名的存到数组里? 然后填充到下面的列表框中? 这样操作方便一些
即: 执行上面代码时, 先显示这个列表框, 我从里面选择某个人的姓名, 然后确定, 就出现了该人的汇总成绩

下面的代码有点臃肿, 我也以前看到过类似列表框的代码, 没有使用System.Drawing , 才只有几行代码, 刚搜索了一下, 没找到

另外还有一点, 如果列表中的项比较多, 窗口要相应的添加高度

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

$form = New-Object System.Windows.Forms.Form
$form.Text = '选择列表'
$form.Size = New-Object System.Drawing.Size(300, 200)
$form.StartPosition = 'CenterScreen'

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Point(75, 120)
$OKButton.Size = New-Object System.Drawing.Size(75, 23)
$OKButton.Text = 'OK'
$OKButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $OKButton
$form.Controls.Add($OKButton)

$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Point(150, 120)
$CancelButton.Size = New-Object System.Drawing.Size(75, 23)
$CancelButton.Text = 'Cancel'
$CancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $CancelButton
$form.Controls.Add($CancelButton)

$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(10, 20)
$label.Size = New-Object System.Drawing.Size(280, 20)
$label.Text = '选择你要查询的学生的姓名:'
$form.Controls.Add($label)

$listBox = New-Object System.Windows.Forms.Listbox
$listBox.Location = New-Object System.Drawing.Point(10, 40)
$listBox.Size = New-Object System.Drawing.Size(260, 20)

#$listBox.SelectionMode = 'MultiExtended'

[void]$listBox.Items.Add('张三')
[void]$listBox.Items.Add('李四')
[void]$listBox.Items.Add('王五')
[void]$listBox.Items.Add('赵六')

$listBox.Height = 70
$form.Controls.Add($listBox)
$form.Topmost = $true

$result = $form.ShowDialog()

if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
        $x = $listBox.SelectedItems
        $x
}
复制代码

作者: 5i365 时间: 2022-9-19 13:48

本帖最后由 5i365 于 2022-9-19 14:09 编辑

回复 16# flashercs

怎样让输出的结果, 上下的内容左对齐? 现在输出后, 属性名和属性值上下没有对齐, 看分数不太明确
在消息框中显示时对齐就好多了
有什么办法用带表格线的形式显示在消息框中?

[System.Windows.Forms.MessageBox]::Show("输出的多行对齐的结果", "标题")

作者: flashercs 时间: 2022-9-19 19:13

本帖最后由 flashercs 于 2022-9-19 19:31 编辑

回复 24# 5i365

# 左对齐
Get-StudentScore | Out-GridView -Title 学生成绩汇总 -Wait
复制代码

可以在GridView中进行筛选姓名,也可以筛选成绩.

作者: 5i365 时间: 2022-9-19 19:54

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

回复 25# flashercs

我使用的那个软件能把ps生成exe, 所以我想
用 列表框 显示这一堆xlsx文件中,所包含的所有姓名,

用 消息框 显示对齐的结果

执行ps1文件操作就相对麻烦了

生成exe 我已经试了, 可以成功, 就是不太完美, 存在上面所提到的问题

作者: flashercs 时间: 2022-9-19 19:56

回复 23# 5i365

利用powershell_ise的 WPF GUI组件 GridView来选择对象,比winform选择要方便得多.可以多选,按Ctrl或Shift进行多选.
16楼又修改了一下.

作者: flashercs 时间: 2022-9-19 20:00

本帖最后由 flashercs 于 2022-9-19 21:08 编辑

回复 26# 5i365

winform选择

Add-Type -LiteralPath .\EPPlus.dll -ErrorAction Stop
$fields = @('姓名:', '语文:', '数学:', '社会:', '历史:')
$keyField = '姓名:'
$groupPso = Get-ChildItem -Path .\*.xlsx -Filter *.xlsx | Where-Object { -not $_.PSIsContainer } | ForEach-Object {
  try {
    $pack = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $_.FullName
    $pso = New-Object psobject
    $a2d = $pack.Workbook.Worksheets[1].Cells.Value
    for ($x = $a2d.GetLowerBound(0); $x -le $a2d.GetUpperBound(0); $x++) {
      $propName = $null
      for ($y = $a2d.GetLowerBound(1); $y -le $a2d.GetUpperBound(1); $y++) {
        if ($null -eq $propName) {
          if ($fields -contains $a2d[$x, $y]) {
            $propName = $a2d[$x, $y]
          }
        } else {
          if ($null -ne $a2d[$x, $y]) {
            $pso | Add-Member -MemberType NoteProperty -Name $propName -Value $a2d[$x, $y]
            $propName = $null
          }
        }
      }
    }
    $pso
  } finally {
    if ($pack) {
      $pack.Dispose()
      $pack = $null
    }
  }
  trap {}
} | Group-Object -Property $keyField
function Get-StudentScore {
  param (
    [string[]]$UserName
  )
  $groupPso | Where-Object { $null -eq $UserName -or $UserName -contains '*' -or $UserName -contains $_.Name } | ForEach-Object {
    $pso = New-Object psobject -Property @{$keyField = $_.Name }
    $_.Group | Measure-Object -Property ($fields -ne $keyField) -Sum | ForEach-Object {
      $pso | Add-Member -MemberType NoteProperty -Name $_.Property -Value ([int]$_.Sum)
    }
    $pso
  }
}
# main
# search 佟玉
# Get-StudentScore -UserName 佟玉 | Format-Table -AutoSize
# search 张三,李四
# Get-StudentScore -UserName 张三, 李四 | Format-Table -AutoSize
# search 所有学生
# Get-StudentScore -UserName * | Format-Table -AutoSize
# search 所有学生
# Get-StudentScore | Format-Table -AutoSize
# 左对齐
# Get-StudentScore | Out-GridView -Title 学生成绩汇总 -Wait
# 筛选姓名
# Get-StudentScore | Out-GridView -Title 学生成绩汇总_多选 -OutputMode Multiple | Out-GridView -Title 学生成绩汇总_已选择 -Wait

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

$form = New-Object System.Windows.Forms.Form
$form.Text = '选择列表'
$form.Size = New-Object System.Drawing.Size(300, 200)
$form.StartPosition = 'CenterScreen'

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Point(75, 120)
$OKButton.Size = New-Object System.Drawing.Size(75, 23)
$OKButton.Text = 'OK'
$OKButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $OKButton
$form.Controls.Add($OKButton)

$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Point(150, 120)
$CancelButton.Size = New-Object System.Drawing.Size(75, 23)
$CancelButton.Text = 'Cancel'
$CancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $CancelButton
$form.Controls.Add($CancelButton)

$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(10, 20)
$label.Size = New-Object System.Drawing.Size(280, 20)
$label.Text = '选择你要查询的学生的姓名:'
$form.Controls.Add($label)

$listBox = New-Object System.Windows.Forms.Listbox
$listBox.Location = New-Object System.Drawing.Point(10, 40)
$listBox.Size = New-Object System.Drawing.Size(260, 20)

$listBox.SelectionMode = 'MultiExtended'

$listBox.DataSource = [System.Collections.ArrayList]@($groupPso.Name)

$listBox.Height = 70
$form.Controls.Add($listBox)
$form.Topmost = $true

$result = $form.ShowDialog()

if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
  $x = $listBox.SelectedItems
  $x
}

Get-StudentScore -UserName $x | Out-GridView -Title 学生成绩汇总 -Wait
($form, $OKButton, $CancelButton, $label, $listBox).Dispose()
复制代码

作者: 5i365 时间: 2022-9-20 07:24

本帖最后由 5i365 于 2022-9-20 09:49 编辑

回复 28# flashercs

多谢大侠, 刚看到,

关于最开始的列表框, 我找到了我说的那种效果, 就是设置一下列表框的属性 $listBox.Dock = 'Fill'
但是我想把OK按钮放在最下面, 宽度和对话框等宽,

另外: Form高度能否设置最大高度? 即:默认是300, 当姓名多时,则自动变为最大高度800?, 以前看到过这种效果, 如下图所示
另外, 如何把姓名按姓的第一个拼音字母按a-z排序?

Add-Type -AssemblyName System.Windows.Forms

$form = New-Object System.Windows.Forms.Form
$form.Text = '双击要查询的姓名'
$Form.FormBorderStyle = "FixedToolWindow"
$form.StartPosition = 'CenterScreen'
$form.ClientSize = '160, 300'

$listBox = New-Object System.Windows.Forms.Listbox
$listBox.Dock = 'Fill'
$listBox.SelectionMode = 'MultiExtended'
$listBox.Font = New-Object drawing.Font("微软雅黑", 10, [Drawing.FontStyle]::Bold)

[void]$listBox.Items.Add('张三')
[void]$listBox.Items.Add('李四')
[void]$listBox.Items.Add('王五')
[void]$listBox.Items.Add('赵六')

$form.Controls.Add($listBox)
$form.Topmost = $true
$result = $form.ShowDialog()

if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
        $x = $listBox.SelectedItems
        $x
}
复制代码

作者: 5i365 时间: 2022-9-20 07:58

本帖最后由 5i365 于 2022-9-20 09:47 编辑

回复 28# flashercs

关于结果显示那个, 感觉太复杂了, 我不需要过滤, 搜索之类的花哨功能
我想了一个简洁实用的方法:
还是用上楼的form方法
它里面仅有一个多行编辑框控件, 把结果按 Format-list 格式显示在里面就可以了下面是能用的代码

Add-Type -AssemblyName System.Windows.Forms

$form = New-Object System.Windows.Forms.Form
$form.Text = '显示结果'
$form.ClientSize = '200, 300'

$Form.FormBorderStyle = "FixedToolWindow"
$form.StartPosition = 'CenterScreen'

$textbox1 = New-Object 'System.Windows.Forms.TextBox'
$textbox1.Name = 'textbox1'
$textbox1.Dock = 'Fill'
$textbox1.Multiline = $True
$textbox1.Font = New-Object drawing.Font("微软雅黑", 10)
$form.Controls.Add($textbox1)

$result = $form.ShowDialog()
复制代码

作者: 5i365 时间: 2022-9-20 08:06

回复 29# 5i365

发现一个问题, 经常有下面的错误提示, 但是也能显示结果,
不知道是不是获取数据时出错了, 这个挺重要, 要是数据错了, 成绩汇总肯定也错了, 学生就哭晕了

ERROR: New-Object : Exception calling ".ctor" with "1" argument(s): "The file is not an valid Package file. If the file is encrypted, please supply the password in th
ERROR: e constructor."
EP3.ps1 (7, 11): ERROR: At Line: 7 char: 11
ERROR: + ...    $pack = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $_.Fu ...
ERROR: +                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:    + CategoryInfo       : InvalidOperation: (

[New-Object], MethodInvocationException
ERROR:    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand
ERROR:
ERROR: Cannot index into a null array.
EP3.ps1 (9, 3): ERROR: At Line: 9 char: 3
ERROR: +       $a2d = $pack.Workbook.Worksheets[1].Cells.Value
ERROR: +       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:    + CategoryInfo       : InvalidOperation: (:) [], RuntimeException
ERROR:    + FullyQualifiedErrorId : NullArray

作者: flashercs 时间: 2022-9-20 15:26

本帖最后由 flashercs 于 2022-9-20 19:00 编辑

出错是因为有的.xlsx不是真正Excel文件,披着羊皮的狼而已.

Add-Type -LiteralPath .\EPPlus.dll -ErrorAction Stop
$fields = @('姓名:', '语文:', '数学:', '社会:', '历史:')
$keyField = '姓名:'
$groupPso = Get-ChildItem -Path .\*.xlsx -Filter *.xlsx | Where-Object { -not $_.PSIsContainer } | ForEach-Object {
  try {
    $_ | Resolve-Path -Relative | Write-Host
    $pack = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $_.FullName -ErrorAction Stop
    $pso = New-Object psobject
    $a2d = $pack.Workbook.Worksheets[1].Cells.Value
    for ($x = $a2d.GetLowerBound(0); $x -le $a2d.GetUpperBound(0); $x++) {
      $propName = $null
      for ($y = $a2d.GetLowerBound(1); $y -le $a2d.GetUpperBound(1); $y++) {
        if ($null -eq $propName) {
          if ($fields -contains $a2d[$x, $y]) {
            $propName = $a2d[$x, $y]
          }
        } else {
          if ($null -ne $a2d[$x, $y]) {
            $pso | Add-Member -MemberType NoteProperty -Name $propName -Value $a2d[$x, $y]
            $propName = $null
          }
        }
      }
    }
    $pso
  } finally {
    if ($pack) {
      $pack.Dispose()
      $pack = $null
    }
  }
  trap { }
} | Group-Object -Property $keyField
function Get-StudentScore {
  param (
    [string[]]$UserName
  )
  $groupPso | Where-Object { $null -eq $UserName -or $UserName -contains '*' -or $UserName -contains $_.Name } | ForEach-Object {
    $pso = New-Object psobject -Property @{$keyField = $_.Name }
    $_.Group | Measure-Object -Property ($fields -ne $keyField) -Sum | ForEach-Object {
      $pso | Add-Member -MemberType NoteProperty -Name $_.Property -Value $_.Sum.ToString('f2')
    }
    $pso
  }
}
# main
# search 佟玉
# Get-StudentScore -UserName 佟玉 | Format-Table -AutoSize
# search 张三,李四
# Get-StudentScore -UserName 张三, 李四 | Format-Table -AutoSize
# search 所有学生
# Get-StudentScore -UserName * | Format-Table -AutoSize
# search 所有学生
# Get-StudentScore | Format-Table -AutoSize
# 左对齐
# Get-StudentScore | Out-GridView -Title 学生成绩汇总 -Wait
# 筛选姓名
# Get-StudentScore | Out-GridView -Title 学生成绩汇总_多选 -OutputMode Multiple | Out-GridView -Title 学生成绩汇总_已选择 -Wait

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$form = New-Object System.Windows.Forms.Form
$form.Text = '双击要查询的姓名'
$Form.FormBorderStyle = "FixedToolWindow"
$form.StartPosition = 'CenterScreen'
$form.Font = New-Object System.Drawing.Font("微软雅黑", 10, [Drawing.FontStyle]::Bold)
$form.ClientSize = '160, 300'

$listBox = New-Object System.Windows.Forms.Listbox
# $listBox.Dock = 'Fill'
$listBox.Location = '0, 0'
$listBox.Size = '160,280'
$listBox.Anchor = 'Left,Top,Right,Bottom'
$listBox.SelectionMode = 'MultiExtended'
$listBox.DataSource = [System.Collections.ArrayList]@($groupPso.Name | Sort-Object)
$form.Controls.Add($listBox)

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = '0,275'
$OKButton.Size = '160,25'
$OKButton.Text = 'OK'
$OKButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$OKButton.Anchor = 'Bottom'
$form.AcceptButton = $OKButton
$form.Controls.Add($OKButton)

$form.TopMost = $true
if ($groupPso.Count -gt 10) {
  $form.ClientSize = '160,700'
}
$result = $form.ShowDialog()

if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
  $selectedItems = $listBox.SelectedItems
}

($form, $OKButton, $listBox).Dispose()
if (-not $selectedItems) {
  exit
}

$form = New-Object System.Windows.Forms.Form
$form.Text = '显示结果'
$form.ClientSize = '200, 300'

$Form.FormBorderStyle = "FixedToolWindow"
$form.StartPosition = 'CenterScreen'

$textbox1 = New-Object 'System.Windows.Forms.TextBox'
$textbox1.Name = 'textbox1'
$textbox1.Dock = 'Fill'
$textbox1.Multiline = $True
$textbox1.ScrollBars = 'Both'
$textbox1.Font = New-Object System.Drawing.Font("微软雅黑", 10)
$form.Controls.Add($textbox1)
$textbox1.AppendText((Get-StudentScore -UserName $selectedItems | Format-List | Out-String -Width ([int]::MaxValue)))
$result = $form.ShowDialog()
($form, $textbox1).Dispose()
复制代码

作者: 5i365 时间: 2022-9-20 16:43

本帖最后由 5i365 于 2022-9-20 19:28 编辑

回复 32# flashercs

多谢大侠

作者: wanghan519 时间: 2024-2-2 15:03

回复 2# idwma

大师，我在很多地方抄袭你这回答的第一句，非常好用

#@&cls&powershell "type '%~0'|out-string|iex"&pause&exit
复制代码

请问，第二个字符，那个@在这里起什么作用，删掉似乎也能运行，只是不知道有什么影响，多谢解惑

作者: idwma 时间: 2024-2-2 18:33

回复 34# wanghan519

不是不是当不起
也是借鉴前辈们的
还真不知道有没得啥子影响

作者: newswan 时间: 2024-2-2 20:32

比较一下

#@&echo "ok"
#&echo "ok"
复制代码

欢迎光临批处理之家 (http://www.bathome.net/)