EPPlus节省200万行200+列数据表多个excel文件

我有功能，保存所有记录从SQL表到excel工作表使用EPPlus。如果我导出少量的数据，一切都很好，但有200+列和500000 +行，我得到OutOfMemory异常。

我想修改我的代码在某种程度上能够保存每个文件50000条记录。

下面是我的代码，适用于小数据:

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语句中的所有内容，而在第二个版本中，我调用了相同的代码两次。

我如何修复我的代码，以删除重复的代码，并把里面的一切使用。
我可以添加下一组(50,000条记录)作为新的工作表，而不是创建新的文件?
保存数据到文件时EPPlus限制是什么?rows x columns吗?我发现EPPlus应该处理超过一百万行，但没有我拥有的那么多列。我认为我可以导出百万行单列，但对于200+列对我来说，5万行是限制。我想知道是否有数量(行x列)，这将限制我的导出将正常工作。我希望这个导出函数是通用的，所以当我传递具有50列的datatable时，它将每个文件导出100,000行，对于2列，它将每个文件导出50万行。

我在过去使用EPPlus时遇到了内存限制，最终生成了多个.xlsx文件作为解决方案(类似于您的方法)。另一种选择是将编译器设置更改为仅针对64位(如果您可以不支持32位平台)。我记得，EPPlus是针对"任何CPU"编译的，所以如果您可以将代码更改为针对"x64"，那么可能会放松内存限制，并允许您生成单个.xlsx文件。在我的情况下，瞄准x64可能会起作用，但我直到事后才想到它，所以我从来没有机会进行测试。

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

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

不幸的是，没有简单的方法将这么多数据与Epplus合并到一个文件中。基本上，打开时将整个文件加载到内存中——要么全部加载，要么什么都不加载。理论上，您可以生成XLSX包含的XML文件(它们是重命名的zip文件)并手动插入它，因为它占用的内存更小，但这不是一件小事。

对于你当前的代码，如果你想避免使用using语句，你总是可以手动调用.dispose()。但我理解你想避免重复代码。如果这样做(但是在复制所有对象数据时要注意内存使用):

const int max = 10;
var loop = 0;
using (var sdr = cmd.ExecuteReader())
{
    var fieldcount = sdr.FieldCount;
    var getfi = new Func<int, FileInfo>(i =>
    {
        var fi = new FileInfo(String.Format(@"c:tempMulti_Files{0}.xlsx", i));
        if (fi.Exists) fi.Delete();
        return fi;
    });
    var savefile = new Action<FileInfo, List<Object[]>>((info, rows) =>
    {
        using (var pck = new ExcelPackage(info))
        {
            var wb = pck.Workbook;
            var ws = wb.Worksheets.Add("RESULTS");
            for (var row = 0; row < rows.Count; row++)
                for (var col = 0; col < fieldcount; col++)
                    ws.SetValue(row + 1, col + 1, rows[row][col]);
            pck.Save();
        }
    });
    var rowlist = new List<Object[]>();
    while (sdr.Read())
    {
        var rowdata = new Object[sdr.FieldCount];
        sdr.GetValues(rowdata);
        rowlist.Add(rowdata);
        if (rowlist.Count == max)
        {
            savefile(getfi(++loop), rowlist);
            rowlist.Clear();
        }
    }
    if (rowlist.Count > 0)
        savefile(getfi(++loop), rowlist);
}

既然你正在创建一个新的excel文件(如果我错了请纠正我)，你可以简单地写一个带有一些特定内容的XML文件。如果包含正确的内容，Excel支持。xml文件。

您可以简单地在内存中创建XML文件的内容，然后将该内容写入.XML文件。您不需要EPPlus包，因此您绕过了EPPlus包的限制。

当然，您必须手动找出需要在. xml文件中写入的内容。如果你要使用格式和公式，它可能是一个复杂的。

看到:

https://www.google.nl/search?q=excel%2520xml%2520format& oq = excel xml % % 2520 2520格式的
https://technet.microsoft.com/en-gb/magazine/2006.01.blogtales.aspx
http://blogs.msdn.com/b/brian_jones/archive/2005/06/27/433152.aspx

简单的解决方案，没有任何技巧(没有测试，但意图应该是明确的)

using (var conn = new SqlConnection(_connectionString))
{
    int filesCount = 1;
    int col = 1, row = 1;
    string fileName = String.Empty;
    int count;
    ExcelPackage pck;
    ExcelWorksheet ws;
    using (var cmd = new SqlCommand(string.Format(DataQuery, tableName), conn))
    {
         cmd.CommandType = CommandType.Text;
         cmd.CommandTimeout = 360;
         conn.Open();
         using (SqlDataReader sdr = cmd.ExecuteReader())
         {
              while (sdr.Read())
              {
                   if (row == 1)
                   {
                       fileName = string.Format(TargetFile, tableName, filesCount);
                       if (File.Exists(fileName))
                       {
                            File.Delete(fileName);
                       }
                       pck = new ExcelPackage(new FileInfo(fileName));
                       ws = pck.Workbook.Worksheets.Add("RESULTS");
                   }
                   count = sdr.FieldCount; 
                   for (int i = 0; i < count; i++)
                   {
                       var val = sdr.GetValue(i);
                       ws.SetValue(row, col++, val);
                   }
                   row++;
                   col = 1;
                   if (row >= 50000)
                   {
                        ws.Cells[ws.Dimension.Address].AutoFitColumns();
                        ws.Cells[1, 1, 1, count].AutoFilter = true;
                        pck.Save();
                        row = 1;
                        filesCount+
                   }
               }
          }
          if (row > 1)
          {
               ws.Cells[ws.Dimension.Address].AutoFitColumns();
               ws.Cells[1, 1, 1, count].AutoFilter = true;
               pck.Save();
          }
     }
}
conn.Close();

相关内容

最新更新

热门标签：