Excel export server side.

[Code]

The excel generated is okay, the data coming is also okay. The only issue is when I try to open it, it Excel repairs it with the error  popup.

[HttpPost]
public ActionResult ExportMultipleGridsFile(List<GridExportData<dynamic>> grids)
{
    try
    {
        var exportStream = new MemoryStream();

        using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, exportStream, SpreadExportMode.Create))
        {
            foreach (var grid in grids)
            {
                var columnsData = JsonConvert.DeserializeObject<IList<ExportColumnSettings>>(HttpUtility.UrlDecode(grid.Model));
                var removableColumns = new List<ExportColumnSettings>();

                // Remove columns with invalid width or field
                foreach (var column in columnsData)
                {
                    if (column.Width == null || column.Width.Equals(new Unit()))
                    {
                        column.Width = new Unit("100px"); // Default width
                    }

                    if (string.IsNullOrWhiteSpace(column.Field))
                        removableColumns.Add(column);
                }

                columnsData = columnsData.Except(removableColumns).ToList();

                dynamic options = JsonConvert.DeserializeObject(HttpUtility.UrlDecode(grid.Option));

                var worksheetName = options.title.ToString();
                if (worksheetName.Length > 31)
                {
                    worksheetName = worksheetName.Substring(0, 31);
                }
                worksheetName = Regex.Replace(worksheetName, @"[:\/\\\?\*\[\]]", "_");

                using (IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter(worksheetName))
                {
                    // Attempt to deserialize the data
                    var data = Retrieve();
                    //var data = JsonConvert.DeserializeObject<List<IntakeReport>>(HttpUtility.UrlDecode(grid.Data));

                    // Check if data is null or empty
                    if (data == null || !data.Any())
                    {
                        // Log or handle the issue as necessary
                        throw new InvalidOperationException("Data is null or empty after deserialization.");
                    }

                    // Add headers with validation
                    using (IRowExporter headerRow = worksheetExporter.CreateRowExporter())
                    {
                        foreach (var column in columnsData)
                        {
                            if (!string.IsNullOrEmpty(column.Title))
                            {
                                using (var cellExporter = headerRow.CreateCellExporter())
                                {
                                    cellExporter.SetValue(column.Title);
                                }
                            }
                        }
                    }

                    // Add rows with validation
                    foreach (var item in data)
                    {
                        using (IRowExporter row = worksheetExporter.CreateRowExporter())
                        {
                            foreach (var column in columnsData)
                            {
                                var cellValue = GetCellValue(item, column.Field);

                                using (var cellExporter = row.CreateCellExporter())
                                {
                                    if (cellValue != null)
                                    {
                                        // Remove newline characters
                                        var cleanedValue = cellValue.ToString().Replace("\n", " ").Replace("\r", " ");
                                        cellExporter.SetValue(cleanedValue);
                                    }
                                    else
                                    {
                                        // Optionally handle null values
                                        cellExporter.SetValue("string Empty");
                                    }
                                }
                            }
                        }
                    }
                }

            }

            workbookExporter.Dispose();
        }

        // Reset the stream position before sending it
        exportStream.Seek(0, SeekOrigin.Begin);

        // Return the file with the correct MIME type and filename
        return File(exportStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "CombinedExport.xlsx");
    }
    catch (Exception ex)
    {
        // Log the exception message (if logging is configured)
        // Example: _logger.LogError(ex, "Export failed");
        // Rethrow the exception to handle it further up the call stack
        throw;
    }
}

private object GetCellValue(dynamic item, string fieldName)
{
    // Check if the item is a dictionary, which is common in dynamic objects like ExpandoObject or JSON objects
    if (item is IDictionary<string, object> dictionary)
    {
        // Try to get the value from the dictionary
        return dictionary.TryGetValue(fieldName, out var value) ? value : null;
    }

    // If not a dictionary, use reflection to get the property
    var property = item.GetType().GetProperty(fieldName);

    // Ensure the property exists and return its value; otherwise return null
    return property != null ? property.GetValue(item, null) : null;
}

[Anton Mironov]

Hi,

Thank you for the details provided.

As this code is not runnable and the Telerik UI Components are responsible for the visualization of the provided data, I would recommend using the approach from the following demo and customize it as per the needs of your application:

• https://demos.telerik.com/aspnet-mvc/grid/server-export

The following article provides all the available information about the server Excel export for the Telerik UI Grid:

• https://docs.telerik.com/aspnet-mvc/html-helpers/data-management/grid/export/server-export

I hope this information helps.

 

Kind Regards,
Anton Mironov
Progress Telerik

[Code]

But, how do we specify multiple sheets? Have any ideas? 

If we have to export an excel with multiple sheets.

[Anton Mironov]

Hi,

As shown in the following demo, the Server Export works for all the pages of the Grid:

• https://demos.telerik.com/aspnet-mvc/grid/server_export

Feel free to export the Grid from the demo above and observe the result - all the items from all the pages of the Grid are available in the exported file.

I hope this is the desired result.

Best Regards,
Anton Mironov