<#
.SYNOPSIS
    根据关键词文件隐藏Excel中不需要的列，智能检测标题行并高亮保留列标题。

.DESCRIPTION
    读取关键词文件，连接当前活动的Excel窗口，智能检测标题行，匹配关键词列名，
    隐藏未匹配的列，最后高亮保留列标题，提升Excel表格的可读性。

.PARAMETER keywordsFile
    关键词文件路径，默认为脚本目录下的 keys\default.txt。

.PARAMETER maxColumnsToScan
    最大扫描列数，默认500。

.PARAMETER maxRowsToCheckForHeader
    最大检查标题行的行数，默认20。

.EXAMPLE
    .\HideColumns.ps1 -keywordsFile "C:\keys\mykeys.txt"
#>

param(
    [string]$keywordsFile = "$PSScriptRoot\keys\default.txt",
    [int]$maxColumnsToScan = 500,
    [int]$maxRowsToCheckForHeader = 20
)

# 错误时停止脚本
$ErrorActionPreference = "Stop"

# 高亮颜色和字体颜色设置
$HIGHLIGHT_COLOR = 13434879  # 浅绿色
$FONT_COLOR = -4165632       # 黑色

function Get-ExcelColumnLetter([int]$colNumber) {
    $dividend = $colNumber
    $columnName = ""
    while ($dividend -gt 0) {
        $modulo = ($dividend - 1) % 26
        $columnName = [char](65 + [int]$modulo) + $columnName
        $dividend = [math]::Floor(($dividend - $modulo) / 26)
    }
    return $columnName
}

try {
    # 1. 读取关键词文件，去除全角空格和前后空格
    if (-not (Test-Path $keywordsFile)) {
        throw "关键词文件不存在: $keywordsFile"
    }

    $keepColumns = Get-Content $keywordsFile -Encoding Default | ForEach-Object {
        ($_ -replace '\u3000', '').Trim()
    } | Where-Object { $_ -ne '' }

    Write-Host ""
    Write-Host "=== 列隐藏工具 V1.2 (优化版) ===" -ForegroundColor Cyan
    Write-Host ("保留列关键词 ({0}个):" -f $keepColumns.Count) -ForegroundColor Yellow
    $keepColumns | ForEach-Object { Write-Host ("  {0}" -f $_) }

    # 2. 连接Excel应用程序
    try {
        $app = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
    }
    catch {
        throw "未找到活动的Excel窗口，请先打开Excel并激活目标工作簿。"
    }

    $worksheet = $app.ActiveWorkbook.ActiveSheet
    Write-Host ""
    Write-Host "已连接到 Microsoft Excel" -ForegroundColor Green

    # 3. 智能检测标题行
    $headerRowNumber = 1
    $emptyStreakThreshold = 5
    $minTitleColumns = 4  # 标题行最少非空列数

    Write-Host ""
    Write-Host "智能检测标题行中..." -ForegroundColor Gray

    for ($row = 1; $row -le $maxRowsToCheckForHeader; $row++) {
        $nonEmptyColumns = 0
        $textColumns = 0
        $emptyStreak = 0

        for ($col = 1; $col -le $maxColumnsToScan; $col++) {
            $cellValue = $worksheet.Cells($row, $col).Text
            $cellValueClean = ($cellValue -replace '\u3000', '').Trim()
            if (-not [string]::IsNullOrWhiteSpace($cellValueClean)) {
                $nonEmptyColumns++
                $emptyStreak = 0
                # 判断是否为文本（排除纯数字、百分比、小数和日期格式）
                if (($cellValueClean -notmatch '^\d+(\.\d+)?%?$') -and ($cellValueClean -notmatch '^\d{4}[-/]\d{1,2}[-/]\d{1,2}$')) {
                    $textColumns++
                }
            }
            else {
                $emptyStreak++
                if ($emptyStreak -ge $emptyStreakThreshold) { break }
            }
        }

        if ($nonEmptyColumns -ge $minTitleColumns) {
            $textRatio = $textColumns / $nonEmptyColumns
            Write-Host ("  行 {0}: 非空列={1}, 文本列={2}, 比例={3:P0}" -f $row, $nonEmptyColumns, $textColumns, $textRatio) -ForegroundColor DarkGray
            if ($textRatio -ge 0.6) {
                $headerRowNumber = $row
                break
            }
        }
    }

    Write-Host ("检测到标题行: 第 {0} 行" -f $headerRowNumber) -ForegroundColor Cyan

    # 4. 检测实际使用列范围
    $realLastColumn = 1
    $emptyStreak = 0

    Write-Host "实际列数检测中..." -ForegroundColor Gray
    for ($col = 1; $col -le $maxColumnsToScan; $col++) {
        $cellValue = $worksheet.Cells($headerRowNumber, $col).Text
        $cellValueClean = ($cellValue -replace '\u3000', '').Trim()
        if (-not [string]::IsNullOrWhiteSpace($cellValueClean)) {
            $realLastColumn = $col
            $emptyStreak = 0
        }
        else {
            $emptyStreak++
            if ($emptyStreak -ge $emptyStreakThreshold) { break }
        }
    }

    Write-Host ("实际使用列数: {0}" -f $realLastColumn) -ForegroundColor Cyan

    # 5. 构建列名映射表 
    $columnMap = @{}
    $emptyColumns = @()

    Write-Host ""
    Write-Host "Excel 标题列:" -ForegroundColor Cyan
    for ($col = 1; $col -le $realLastColumn; $col++) {
        $rawName = $worksheet.Cells($headerRowNumber, $col).Text
        $cleanName = ($rawName -replace '\u3000', '').Trim()
        if ($cleanName -ne '') {
            $columnMap[$col] = $cleanName
        }
        else {
            $emptyColumns += $col
        }
    }
    foreach ($col in $columnMap.Keys) {
        Write-Host ("  {0}: {1}" -f (Get-ExcelColumnLetter $col), $columnMap[$col])
    }

    Write-Host ("空白列数: {0}" -f $emptyColumns.Count) -ForegroundColor DarkCyan

    # 6. 关键词清洗并统一小写
    $keepColumnsLower = $keepColumns | ForEach-Object { $_.ToLower() }

    # 7. 精确匹配列号（忽略大小写）
    $columnsToKeep = $columnMap.GetEnumerator() | Where-Object {
        $colNameLower = $_.Value.ToLower()
        $keepColumnsLower -contains $colNameLower
    } | Select-Object -ExpandProperty Key

    Write-Host ""
    Write-Host "匹配到的列:" -ForegroundColor Green
    foreach ($col in $columnsToKeep) {
        Write-Host ("  {0}: {1}" -f (Get-ExcelColumnLetter $col), $columnMap[$col])
    }

    # 8. 输出未匹配的关键词
    $unmatchedKeywords = $keepColumnsLower | Where-Object {
        $k = $_
        -not ($columnMap.Values | ForEach-Object { $_.ToLower() } | Where-Object { $_ -eq $k })
    }
    if ($unmatchedKeywords.Count -gt 0) {
        Write-Host ""
        Write-Host "未匹配到的关键词:" -ForegroundColor Yellow
        $unmatchedKeywords | ForEach-Object { Write-Host ("  {0}" -f $_) }
    }

    # 9. 输出未匹配的Excel列名
    $unmatchedColumns = $columnMap.GetEnumerator() | Where-Object {
        $colNameLower = $_.Value.ToLower()
        -not ($keepColumnsLower -contains $colNameLower)
    }
    if ($unmatchedColumns.Count -gt 0) {
        Write-Host ""
        Write-Host "未匹配到关键词的Excel列:" -ForegroundColor DarkGray
        foreach ($col in $unmatchedColumns) {
            Write-Host ("  {0}: {1}" -f (Get-ExcelColumnLetter $col.Key), $col.Value)
        }
    }

    if (-not $columnsToKeep) {
        Write-Host ""
        Write-Host "错误：未找到匹配列！" -ForegroundColor Red
        Write-Host "工作表实际列名:" -ForegroundColor Gray
        $columnMap.GetEnumerator() | Sort-Object Key | ForEach-Object {
            Write-Host ("  {0}: {1}" -f (Get-ExcelColumnLetter $_.Key), $_.Value)
        }
        exit 1
    }

    Write-Host ""
    Write-Host "保留列列表:" -ForegroundColor Green
    foreach ($col in $columnsToKeep | Sort-Object) {
        Write-Host ("  {0}: {1}" -f (Get-ExcelColumnLetter $col), $columnMap[$col]) -ForegroundColor Cyan
    }

    # 10. 处理列隐藏
    Write-Host ""
    Write-Host "开始隐藏不需要的列..." -ForegroundColor Gray

    # 先全部显示，避免残留隐藏
    $worksheet.Columns.Hidden = $false

    $allColumns = 1..$realLastColumn
    $columnsToHide = $allColumns | Where-Object { $_ -notin $columnsToKeep -and $_ -notin $emptyColumns }

    if ($columnsToHide.Count -gt 0) {
        Write-Host "隐藏列范围:" -ForegroundColor Magenta

        $ranges = @()
        $start = $null
        $prev = $null
        foreach ($col in ($columnsToHide | Sort-Object)) {
            if ($null -eq $start) {
                $start = $col
                $prev = $col
            }
            elseif ($col -eq $prev + 1) {
                $prev = $col
            }
            else {
                $ranges += "$(Get-ExcelColumnLetter $start):$(Get-ExcelColumnLetter $prev)"
                $start = $col
                $prev = $col
            }
        }
        if ($null -ne $start) {
            $ranges += "$(Get-ExcelColumnLetter $start):$(Get-ExcelColumnLetter $prev)"
        }
        foreach ($range in $ranges) {
            Write-Host ("  {0}" -f $range)
            $worksheet.Range($range).EntireColumn.Hidden = $true
        }
    }
    else {
        Write-Host "无列需要隐藏，全部为保留列。" -ForegroundColor Gray
    }
 
    # 11. 高亮保留列标题，其他列标题恢复默认
    for ($col = 1; $col -le $realLastColumn; $col++) {
        $cell = $worksheet.Cells($headerRowNumber, $col)
        $cell.Interior.ColorIndex = -4142  # 无填充色
        $cell.Font.ColorIndex = -4105      # 自动字体颜色
    }

    foreach ($col in $columnsToKeep) {
        try {
            $headerCell = $worksheet.Cells($headerRowNumber, $col)
            $headerCell.Interior.Color = $HIGHLIGHT_COLOR
            $headerCell.Font.Color = $FONT_COLOR
        }
        catch {
            Write-Host ("警告: 无法高亮列 {0}" -f $col) -ForegroundColor Yellow
        }
    }

    # 12. 完成报告
    Write-Host ""
    Write-Host "=== 操作完成 ===" -ForegroundColor Green
    Write-Host ("标题行: 第 {0} 行" -f $headerRowNumber) -ForegroundColor Cyan
    Write-Host ("实际列数: {0}" -f $realLastColumn) -ForegroundColor Cyan
    Write-Host ("保留列数: {0}" -f $columnsToKeep.Count) -ForegroundColor Cyan
    Write-Host ("隐藏列数: {0}" -f $columnsToHide.Count) -ForegroundColor Cyan

    $keepColumnLetters = $columnsToKeep | ForEach-Object { Get-ExcelColumnLetter $_ }
    Write-Host ("保留列: {0}" -f ($keepColumnLetters -join ', ')) -ForegroundColor Cyan

    $keepColumnNames = $columnsToKeep | ForEach-Object { $columnMap[$_] }
    Write-Host ("对应列名: {0}" -f ($keepColumnNames -join ', ')) -ForegroundColor Cyan
}
catch {
    Write-Host ""
    Write-Host ("错误: {0}" -f $_) -ForegroundColor Red
    exit 1
}
finally {
    # 释放COM对象，防止Excel进程残留
    try {
        if ($worksheet) { [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null }
        if ($app) { [System.Runtime.Interopservices.Marshal]::ReleaseComObject($app) | Out-Null }
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
    }
    catch {}
}
