Most efficient way to extract data from multiple .xlsx files using Excel Interop in c#?

Question

I have just started getting into Excel Interop and was wondering how I could improve the performance of my application by implementing either multithreading or multitasking.

My code looks something like this atm:

foreach(var path in arrayOfExcelFilePaths){
    excelApp.Workbooks.Open(path);
}
foreach(var wb in excelApp.Workbooks){
    //Read stuff and maybe write stuff
}

Thanks in advance for anybody looking into this.

I don't know if multithreading will imprve the read times if you're using a single instance of Excel. You could try creating a separate excel application for each workbook. Also, I'd try making sure my read operations are optimised before trying multithreading as that is usually the cause of poor exel interop IO. — ekke
– ekke, Commented Apr 18, 2020 at 15:49
Thanks for the response. What do you mean exactly by optimizing read operations? — crims0n
– crims0n, Commented Apr 18, 2020 at 16:04
What I mean is that your read operation should read all the data in one go into a an array. This is usually quite fast even for very large datasets. The slowness of interop is mainly due the marshalling between managed/unmanaged code and the IPC, so reducing the number of marshalling operations usually speeds things up considerably. If you like I can answer with more detail but I'd ask you to update your answer with some code of the read/write. — ekke
– ekke, Commented Apr 18, 2020 at 20:53

user13348192 · Accepted Answer · 2020-04-18 16:08:20Z

1

Using Interop is a bad idea in general. The execution of your code won’t differ much from its equivalent written in a VBA macro, because Interop simply runs the Excel process in the background and replicates its functionality 1-to-1.

Try OfficeOpenXML or any other library that allows you manipulate the content of excel sheets without running MS Excel as Interop does.

answered Apr 18, 2020 at 16:08

user13348192

694 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jscarle · Accepted Answer · 2020-04-18 18:34:37Z

0

I highly recommend you use the EPPlus 5 library, which is a newer version of the EPPlus library. Its fast and reliable, I've been using it for years without any issues.

answered Apr 18, 2020 at 18:34

jscarle

1,3201 gold badge14 silver badges21 bronze badges

Collectives™ on Stack Overflow

Most efficient way to extract data from multiple .xlsx files using Excel Interop in c#?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related