using ClosedXML.Excel;

var path = @"d:\DEMONET\doc\S1产销协同_UAT测试报告v1.0.xlsx";
using var wb = new XLWorkbook(path);

var outputPath = @"d:\DEMONET\doc\report_verify.txt";
using var writer = new StreamWriter(outputPath, false, System.Text.Encoding.UTF8);

writer.WriteLine("=== Sheets ===");
int totalNormal = 0, totalEx = 0;
foreach (var ws in wb.Worksheets)
{
    var lastRow = ws.LastRowUsed()?.RowNumber() ?? 0;
    writer.WriteLine($"  [{ws.Name}] rows={lastRow}");
    
    // Count exception rows (colored)
    if (ws.Name != "目录")
    {
        for (int r = 12; r <= lastRow; r++)
        {
            var cell = ws.Cell(r, 1);
            if (!cell.IsEmpty())
            {
                var fill = cell.Style.Fill.BackgroundColor;
                if (fill.HasValue && fill.ToString() == "FFFCE4D6")
                    totalEx++;
                else if (!fill.HasValue || fill.ToString() == "FFFFFFFF" || fill.ToString() == "FFFFFF")
                {
                    var val = cell.Value.ToString();
                    if (!val.Contains("【异常】") && !val.Contains("▼"))
                        totalNormal++;
                }
            }
        }
    }
}
writer.WriteLine();
writer.WriteLine($"Total normal cases: {totalNormal}");
writer.WriteLine($"Total exception cases: {totalEx}");
writer.WriteLine($"Grand total: {totalNormal + totalEx}");

writer.WriteLine();
writer.WriteLine("=== 1 合同评审 (all rows) ===");
var ws1 = wb.Worksheet("1 合同评审");
var lastR = ws1.LastRowUsed()?.RowNumber() ?? 0;
for (int r = 1; r <= lastR; r++)
{
    var vals = new List<string>();
    for (int c = 1; c <= 20; c++)
    {
        var cell = ws1.Cell(r, c);
        if (!cell.IsEmpty())
        {
            var fill = cell.Style.Fill.BackgroundColor;
            var tag = fill.HasValue && fill.ToString() == "FFFCE4D6" ? "[EX]" : "";
            vals.Add($"{cell.Address}{tag}={cell.Value}");
        }
    }
    if (vals.Count > 0)
        writer.WriteLine($"  Row {r}: {string.Join(" | ", vals)}");
}

writer.WriteLine();
writer.WriteLine("=== DONE ===");