优化从Excel和Office导入数据的速度
优化从Excel和Office导入数据的速度
提问于 2022-09-24 13:58:27
我编写了一个程序，从.xlsx文件中导入数据，然后根据导入的数据创建许多XML文件。现在，一开始我尝试使用IronXL来实现这一点，但是后来我意识到如果我想发布我的程序，这不是一个免费的选择，所以我尝试使用Office做同样的事情。现在我有了两个代码，它们都做同样的事情，而且它们都做得很好，但是IronXL方法要快得多(对于两个工作表，大约8秒和40秒检查，但是总的来说，我将有10-15个工作表，所以时间显然会变长)。我可以以任何方式优化我的代码吗?还是说IronXL方法会更快，而我对它无能为力呢？以下是的代码：
static public void LoadExcelDataInterop()
{
//Normally floats are imported as numbers with a comma, e.g. 12,5 rather than 12.5
//Everywhere else in the code, the correct format is 12.5
//When I printed the weapons' data into a file, the format was with a comma and this caused errors
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);
}下面是Interop的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 }); }
//OPTION 1 for StatsHolder fields - doesn't work, throws an error ('Object of type 'System.Single' cannot be converted to type 'System.Int32')
/*
FieldInfo[] fields = typeof(SL_WeaponStats).GetFields();
foreach (var field in fields)
{
if (headingName == field.Name) { field.SetValue(StatsHolder, (float)workingCell.Value); }
}
*/
//OPTION 2 for StatsHolder fields
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()
{
//Normally IronXL imports floats as numbers with a comma, e.g. 12,5 rather than 12.5
//Everywhere else in the code, the correct format is 12.5
//When I printed the weapons' data into a file, the format was with a comma
//And later when I copied it to the other Programme, I was getting errors because the format should be with a dot
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));item.Value.ToString());
}
}
}
}
}
LoadExcel_Weapons();
}以及SL_weapon用于IronXL的构造函数：
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;
}
}
}
}回答 2
Stack Overflow用户
发布于 2022-09-29 07:03:36
Marcin Pagórek是正确的，IronXL比Office更快，但是仅仅缓存来自IronXL的单元格值的数据读取器将会更快。
然而，IronXL是一个多用途的工具，它提供了整个Excel编辑和转换工具的生态系统，可以根据您的需求进行解析和编辑(而不是面向速度的数据阅读器)。
我们(免责声明:我为铁软件工作)喜欢的想法，增加一个快速阅读选项，以提高IronXL的速度更多，所以它已被记录为一个功能请求。
IronXL的开发是免费的，尽管商业使用确实需要一个许可证，最低售价为499美元。许可信息显示在https://ironsoftware.com/csharp/excel/licensing/上
Stack Overflow用户
发布于 2022-09-24 18:22:30
IronXL在内部使用NPOI。如果下载nuget包并对dll进行反编译，您将看到它有来自NPOI、BouncyCastle、Newtonsoft、ICSharpCode、ImageSharp以及其他可能的嵌入式代码。在我看来，他们采取了一堆开放源代码，并将其打包，使其看起来独立，这样他们就可以为它收费。我对你自己的工作收费没有任何问题，但当你试图出售别人的作品时，感觉有点可疑。
不管怎样..。回答有关Excel性能的问题。读取Excel数据(据我所知)最快的库是Sylvan.Data.Excel。我将其与其他几个流行的.NET Excel库和它以相当大的差距是最快的。进行了比较。我还应该指出，我是这个库的作者，所以可能需要自己来验证我的性能声明。
我的库将Excel数据公开为DbDataReader，这是用于读取数据记录的标准ADO.NET抽象类。您还可以签出Sylvan.Data，它提供了一个数据绑定器，可以轻松地将DbDataReader绑定到.NET对象。
使用Sylvan.Data.Excel和Sylvan.Data，您的代码可能非常简单：
using var reader = ExcelDataReader.Create("mydata.xlsx");
List<Weapon> weapons = reader.GetRecords<Weapon>().ToList();它可能会更复杂一些，因为看起来绑定代码对它有一定的复杂性，但是您应该不会有任何困难来适应我的库。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73837788
复制相关文章
相似问题
腾讯云开发者
