c# - 如何优化 C# 中的 Excel 表读取？

Question

我正在开发一个将解析大约 5500x9 单元格范围的应用程序，我确实设法让基础知识以某种方式工作，但我在这方面很陌生，解决方案非常基础，甚至需要很多时间得到 100 行，更不用说 5.5k，现在我被困在这里，因为到目前为止我检查过的任何教程都没有真正的帮助，或者与我当前的代码相比没有提供任何更好的性能。

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(@"C:\...\report.xlsx", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        Excel.Range range = xlWorkSheet.UsedRange;
        int rows = range.Rows.Count;

        for (int i = 2; i <= 15; i++)
        {
            Devices.Add(new DeviceInfo((string)(range.Cells[i, 1] as Excel.Range).Value2, (string)(range.Cells[i, 2] as Excel.Range).Value2, (string)(range.Cells[i, 3] as Excel.Range).Value2, (string)(range.Cells[i, 4] as Excel.Range).Value2, (string)(range.Cells[i, 5] as Excel.Range).Value2, (string)(range.Cells[i, 6] as Excel.Range).Value2, (string)(range.Cells[i, 7] as Excel.Range).Value2, (string)(range.Cells[i, 8] as Excel.Range).Value2, (string)(range.Cells[i, 9] as Excel.Range).Value2, (string)(range.Cells[i, 10] as Excel.Range).Value2, Convert.ToDateTime((range.Cells[i, 11] as Excel.Range).Value2), Convert.ToDateTime((range.Cells[i, 12] as Excel.Range).Value2)));
        }

        xlWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        xlApp.Quit();

唯一想到的是将整行作为单个范围读取，然后在构造函数中处理该数据。但是，我认为这也不实用，因为获取所有数据仍然需要很多时间。

DeviceInfo 类只是与 Excel 工作表中的列匹配的属性列表。

Answer 1

我最近也有类似的需求，最终使用了 EPPlus 4.1.0

这是一个优秀的库，已经存在了一段时间，文档齐全，并且得到了积极维护。

您可以安装 Nuget package使用包管理器控制台。

他们的 Samples Solution用代码涵盖所有用例，您可以从字面上复制粘贴并只需修改一些东西，您就可以开始了。

是的，它非常快!**

这是您的用例的示例代码。摘自 Codeplex 网站上的示例项目。

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 * 
 * All rights reserved.
 * 
 * EPPlus is an Open Source project provided under the 
 * GNU General Public License (GPL) as published by the 
 * Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 * 
 * EPPlus provides server-side generation of Excel 2007 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 *
 * 
 * The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 * 
 * The code for this project may be used and redistributed by any means PROVIDING it is 
 * not sold for profit without the author's written consent, and providing that this notice 
 * and the author's name and all copyright notices remain intact.
 * 
 * All code and executables are provided "as is" with no warranty either express or implied. 
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 *
 * Code change notes:
 * 
 * Author                           Change                      Date
 *******************************************************************************
 * Jan Källman      Added       10-SEP-2009
 *******************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using OfficeOpenXml;

namespace EPPlusSamples
{
    /// <summary>
    /// Simply opens an existing file and reads some values and properties
    /// </summary>
    class Sample2
    {
        public static void RunSample2(string FilePath)
        {
            Console.WriteLine("Reading column 2 of {0}", FilePath);
            Console.WriteLine();

            FileInfo existingFile = new FileInfo(FilePath);
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                // get the first worksheet in the workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                int col = 2; //The item description
                // output the data in column 2
                for (int row = 2; row < 5; row++)
                    Console.WriteLine("\tCell({0},{1}).Value={2}", row, col, worksheet.Cells[row, col].Value);

                // output the formula in row 5
                Console.WriteLine("\tCell({0},{1}).Formula={2}", 3, 5, worksheet.Cells[3, 5].Formula);                
                Console.WriteLine("\tCell({0},{1}).FormulaR1C1={2}", 3, 5, worksheet.Cells[3, 5].FormulaR1C1);

                // output the formula in row 5
                Console.WriteLine("\tCell({0},{1}).Formula={2}", 5, 3, worksheet.Cells[5, 3].Formula);
                Console.WriteLine("\tCell({0},{1}).FormulaR1C1={2}", 5, 3, worksheet.Cells[5, 3].FormulaR1C1);

            } // the using statement automatically calls Dispose() which closes the package.

            Console.WriteLine();
            Console.WriteLine("Sample 2 complete");
            Console.WriteLine();
        }
    }
}

( ** = 如果您发现您的用例的性能 Not Acceptable ，那么您可以看看这个修复了一些性能问题的 EPPlus 分支。

https://github.com/RadoslavGatev/EPPlus-Performance

请注意，这些性能问题是针对非常大的 excel 数据集的——我们讨论的是 100 和 1000 个表格中的 50,000 多个单元格——大多数用例不会遇到它。)