Getting exception while exporting data to excel using server export in kendo mvc grid

[BINTA]

Hi,

We have an export to Excel functionality implemented for Kendo MVC Grid. We have used Telerik.Documents.SpreadSheetProcessing and Kendo.MVC.Export libraries to achieve this functionality.
Followed the steps mentioned in this url :https://docs.telerik.com/aspnet-mvc/html-helpers/data-management/grid/export/server-export.

Now, this seems not working and we are receiving the below exception

System.InvalidOperationException: The workbook must have at least one worksheet.     at Telerik.Documents.SpreadsheetStreaming.Exporters.Xlsx.XlsxWorkbookExporterBase.CompleteWriteOverride()     at Telerik.Documents.SpreadsheetStreaming.Core.EntityBase.Dispose(Boolean disposing)     at Telerik.Documents.SpreadsheetStreaming.Core.EntityBase.Dispose()     at Kendo.Mvc.Export.Helpers.CollectionToStream(SpreadDocumentFormat format, IEnumerable data, IList`1 model, String title, Action`1 columnStyleAction, Action`1 rowStyleAction, Action`1 cellStyleAction)     at Kendo.Mvc.Export.ExportExtensions.ToXlsxStream(IEnumerable instance, IList`1 model, String title, Action`1 columnStyleAction, Action`1 rowStyleAction, Action`1 cellStyleAction)     at SGE.Aladdin.Web.Controllers.ReportController.<ExportWorkRequestListInExcel>d__273.MoveNext() in D:\Aladdin Dev Code\SGE.Aladdin.Web\Controllers\ReportController.cs:line 21500

Exception is getting triggered when we convert the list to ToXlsxStream. Not able to figure out what exactly has gone wrong. Sharing the code below:

Jquery code
--------------
 $("#btnExcelWrReport").click(function () {
            $('#WaitModal').modal('show');

            var grid = $("#WorkRequestListReport").data("kendoGrid");

            var currentdate = new Date();
            var filename = "WorkRequestReport_" + currentdate.getDate() +
                + (currentdate.getMonth() + 1) +
                + currentdate.getFullYear() +
                + currentdate.getHours() +
                + currentdate.getMinutes() +
                + currentdate.getSeconds();
            var options = {
                format: $(this).data("format"),
                title: filename
            }
            $("#export-data").val(encodeURIComponent(JSON.stringify(options)));
            $("#export-model").val(encodeURIComponent(JSON.stringify(grid.columns)));

            $("#frmdownload").submit();

        });

    $("#frmdownload").submit(function (event) {

        event.preventDefault();
        var actionURL = '@Url.Action("ExportWorkRequestListInExcel", "Report")';
        $('#WaitModal').modal('show');
        var postData = { model: $("#export-model").val(), data: $("#export-data").val() };

        $.ajax({
            url: actionURL,
            data: postData,
            type: 'POST',
            success: function (data) {
                    $('#WaitModal').modal('hide');
                    window.location.href = '@Url.Action("DownloadReportInExcel", "Report")?fileGuid=' + data.FileGuid + '&filename=' + data.FileName + '&mimeType=' + data.MimeType;
                @*if (data.FileGuid != "") {
                    $('#WaitModal').modal('hide');
                    window.location.href = '@Url.Action("DownloadReportInExcel", "Report")?fileGuid=' + data.FileGuid + '&filename=' + data.FileName + '&mimeType=' + data.MimeType;
                }
                else {
                      $('#WaitModal').modal('hide');
                    $('#MessageModal').modal('show');
                    $('#Message').html("@CommonResources.NoRecordsToExport");
                }*@
            }
        });
        return false;
    });

Controller
----------------

var columnsData = JsonConvert.DeserializeObject<IList<ExportColumnSettings>>(HttpUtility.UrlDecode(model));
                dynamic options = JsonConvert.DeserializeObject(HttpUtility.UrlDecode(data));
                SpreadDocumentFormat exportFormat = options.format.ToString() == "csv" ? exportFormat = SpreadDocumentFormat.Csv : exportFormat = SpreadDocumentFormat.Xlsx;
                Action<ExportCellStyle> wrReportCellStyle = new Action<ExportCellStyle>(ChangeWRReportCellStyle);


                string fileName = string.Format("{0}.{1}", options.title, options.format);
                string mimeType = Helpers.GetMimeType(exportFormat);
                Session["IsData"] = "0";
                var workRequestList= await GetExportWorkRequestList();

                int i = 0;
                while (Convert.ToString(Session["IsData"]) != "1")
                { 
                    i++;
                }


                string handle = Guid.NewGuid().ToString();
                string title = DateTime.Now.ToString();
                try {
                    title = options.title;
                }
                catch(Exception ex) { }
                Stream exportStream = exportFormat == SpreadDocumentFormat.Xlsx ? workRequestList.ToXlsxStream(columnsData, title, cellStyleAction: wrReportCellStyle) : workRequestList.ToCsvStream(columnsData);


                TempData[handle] = exportStream;
                return Json(new { FileGuid = handle, FileName = fileName, MimeType = mimeType });
            }
            catch (Exception ex)
            {
                ExceptionLogHelper.Log("ReportController", "ExportWorkRequestListInExcel", ex);
                return Json(new { FileGuid = string.Empty });

}

Please help me. I am not sure why this suddenly stopped working. In a different page, this same code works fine for an excel export.

                                                           

[Anton Mironov]

Hi Binta,

Thank you for the code snippets and the details provided.

The fastest route to getting you up and running is if you could provide a runnable, isolated, sample project. Examining this project will let us replicate the issue locally and further troubleshoot it.

I do not need any sensitive data - just a couple of dummy records will be enough.

Looking forward to hearing back from you.

Kind Regards,
Anton Mironov
Progress Telerik