解析大型Excel文件列表失败

本文关键字：列表 失败 文件 Excel 大型 | 更新日期: 2023-09-27 18:10:40

这是c#/VSTO程序。我一直在做一个数据采集项目。范围基本上是"处理各种第三方公司发送的Excel文件"。实际上，这意味着:

  1. 通过搜索方法找到包含我想要的数据的列。
  2. 从工作簿抓取数据
  3. 清理数据，运行一些计算等
  4. 将清理后的数据输出到新工作簿

我编写的程序非常适合中小型数据集，约25个工作簿，合计约1000行相关数据。我从这些工作簿中抓取了7列的数据。但是，我遇到的一个边缘情况是，我偶尔需要运行一个大得多的数据集，大约50个工作簿，总共有大约8000行相关数据(可能还有大约2000行重复数据，我也必须删除)。

我目前正在通过Parallel.ForEach循环放置文件列表，其中我打开new Excel.Application()以处理具有多个ActiveSheet的每个文件。并行处理在较小的数据集上运行得比依次处理每个数据集快得多。但在更大的数据集上，我似乎碰壁了。

我开始得到消息:Microsoft Excel is waiting for another application to complete an OLE action，最终它只是失败。切换回顺序foreach确实允许程序完成，但它只是慢慢地进行—从1-3分钟的并行中型数据集到20分钟以上的顺序大型数据集。如果我把ParallelOptions.MaxDegreeOfParallelism设置为10，它将完成循环，但仍然需要15分钟。如果我把它设为15，它就失败了。如果没有必要，我也真的不喜欢弄乱TPL设置。我也试过插入Thread.Sleep来手动减慢速度，但这只会使失败发生在更远的地方。

我关闭工作簿，退出应用程序，然后ReleaseComObject到Excel对象，GC.Collect和GC.WaitForPendingFinalizers在每个循环结束。

我现在的想法是:

  1. 将列表分成两半并分别运行
  2. 并行打开一些new Excel.Application()，但在Excel实例中按顺序运行文件列表(有点像#1，但使用不同的路径)
  3. 按文件大小分隔列表，并独立/顺序运行一小部分非常大的文件，其余的按
    4. 运行。

希望得到帮助的事情:

  1. 关于确保我的记忆被清除的建议(也许Process.Id在所有的开始和结束中都被扭曲了?)
  2. 关于订购并行进程的建议-我想知道我是否可以先把"大"家伙扔进去，这将使长时间运行的进程更稳定。

我一直在看:http://reedcopsey.com/2010/01/26/parallelism-in-net-part-5-partitioning-of-work/，他说:"有了关于你的工作的先验知识，可能比默认的Partitioner更有意义地分区数据。"但是我很难真正知道什么/如果分区有意义。

非常感谢任何见解!

所以作为一般规则，我对Excel 2010进行测试，因为我们在这里同时使用了2010和2013。我在2013年运行了它，运行时间大约是4分钟，这是我所期望的。在我放弃2010兼容性之前，还有其他想法吗?2010年的机器是64位机，安装64位Office, 2013年的机器是64位机，安装32位Office。这有什么关系吗?

解析大型Excel文件列表失败

几年前我从事excel文件和自动化的工作。然后我遇到了任务管理器中僵尸进程的问题。虽然我们的程序结束了，我认为我退出excel正确，进程没有退出。

这个解决方案不是我喜欢的，但它很有效。我可以这样总结解决方案。

1)不要连续使用两个点，如:

workBook.ActiveSheet.PageSetup

用变量代替。当你完成后，释放并取消它们。

示例:不这样做:

m_currentWorkBook.ActiveSheet.PageSetup.LeftFooter = str.ToString();

遵循此函数中的实践。(此函数将条形码添加到excel页脚)

    private bool SetBarcode(string text)
    {
            Excel._Worksheet sheet;
            sheet = (Excel._Worksheet)m_currentWorkbook.ActiveSheet;
            try
            {
                StringBuilder str = new StringBuilder();
                str.Append(@"&""IDAutomationHC39M,Regular""&22(");
                str.Append(text);
                str.Append(")");
                Excel.PageSetup setup;
                setup = sheet.PageSetup;
                try
                {
                    setup.LeftFooter = str.ToString();
                }
                finally
                {
                    RemoveReference(setup);
                    setup = null;
                }
            }
            finally
            {
                RemoveReference(sheet);
                sheet = null;
            }
            return true;
    }

这是RemoveReference函数(在此函数中输入null不起作用)

    private void RemoveReference(object o)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
        }
        catch
        { }
        finally
        {
            o = null;
        }
    }

如果你在任何地方都遵循这个模式，它保证没有泄漏，没有僵尸进程等。

2)为了创建excel文件，你可以使用excel应用程序，但是从excel中获取数据，我建议使用OleDB。你可以像对待数据库一样对待excel，并通过sql查询、数据表等从excel中获取数据。

示例代码:(代替填充数据集，您可以使用datareader来提高内存性能)

    private List<DataTable> getMovieTables()
    {
        List<DataTable> movieTables = new List<DataTable>();
        var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text'""; ;
        using (var conn = new OleDbConnection(connectionString))
        {
            conn.Open();
            DataRowCollection sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }).Rows;
            foreach (DataRow sheet in sheets)
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM [" + sheet["TABLE_NAME"].ToString() + "] ";
                    var adapter = new OleDbDataAdapter(cmd);
                    var ds = new DataSet();
                    try
                    {
                        adapter.Fill(ds);
                        movieTables.Add(ds.Tables[0]);
                    }
                    catch (Exception ex)
                    {
                        //Debug.WriteLine(ex.ToString());
                        continue;
                    }
                }
            }
        }
        return movieTables;
    }

作为@Mustafa d man提出的替代解决方案，我建议您使用EPPlus的第4版测试版。我在几个项目中使用它，没有出现任何问题。

优点:

    快速
  • 没有内存泄漏(我不能告诉版本<4)
  • 不要求在使用Office的机器上安装Office

缺点:

  • 只能用于。xlsx文件(Excel 2007/2010)

我用以下代码在20个excel文件上测试了它，每个文件大约12.5 MB(每个文件中超过50k条记录)，我认为这足以说明它没有崩溃:)

 Console.Write("Path: ");
 var path = Console.ReadLine();
 var dirInfo = new DirectoryInfo(path);
 while (string.IsNullOrWhiteSpace(path) || !dirInfo.Exists)
 {
     Console.WriteLine("Invalid path");
     Console.Write("Path: ");
     path = Console.ReadLine();
     dirInfo = new DirectoryInfo(path);
 }
 string[] files = null;
 try
 {
     files = Directory.GetFiles(path, "*.xlsx", SearchOption.AllDirectories);
 }
 catch (Exception ex)
 {
     Console.WriteLine(ex.Message);
     Console.ReadLine();
     return;
 }
 Console.WriteLine("{0} files found.", files.Length);
 if (files.Length == 0)
 {
     Console.ReadLine();
     return;
 }
 int succeded = 0;
 int failed = 0;

 Action<string> LoadToDataSet = (filePath) =>
 {
     try
     {
         FileInfo fileInfo = new FileInfo(filePath);
         using (ExcelPackage excel = new ExcelPackage(fileInfo))
         using (DataSet dataSet = new DataSet())
         {
             int workSheetCount = excel.Workbook.Worksheets.Count;
             for (int i = 1; i <= workSheetCount; i++)
             {
                 var worksheet = excel.Workbook.Worksheets[i];
                 var dimension = worksheet.Dimension;
                 if (dimension == null)
                     continue;
                 bool hasData = dimension.End.Row >= 1;
                 if (!hasData)
                     continue;
                 DataTable dataTable = new DataTable();
                 //add columns
                 foreach (var firstRowCell in worksheet.Cells[1, 1, 1, dimension.End.Column])
                 dataTable.Columns.Add(firstRowCell.Start.Address);
                 for (int j = 0; j < dimension.End.Row; j++)
                     dataTable.Rows.Add(worksheet.Cells[j + 1, 1, j + 1, dimension.End.Column].Select(erb => erb.Value).ToArray());
                 dataSet.Tables.Add(dataTable);
             }
             dataSet.Clear();
             dataSet.Tables.Clear();
         }
         Interlocked.Increment(ref succeded);
     }
     catch (Exception)
     {
         Interlocked.Increment(ref failed);
     }
 };
 Stopwatch sw = new Stopwatch();
 sw.Start();
 files.AsParallel().ForAll(LoadToDataSet);
 sw.Stop();
 Console.WriteLine("{0} succeded, {1} failed in {2} seconds", succeded, failed, sw.Elapsed.TotalSeconds);
 Console.ReadLine();
相关文章：