优化Excel数据导入速度：IronXL与Office Interop对比及优化咨询

我写了一个从.xlsx文件导入数据并生成大量XML文件的程序。一开始用IronXL实现，但发现发布时它不是免费选项，所以换成了Office Interop。现在两种实现都能正常工作，但IronXL速度快很多——处理两个工作表时，IronXL约8秒，Office Interop要40秒，后续要处理10-15个工作表，耗时差距会更大。想问下我的Office Interop代码能不能优化，还是说IronXL天生就更快，没优化空间？

Office Interop实现代码 #

主加载方法 #

static public void LoadExcelDataInterop()
{
    // 默认浮点数导入时用逗号作为小数分隔符，比如12,5而不是12.5
    // 代码里其他地方都要求用12.5的格式
    // 之前把武器数据输出到文件时是逗号格式，导致了错误
    System.Globalization.CultureInfo customCulture = (System.Globalization.CultureInfo)System.Threading.Thread.CurrentThread.CurrentCulture.Clone();
    customCulture.NumberFormat.NumberDecimalSeparator = ".";
    System.Threading.Thread.CurrentThread.CurrentCulture = customCulture;

    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook wb = xlApp.Workbooks.Open(@"D:\Outward - r2modman\Mods\OutwardDe\profiles\Outward\BepInEx\plugins\Outward_Mod_Weapons_ValuesToImport.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

    foreach (Excel.Worksheet ws in wb.Worksheets)
    {
        if (ws.Name == "Swords_1h" || ws.Name == "Swords_2h"
            //     || ws.Name == "Axes_1h"    || ws.Name == "Axes_2h"
            //     || ws.Name == "Maces_1h"   || ws.Name == "Maces_2h"
            //     || ws.Name == "Halberds"   || ws.Name == "Staves"
            //     || ws.Name == "Spears"     || ws.Name == "Gauntlets"
            //     || ws.Name == "Bows"       || ws.Name == "Shields"
            //     || ws.Name == "Chakrams"   || ws.Name == "Daggers"     || ws.Name == "Pistols"
            )
        {

            for (int x = 2; x > 0; x++)
            {
                var cell = ws.Cells[x, 2] as Excel.Range;
                string cell_name = (string)cell.Value;

                if (cell_name == null) { break; }
                else
                {
                    dict_Weapons.Add(cell_name, new SL_Weapon(wb, ws, cell));
                }
            }
        }
        Marshal.ReleaseComObject(ws);
    }

    wb.Close(false, null, null);
    xlApp.Quit();

    Marshal.ReleaseComObject(wb);
    Marshal.ReleaseComObject(xlApp);
}

SL_Weapon构造函数 #

public SL_Weapon(Excel.Workbook wb, Excel.Worksheet ws, Excel.Range cell)
{
    StatsHolder = new SL_WeaponStats
    {
        Damage_Bonus = new float[9],
        Damage_Resistance = new float[9],
    };
    var SH = ((SL_WeaponStats)StatsHolder);
    SH.BaseDamage = new List<SL_Damage>();
    Effects = new List<OE_Effect>();

    for (int i = 1; i <= ws.Columns.Count; i++)
    {
        var heading = ws.Cells[1, i] as Excel.Range;
        string headingName = (string)heading.Value;

        var workingCell = ws.Cells[cell.Row, heading.Column] as Excel.Range;
        var workingCell_nextCell = ws.Cells[cell.Row, heading.Column + 1] as Excel.Range;

        if (headingName == null) { break; }
        else if (workingCell.Value == null) { continue; }
        else
        {
            if (headingName == "Name") { Name = (string)workingCell.Value; }
            if (headingName == "ID") { Target_ItemID = New_ItemID = (int)workingCell.Value; }
            
            if (headingName == "DMG Physical") { SH.BaseDamage.Add(new SL_Damage { Damage = (float)workingCell.Value, Type = "Physical" }); }
            if (headingName == "DMG 2") { SH.BaseDamage.Add(new SL_Damage { Damage = (float)workingCell.Value, Type = (string)workingCell_nextCell.Value }); }
            if (headingName == "DMG 3") { SH.BaseDamage.Add(new SL_Damage { Damage = (float)workingCell.Value, Type = (string)workingCell_nextCell.Value }); }

            // 选项1：通过反射赋值StatsHolder字段——不行，会报错（无法将System.Single转换为System.Int32）
            /*
            FieldInfo[] fields = typeof(SL_WeaponStats).GetFields();
            foreach (var field in fields)
            {
                if (headingName == field.Name) { field.SetValue(StatsHolder, (float)workingCell.Value); }
            }
            */

            // 选项2：直接赋值StatsHolder字段
            if (headingName == "MaxDurability") { SH.MaxDurability = (int)workingCell.Value; }
            if (headingName == "RawWeight") { SH.RawWeight = (float)workingCell.Value; }
            if (headingName == "BaseValue") { SH.BaseValue = (int)workingCell.Value; }

            if (headingName == "StamCost") { SH.StamCost = (float)workingCell.Value; }
            if (headingName == "AttackSpeed") { SH.AttackSpeed = (float)workingCell.Value; }
            if (headingName == "Impact") { SH.Impact = (float)workingCell.Value; }
            
            if (workingCell_nextCell.Value != null) 
            {
                if (headingName == "Effect 1" || headingName == "Effect 2" || headingName == "Effect 3")
                { Effects.Add(new OE_Effect { StatusEffect = (string)workingCell.Value, Buildup = (int)workingCell_nextCell.Value }); }

            }
        }

        foreach (Excel.Worksheet worksheet in wb.Worksheets)
        {
            if (worksheet.Name == "Damage_BonusOrRes")
            {
                var item_dmgBonus = ((SL_EquipmentStats)StatsHolder).Damage_Bonus;
                var item_dmgResistance = ((SL_EquipmentStats)StatsHolder).Damage_Resistance;

                for (int x = 2; x < 10; x++ )
                {
                    Excel.Range workingCell2 = worksheet.Cells[x, 1] as Excel.Range;
                    string workingCell2Name = (string)workingCell2.Value;
                    if (workingCell2Name == null) { break; }
                    else if (workingCell2.Value.ToString() == cell.Value.ToString())
                    {
                        for (int y = 0; y < 6; y++)
                        {
                            if ((worksheet.Cells[x, y + 2] as Excel.Range).Value != null)
                            {
                                item_dmgBonus[y] = (float)(worksheet.Cells[x, y + 2] as Excel.Range).Value;
                            }
                            if ((worksheet.Cells[x, y + 8] as Excel.Range).Value != null)
                            {
                                item_dmgResistance[y] = (float)(worksheet.Cells[x, y + 8] as Excel.Range).Value;
                            }

                        }
                    }
                }
                
            }
        }
    }
}

IronXL实现代码 #

主加载方法 #

public static void LoadExcelDataIronXL()
{
    // 默认IronXL导入浮点数时用逗号作为小数分隔符，比如12,5而不是12.5
    // 代码里其他地方都要求用12.5的格式
    // 之前把武器数据输出到文件时是逗号格式，后续复制到其他程序时因为格式不对报错了
    System.Globalization.CultureInfo customCulture = (System.Globalization.CultureInfo)System.Threading.Thread.CurrentThread.CurrentCulture.Clone();
    customCulture.NumberFormat.NumberDecimalSeparator = ".";
    System.Threading.Thread.CurrentThread.CurrentCulture = customCulture;


    WorkBook wb = WorkBook.Load("D:/Outward - r2modman/Mods/OutwardDe/profiles/Outward/BepInEx/plugins/Outward_Mod_Weapons_ValuesToImport.xlsx");

    void LoadExcel_Weapons()
    {
        foreach (var ws in wb.WorkSheets)
        {
            if (ws.Name == "Swords_1h" || ws.Name == "Swords_2h"
            //     || ws.Name == "Axes_1h"    || ws.Name == "Axes_2h"
            //     || ws.Name == "Maces_1h"   || ws.Name == "Maces_2h"
            //     || ws.Name == "Halberds"   || ws.Name == "Staves"
            //     || ws.Name == "Spears"     || ws.Name == "Gauntlets"
            //     || ws.Name == "Bows"       || ws.Name == "Shields"
            //     || ws.Name == "Chakrams"   || ws.Name == "Daggers"     || ws.Name == "Pistols"
               )
            {
                foreach (var item in ws.Columns[1])
                {
                    if (item.IsEmpty) { break; }
                    else if (item.RowIndex == 0) { continue; }
                    else
                    {
                        dict_Weapons.Add(item.Value.ToString(), new SL_Weapon(wb, ws.Name, item));
                    }
                }
            }
        }
    }
    LoadExcel_Weapons();
}

（注：原代码存在笔误，已修正多余的item.Value.ToString());语句）

SL_Weapon构造函数 #

public SL_Weapon(WorkBook wb, string worksheetName, Cell cell)
{
    WorkSheet ws = wb.GetWorkSheet(worksheetName);
    RangeRow row = ws.Rows[cell.RowIndex];
    StatsHolder = new SL_WeaponStats
    {
        Damage_Bonus = new float[9],
        Damage_Resistance = new float[9],
    };
    var SH = ((SL_WeaponStats)StatsHolder);
    SH.BaseDamage = new List<SL_Damage>();
    Effects = new List<OE_Effect>();

    foreach (var heading in ws.Rows[0])
    {
        var headingColumn = heading.ColumnIndex;
        var headingName = heading.ToString();

        if (headingName == "Name") { Name = row.Columns[headingColumn].ToString(); }
        if (headingName == "ID") { Target_ItemID = New_ItemID = row.Columns[headingColumn].IntValue; }

        if (headingName == "DMG Physical") { SH.BaseDamage.Add(new SL_Damage { Damage = row.Columns[headingColumn].FloatValue, Type = "Physical" }); }
        if (headingName == "DMG 2") { SH.BaseDamage.Add(new SL_Damage { Damage = row.Columns[headingColumn].FloatValue, Type = row.Columns[headingColumn + 1].ToString() }); }
        if (headingName == "DMG 3") { SH.BaseDamage.Add(new SL_Damage { Damage = row.Columns[headingColumn].FloatValue, Type = row.Columns[headingColumn + 1].ToString() }); }
        
        if (headingName == "MaxDurability")     { SH.MaxDurability = row.Columns[headingColumn].IntValue; }
        if (headingName == "RawWeight")         { SH.RawWeight = row.Columns[headingColumn].FloatValue; }
        if (headingName == "BaseValue")         { SH.BaseValue = row.Columns[headingColumn].IntValue; }

        if (headingName == "StamCost")          { SH.StamCost = row.Columns[headingColumn].FloatValue; }
        if (headingName == "AttackSpeed")       { SH.AttackSpeed = row.Columns[headingColumn].FloatValue; }
        if (headingName == "Impact")            { SH.Impact = row.Columns[headingColumn].FloatValue; }
        

        if (headingName == "Effect 1") { Effects.Add(new OE_Effect { StatusEffect = row.Columns[headingColumn].ToString(), Buildup = row.Columns[headingColumn + 1].IntValue }); }
        if (headingName == "Effect 2") { Effects.Add(new OE_Effect { StatusEffect = row.Columns[headingColumn].ToString(), Buildup = row.Columns[headingColumn + 1].IntValue }); }
        if (headingName == "Effect 3") { Effects.Add(new OE_Effect { StatusEffect = row.Columns[headingColumn].ToString(), Buildup = row.Columns[headingColumn + 1].IntValue }); }

    }

    var ws_DmgBonusOrRes = wb.GetWorkSheet("Damage_BonusOrRes");
    var item_dmgBonus = ((SL_EquipmentStats)StatsHolder).Damage_Bonus;
    var item_dmgResistance = ((SL_EquipmentStats)StatsHolder).Damage_Resistance;

    foreach (var cell1 in ws_DmgBonusOrRes.Columns[0])
    {
        if (cell1.IsEmpty) { break; }
        else if (cell1.RowIndex == 0) { continue; }
        else if (cell1.Value.ToString() == cell.ToString())
        {
            for (int i = 0; i < 6; i++)
            {
                item_dmgBonus[i] = ws_DmgBonusOrRes.Rows[cell1.RowIndex].Columns[i + 1].FloatValue;
                item_dmgResistance[i] = ws_DmgBonusOrRes.Rows[cell1.RowIndex].Columns[i + 7].FloatValue;
            }
        }
    }
}

优化建议 #

1. 批量读取单元格数据，避免逐个访问 #

Office Interop每次单独访问Range对象都会和Excel进程交互，开销极大。可以一次性把整个工作表的已用区域数据读取到二维数组里，再在内存中处理：

// 在主方法中读取整个工作表的已用区域数据
var usedRange = ws.UsedRange;
object[,] data = usedRange.Value as object[,];
// 之后通过数组索引访问数据，比如data[rowIndex, columnIndex]

这是Office Interop性能提升的核心优化点，能大幅减少跨进程调用的次数。

2. 预加载Damage_BonusOrRes工作表数据 #

你的SL_Weapon构造函数中，每次循环列都会遍历所有工作表查找Damage_BonusOrRes，还会逐行匹配武器名称。可以提前把这个工作表的数据读取到字典中，比如Dictionary<string, (float[] bonus, float[] resistance)>，后续直接通过武器名称查询，无需反复遍历工作表。

3. 关闭Excel界面交互 #

Office Interop默认会启动Excel进程并显示界面，关闭界面相关设置能减少资源消耗：

xlApp.Visible = false;
xlApp.ScreenUpdating = false;
xlApp.DisplayAlerts = false;

批量处理时关闭这些设置能显著提速，退出前可根据需要恢复。

4. 优化COM对象释放逻辑 #

频繁调用Marshal.ReleaseComObject会增加额外开销，且容易引发内存泄漏问题。可以在所有COM操作完成后统一释放对象，或使用包装类简化释放流程，避免逐个释放工作表等对象。

关于IronXL的性能优势 #

IronXL是直接解析Excel文件格式（无需依赖Excel进程），属于进程内操作；而Office Interop通过COM调用外部Excel进程，属于跨进程操作，本身就有更高的通信开销。因此IronXL天生在性能上有优势，但通过上述优化，Office Interop的性能能大幅接近IronXL，满足你的业务需求。

内容的提问来源于stack exchange，提问作者Marcin Pagórek