<div class="row">
    <div class="col">
        <div class="form-group">
            @Html.LabelFor(model => model.TESTDATUM, htmlAttributes: new { @class = "control-label" })
            <div>
                @Html.Kendo().DatePickerFor(model => model.TESTDATUM)
                @Html.ValidationMessageFor(model => model.TESTDATUM, "", new { @class = "text-danger" })
            </div>
        </div>
    </div>
</div>

<div style="width: 17.5em">
    @Html.Kendo().DatePickerFor(model => model.TESTDATUM)
</div>

public class Dropdowns
{        
    public static List<SelectListItem> Status
    {
        get
        {
            return new List<SelectListItem>()
            {
                new SelectListItem()
                {
                    Text = Resource.ACTIVE,
                    Value = "Aktiv"
                },
                new SelectListItem()
                {
                    Text = Resource.DELETED,
                    Value = "Gelöscht"
                },
                new SelectListItem()
                {
                    Text = Resource.EXPIRED,
                    Value = "Ausgelaufen"
                }
            };
        }
    }
}

[Required]
[UIHint("STATUSEditor")]        
[Display(Name = "STATUS", ResourceType = typeof(Resource))]
public string STATUS { get; set; }

@using Kendo.Mvc.UI
@model Models.IDTB_CHARGE

@(
Html.Kendo().DropDownListFor(m => m.STATUS)
    .Name("STATUS")
    .DataValueField("Value")
    .DataTextField("Text")
    .ValuePrimitive(true)    
    .BindTo(Areas.Chargen.Helper.Dropdowns.Status)
)

 columns.Bound(c => c.STATUS).ClientTemplate("#=STATUS.Text#").Width(130);

@(Html.Kendo().Grid<ReportEOSViewModel>()
    .Name("EOSreports")
    .Filterable(cfg => cfg.Extra(false))
    .Columns(columns =>
    {
            columns.Bound(p => p.Well.WellNumber).Filterable(flt => flt.UI("reos_well_filter")
                .Operators(op => op.ForString(fs => fs.Clear().IsEqualTo("Is equal to")))
                .Multi(false).Extra(false));
           // More columns...
    })
    .Pageable()
    .Events(e => e.Filter("reos_filter"))
    .Scrollable()
    .DataSource(ds => ds
        .Ajax()
        .Batch(true)
        .PageSize(50)
        .Read(rd => rd.Action("GetList", "ReportEOS", new { id = Model }))
    )
)

function reos_well_filter(element) {
    element.kendoDropDownList({
        dataSource: {
            transport: {
                read: "@Url.Action("DropDownList", "Well")"
            }
        },
        autoWidth: true,
        dataTextField: "Value",
        dataValueField: "Id",
        optionLabel: "--select well--",
        filter: "startswith"
    });
}

function reos_filter(e) {
    if (e.field === "Well.WellNumber") {
        let flt = this.dataSource.filter();
        if (flt !== undefined && flt !== null) {
            for (let i = flt.filters.length - 1; i >= 0; i--) {
                if (flt.filters[i].field === "WellRefFK")
                    flt.filters.splice(i, 1)
            }
        }
        if (e.filter !== null) {
            e.filter.filters[0].field = "WellRefFK";
        }
        else {
            this.dataSource.filter(flt);
            e.preventDefault();
        }
    }
}

[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;
}