异步阅读Excel工作表 [英] Read Excel Worksheets Asynchronously

问题描述

我正在尝试异步读取给定工作簿的所有Excel工作表，但是这并没有发生.想法是将每个Excel工作表中的前123个单元格相加，并在最后打印.该代码可以编译并运行，没有错误，但是由于 async ，它不会读取所有工作表，只是跳过了这一部分.

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;
        }
    }
}

添加了 Information 类以使用Task，可能可以跳过它:

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;
    }
}

依赖项:

• 应添加参考 Microsoft.Office.Interop.Excel ；
• 将 string文件更改为Path = @"C:\ Users \ Desktop \ Sample.xlsx"; 并确保在Excel文件中每张纸的第一列中有一些数字，以获得结果；

• 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?

推荐答案

有两个问题.首先，任务列表永远不会填充，因此 WhenAll 调用不会执行任何操作.其次，main函数从不等待 CalculateAllWorksheetsAsync 的结果.我已经对您的代码进行了一些修改，请参见下文:

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);
        }
    }
}

示例输出:

这篇关于异步阅读Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！