NodeJs/Express writing xlsx-template and sending it back for browser download

Question

I have this excel where i want to replace the scalar values as the xlsx-template docs say:

| Extracted on: | ${extractDate} | and I want to send it over the response object. The file gets written correctly in the output path but in the client it gives me a binary output. Bellow is the backend call to the Url

this.downloadSheet = async (req, res) => {

    let seriesNumber = Number(req.body.seriesNumber);
    let sheetPath = path.resolve('../filePath/excel.xlsx');
    let sheetFinalOuput = path.resolve('../filePath/excelOutput.xlsx');
    #Get data needed to swap for the excel
    let excelDataStream = await ReportService.getBalanceSheetAllInOne(balanceSheetPath, seriesNumber);
    fs.readFile(path.resolve(balanceSheetPath), function(err, data) {
      // Create a template
      let template = new XlsxTemplate(data);

      // Replacements take place on first sheet
      const sheetNumber = 1;

      // Perform substitution
      template.substitute(sheetNumber, excelDataStream);

      // Get binary data
      var mydata = template.generate({ type: 'nodebuffer'});

      fs.writeFile(path.resolve(balanceSheetFinalOuput), mydata, function(err) {
        if(err) {
          return console.log(err);
        }
        console.log(`Wrote data in file, check please!`);

        // Send File & set headers
        res.set({'Content-Disposition': `attachment; filename=balanceIncome${seriesNumber}.csv`, 'Content-Type': 'text/csv'});
        res.write(mydata);
        res.end();
      });
    });
  };

client function http post request

$http.post(`${settings.apiHost}/api/panel/report/balanceSheet/allInOne/false`,{seriesNumber: $scope.seriesNumber})
        .success(function (response) {
          var file = new Blob([response], {type: 'text/csv'});

          var isGoogleChrome = window.chrome != null && window.navigator.vendor === "Google Inc.";
          var isIE = /*@cc_on!@*/false || !!document.documentMode;
          var isEdge = !isIE && !!window.StyleMedia;


          if (isGoogleChrome){
            var url = window.URL || window.webkitURL;

            var downloadLink = angular.element('<a></a>');
            downloadLink.attr('href',url.createObjectURL(file));
            downloadLink.attr('target','_self');
            downloadLink.attr('download', `balanceSheet_${$scope.seriesNumber}.csv`);
            downloadLink[0].click();
          }
          else if(isEdge || isIE){
            window.navigator.msSaveOrOpenBlob(file,`balanceSheet_${$scope.seriesNumber}.csv`);

          }
          else {
            var fileURL = URL.createObjectURL(file);
            window.open(fileURL);
          }
        })

res.write? res.send is what you need https://expressjs.com/en/api.html#res.send — 王仁宏, May 08 '19 at 19:29
I tried that, and I also tried res.sendFile(path.resolve('dataPath.xlsx')); The download file is messed up — freecks, May 08 '19 at 19:53
i would change this api to GET method and use window.open to download it, not a blob link. If you must to do this, try https://stackoverflow.com/a/14737423/11303576 to solve response type problem. — 王仁宏, May 08 '19 at 20:13
i forgot res.send(buffer) not need send header as a file, delete it to prevent $http.post to decode it, then try convert nodebuffer into arraybuffer, and build blob by the arraybuffer — 王仁宏, May 08 '19 at 20:18
Do you mind posting a more concrete example? Or explain a little more on the general process? — freecks, May 08 '19 at 21:06

score 1 · Accepted Answer · answered May 09 '19 at 00:31

1

client side

const url = `${settings.apiHost}/api/panel/report/balanceSheet/allInOne/false/${$scope.seriesNumber}`;
window.open(url);

server side(change router params and method to GET)

let seriesNumber = Number(req.params.seriesNumber);
... 
fs.writeFile(path.resolve(balanceSheetFinalOuput), mydata, function(err) {
  if(err) {
    return console.log(err);
  }
  console.log(`Wrote data in file, check please!`);

  // Send File & set headers
  res.set({'Content-Disposition': `attachment; filename=balanceIncome${seriesNumber}.csv`, 'Content-Type': 'text/csv'});
  res.send(mydata);
});

another way

client side

...
.success(function (nodeBuffer) {
  let arraybuffer = Uint8Array.from(nodeBuffer).buffer;
  var file = new Blob(arraybuffer, {type: 'text/csv'});
  ....
}

server side

...
fs.writeFile(path.resolve(balanceSheetFinalOuput), mydata, function(err) {
  if(err) {
    return console.log(err);
  }
  console.log(`Wrote data in file, check please!`);
  res.send(mydata);
});
...

answered May 09 '19 at 00:31

王仁宏

396
1
9

The first solution worked like a charm! For some reason, I had different binary output in the excel. Either way, thank you for taking the time and answering. I need the url end point to be secure, for internal authentication. Can I send the http headers with the window object? I think in this case, solution 2 works best. I will try to make it work. Thanks – freecks May 13 '19 at 15:27
1

no, window.open method don't accept custom header. The solution 2 point is @feecks what you output from server and what you get in browser, does browser can read that buffer? if not, how can make it read, convert to what? how to transform buffer to blob? that may cost some time to try. hope this tips can help you. – 王仁宏 May 16 '19 at 02:11
Thank you this has been of great help! – freecks May 17 '19 at 14:27

NodeJs/Express writing xlsx-template and sending it back for browser download

1 Answers1