// 想象你在處理一個(gè)2GB的CSV日志文件...
using var fs = new FileStream("huge-log.csv", FileMode.Open, FileAccess.Read);
using var reader = new StreamReader(fs, Encoding.UTF8, bufferSize: 4096);

string line;
while ((line = await reader.ReadLineAsync()) != null)
{
    ProcessLine(line); // 流式處理，內(nèi)存占用恒定
}

// 方法A：靜態(tài)方法（簡潔但不夠靈活）
var files = Directory.GetFiles(@"C:\Inputs", "*.xlsx");

// 方法B：實(shí)例化對(duì)象（推薦?。?
var dirInfo = new DirectoryInfo(@"C:\Inputs");
var excelFiles = dirInfo.GetFiles("*.xls*", SearchOption.AllDirectories)
    .Where(f => f.Length > 0 && !f.Name.StartsWith("~$"))
    .OrderBy(f => f.CreationTime);

sequenceDiagram
    participant CSharpApp
    participant CLR
    participant COMProxy
    participant ExcelProcess

    CSharpApp->>CLR: new Excel.Application()
    CLR->>COMProxy: CreateInstance("Excel.Application")
    COMProxy->>ExcelProcess: 啟動(dòng) EXCEL.EXE 并綁定
    ExcelProcess-->>COMProxy: 返回 IDispatch 接口
    COMProxy-->>CLR: 包裝為 RCW (Runtime Callable Wrapper)
    CLR-->>CSharpApp: 返回 Application 實(shí)例

    CSharpApp->>ExcelProcess: 調(diào)用 Workbooks.Open(...)
    CSharpApp->>ExcelProcess: 讀取 Cells.Value2
    CSharpApp->>ExcelProcess: 修改樣式/公式

    CSharpApp->>COMProxy: Marshal.ReleaseComObject(obj)
    COMProxy->>ExcelProcess: 減少引用計(jì)數(shù)
    alt 引用為0
        ExcelProcess->>OS: 終止進(jìn)程
    end

Task.Run(() =>
{
    var app = new Application(); // ?? 在MTA線程上調(diào)用STA組件！
});

Thread t = new Thread(() =>
{
    try
    {
        var app = new Application { Visible = false };
        // ... 執(zhí)行轉(zhuǎn)換邏輯
    }
    finally
    {
        if (app != null)
        {
            app.Quit();
            Marshal.ReleaseComObject(app);
        }
    }
});
t.SetApartmentState(ApartmentState.STA); // 關(guān)鍵！
t.Start();
t.Join(); // 等待完成

public void Convert(string input, string output)
{
    // 開啟Excel...
    // 打開文件...
    // 遍歷每個(gè)sheet...
    // 處理合并單元格...
    // 寫入CSV...
    // 關(guān)閉釋放...
    // 日志記錄...
    // 異常處理...
    // 進(jìn)度通知...
    // ...
}

public abstract class FileConverter
{
    protected string InputPath { get; set; }
    protected string OutputPath { get; set; }
    protected ILogger Logger { get; set; }

    public FileConverter(string input, string output, ILogger logger)
    {
        InputPath = input;
        OutputPath = output;
        Logger = logger ?? throw new ArgumentNullException(nameof(logger));
    }

    public abstract void Convert();
}

public class ExcelToCsvConverter : FileConverter
{
    public override void Convert()
    {
        Logger.Log("開始Excel轉(zhuǎn)CSV...");

        using var session = new ExcelSession(); // RAII風(fēng)格資源管理
        var workbook = session.App.Workbooks.Open(InputPath);

        foreach (Worksheet sheet in workbook.Sheets)
        {
            if (!IsSheetValid(sheet)) continue;

            var data = ExtractData(sheet);
            WriteCsv(data, $"{OutputPath}_{sheet.Name}.csv");
        }
    }
}

classDiagram
    class FileConverter {
        <<abstract>>
        +string InputPath
        +string OutputPath
        +ILogger Logger
        +Convert()
    }
    class ExcelToCsvConverter {
        +Convert()
    }
    class CsvToExcelConverter {
        +Convert()
    }
    FileConverter <|-- ExcelToCsvConverter
    FileConverter <|-- CsvToExcelConverter
    ILogger <-- FileConverter : 依賴

public class AccountRecord
{
    public int Id { get; set; }
    [property: JsonProperty(ItemConverterType = typeof(StringEnumConverter))]
    public AccountStatus Status { get; set; }
    public decimal Balance { get; set; }
}

// 解析時(shí)主動(dòng)驗(yàn)證
if (decimal.TryParse(rawValue, NumberStyles.AllowCurrencySymbol, 
                     CultureInfo.CurrentCulture, out var amount))
{
    record.Balance = amount;
}
else
{
    logger.Warn($"無法解析金額: {rawValue}");
    record.Balance = 0m; // 或拋出自定義異常
}

#nullable enable
public string CustomerName { get; set; } = null!; // 明確告訴編譯器這里不會(huì)為空
public string? Email { get; set; } // 可空引用

Excel.Application app = null;
Excel.Workbook wb = null;

try
{
    app = new Excel.Application
    {
        Visible = false,
        DisplayAlerts = false,
        ScreenUpdating = false // 關(guān)鍵！大幅提升性能
    };

    wb = app.Workbooks.Open(filePath, ReadOnly: true);

    // 安全獲取第一個(gè)有效工作表
    var ws = GetFirstVisibleSheet(wb);
    if (ws == null) throw new InvalidOperationException("無可用數(shù)據(jù)表");

    ProcessSheet(ws, outputPath);
}
catch (IOException ex)
{
    throw new ConversionException($"文件被占用或不存在: {filePath}", ex);
}
finally
{
    wb?.Close();
    app?.Quit();
    ReleaseComObjects(app, wb); // 自定義釋放工具函數(shù)
}

static object ConvertCellValue(object cellValue)
{
    return cellValue switch
    {
        null => "",
        double d when IsOleDate(d) => DateTime.FromOADate(d),
        double d => d,
        bool b => b,
        _ => cellValue.ToString()
    };
}

static bool IsOleDate(double value)
{
    try
    {
        var dt = DateTime.FromOADate(value);
        return dt >= new DateTime(1900, 1, 1) && dt <= DateTime.Now.AddYears(1);
    }
    catch
    {
        return false;
    }
}

static readonly Encoding Utf8WithBom = new UTF8Encoding(encoderShouldEmitUTF8Identifier: true);

using var writer = new StreamWriter(outputPath, false, Utf8WithBom);

// ? 每次Cells[i,j]都是一次COM調(diào)用，O(n2)復(fù)雜度！
for (int i = 0; i < rows; i++)
{
    for (int j = 0; j < cols; j++)
    {
        worksheet.Cells[i + 1, j + 1] = data[i][j];
    }
}

// ? 先準(zhǔn)備好二維數(shù)組
var dataArray = new object[data.Count, data[0].Count];
for (int i = 0; i < data.Count; i++)
{
    for (int j = 0; j < data[i].Count; j++)
    {
        dataArray[i, j] = data[i][j];
    }
}

// ? 一次性寫入Range
var range = worksheet.Range[worksheet.Cells[1,1], 
                           worksheet.Cells[data.Count, data[0].Count]];
range.Value2 = dataArray;

void ApplyProfessionalFormatting(Worksheet ws, int rowCount, int colCount)
{
    // 標(biāo)題行加粗+背景色
    var header = ws.Range["A1", $"Z{1}"].Resize[1, colCount];
    header.Font.Bold = true;
    header.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(79, 129, 189));
    header.Font.Color = ColorTranslator.ToOle(Color.White);

    // 自動(dòng)調(diào)整列寬
    ws.UsedRange.Columns.AutoFit();

    // 添加邊框
    var tableRange = ws.Range["A1", $"Z{rowCount}"].Resize[rowCount, colCount];
    tableRange.Borders.LineStyle = XlLineStyle.xlContinuous;
    tableRange.Borders.Weight = XlBorderWeight.xlThin;
}

static void ReleaseComObjects(params object[] objects)
{
    foreach (var obj in objects.Where(o => o != null))
    {
        try { Marshal.ReleaseComObject(obj); }
        catch (InvalidComObjectException) { /* 已經(jīng)被回收 */ }
    }
    GC.Collect(); // 促使Finalizer盡快執(zhí)行
    GC.WaitForPendingFinalizers();
}

public class RobustFileConverter : FileConverter
{
    protected override void ConvertCore()
    {
        try
        {
            base.ConvertCore();
        }
        catch (FileNotFoundException)
        {
            HandleMissingInput();
        }
        catch (UnauthorizedAccessException)
        {
            RequestPermissionAndRetry();
        }
        catch (COMException ex) when (ex.ErrorCode == -2147221040)
        {
            // CLSID未注冊(cè)？可能是缺少Office
            FallbackToOpenXmlLibrary();
        }
        catch (Exception unexpected)
        {
            LogCriticalError(unexpected);
            CreateDiagnosticPackage(); // 打包現(xiàn)場(chǎng)信息便于排查
            throw;
        }
    }
}