Pivot Excel With NReco, System Out of Memory Exception

Question

I m getting System Out of Memory exception while creating pivot table with NReco ExcelPivotTableWriter

 public void Write(PivotTable pvtTbl)
        {
            var tbl = getPivotDataAsTable(pvtTbl.PivotData);
            var rangePivotTable = wsData.Cells["A1"].LoadFromDataTable(tbl, false);

            var pivotTable = ws.PivotTables.Add(
                    ws.Cells[1, 1],
                    rangePivotTable, "pvtTable");

            foreach (var rowDim in pvtTbl.Rows)
                pivotTable.RowFields.Add(pivotTable.Fields[rowDim]);
            foreach (var colDim in pvtTbl.Columns)
                pivotTable.ColumnFields.Add(pivotTable.Fields[colDim]);

            pivotTable.ColumGrandTotals = false;
            pivotTable.DataOnRows = false;
            pivotTable.ColumGrandTotals = false;            
            pivotTable.RowGrandTotals = false;


            if (pvtTbl.PivotData.AggregatorFactory is CompositeAggregatorFactory)
            {                
                var aggrFactories = ((CompositeAggregatorFactory)pvtTbl.PivotData.AggregatorFactory).Factories;
                for (int i = 0; i < aggrFactories.Length; i++)
                {
                    var dt = pivotTable.DataFields.Add(pivotTable.Fields[String.Format("value_{0}", i)]);
                    dt.Function = SuggestFunction(aggrFactories[i]);

                    string columnName = "";
                    if (dt.Function == OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Sum)
                          columnName = ((NReco.PivotData.SumAggregatorFactory)aggrFactories[i]).Field;
                    else if(dt.Function == OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Average)
                         columnName = ((NReco.PivotData.AverageAggregatorFactory)aggrFactories[i]).Field;

                    if (columnNames.ContainsKey(columnName))
                        dt.Name = columnNames[columnName].ToString();
                    else
                        dt.Name = aggrFactories[i].ToString();                             
                }
            }
            else
            {
                pivotTable.DataFields.Add(pivotTable.Fields["value"]).Function = SuggestFunction(pvtTbl.PivotData.AggregatorFactory);
            }

        }

error occures while creating rangePivotTable

   var rangePivotTable = wsData.Cells["A1"].LoadFromDataTable(tbl, false);

The LazyTotal mode is true

   var ordersPvtData = new PivotData(dimentionsArray, composite, true);

The dataset has 200k rows. It is not too much i think. I have 8 gb ram on windows 10. NReco is free version. Any solution ?

Answer 1

8G may not be enough physical memory depending upon how large each of the 200K rows are and the memory consumption of the other applications running on your system.

Before you run this program, start the Windows Task Manager and click on the Performance tab.

Note the Available and Free Memory values. Then run your program and watch how the memory is consumed. If your program does consume all of your available memory, then your options are...

1. Free up more memory by removing other applications that consume memory.
2. Add more physical memory to your system.
3. Modify your program to make it more memory efficient. (this includes removal of memory leaks)
4. Some combination of the prior three options.

Answer 2

You should be able to slice through 200k rows pretty easily. Try it like this . . .

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\your_path_here\SampleFile.xlsx");
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Data Source";
Worksheet sheet2 = workbook.CreateEmptySheet();
sheet2.Name = "Pivot Table";
CellRange dataRange = sheet.Range["A1:G200000"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);
var r1 = pt.PivotFields["Vendor No"];
r1.Axis = AxisTypes.Row;
pt.Options.RowHeaderCaption = "Vendor No";

var r2 = pt.PivotFields["Description"];
r2.Axis = AxisTypes.Row;
pt.DataFields.Add(pt.PivotFields["OnHand"], "SUM of OnHand", SubtotalTypes.Sum);
pt.DataFields.Add(pt.PivotFields["OnOrder"], "SUM of OnOrder", SubtotalTypes.Sum);
pt.DataFields.Add(pt.PivotFields["ListPrice"], "Average of ListPrice", SubtotalTypes.Average);

pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
workbook.SaveToFile("PivotTable.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("PivotTable.xlsx");