Webapi export to excel corrupted file downloaded

Question

I used OfficeOpenXml ExcelPackage to generate excel from a list of Objects This succesfully downloads the file, however when I open the file excel complains saying it is corrupted and not saved in proper file format. I also tried using FIleSaver.js but no luck with it either. Same error. Any suggestions on how to fix this?

Server Code:

 ExcelPackage _excelPackage = new ExcelPackage();
 ExcelWorksheet _sheet = _excelPackage.Workbook.Worksheets.Add("New Sheet");
 int currentRow = 1;
            foreach (var prod in Products)
            {
                 _sheet.Cells[currentRow, 0].Value = prod.Id;
                 _sheet.Cells[currentRow, 0].Value = prod.Name;
                 _sheet.Cells[currentRow, 0].Value = prod.Price;
                 currentRow++;
             }


HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);    
byte[] stream = _excelPackage.GetAsByteArray()
response.Content = new ByteArrayContent(stream);

response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
    FileName = "Products.xlsx"
};
return response;

Client Side (AngularJS Service Code):

var req = {
    url: fpReportsWebAPIURL + 'api/Export/DownloadFile',
    method: 'POST',
    responseType: 'arraybuffer',
    //data: json, //this is your json data string
    headers: {
        'Content-type': 'application/json',
        'Accept': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    }
};

return $http(req).then(function (data) {
    var type = data.headers('Content-Type');
    var blob = new Blob([data], {
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    });
    saveAs(blob, 'Products' + '.xlsx');
    return true;
});

When I open the Excel I get error as "Excel found unreadable content in Products.xlsx Do you want to recver the contents of this workbook.......".

I inspected the API response and saw that WebApi returns some data in OfficeOpenML format not sure if this is the issue

Please help me in resolving this problem. Found a similar issue which was unanswered.

solution found please refer below link hope it helps to others https://stackoverflow.com/a/28148359/5475124 — Noman Chali, Mar 01 '18 at 10:34

score 0 · Answer 1 · answered Mar 29 '20 at 09:21

I think the problems is in the way you are saving the file to a stream, the ExcelPackage object has it's own method to save it to a memory stream, i would fix it like this:

Use a using block

using (ExcelPackage xls = new ExcelPackage())

then write it to a MemoryStream

MemoryStream ms = new System.IO.MemoryStream();
xls.SaveAs(ms);

before you close the stream write it to your response

response.BinaryWrite(ms.ToArray());
ms.close(); //close the MemoryStream

that should work

Webapi export to excel corrupted file downloaded

1 Answers1