0

I've a little problem with generating piviot table using Epplus in C#. After generated table I want to create piviot table based on this table:
http://imgur.com/Agxp1mK
Before addition pivot table to spreedsheet everything was fine but after added below code during saving stream throws exception:

An exception of type 'System.NullReferenceException' occurred in EPPlus.dll but was not handled in user code Additional information: Object reference not set to an instance of an object.

In time dubbuging I can't find object which is null.
My debugging track:

System.NullReferenceException was unhandled by user code
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=EPPlus
  StackTrace:
       at OfficeOpenXml.ExcelWorksheet.SavePivotTables()
       at OfficeOpenXml.ExcelWorksheet.Save()
       at OfficeOpenXml.ExcelWorkbook.Save()
       at OfficeOpenXml.ExcelPackage.Save()
       at OfficeOpenXml.ExcelPackage.SaveAs(Stream OutputStream)
       at TestController.GetSpreedsheetPivot(ExcelPackage package, String fileName)
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12()
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)

Code:

FileInfo newFile = new FileInfo("Excel.xlsx");
ExcelPackage pkg = new ExcelPackage(newFile);

ExcelWorksheet worksheet = pkg.Workbook.Worksheets.Add("Table");
worksheet.Cells["A1"].LoadFromDataTable(table, true); //add datatable values to worksheet

//Create pivot table at A20 using values from A1:F10 named pivTable
var pivotTable = worksheet.PivotTables.Add(worksheet.Cells["H1"], worksheet.Cells["A1:E63"], "pivTable");

//Assign which Rows and Columns of A1:F10 are data or headers
pivotTable.ShowHeaders = true;
pivotTable.FirstHeaderRow = 1;
pivotTable.FirstDataCol = 1;
pivotTable.FirstDataRow = 2;

//Row Labels
pivotTable.RowFields.Add(pivotTable.Fields["Grid"]);
pivotTable.RowFields.Add(pivotTable.Fields["Story"]);
pivotTable.RowFields.Add(pivotTable.Fields["Zone"]);
pivotTable.DataOnRows = false;

pivotTable.DataFields.Add(pivotTable.Fields["Quantity"]);

pivotTable.ColumnFields.Add(pivotTable.Fields["Name of material"]);

var stream = new MemoryStream();
pkg.SaveAs(stream);

string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

stream.Position = 0;
return File(stream, contentType, fileName)
Alex
  • 21
  • 1
  • 4
  • `NullReferenceException` is a common situation for beginner programmers. The link provided should help you understand the problem. Then use the debugger to find what/where/when you have a variable that is `null`. – Soner Gönül Jan 27 '16 at 09:33
  • during savePiviotTable – Alex Jan 27 '16 at 09:34
  • @SonerGönül it is not duplicate. Unfortunately I haven't expand more inforamtion during debbuging from this library. Before added code in post all was fine. My once info from debbuger is it that nullException is inside savePivotTable() method – Alex Jan 27 '16 at 09:44
  • Your question doesn't contain any references to `savePivotTable`. Can you please update it? – Simon MᶜKenzie Jan 27 '16 at 10:02
  • @SimonMᶜKenzie done! :) – Alex Jan 27 '16 at 10:23
  • Looks like a bug in EPPlus. You should file a bug report in the issue tracker – Daniel Hilgarth Jan 27 '16 at 10:32
  • In my opinion this is something wrong in my code because on other table adding pivot table is fine. – Alex Jan 27 '16 at 10:37
  • 1
    @Alex: No, it's a bug in the library. It is not supposed to throw NRE outside its own code. If you handled the library incorrectly in some way, it should throw a MEANINGFUL exception, helping you in fixing your issue. With the NRE, this is not possible, so it's a bug in the library. – Daniel Hilgarth Jan 27 '16 at 11:27
  • After few hours I research that problem with saving pkg occurs during saving like stream, if I execute pkg.Save() method I don't find any problem . Now, how return file in MVC not using MemoryStream? Any idea? – Alex Jan 27 '16 at 13:06
  • [This answer](http://stackoverflow.com/a/14048542/622391) has a workaround where a `MemoryStream` is created from the output of the package's `GetAsByteArray()` method. – Simon MᶜKenzie Jan 27 '16 at 22:25

0 Answers0