I'm needing to access Excel workbooks from .Net. I know all about the different ways of doing it (I've written them up in a blog post), and I know that using a native .Net component is going to be the fastest. But the question is, which of the components wins? Has anybody benchmarked them? I've been using Syncfusion XlsIO, but that's very slow for some key operations (like deleting rows in a workbook containing thousands of Named ranges).
Can't help you with your original question, but are you aware that you can access Excel files using an OleDbConnection, and therefore treat it as a database? You can then read worksheets into a DataTable, perform all the changes you need to the data in your application, and then save it all back to the file using an OleDbConnection.
Yes but I'm not going to publish them both out of a courtesy to Syncfusion (they ask you not to publish benchmarks), because I'm not an experienced tester so my tests are probably somewhat flawed but mostly because what you actually benchmark makes a huge difference to who wins and by how much.
I took one of their "performance" examples and added the same routine in EPPlus to compare them. XLSIO was around 15% faster with just straightforward inserts, depending on the row/column ratio (I tried a few), memory usage seemed very similar. When I added a routine that, after all the rows were added, deleted every 10th row and then inserted a new row 2 rows up from that - XLSIO was significantly slower in that circumstance.
A generic benchmark is pretty-much useless to you. You need to try them against each other in the specific scenarios you use.
I have been using EPPlus for a few years and the performance has been fine, I don't recall shouting at it.
More worthy of your consideration is the functionality, support (Syncfusion have been good, in my experience), Documentation, access to the source code if that is important, and - importantly - how much sense the API makes to you, the syntax can be quite different. eg. Named Styles
headerStyle.BeginUpdate(); workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(255, 174, 33)); headerStyle.Color = System.Drawing.Color.FromArgb(255, 174, 33); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft] .LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight] .LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop] .LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate();
ExcelNamedStyleXml headerStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HeaderStyle"); headerStyle.Style.Fill.PatternType = ExcelFillStyle.Solid; // <== needed or BackgroundColor throws an exception headerStyle.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 174, 33)); headerStyle.Style.Font.Bold = true; headerStyle.Style.Border.Left.Style = ExcelBorderStyle.Thin; headerStyle.Style.Border.Right.Style = ExcelBorderStyle.Thin; headerStyle.Style.Border.Top.Style = ExcelBorderStyle.Thin; headerStyle.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;