This project has moved and is read-only. For the latest updates, please go here.
2
Vote

DoddleReport.OpenXml and large number of rows

description

First of, great project you have created - it is really easy to use.

I have in the past used EPPlus to write directly to Excel (XLSX). One of the challenges was reports with over 1 million rows - Excel can't handle that. So it should be splitted to more worksheets.

I didn't know how DoddleReport would handle this many rows, so i wrote a small piece of code in a MVC controller that would try to make a report with many rows:

public virtual ReportResult Report()
    {
        var t = new
        {
            Date = DateTime.Now,
            User = "Jay",
            Transaction = "ABCD"
        };

        var reportData = new List<dynamic>();

        for (int i = 0; i < 1000200; i++)
        {
            reportData.Add(t);
        }

        // Create the report and turn our query into a ReportSource
        var report = new Report(reportData.ToReportSource());

        // Customize the Text Fields
        report.TextFields.Title = "Report";
        report.TextFields.SubTitle = "Subtitle";
        report.TextFields.Header = string.Format(@"
Report Generated: {0}", DateTime.Now);
        return new ReportResult(report);
    }
While it works great with small amount of data, these amounts resulted in a Exception at first (I have 8GB ram), and later it seemed to just go dead and not produce any report.

System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at ClosedXML.Excel.XLAlignment.set_Horizontal(XLAlignmentHorizontalValues value)
at DoddleReport.OpenXml.Extensions.CopyToXlStyle(ReportStyle reportStyle, IXLStyle xlStyle) in c:\Users\Matt\Development\Projects\DoddleReport\src\DoddleReport.OpenXml\Extensions.cs:line 51
at DoddleReport.OpenXml.ExcelReportWriter.RenderRow(Int32 rowCount, ReportRow reportRow, IXLRow dataRow) in c:\Users\Matt\Development\Projects\DoddleReport\src\DoddleReport.OpenXml\ExcelReportWriter.cs:line 176
at DoddleReport.OpenXml.ExcelReportWriter.WriteReport(Report report, XLWorkbook workbook) in c:\Users\Matt\Development\Projects\DoddleReport\src\DoddleReport.OpenXml\ExcelReportWriter.cs:line 341
at DoddleReport.OpenXml.ExcelReportWriter.WriteReport(Report report, Stream destination) in c:\Users\Matt\Development\Projects\DoddleReport\src\DoddleReport.OpenXml\ExcelReportWriter.cs:line 59
at DoddleReport.Web.ReportResult.ExecuteResult(ControllerContext context) in c:\Users\Matt\Development\Projects\DoddleReport\src\DoddleReport.Web\ReportResult.cs:line 59

So if you have the time, I think you should look into this. I have made large reports before with no issues, and this test only had 3 small columns of data. I do not know if the fault lies in DoddleReport.OpenXml og ClosedXml or maybe both?

comments

fluxmunki wrote Jul 4, 2013 at 12:29 PM

So I started looking at this because I was experiencing the OutOfMemoryException, and also the export was taking like 7 minutes when it did succeed. Based on the "Performance and Memory" guidance from ClosedXml Documentation

I turned off events (I'm fairly sure we don't need them). This reduced it from 7 minutes to 38 seconds!

I tried adding usings. This didn't seem to make any noticeable difference, and I still got OutOfMemoryExceptions

I tried using InsertRowsBelow to bulk add rows rather than adding them one at a time... this may have made a negligible difference.

In the end I stopped work on this because I couldn't avoid the OutOfMemoryException (maybe someone with better knowledge of the code could).