Read Excel Worksheets Asynchronously

Question

I am trying to read all the Excel worksheets of a given workbook asyncronously, but it is somehow not happenning. The idea is to sum the first 123 cells in every Excel sheet and to print it at the end. The code compiles and runs without errors, but it does not read all the worksheets, it simply skips that part, because of the async.

namespace SyncAndAsync
{
    using System;
    using System.Collections.Generic;
    using System.Threading.Tasks;
    using Excel = Microsoft.Office.Interop.Excel;

    class Startup
    {
        static void Main()
        {
            string filePath = @"C:\Users\Desktop\Sample.xlsx";
            Excel.Application excel = new Excel.Application();
            excel.Visible = true;
            excel.EnableAnimations = false;
            Excel.Workbook wkb = Open(excel, filePath);

            var calculation = CalculateAllWorksheetsAsync(wkb);

            //foreach (var item in calculation)
            //{
            //    Console.WriteLine(item);
            //}

            excel.EnableAnimations = true;
            wkb.Close(true);
            excel.Quit();
        }

        static async Task<List<Information>> CalculateAllWorksheetsAsync(Excel.Workbook wkb)
        {

            List<Task<Information>> tasks = new List<Task<Information>>();

            foreach (Excel.Worksheet wks in wkb.Worksheets)
            {
                Task.Run(() => CalculateSingleWorksheetAsync(wks));
            }

            var results = await Task.WhenAll(tasks);
            return new List<Information>(results);
        }

        static async Task<Information> CalculateSingleWorksheetAsync(Excel.Worksheet wks)
        {
            Information output = new Information();
            int result = 0;
            await Task.Run(() =>
            {
                for (int i = 1; i <= 123; i++)
                {
                    result += (int)(wks.Cells[i, 1].Value);
                }
            });

            output.WorksheetName = wks.Name;
            output.WorksheetSum = result;
            Console.WriteLine($"{wks.Name} - {result}");
            return output;
        }

        static Excel.Workbook Open(Excel.Application excelInstance,
                                            string fileName, bool readOnly = false,
                                            bool editable = true, bool updateLinks = true)
        {
            Excel.Workbook book = excelInstance.Workbooks.Open(
                fileName, updateLinks, readOnly,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            return book;
        }
    }
}

The Information class is added to use the Task, probably it could be skipped:

namespace SyncAndAsync
{
    class Information
    {
        public string WorksheetName { get; set; } = "";
        public int WorksheetSum { get; set; } = 0;
    }
}

Dependencies:

• The reference Microsoft.Office.Interop.Excel should be added;
• Change the string filePath = @"C:\Users\Desktop\Sample.xlsx"; to something relevant and make sure in the Excel file there are some numbers in the first column on every sheet, to get results;

The question - How to make the asynchronous run and display the sums of all worksheets?

Answer 1

There are a couple of issues. First, the list of tasks never gets populated, so the WhenAll call doesn't do anything. Secondly, The main function never awaits the result of CalculateAllWorksheetsAsync. I've made some modifications to your code, see below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTest {
    public class Information {
        public Information(string name, int sum) {
            Name = name;
            Sum = sum;
        }

        public string Name { get; set; }
        public int Sum { get; set; }
    }

    class Program {
        static void Main(){
            MainAsync().GetAwaiter().GetResult();
        }

        private static async Task MainAsync() {
            const string filePath = @"D:\file.xlsx";
            var excel = new Excel.Application {Visible = true, EnableAnimations = false};
            var wkb = Open(excel, filePath);

            var calculation = await CalculateAllWorksheetsAsync(wkb);

            foreach (var item in calculation) {
                Console.WriteLine($"{item.Name} - {item.Sum}");
            }

            excel.EnableAnimations = true;
            wkb.Close(true);
            excel.Quit();
            Console.Read();
        }

        private static 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();
        }

        private static async Task<Information> CalculateSingleWorksheetAsync(Excel.Worksheet wks) {
            int result = await Task.Run(() =>
                Enumerable.Range(1, 123).Sum(index => (int) (wks.Cells[index, 1].Value2)));

            Console.WriteLine($"{wks.Name} - {result}");
            return new Information(wks.Name, result);
        }

        private static Excel.Workbook Open(Excel.Application excelInstance,
            string fileName, bool readOnly = false,
            bool editable = true, bool updateLinks = true) {
            return excelInstance.Workbooks.Open(
                fileName, updateLinks, readOnly,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
        }
    }
}

Sample output: