flashercs

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 学生成绩汇总_已选择 -WaitCOPY

flashercs

回复 17# 5i365


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

flashercs

回复 19# 5i365


    16楼 已修改.

flashercs

回复 24# 5i365

# 左对齐
Get-StudentScore | Out-GridView -Title 学生成绩汇总 -WaitCOPY

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

flashercs

回复 23# 5i365


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

flashercs

回复 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()COPY

flashercs

出错是因为 有的.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()COPY