c# - EPPlus 将包含 200 多列数据表的 200 万行保存到多个 excel 文件

Question

我有使用 EPPlus 将 SQL 表中的所有记录保存到 excel 工作表的功能。如果我导出少量数据，一切正常，但如果有 200 多列和 500 000 多行，我会遇到 OutOfMemory 异常。

我想修改我的代码，使每个文件能够保存 50 000 条记录。

这是我的适用于小数据的代码:

private Task SaveAsync(string tableName)
{

    return Task.Run(() =>
    {
        try
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand(string.Format(DataQuery, tableName), conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 360;
                    conn.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        var fileName = string.Format(TargetFile, tableName);
                        if (File.Exists(fileName))
                        {
                            File.Delete(fileName);
                        }

                        sdr.Read();
                        var numberOfRecordsInTable = sdr.GetInt32(0);

                        sdr.NextResult();

                        using (ExcelPackage pck = new ExcelPackage(new FileInfo(fileName)))
                        {
                            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Results");

                            int count = sdr.FieldCount;
                            int col = 1, row = 1;

                            for (int i = 0; i < count; i++)
                            {
                                ws.SetValue(row, col++, sdr.GetName(i));
                            }
                            row++;
                            col = 1;
                            while (sdr.Read())
                            {
                                for (int i = 0; i < count; i++)
                                {
                                    var val = sdr.GetValue(i);
                                    ws.SetValue(row, col++, val);
                                }
                                row++;
                                col = 1;
                            }
                            //autosize
                            ws.Cells[ws.Dimension.Address].AutoFitColumns();
                            //autofiltr
                            ws.Cells[1, 1, 1, count].AutoFilter = true;
                        }
                    }
                    conn.Close();
                }
            }
        }
        catch (Exception e)
        {
            Debug.WriteLine("Error at: " + Thread.CurrentThread.ManagedThreadId);
            Debug.WriteLine(e);
        }
    });
}

以及我修改后的代码，将每个文件的记录拆分为 50 000:

private Task SaveAsync2(string tableName)
{
    return Task.Run(() =>
    {
        try
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand(string.Format(DataQuery, tableName), conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 360;
                    conn.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {

                        var fileName = string.Format(TargetFile, tableName,"");
                        if (File.Exists(fileName))
                        {
                            File.Delete(fileName);
                        }

                        sdr.Read();
                        var max = sdr.GetInt32(0);
                        int filesCount = 1;
                        if (max > 50000)
                        {
                            fileName = string.Format(TargetFile, tableName, filesCount);
                        }

                        sdr.NextResult();

                        ExcelPackage pck = new ExcelPackage(new FileInfo(fileName));
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("RESULTS");

                        int count = sdr.FieldCount;

                        int col = 1, row = 1;

                        for (int i = 0; i < count; i++)
                        {
                            ws.SetValue(row, col++, sdr.GetName(i));
                        }
                        row++;
                        col = 1;
                        while (sdr.Read())
                        {
                            for (int i = 0; i < count; i++)
                            {
                                var val = sdr.GetValue(i);
                                ws.SetValue(row, col++, val);
                            }
                            row++;
                            col = 1;

                            if (row > 50000)
                            {
                                pck.Save();
                                filesCount++;
                                fileName = string.Format(TargetFile, tableName, filesCount);

                                pck = new ExcelPackage(new FileInfo(fileName));
                                ws = pck.Workbook.Worksheets.Add("RESULTS");

                                count = sdr.FieldCount;

                                col = 1;
                                row = 1;

                                for (int i = 0; i < count; i++)
                                {
                                    ws.SetValue(row, col++, sdr.GetName(i));
                                }
                                row++;
                                col = 1;
                            }
                        }

                        //autosize
                        ws.Cells[ws.Dimension.Address].AutoFitColumns();
                        //autofiltr
                        ws.Cells[1, 1, 1, count].AutoFilter = true;

                        pck.Save();
                    }
                }
                conn.Close();

            }
        }
        catch (Exception e)
        {
            Debug.WriteLine("Error at: " + Thread.CurrentThread.ManagedThreadId);
            Debug.WriteLine(e);
        }
    });
}

基本上这工作正常，但在我的代码的第一个版本中，我在 using 语句中使用了所有内容，而在第二个版本中，我调用了相同的代码两次。

1. 如何修复我的代码以删除重复代码并将所有内容放入 using 中。
2. 我可以添加下一组(50 000 条记录)作为新工作表而不是创建新文件吗？
3. 将数据保存到文件时，EPPlus 限制是多少？ 行 x 列？我发现 EPPlus 应该处理超过百万行的信息，但没有我拥有的那么多列。我认为我可以用单列导出百万行，但对于 200 多列来说，50 000 行是限制。我想知道是否有数字(行 x 列)会限制我的导出工作正常。我希望该导出功能是通用的，因此当我传递包含 50 列的数据表时，它将导出例如每个文件 100 000 行，对于 2 列，它将每个文件导出 50 万行。

Answer 1

我过去曾遇到过 EPPlus 的内存限制，最终生成了多个 .xlsx 文件作为解决方法(类似于您的方法)。另一种选择是将您的编译器设置更改为仅针对 64 位(如果您可以在不支持 32 位平台的情况下通过)。我记得，EPPlus 是为“任何 CPU”编译的，因此如果您可以将代码更改为目标“x64”，这可能会放宽内存限制并允许您生成单个 .xlsx 文件。以 x64 为目标在我的案例中可能会奏效，但我直到事后才想到它，所以我一直没有机会进行测试。

更新:我刚刚使用 EPPlus 3.1.3 进行了快速测试，创建了 500,000 行，每行 70 列。在生成内存不足异常之前，我的 32 位应用程序能够生成大约 119,000 行。将目标切换到 x64 后，它成功生成了所有 500,000 行，尽管它花了很长时间。创建实际的工作表只花了几分钟，但 ExcelPackage.SaveAs() 花了将近 20 分钟。 RAM 消耗也相当高(大约 11GB 的 RAM)。生成的 .xlsx 为 220MB，32 位 Excel 无法打开(内存不足)。 底线:针对 x64 可能不是一个可行的解决方案；您最好将输出拆分为多个 .xlsx 文件。

我很想删除这个答案，因为它已被证明是一个死胡同，但决定保留它以防它帮助其他人在未来避免这条路。