Excel corrupt after download from lambda function nodejs + angular

Question

I have written code in Nodejs which generates excel file. After that generated file is transformed to client request. I am making request from angular based frontend. Here's complete code. I am using exceljs to create excel file.

        let [row] = await conn.execute(
            "Query",
            []
        );
        
        let abc = Object.keys(row[0]);
        
        var workbook = new excel.Workbook();
        var worksheet = workbook.addWorksheet('Sheet1');

        let clm = [];

        abc.forEach(element => {                
            clm.push({ header: element, key: element });
        });

        worksheet.columns = clm;

        worksheet.addRows(row);

        var tempFilePath = tempfile('.xlsx');
        workbook.xlsx.writeFile(tempFilePath).then(function () {

            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.setHeader("Content-Disposition", "attachment; filename=temp.xlsx");

            res.sendFile(tempFilePath, function (err) {
                // console.log('---------- error downloading file: ' + err);
            });

        });

Here's my angular code.

reports(params: any): Observable<any> {
     
        return this.http.post(environment.apiUrl + '/reports', JSON.stringify(params), { responseType: 'arraybuffer' })
            .pipe(map((result) => {
                return result;
            }),
                catchError((err) => {
                    throw err;
                })
            );

    }

this.apicallService
      .reports(json)
      .subscribe(
        (result) => {
          
          var blob = new Blob([result], { type : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});
          var link = document.createElement('a');
          link.href = URL.createObjectURL(blob);
          link.download = filename;
          link.click();
        },
        (err) => {
          this.notifyService.showError(err.message, 'Error');
        }
      );

Above code is working well in local environment. But when I published code on aws lambda, excel file is being downloading but it got corrupted.

I have taken reference from this topic too but no luck.

Corrupt XLSX file after downloading

I think its API gateway problem. But I am not able to figure it out. Please suggest the way to solve it.

If it is then could you please suggest a solution? It'll be appreciated. — user711241, May 11 '23 at 15:34

score 0 · Answer 1 · answered May 12 '23 at 19:49

After spending a lot of time to understand the problem, solution arise. So the problem was API Gateway and Lambda function communicates as base64 stream not as Binary stream. That's why I converted my response as base64 string and send back to client.

At client side I have converted base64 string to Excel. Here's complete code.

Nodejs Function

let [row] = await conn.execute(
            Query",
            []
        );

        let abc = Object.keys(row[0]);

        var workbook = new excel.Workbook();
        var worksheet = workbook.addWorksheet('Sheet1');

        let clm = [];

        abc.forEach(element => {
            clm.push({ header: element, key: element });
        });

        worksheet.columns = clm;

        worksheet.addRows(row);

        const wbout = await workbook.xlsx.writeBuffer();
        
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader("Content-Disposition", "attachment; filename=temp.xlsx");
        res.isBase64Encoded = true;

        res.status(200).send(Buffer.from(wbout).toString('base64'));

Angular Code

return this.http.post(environment.apiUrl + '/reports', JSON.stringify(params), {responseType: 'text'})
        .pipe(map((result) => {
            return result;
        }),
            catchError((err) => {
                throw err;
            })
        );

this.apicallService
      .reports(json)
      .subscribe(
        (result) => {
          
          let blob = this.base64ToBlob(result, result.length);
          var link = document.createElement('a');
          link.href = URL.createObjectURL(blob);
          link.download = filename;
          link.click();

        },
        (err) => {
          this.notifyService.showError(err.message, 'Error');
        }
      );
      
      
      public base64ToBlob(b64Data: any, sliceSize = 512) {    
        let byteCharacters = atob(b64Data); //data.file there
        let byteArrays = [];
        for (let offset = 0; offset < byteCharacters.length; offset += sliceSize) {
          let slice = byteCharacters.slice(offset, offset + sliceSize);

          let byteNumbers = new Array(slice.length);
          for (var i = 0; i < slice.length; i++) {
            byteNumbers[i] = slice.charCodeAt(i);
          }
          let byteArray = new Uint8Array(byteNumbers);
          byteArrays.push(byteArray);
        }
        return new Blob(byteArrays, { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      }

These are the links from where I took references.

API gateway returning corrupted excel file https://www.serverless.com/framework/docs/providers/aws/events/apigateway#binary-media-types Angular 4 - convert base 64 to a downloadable file

Thanks all.

Excel corrupt after download from lambda function nodejs + angular

1 Answers1