如何用Import-Excel模块转换Excel门禁日志表格布局并导出

完整实现代码 #

# 导入Excel数据，指定姓名和日期列（假设列索引1是姓名，2是日期）
$records = Import-Excel -Path "$target\test.xlsx" -ImportColumns @(1, 2)

# 提取并格式化去重日期，排序后作为表头
$uniqueDates = $records.Date | ForEach-Object { $_.ToString("MM/dd/yyyy") } | Sort-Object -Unique

# 提取去重姓名并按字母排序
$uniqueNames = $records.Name | Sort-Object -Unique

# 按姓名+日期分组，统计每组的门禁次数
$groupedStats = $records | Group-Object -Property Name, { $_.Date.ToString("MM/dd/yyyy") } | ForEach-Object {
    [PSCustomObject]@{
        Name  = $_.Group[0].Name
        Date  = $_.Values[1]
        Count = $_.Count
    }
}

# 构建每个用户的统计对象
$outputData = foreach ($name in $uniqueNames) {
    # 初始化用户对象，设置姓名属性
    $userStats = [PSCustomObject]@{ Name = $name }
    
    # 为每个日期添加次数属性，无记录则填0
    foreach ($date in $uniqueDates) {
        $count = ($groupedStats | Where-Object { $_.Name -eq $name -and $_.Date -eq $date }).Count
        $userStats | Add-Member -NotePropertyName $date -NotePropertyValue ($count ?? 0)
    }
    
    $userStats
}

# 将结果导出到新的Excel文件
$outputData | Export-Excel -Path "$target\门禁统计结果.xlsx" -AutoSize -BoldTopRow

代码说明 #

• 提取去重数据：用Sort-Object -Unique替代循环累加+Get-Unique，操作更高效简洁。
• 分组统计：通过Group-Object按姓名和格式化后的日期分组，直接获取每组的记录数（即门禁次数）。
• 构建输出对象：遍历每个姓名，为其添加所有日期的次数属性，无门禁记录的日期自动填充0。
• 导出Excel：用Export-Excel生成最终表格，自动调整列宽并加粗表头，提升可读性。

关键优化点 #

• 摒弃低效的数组累加操作，改用PowerShell原生集合处理cmdlet，提升运行效率。
• 通过分组查询快速匹配用户的每日门禁次数，避免重复遍历全量数据。
• 严格匹配需求结构：姓名为行、日期为列，单元格精准填充对应次数。

内容的提问来源于stack exchange，提问作者MDJ