Exporting large(>500000 rows) Datasets into xlsx

Question

I have problem with export dataset into xlsx. My code:

DataSet freeTable = new DataSet();
freeTable = FoxDal.Instance.freeTable;
XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add(freeTable.Tables[0], "sheet1");
wb.SaveAs(@"E:\test5.xlsx");

While running code VS give's me prompt ( asking me where is XLReentrantEnumerableSet.cs) if i dont choose it VS throw exception System.OutOfMemoryException. Im using ClosedXML. There are some attached libraries for this framework? With small tables my code working corectly.

If you have fox pro tag to mean that the source is a foxpro table, then the fastest way to transfer data to Excel is to use Excel's QueryTables.Add. Second option is to transfer data using Excel's CopyFormRecordset. EPPLus would be slower but works. — Cetin Basoz, Mar 13 '17 at 09:32

score 0 · Answer 1 · answered Mar 10 '17 at 12:41

I change framework to EPPlus and now i can convert large files. My new code:

        DataSet freeTable = new DataSet();
        freeTable = FoxDal.Instance.freeTable;
        FileInfo fileInfo = new FileInfo(@"E:\Test.xlsx");
        using (ExcelPackage pck = new ExcelPackage())
        {
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("table");
            ws.Cells["A1"].LoadFromDataTable(freeTable.Tables[0], true);
            pck.SaveAs(fileInfo);
        }

Exporting large(>500000 rows) Datasets into xlsx

1 Answers1