CODEWITHSUNDEEP
javascriptc#angularjsexcelasp.net-web-api
Nadeem Jamali
Nadeem Jamali

Reputation: 1423

Downloading Excel file xlsx in Angularjs and WebApi


I am working on a task, in which I have to download a report in xlsx format. The report file is generated successfully from server, and is received on client side as well. But it is not opening and producing invalid format error.

Below is the code of server side.

var output = await reportObj.GetExcelData(rParams);
    if (output != null){
        var result = new HttpResponseMessage(HttpStatusCode.OK)
        {
            Content = new ByteArrayContent(output.ConentBytes)
        };
        result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
        {
            FileName = output.FileName
        };
 return result;
 }


Here is the code for client side:

        var saveData = function (response) {

        if (response.status === 200) {
            var reportData = response.data;

            var b = new Blob([reportData], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
            saveAs(b,"ReportFile.xlsx");//this is FileSaver.js function
        } else {
            console.log(response.statusText);
        }

    };


    $scope.getExcelFile = function(reportName, reportParams) {

        reportDataService.getExcelReportData(reportName, reportParams, saveData);

    }


Below is the error message:
Excel could not open newFile.xlsx because some content is unreadable. Do you want to open and repair this workbook?

On clicking repair, following error appears: Excel cannot open this file.
The file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Can some one guide me what I am doing wrong? Meanwhile, the same server side file generator objects works smoothly in ASP.Net forms application, and the file opens without any error as well.
Thank you.

Upvotes: 14

Views: 58593

Answers (5)

Gajender Singh
Gajender Singh

Reputation: 1313

first install these module 

import * as Excel from 'exceljs';
import * as fs from 'file-saver';

In your function write these

 const workbook = new Excel.Workbook();
  var worksheet =  workbook.addWorksheet('sheet');
  worksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 32 }
  ];
 var buff = workbook.xlsx.writeBuffer().then(function (data) {
    var blob = new Blob([data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
    fs.saveAs(blob, "publications.xlsx");
  });

Fs is used to access file system and download file. You can also insert img https://www.npmjs.com/package/exceljs#images

Upvotes: 0

Mithun Kumar
Mithun Kumar

Reputation: 31

I was facing the same error , content was in hexa format, so i added a response type as arraybuffer, problem got resolved. please see below.

$http({
    url: '/api/sendPMOToBackendUpdate',
    method: "POST",
    headers: {'Content-type': 'application/json'},
    data: backendTsData,
    responseType: 'arraybuffer'
}).success(function(data, status, headers){
    var file = new Blob([ data ], { type : 'application/vnd.ms-excel'});
    var defaultFileName ="TSC-"+$scope.user.name+"-"+$scope.user.ohrId+".xls";
    saveAs(file,defaultFileName);
}).error(function(err) {
    console.log('Error: ' + err);
});

Upvotes: 3

gm2008
gm2008

Reputation: 4325

I encountered a similar problem when writing excel using Javascript library Excel Builder. At the end, I found the reason was that a control character '\u001a' was included in data.

The solution is to encode the control char in Excel's way as '_x001a_'.

The way I diagnosed the problem was like this:

.xlsx file is just a zipped xml file. You can open it with 7-zip. Inside the xl/ folder there is a file sharedString.xml containing all strings. Extract the file and open it with Notepad++. If you see any control character, then it might be the cause.

Upvotes: 0

Pradeep Korriya
Pradeep Korriya

Reputation: 21

You a just need to do one thing only that. include following js to save file locally. Download it from "https://github.com/eligrey/FileSaver.js/" your response data should be in blob type.

I have implemented it and its working.

function downloadfile(url,defaultFileName){
  var self = this;
    var deferred = $q.defer();
    $http.get(url, { responseType: "blob" }).then(
       function (data){
          saveAs(data.data, defaultFileName)
          deferred.resolve(defaultFileName);                    
        }, function (data) {
           var e = /* error */
            deferred.reject(e);
        });
        return deferred.promise;
}

Upvotes: 2

hansmaad
hansmaad

Reputation: 18915

I expect your $http call is missing the response type configuration. This is the way I download office files:

function download(url, defaultFileName) {
    var self = this;
    var deferred = $q.defer();
    $http.get(url, { responseType: "arraybuffer" }).then(
        function (data, status, headers) {
            var type = headers('Content-Type');
            var disposition = headers('Content-Disposition');
            if (disposition) {
                var match = disposition.match(/.*filename=\"?([^;\"]+)\"?.*/);
                if (match[1])
                    defaultFileName = match[1];
            }
            defaultFileName = defaultFileName.replace(/[<>:"\/\\|?*]+/g, '_');
            var blob = new Blob([data], { type: type });
            saveAs(blob, defaultFileName);
            deferred.resolve(defaultFileName);                    
        }, function (data, status) {
            var e = /* error */
            deferred.reject(e);
        });
    return deferred.promise;
}

Upvotes: 39

Related Questions