C# – Reading from Excel Spreadsheets with Asynchronous Programming (Async)

Reading from Excel in C# is actally not at tough as many VBA developers would assume. The simple reading is actually quite straightforward – there is only small little trick, requiring the adding of the Microsoft.Office.Interop.Excel reference to the Visual Studio project. After this, it is like using a bit of VBA in the luxury of Visual Studio.

What is done here?

  • An Excel file with 33 worksheets is processed. In every worksheet, there is a column A with up to 1234 random numbers. The task is to provide a report with the sum of the numbers per worksheet;
  • Two methods are compared – reading from Excel with Async and Reading from Excel Sync. The reading from Excel with Async reads all the worksheets in the same time and thus provides better results. The Sync method reads one by one. While reading the worksheets, an information is displayed. Thus, it is visible that the async is not reading one by one.
  • At the end the results are displayed at the console.

So, let’s start the analysis with the most important class of the solution – AsyncReader.cs:

It has 2 methods, that actually are doing the whole work – CalculateSingleWorksheetAsync and CalculateAllWorksheetsAsync. The latter takes the workbook and loops through the worksheets, writing every worksheet to a task with the help of multithreading and LINQ – var tasks = wkb.Worksheets.Cast<Excel.Worksheet>().Select(CalculateSingleWorksheetAsync);

On the next line, the await keyword asks the program to wait, until all worksheets are processed. In the CalculateSingleWorksheetsAsync, the worksheets are passed as a coming argument. The await Task.Run(()=>) returns the sum of the values in the first column, if these are integers.

At the end of the code, there is a printing of the information on the console, for which the Override ToString() method of the Information class is used.

The ExcelFeatures.CloseExcelExe(excel); is needed to kill the Excel instance, which stays alive in your PC (thanks to Rubberduckvba for the notice):

The Results

The results of the whole task are actually not as impressive as someone would expect. This is probably because the writing to the console and the openning of the Excel file takes a constant time, which is independent of the sync and async:

Still more than twice processing time difference in 33 worksheets is ok.

The whole code is in GitHub here. The ExcelFeatures.cs is probably going to be enlarged. If you have any ideas for improvement, feel free to submit a pull request.

Cheers! 🙂

About

A VBA Developer.

Tagged with: , , ,