0

So I have kind of a general question here regarding Interop operations on Microsoft Excel Sheets. To give you some background, I have been tasked to write a tool in C# to read and manipulate large Excel sheets.

Now, I have come across the question whether it would be better to read an Excel file into an array all at once or cell by cell in terms of performance.

The latter meaning looping through the whole sheet and creating thousands of access requests, but only gathering the information really needed. Also, Interop seems to produce random COMExceptions pretty frequently, slowing up the whole process.

On the other hand though, reading the whole sheet at once, while not every piece of information is needed, creates a lot of data overhead that can get heavy on storage and runtime performance. The advantage being the need for only a single Interop request, avoiding most Interop COMExceptions.

So which method would you prefer?

Fabian Berndt
  • 9
  • 1
  • Its really opinion based, which isnt an SO thing. – BugFinder Jun 13 '19 at 11:31
  • https://stackoverflow.com/questions/356371/excel-interop-efficiency-and-performance - but if you have a real problem, the best way could be to export the excel sheet into a CSV and read it from there. – gbjbaanb Jun 13 '19 at 11:31

1 Answers1

3

Reading the whole sheet at once should be preferable because if you need multiple cell information then you will have to fetch the cell information multiple times. Getting the whole data into an array will give you better performance overall.

Gourav
  • 31
  • 4