I wrote a programme that imports data from an .xlsx file and then creates many XML files based on the imported data. Now, at first I tried doing that using IronXL but then I realized that it's not a free option if I wanted to publish my programme, so I tried doing the same thing using Office Interop. So now I've got two codes, both do the same thing and they do it right, but IronXL method is way faster (~8 seconds vs ~40 seconds checked for two worksheets, but overall I will have around 10-15 worksheets so the time will obviously get longer). Can I optimize my code in any way or is it just that IronXL method will be faster and there's nothing I can do about it? Here's my code for Office Interop:
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);
}
And here's the constructor of SL_Weapon for Interop:
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;
}
}
}
}
}
}
}
}
Here's the code for 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();
}
And the constructor of SL_weapon for 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;
}
}
}
}
CodePudding user response：
IronXL uses NPOI internally. If you download the nuget package and decompile the dll, you'll see that it has embedded code from: NPOI, BouncyCastle, Newtonsoft, ICSharpCode, ImageSharp, and probably others. It seems to me that they have taken a bunch of open-source code and packaged it up to make it look standalone so that they can charge money for it. I don't have any issue with charging money for your own work, but when you're trying to sell someone else's work that feels a bit shady.
Anyway... to answer your question about Excel performance. The fastest library for reading Excel data (that I'm aware of) is Sylvan.Data.Excel. I've benchmarked it against several other popular .NET Excel libraries, and it is the fastest by quite a wide margin. I should also point out that I'm the author of this library, so will probably want to verify my performance claims yourself.
My library exposes Excel data as a DbDataReader, the standard ADO.NET abstract class for reading data records. You might also check out Sylvan.Data, which provides a data binder that can easily bind a DbDataReader to .NET objects.
With Sylvan.Data.Excel and Sylvan.Data your code might be as simple as:
using var reader = ExcelDataReader.Create("mydata.xlsx");
List<Weapon> weapons = reader.GetRecords<Weapon>().ToList();
It will likely be a bit more complex than that, because it looks like your binding code has some complexity to it, but you shouldn't have any trouble adapting that to my libraries.
CodePudding user response：
Marcin Pagórek is correct that IronXL is faster than Office Interop, but a data reader that just caches cell values from the XML will be faster still.
However IronXL is a multi-purpose tool that provides the entire ecosystem of Excel editing and conversion tools, that can parse and edit per your requirements (not a speed-orientation data reader).
We (Disclaimer: I work for Iron Software) like the idea of adding a fast reading-only option to increase the speed of IronXL even more, so it has been logged as a feature request.
IronXL is free for development, though commercial use does require a license starting at a low $499. Licensing information is shown at https://ironsoftware.com/csharp/excel/licensing/