Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,

Requirement is user will upload an excel file with students names and other data. After uploading, on same click event user should get an excel file stating row by row status whether student data created successfully or any error message.

On upload Click I call below action and post Excel and do all stuff validations in MVC action

function uploadFile(fileType, fileUpload) {
            if (window.FormData !== undefined) {
                var files = fileUpload.files;

                // Create FormData object
                var fileData = new FormData();

                // Looping over all files and add it to FormData object
                fileData.append(files[0].name, files[0]);

                $.ajax({
                    url: '/Admin/UploadNewStudents',
                    type: "POST",
                    contentType: false,// "application/vnd.ms-excel", // Not to set any content header
                    processData: false, // Not to process data
                    data: fileData,
                    success: function (result) {
                        alert(result);
                    },
                    error: function (xhr) {
                        
                    }
                });
            } else {
                alert("FormData is not supported.");
            }
        }




[HttpPost]
public ActionResult UploadNewStudent()
{
    try
    {
        HttpFileCollectionBase file = Request.Files;
        string Message = StudentExcel.ValidateExcel(Request);
        if (!string.IsNullOrEmpty(Message))
            return Json(Message, JsonRequestBehavior.AllowGet);
        else
        {
            HttpPostedFileBase pfile = Request.Files[0];
            DataSet ds = StudentExcel.GetExcelData(pfile);

            using (ExcelPackage pck = new ExcelPackage())
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
                ws.Cells["A1"].LoadFromDataTable(ds.Tables[0], true);
                Byte[] fileBytes = pck.GetAsByteArray();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=DataTable.xlsx");
                Response.Charset = "";
                Response.ContentType = "application/vnd.ms-excel";
                StringWriter sw = new StringWriter();
                Response.BinaryWrite(fileBytes);
                Response.End();
            }
        }

    }
    catch (Exception ex)
    {

    }
    return View();
}


I get desired Datatable in below code and setting into EPplus package and trying to download but Excel is not downloading. I don't want to save excel in server.

DataSet ds = StudentExcel.ValidateExcelData(pfile);


What I have tried:

Googling,

I think since action method is post I am not able to download excel. New to MVC, any help will be appreciated
Posted
Updated 15-Sep-20 4:51am
Comments
F-ES Sitecore 15-Sep-20 11:19am    
You can't handle file downloads via ajax as your js is handling the response and it can't write that response to a file. Rather than using ajax just use a normal form that points to your action and have the file as a normal form element. That will cause the browser to run the action and handle the response, which will kick off the download manager.

1 solution

Avoid using Response.BinaryWrite in MVC. Use a custom ActionResult instead:
C#
public class ExcelResult : FileResult
{
    private const string MimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    public ExcelResult([NotNull] ExcelPackage package) : base(MimeType)
    {
        if (package is null) throw new ArgumentNullException(nameof(package));
        Package = package;
    }

    public ExcelPackage Package { get; }

    protected override void WriteFile([NotNull] HttpResponseBase response)
    {
        Package.SaveAs(response.OutputStream);
    }
}
C#
[HttpPost]
public ActionResult UploadNewStudent()
{
    try
    {
        string message = StudentExcel.ValidateExcel(Request);
        if (!string.IsNullOrEmpty(message)) return Json(message);
        
        HttpPostedFileBase pfile = Request.Files[0];
        DataSet ds = StudentExcel.GetExcelData(pfile);

        ExcelPackage pck = new ExcelPackage();
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
        ws.Cells["A1"].LoadFromDataTable(ds.Tables[0], true);
        return new ExcelResult(pck) { FileDownloadName = "DataTable.xlsx" };
    }
    catch (Exception ex)
    {
        return Json(ex.Message);
    }
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900