为什么搜索多张纸这么慢? [英] Why is Searching Multiple Sheets So Slow?

问题描述

我开始编写一个小型包装器类来照顾我的excel操作:

I started writing a small wrapper class to take care of my excel operations:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection; 

namespace CSVReader
{
    class ExcelManager
    {
        // Holds instance of application.
        public Excel.Application application;

        /**
         * Class Constructor.
         */
        public ExcelManager()
        {
            // Create a new application instance.
            application = new Excel.Application();
        }

        /**
         * Helper to open workbooks.
         */
        public void Open(string filename) {
           application.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                Type.Missing, Type.Missing);
        }

        /**
         */
        public Excel.Range Find(string search)
        {
            Excel.Workbooks books = application.Workbooks;

            Excel.Range currentFind = null;
            Excel.Range firstFind = null;

            // Search all workbooks.
            foreach(Excel.Workbook book in books)
            {
                // Get first sheet.
                Excel.Worksheet sheet = book.Worksheets.get_Item(1);

                // Get all data for sheet.
                Excel.Range firstCell = sheet.Range["A1", Type.Missing];
                Excel.Range lastCell  = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                Excel.Range sheetData = sheet.Range[firstCell, lastCell];

                currentFind = sheetData.Find(search, Type.Missing, 
                    Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                    Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                    Type.Missing, Type.Missing);

                while (currentFind != null)
                {
                    // Keep track of the first range you find. 
                    if (firstFind == null)
                    {
                        firstFind = currentFind;
                    }

                    // If you didn't move to a new range, you are done.
                    else if (currentFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)
                          == firstFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing))
                    {
                        break;
                    }

                    currentFind = sheetData.FindNext(currentFind);
                }
            }

            return currentFind;
        }
    }
}

我实例化该类，并告诉它加载两个工作簿并搜索一个字符串:

I instantiate the class and tell it to load two workbooks and search for a string:

ExcelManager manager = new ExcelManager(); 

manager.Open(@"c:\test\test1.xls");
manager.Open(@"c:\test\test2.XLS");

Excel.Range result = manager.Find('test cell');
if (result != null)
{
    // Do something funky.
}
else
{
    // Use a log file instead.
    Console.WriteLine("item was not found found in the current sheet.");
}

问题是，当我运行此代码时，即使使用小型工作簿，它的速度也非常慢.我的C#知识很少，因此我整天都在关注教程.这是搜索多个工作表的好方法吗?使用OLE会更快吗?该应用程序的目的仅仅是运行检查以汇总未在我打开的工作簿的任何工作表中显示的值.

The problem is when i run this code it is incredibly slow, even with small sized workbooks. My C# knowledge is minimal so I've been following tutorials the whole day. Is this a good way to go about searching multiple sheets? would using OLE be any faster? The purpose of this app is simply to run a check to summarize values that don't appear in any of the sheets in my open workbooks.

推荐答案

我的第一个回答是interop使用您的Excel安装来收集信息.Excel安装中的所有初始化逻辑都将运行，这将使代码的加载时间非常缓慢.

My first response would be that interop uses your Excel installation to gather the information. Any initialization logic from the Excel installation will be run and will make for a very slow loading time of the code.

如果是这种情况，您可以做些什么来测试:对哪个函数调用进行基准测试会降低搜索速度.查找功能或ExcelManager类的加载/打开功能.

What you can do to test if this is the case: Benchmark which of the function calls make the searching slow. The find function or the loading of your ExcelManager class / the open function.

如果事实证明速度损失不是由find函数引起的，则可以考虑使用一个库来解析文件本身，而不是使用interop.

If it turns out the speed loss is not caused by the find function you might consider a library that parses the file itself instead of using interop.

这篇关于为什么搜索多张纸这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！