how to download the excel file on clicking a button using npm package exceljs

Question

I have created a get api in nodejs where I am using a third party package exceljs, The get api is working fine and when I am calling the get api in browser as http://localhost:3000/createExcel it is downloading the file as excel sheet but I want that file to be downloaded by client on clicking download button so I have created a simple html file with button as Download Excel File and I am calling the get api on that button using axios but nothing is happening it is not downloading the file ?

Any idea how to resolve this problem :-

My server side code :-

const MongoClient = require("mongodb");
const express = require("express");
const cors = require('cors');
const url = "mongodb://127.0.0.1:27017";
const Excel = require("exceljs");

const app = express();
app.use(cors);


MongoClient.connect(url, { useUnifiedTopology: true }, async (err, db) => {
  // Handle error
  if (err) {
    throw err;
  }
  let dbo = db.db("ronak");
  dbo
    .collection("excelData")
    .find({})
    .toArray((err, result) => {
      if (err) {
        throw err;
      }
      app.get("/createExcel", (req, res, next) => {
        var workbook = new Excel.Workbook();

        workbook.creator = "Me";
        workbook.lastModifiedBy = "Him";
        workbook.created = new Date(1985, 8, 30);
        workbook.modified = new Date();
        workbook.lastPrinted = new Date(2016, 9, 27);
        workbook.properties.date1904 = true;

        workbook.views = [
          {
            x: 0,
            y: 0,
            width: 10000,
            height: 20000,
            firstSheet: 0,
            activeTab: 1,
            visibility: "visible",
          },
        ];
        var worksheet = workbook.addWorksheet("Sales");
        worksheet.columns = [
          { header: "brand", key: "brand", width: 30 },
          { header: "emp_id", key: "emp_id", width: 10 },
          {
            header: "quarter_no_with_start_month",
            key: "quarter_no_with_start_month",
            width: 20,
          },
          { header: "target", key: "target", width: 20 },
          { header: "units", key: "units", width: 20 },
          { header: "value", key: "value", width: 20 },
        ];
        worksheet.eachRow({ includeEmpty: true }, function (row) {
          row.eachCell(function (cell, colNumber) {
            cell.font = {
              name: "Arial",
              family: 2,
              bold: false,
              size: 14,
            };
            cell.alignment = {
              vertical: "middle",
              horizontal: "center",
            };
          });
        });
        worksheet.addRows(result);

        res.setHeader(
          "Content-Type",
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        );
        res.setHeader(
          "Content-Disposition",
          "attachment; filename=" + "Report.xlsx"
        );
        workbook.xlsx.write(res).then((data) => {
          res.end();
          console.log("File write done", data);
        });
      });
      db.close();
    });
});


app.listen(3000, (err) => {
  if (err) {
    console.log("Error connecting to port 3000:", err);
  } else {
    console.log("Listening on port", 3000);
  }
});

And here is my html code which is in vuejs :-

<!DOCTYPE html>
<html lang="en">
<head>
  <title></title>
  <!-- the star of the show - the Vue library! -->
  <script src="https://unpkg.com/vue/dist/vue.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/axios/0.19.2/axios.min.js"></script>
  <script>
    // when life is settled, load up the fun stuff
    document.addEventListener('DOMContentLoaded', function () {
      new Vue({
        el: '#app',
        // define data - initial display text
        methods: {
            async downloadExcelFile() {
               const res = await axios.get('http://localhost:3000/createExcel')
                console.log(res);
            }
        }
      })
    })
  </script>
</head>
<body>
  <div id="app">
    <button v-on:click="downloadExcelFile">Download Excel File</button>
  </div>
</body>
</html>

lx1412 · Accepted Answer · 2020-04-29 08:15:52Z

2

An ajax request never saves files automatically for you. So open the url http://localhost:3000/createExcel on a new window. Try

methods: {
        async downloadExcelFile() {
           window.open('http://localhost:3000/createExcel', '_blank');
        }
    }

answered Apr 29, 2020 at 8:15

lx1412

1,1907 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paweł Siemienik · Accepted Answer · 2020-04-29 15:01:47Z

0

I recommend using file-saver: https://www.npmjs.com/package/file-saver

npm install --save file-server

Js code:

import { saveAs } from 'file-saver';

//....//

const blob = new Blob([res], {
  type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
});
saveAs(blob, "file.xlsx");

I think also it can be useful for use: https://github.com/Siemienik/xlsx-renderer It should make file generation easier and more configurable because all file structure is created in Excel and used as a template.

Edit: As far as I know, window.open(...,'_blank') often is blocked by the browser.

edited Apr 29, 2020 at 15:01

answered Apr 29, 2020 at 14:55

Paweł Siemienik

686 bronze badges

Collectives™ on Stack Overflow

how to download the excel file on clicking a button using npm package exceljs

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related