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)