The most straightforward way to download a file in mvc

Question

I am currently saving an excel file like so on my c drive.

    public ActionResult Export()
    {
        try
        {
            Excel.Application application = new Excel.Application();
            Excel.Workbook workbook = application.Workbooks.Add(System.Reflection.Missing.Value);
            Excel.Worksheet worksheet = workbook.ActiveSheet;
            var people = db.People.ToList();
            worksheet.Cells[1, 1] = "Last Name";
            worksheet.Cells[1, 2] = "First Name";
            int row = 2;
            foreach (var person in people)
            {
                worksheet.Cells[row, 1] = person.PersonFName;
                worksheet.Cells[row, 2] = person.PersonLName;
                row++;
            }
            workbook.SaveAs("c:\\test\\worksheet.xls");
            workbook.Close();
            Marshal.ReleaseComObject(workbook);

            application.Quit();
            Marshal.FinalReleaseComObject(application);
            ViewBag.Result = "Done";
        }
        catch(Exception ex)
        {
            ViewBag.Result = ex.Message;

        }

       return File("c:\\test\\workseet.xls", "application/vnd.ms-excel", "workseet.xls");


       // return View("Success");
    }

I can go to c:\\test\workseet.xls and it exists there I can do what ever with it...

I am wanting to transform my method from return a view to return a file download...

I figured that it was as simple as this:

    return File("c:\\test\\workseet.xls", "application/vnd.ms-excel", "workseet.xls");

However when I do this method and click the link to download, it gives me this error.

The process cannot access the file 'c:\test\workseet.xls' because it is being used by another process.

You must close the workbook first. Instead of saving to the disk though, you can save to a MemoryStream and use `return File(myStream,..)` to avoid. Are you using EPPlus ? — Panagiotis Kanavos, Dec 13 '16 at 14:53
How are you generating the Excel file? What library are you using? — mason, Dec 13 '16 at 14:53
PS `xls` is *not* the correct type for xlsx documents generated by any of the OpenXML libraries like EPPlus. It's the old, pre-2003 format that isn't used any more. — Panagiotis Kanavos, Dec 13 '16 at 14:54
Well there's your problem. Do not use that library on a web server. That's a [bad idea](https://support.microsoft.com/en-us/kb/257757). Instead, use [Open XML SDK](https://msdn.microsoft.com/en-us/library/office/bb448854.aspx), [NPOI](http://npoi.codeplex.com/), or (my favorite) [EPPlus](http://epplus.codeplex.com/). — mason, Dec 13 '16 at 14:56
*DON'T*. That requires that Excel is installed on the server, a very bad idea. It also means that *any* request will start its own instance. If you forget to close it properly, you'll soon run out of memory. If you do close it properly, you'll waste a lot of time waiting for Excel to start — Panagiotis Kanavos, Dec 13 '16 at 14:56
Use EPPlus to generate an actual, current `xlsx` file. xlsx is a zip package that contains xml files. You don't need Excel at all to use it. — Panagiotis Kanavos, Dec 13 '16 at 14:57
Another issue with using the Office Interop libraries on a web server is that it requires Office to be installed. And your copy of license is probably not licensed to be used on a server. — mason, Dec 13 '16 at 14:58
Possible duplicate of [How do I export To Excel?](http://stackoverflow.com/questions/32837808/how-do-i-export-to-excel) — Panagiotis Kanavos, Dec 13 '16 at 15:00
no I am not wondering how to export to excel. that part is fine. I do see the problem however.... I am saving to excel just fine, but when return the file to download. it says it is still being used by another process.... so it actually needs to just wait until it fully closes to be used. — Travis Tubbs, Dec 13 '16 at 15:06
I updated my question to include all of my code for the function I am using to generate the file. It works perfectly as expected... However, when I use return file, that is the part that it doesnt like... if I just forget about it and leave it on the server, nothing is wrong... How do I adjust what I already have to download the file. One of my requirements forces me to use microsoft office for this library. — Travis Tubbs, Dec 13 '16 at 15:10
because my requirements of my project state that I am to use the official microsoft office library for generating my files. It isn't by choice. I do understand that there are other more preferable options, however sometimes customers do not trust sources other than what they are used to. — Travis Tubbs, Dec 13 '16 at 16:16
@TravisTubbs Well then you have a duty to explain to your customers the various things I told you. When you explain these reasons, they're likely going to appreciate that you caught this issue and listen to your recommendations for what to do instead. If they're dead set on using something Microsoft developed, there's always the Open XML SDK that I linked to above. — mason, Dec 13 '16 at 16:21
After doing some research I am definitely in agreement with you on the notion of not using Office Interop @mason . Open XML does not seem to be a comprehensive enough of a solution because I need to have not only the excel files generated but also pdf files. I did some research on EPPlus. It seems to be the right solution. It doesnt rely on anything besides the library right? It seems that way. Any drawbacks on it from your person experience with it? — Travis Tubbs, Dec 14 '16 at 19:41
You should consider that generating PDF's and generating Excel documents are two separate things. You should use a library specifically for generating PDF's if you need them from your application. EPPlus can't generate PDF's either. Open XML is more comprehensive than EPPlus because it can be used to manipulate any of the Open XML document types, not just XLSX. EPPlus has no external dependencies. My only issue with it is very minor - the logic that autofits column widths doesn't do a great job. — mason, Dec 14 '16 at 19:48
I did further research and I have decided that EPPlus is going to be what I use. Can you point me to some useful examples here on stack making worksheets using EPPlus? I am also interested in generating a file to download to the client without using the file system. — Travis Tubbs, Dec 15 '16 at 13:46

score 2 · Answer 1 · edited May 23 '17 at 12:30

This duplicate question is just one of those that show how to use EPPlus to generate Excel files on the server side in a scaleable manner. It's actually a lot easier than using Excel interop and a lot faster. You don't even have to save the file to the disk.

public ActionResult ExportData()
{

    //Somehow, load data to a DataTable

    using (ExcelPackage package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("My Sheet");
        //true generates headers
        ws.Cells["A1"].LoadFromDataTable(dataTable, true);

        //Save the workbook to a stream
        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

You can use LoadFromDataTable to fill a sheet from a data table or LoadFromCollection to load data from a collection, eg List<Sale>.

Both methods return an ExcelRange object (a range of cells) that you can use to format individual cells, rows, and columns. You can also create tables from a range and apply themes.

The duplicate goes even farther and shows how you can avoid even the MemoryStream

The most straightforward way to download a file in mvc

1 Answers1