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

1 Answer 103 Views
Grid
BINTA
Top achievements
Rank 1
BINTA asked on 25 Oct 2023, 05:21 PM

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.

                                                           

1 Answer, 1 is accepted

Sort by
0
Anton Mironov
Telerik team
answered on 30 Oct 2023, 08:09 AM

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

Stay tuned by visiting our public roadmap and feedback portal pages. If you're new to the Telerik family, be sure to check out our getting started resources, as well as the only REPL playground for creating, saving, running, and sharing server-side code.

Tags
Grid
Asked by
BINTA
Top achievements
Rank 1
Answers by
Anton Mironov
Telerik team
Share this question
or