Cannot export kendo grid data to Excel spreadsheet

Question

I am exporting data from a Kendo Grid. The code in the controller breaks with a error at the foreach loop.

ERROR MESSAGE

  -InvalidCastException-


{"Unable to cast object of type '<>f__AnonymousType2`6[System.DateTime,System.String,System.Int32,System.String,System.String,System.String]' to type 'ZoomAudits.DAL.TypedViewClasses.ReportPhoneSupportResultRow'."}

Stacktrace:

at UtilityWebSite.Controllers.ReportsController.Export(DataSourceRequest request, ReportsPhoneSupportSearchVM model) in c:\Users\texas_000\Desktop\UtilityWebSite\UtilityWebSite\Controllers\ReportsController.cs:line 130 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.ActionInvocation.InvokeSynchronousActionMethod() at System.Web.Mvc.Async.AsyncControllerActionInvoker.b__36(IAsyncResult asyncResult, ActionInvocation innerInvokeState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult2.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase1.End() at System.Web.Mvc.Async.AsyncResultWrapper.End[TResult](IAsyncResult asyncResult, Object tag) at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.b__3c() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass45.b__3e()

I have searched for possible answers but I am unable to figure out what needs to be changed? The project is setup with LLBL and I am unfamiliar with it. Any help will be great. If you need more information please let me know. thank you

public FileResult Export([DataSourceRequest]DataSourceRequest request, ReportsPhoneSupportSearchVM model)
    {
        ReportPhoneSupportResultTypedView results = new ReportPhoneSupportResultTypedView();
        string[] userIds = model.UserId.Split(',');
        foreach (string userId in userIds)
        {
            int iUserId = 0;
            if (Int32.TryParse(userId, out iUserId))
            {

                RetrievalProcedures.FetchReportPhoneSupportResultTypedView(results, model.FromDate, model.ToDate, iUserId);
            }
        }

        var Results = from Reslt in results
                       select new 
                      {
                          ActivityDate = Reslt.ActivityDate,
                          Action = Reslt.Action,
                          Assignment = Reslt.Assignment,
                          Description = Reslt.Description,
                          Result = Reslt.Result,
                          ToFrom = Reslt.ToFrom
                      };


        //Get the data representing the current grid state - page, sort and filter
        IEnumerable ExcelResults = Results.ToDataSourceResult(request).Data;

        //Create new Excel workbook
        var workbook = new HSSFWorkbook();

        //Create new Excel sheet
        var sheet = workbook.CreateSheet();

        //(Optional) set the width of the columns
        sheet.SetColumnWidth(0, 10 * 256);
        sheet.SetColumnWidth(1, 50 * 256);
        sheet.SetColumnWidth(2, 50 * 256);
        sheet.SetColumnWidth(3, 50 * 256);

        //Create a header row
        var headerRow = sheet.CreateRow(0);

        //Set the column names in the header row
        headerRow.CreateCell(0).SetCellValue("ActivityDate");
        headerRow.CreateCell(1).SetCellValue("Assignment");
        headerRow.CreateCell(2).SetCellValue("Action");
        headerRow.CreateCell(3).SetCellValue("ToFrom");
        headerRow.CreateCell(2).SetCellValue("Result");
        headerRow.CreateCell(3).SetCellValue("Description");

        //(Optional) freeze the header row so it is not scrolled
        sheet.CreateFreezePane(0, 1, 0, 1);

        int rowNumber = 1;

        //Populate the sheet with values from the grid data
        foreach (ReportPhoneSupportResultRow ER in ExcelResults)
        {
            //Create a new row
            var row = sheet.CreateRow(rowNumber++);

            //Set values for the cells
            row.CreateCell(0).SetCellValue(ER.ActivityDate);
            row.CreateCell(1).SetCellValue(ER.Assignment);
            row.CreateCell(2).SetCellValue(ER.Action);
            row.CreateCell(3).SetCellValue(ER.ToFrom);
            row.CreateCell(2).SetCellValue(ER.Result);
            row.CreateCell(3).SetCellValue(ER.Description);
        }

        //Write the workbook to a memory stream
        MemoryStream output = new MemoryStream();
        workbook.Write(output);

        //Return the result to the end user

        return File(output.ToArray(),   //The binary data of the XLS file
            "application/vnd.ms-excel", //MIME type of Excel files
            "GridExcelExport.xls");     //Suggested file name in the "Save as" dialog which will be displayed to the end user

    }

Answer 1

Got the solution. use this code in the FileExport Action

ReportPhoneSupportResultTypedView results = new ReportPhoneSupportResultTypedView();
    string[] userIds = model.UserId.Split(',');
    foreach (string userId in userIds)
    {
        int iUserId = 0;
        if (Int32.TryParse(userId, out iUserId))
        {

            RetrievalProcedures.FetchReportPhoneSupportResultTypedView(results, model.FromDate, model.ToDate, iUserId);
        }
    }


    //Get the data representing the current grid state - page, sort and filter
    IEnumerable ExcelResults = ((IEnumerable)results).ToDataSourceResult(request).Data;