How to transfer database data to excel

Question

I have used below method to transfer data to excel in my project. It works fine - if I use id first.
But if i use id second and use ajax it is not working. I need to use the second one because I want to pass parameters. If anyone could help, I would appreciate it.

Not working This Way

//Index.cshtml
 <a href="#" id="second" onclick="transfertoexcel()">Export</a>

 function transfertoexcel() {
     var objdate = {
         '_fitsdate': $('#firstdateid').val(),
         '_lastdate': $('#lastdateid').val()
     };
     $.ajax({
         url: "/BankTransaction/EportExcel",
         type: 'Get',
         contentType: 'application/x-www-form-urlencoded; charset=utf-8',
         data: objdate,
         success: function () {
             alert("Data Tranasfer Successfully!")
             window.location.href = "/BankTransaction/Index";
         },
         error: function () {
             alert("Data Not Send")
         }
     })
 }
//Controller
 public IActionResult EportExcel(DateTime _fitsdate, DateTime _lastdate)
 {
    
     try
     {
         var data =  _context.tbl_BankTransactions
                  .Where(x=>x.Date>=_fitsdate&& x.Date<=_lastdate).ToList();
       
         if (data.Count > 0 && data !=null)
         { using (XLWorkbook wb = new XLWorkbook())
             {
                 wb.Worksheets.Add(ToconvertDatatable(data.ToList()));
                 using ( MemoryStream strem = new MemoryStream())
                 {
                     wb.SaveAs(strem);
                     string filename = $"Data{DateTime.Now.ToString("dd/mm/yyyy")}.xlsx";
                     return File(strem.ToArray(),"application/vnd.openxmlformats-officedocuments.spreadsheetml.sheet" ,filename);
                 }
             } 
         }
         TempData["Errorsms"] = "Data Not Fount";
     }
     catch (Exception)
     {
         throw;
     }
      return View("Index");
 }
 public DataTable ToconvertDatatable<T>(List<T> items)
 {
     DataTable dt = new DataTable(typeof(T).Name);
     PropertyInfo[] propeInfo = typeof(T).GetProperties(BindingFlags.Public|BindingFlags.Instance);
     foreach (PropertyInfo prop in propeInfo) 
     {
         dt.Columns.Add(prop.Name);  
     }
     foreach (T item in items)
     {
         var values=new object[propeInfo.Length];
         for (int i = 0; i<propeInfo.Length;i++)
         {
             values[i]=propeInfo[i].GetValue(item, null);
         }
         dt.Rows.Add(values);    
     }
     return dt;
 }

This Way Its working

//Index.cshtml
 <a id="first" asp-action="EportExcel" asp-controller="BankTransaction">expor</a>

//Controller

public IActionResult EportExcel()

{

       try

  {

      var data =  _context.tbl_BankTransactions

               .ToList();

              if (data.Count > 0 && data !=null)

      { using (XLWorkbook wb = new XLWorkbook())

          {

              wb.Worksheets.Add(ToconvertDatatable(data.ToList()));

              using ( MemoryStream strem = new MemoryStream())

              {

                  wb.SaveAs(strem);

                  string filename = $"Data{DateTime.Now.ToString("dd/mm/yyyy")}.xlsx";

                  return File(strem.ToArray(),"application/vnd.openxmlformats-officedocuments.spreadsheetml.sheet" ,filename);

              }

          } 

      }

      TempData["Errorsms"] = "Data Not Fount";

  }

  catch (Exception)

  {

      throw;

  }

   return View("Index");
```  }

Answer 1

I'm happy to answer it

I thinkg, the issue may be related to how the AJAX request is handled. The direct HTML link calling the controller method works fine, but the AJAX call fails to export the Excel file. This could be due to differences in how AJAX requests handle file downloads.

Adjust the AJAX Request: Try changing the AJAX request's type from GET to POST, and ensure the contentType is correctly set. GET requests may have a limit on the length of data passed, while POST can handle larger data.

$.ajax({

url: "/BankTransaction/EportExcel",

type: 'POST',

data: objdate,

xhrFields: {

    responseType: 'blob'

},

success: function (data) {

    var blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

    var link = document.createElement('a');

    link.href = window.URL.createObjectURL(blob);

    link.download = "Data" + new Date().toLocaleDateString() + ".xlsx";

    link.click();

    alert("Data Transfer Successfully!");

    window.location.href = "/BankTransaction/Index";

},

error: function () {

    alert("Data Not Sent");

}

});