c# Asp.NET MVC downloading excel file using FileStreamResult

Question

I need to build a method, that will receive model, build excel from it (building and receiving part is done without problems) and then export (let user download it) using memory stream (without saving it on the server). I am new to ASP.NET and MVC so i found guide and built this as a tutorial project:

    public FileResult Download()
    {
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

        object misValue = System.Reflection.Missing.Value;

        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlWorkSheet.Cells[1, 1] = "ID";
        xlWorkSheet.Cells[1, 2] = "Name";
        xlWorkSheet.Cells[2, 1] = "1";
        xlWorkSheet.Cells[2, 2] = "One";
        xlWorkSheet.Cells[3, 1] = "2";
        xlWorkSheet.Cells[3, 2] = "Two";


        var path = "C:\\Work-Work\\TestFolder\\XCLbuildTry1\\csharp-Excel.xls";
        xlWorkBook.SaveAs(path);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);
        return File(path, "application/vnd.ms-excel", "WidgetData.xlsx");
    }

Now i need to change this code to make this method sent my excel file without saving my excel file on server. I have tried to google some guides and answers here, on stack, but for now i cant find a solution, which i can implement.

I think i should use FileStreamResult, but all guides give no particular information about creating and inserting my file into stream.

score 11 · Accepted Answer · edited Nov 10 '16 at 15:24

11

Unfortunately it does not appear that the Microsoft.Office.Interop.Excel.Workbook interface (msdn) will allow you to convert your workbook to a memory stream.

However, in the past I have used an external library, EPPlus, which has worked fantastically.

See below for code using EPPlus which will not require you to save the file on the server.

BusinessLogic

public MemoryStream Download() {
    MemoryStream memStream;

    using (var package = new ExcelPackage()) {
        var worksheet = package.Workbook.Worksheets.Add("New Sheet");

        worksheet.Cells[1, 1].Value = "ID";
        worksheet.Cells[1, 2].Value = "Name";
        worksheet.Cells[2, 1].Value = "1";
        worksheet.Cells[2, 2].Value = "One";
        worksheet.Cells[3, 1].Value = "2";
        worksheet.Cells[3, 2].Value = "Two";  

        memStream = new MemoryStream(package.GetAsByteArray());
    }

    return memStream;
}

Controller

public FileStreamResult Download() {
    var memStream = BusinessLogic.Download();
    result File(memStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 
}

edited Nov 10 '16 at 15:24

rainbowShiningUnicorn

55
1
1
6

answered Nov 10 '16 at 08:33

Chawin

1,438
1
21
33

Looks like it will work fine, however my `worksheet` is blocked by 'read-only' mode. I have tried some solutions on stack: http://stackoverflow.com/questions/20751808/make-column-or-cells-readonly-with-epplus -> Gives no result at all, but no warnings too http://stackoverflow.com/questions/19999531/how-to-create-readonly-excel-sheet-using-c-sharp-net -> Gives warning `No definition` http://stackoverflow.com/questions/18059979/creating-an-excel-file-with-epplus-but-it-is-always-read-only -> I have no idea, how browser can make VS give warnings on my code (last answer) – rainbowShiningUnicorn Nov 10 '16 at 12:59
@rainbowShiningUnicorn Is the sheet read-only or is the entire workbook read only when saved? – Chawin Nov 10 '16 at 15:25
Property `Cells` have only `get` method. – rainbowShiningUnicorn Nov 11 '16 at 08:16

c# Asp.NET MVC downloading excel file using FileStreamResult

1 Answers1

Linked