0

I am trying to download an excel file with large data set. On server side I am using EPPlus library to generate excel. I then pass the stream obj to client side. Client side code download the excel file using that stream obj.
When I open the downloaded file I get corrupt file error message. Where I am going wrong?

Server side code (C#)

public async Task<IActionResult> DownloadExcel(Model postData)
{
    string apiUrl = "Api/URL/Get";
    apiUrl += "?Status=" + postData.Status +
              "&Gender=" + postData.Gender +
              "&CurrentAge=" + postData.CurrentAge;

    var result = await CallAPI.GetListFromApiAsync<ResultModel>(apiUrl);

    var stream = new MemoryStream();
    using (ExcelPackage excel = new ExcelPackage(stream))
    {
        var workSheet = excel.Workbook.Worksheets.Add("Sheet1");

        //  Column
        workSheet.Cells[1, 1].Value = "Column 1";
        workSheet.Cells[1, 2].Value = "Column 2";
        .
        .
        .
        workSheet.Cells[1, 19].Value = "Column 19";

        // Row
        int recordIndex = 2;
        foreach (var d in result)
        {
            workSheet.Cells[recordIndex, 1].Value = d.data1;
            workSheet.Cells[recordIndex, 2].Value = d.data2;
            .
            .
            .
            workSheet.Cells[recordIndex, 19].Value = d.data4;
            recordIndex++;
        }
        for (int i = 1; i < 20; i++)
        {
            workSheet.Column(i).AutoFit();
        }

        string fileName = @"download.xlsx";
        string fileType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        stream.Position = 0;
        return File(stream, fileType, fileName);
    }
}

Client side ajax call

$.ajax({
    url: "/SomeURL/DownloadExcel",
    type: "POST",
    data: form.serialize(),
    success: function (result) {
        var binaryData = [];
        binaryData.push(result);
        var blob = new Blob([binaryData], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;' });
        var link = document.createElement('a');
        link.href = window.URL.createObjectURL(blob);
        link.download = 'download.xlsx';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
});
Alvin
  • 290
  • 5
  • 25
  • I've already answered this for MVC https://stackoverflow.com/questions/27874360/download-an-excel-file-in-jquery-ajax-request-from-asp-net-mvc/30246687#30246687. You can take this as starting point. – SouXin Jun 21 '18 at 19:26
  • @SouXin How does that help? EPPlus vs ClosedXML. I tried to use mime type octet but no luck.Notice i do get excel downloaded but the file is corrupt – Alvin Jun 21 '18 at 20:17
  • Oh sorry, I just forgot it was an closedXml st the beginning. The idea is to return bytestream to the src of iframe. If i remember correctly. – SouXin Jun 21 '18 at 20:21
  • @SouXin I tried even that `return stream.ToArray();` last line on server side instead of return file – Alvin Jun 21 '18 at 20:26
  • In epplus you have to save as bytearray. And then return. You don't need memory stream at the beginning – SouXin Jun 21 '18 at 20:29
  • @SouXin Ok I tried this as well. `byte[] bytes = excel.GetAsByteArray(); return bytes;` Do you have any working code or example? – Alvin Jun 21 '18 at 20:36
  • I will try to find tomorrow – SouXin Jun 21 '18 at 20:37
  • @SouXin I appreciate it. – Alvin Jun 21 '18 at 20:39
  • Im getting same problem.. Did you fixed? – Yuri Morales Sep 04 '18 at 16:19

0 Answers0