CODEWITHSUNDEEP
asp.netexcelasp.net-mvc-5
MJ X
MJ X

Reputation: 9044

How to create new Excel file to clients computer in ASP.Net MVC

I have ASP.net MVC 5 Application, I want to generate report in excel

currently I have specified a path, but what I want is that excel should be downloaded in client computer not on the path.

public void GenerateExcel()
{
     Excel.Application application = new Excel.Application();
            Excel.Workbook workbook = application.Workbooks.Add(System.Reflection.Missing.Value);
            Excel.Worksheet worksheet = workbook.ActiveSheet;

            worksheet.Cells[1, 1] = "ID";
            worksheet.Cells[1, 2] = "Full Name";
            worksheet.Cells[1, 3] = "Position Title";
            worksheet.Cells[1, 4] = "Unit";
            worksheet.Cells[1, 5] = "Mobile";
            worksheet.Cells[1, 6] = "Email";
            worksheet.Cells[1, 7] = "Supervisor Email";
            worksheet.Cells[1, 8] = "Date and Time of Travel";
            worksheet.Cells[1, 9] = "Type of Trip";
            worksheet.Cells[1, 10] = "Distination";



            var query = from v in db.MyContextVR
                        join t in db.MyTripTypeContext on v.TypeOfTripId equals t.Id
                        select new VRsVM
                        {
                            Id = v.Id,
                            FullName = v.FullName,
                            PostitionTitle = v.PostitionTitle,
                            Unit = v.Unit,
                            Mobile = v.Mobile,
                            Email = v.Email,
                            SupervisorEmail = v.SupervisorEmail,
                            DateAndTimeOfTravel = v.DateAndTimeOfTravel,
                            TripName = t.TripName,
                            Distination = v.Distination
                        };


            int row = 2;
            foreach (var item in query.ToList())
            {
                worksheet.Cells[row, 1] = item.Id;
                worksheet.Cells[row, 2] = item.FullName;
                worksheet.Cells[row, 3] = item.PostitionTitle;
                worksheet.Cells[row, 4] = item.Unit;
                worksheet.Cells[row, 5] = item.Mobile;
                worksheet.Cells[row, 6] = item.Email;
                worksheet.Cells[row, 7] = item.SupervisorEmail;
                worksheet.Cells[row, 8] = item.DateAndTimeOfTravel;
                worksheet.Cells[row, 9] = item.TripName;
                worksheet.Cells[row, 10] = item.Distination;


                row++;
            }
            workbook.SaveAs("D:\\tempex/myreport.xlsx");

            workbook.Close();
}

Upvotes: 1

Views: 3785

Answers (4)

giri-webdev
giri-webdev

Reputation: 535

Save the excel file into the server and then convert it in to the byte array then return it to the client by File() helper method as ActionResult/FileResult from the asp.net mvc action method

  public ActionResult ExportToExcel(...) {

        your codes....

        string tempPath = AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + DateTime.Now.Millisecond + "_temp";
        workbook.SaveAs(tempPath, workbook.FileFormat);
        tempPath = workbook.FullName;
        workbook.Close();    
        byte[] result = File.ReadAllBytes(tempPath);
        File.Delete(tempPath);

       this.Response.AddHeader("Content-Disposition", "Employees.xls");
       this.Response.ContentType = "application/vnd.ms-excel";

       return File(result, "application/vnd.ms-excel");

  }

Upvotes: 1

Héctor Álvarez
Héctor Álvarez

Reputation: 494

Assuming you already made the excel spreadsheet:

public HttpResponseMessage GetExcel()
{
    var workbook = yourExcel; //Whatever you do to create one.
    HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "Excel download");
    MemoryStream myStream = new MemoryStream();
    workbook.Write(myStream); // This is for NPOI, you'll have to do the right one for your tool (which is unspecified currently).
    response.Content = new ByteArrayContent(myStream.ToArray());
    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
    {
        FileName = fileName
    };
    myStream.Close();
}

Don't forget to include your custom try-catch logic!

Upvotes: 1

Mo09890
Mo09890

Reputation: 174

You've got a couple of options with this one when it comes to generating the Excel file, personally I've had great results using the free ClosedXml library which is also available via NuGet. I wont go into this much as I feel it's not the point of the question!

Regardless of your choice of generating libraries if you want to send the Excel file back to the client as a download in Mvc you will want to first save the file to a stream in order to get the data for the file

using (System.IO.MemoryStream outputStream = new System.IO.MemoryStream())
{
     workbook.SaveAs(outputStream);
     outputStream.Seek(0, SeekOrigin.Begin);
     data = new byte[outputStream.Length];
     outputStream.Read(data, 0, (int)outputStream.Length);
}

Then once you have the data from the template you can send it back to the user from within your controller action:

public ActionResult Download() {
    byte[] data = GetExcel();   
    return File(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Filename.xlsx")
}

EDIT

There is also an overload of the File method that takes a Stream so you can skip converting it to a byte array if you already have a stream for the file

Upvotes: 0

Florin Secal
Florin Secal

Reputation: 941

You can use something like EPPlus to easily create an Excel document in the memory stream. After, you can use content-disposition to serve it to the client (as a download).

Follow this great tutorial by Corey Adler:
Easy Excel Interaction with EPPlus

Upvotes: 0

Related Questions