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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
namespace TriedExcel.Reader { using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Excel = Microsoft.Office.Interop.Excel; class AsyncReader { public string FilePath { get; set; } public AsyncReader(string filePath) { FilePath = filePath; } public async Task MainAsync() { var excel = new Excel.Application { Visible = true, EnableAnimations = false }; var wkb = ExcelFeatures.Open(excel, this.FilePath); var calculation = await CalculateAllWorksheetsAsync(wkb); Information.PrintInformation(calculation); excel.EnableAnimations = true; wkb.Close(true); excel.Quit(); ExcelFeatures.CloseExcelExe(excel); } public async Task<List<Information>> CalculateAllWorksheetsAsync(Excel.Workbook wkb) { var tasks = wkb.Worksheets.Cast<Excel.Worksheet>().Select(CalculateSingleWorksheetAsync); var results = await Task.WhenAll(tasks); return results.ToList(); } public async Task<Information> CalculateSingleWorksheetAsync(Excel.Worksheet wks) { int lastRow = ExcelFeatures.LastRowPerColumn(1, wks); int result = await Task.Run(() => { int resultFromCalculation = 0; int resultTryParse; for (int i = 1; i < lastRow; i++) { if (Int32.TryParse(wks.Cells[i, 1].Text, out resultTryParse)) { resultFromCalculation += resultTryParse; } } return resultFromCalculation; }); Information infoToReturn = new Information(wks.Name, result, lastRow); Console.WriteLine(infoToReturn.ToString()); return infoToReturn; } } } |
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):
|
1 2 3 4 |
public static void CloseExcelExe(Excel.Application excel) { Marshal.ReleaseComObject(excel); } |
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:
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! 🙂