Node Js Create Excel File and download

on

 

const express = require("express");
const excelgenerate = require("excel4node");
const app = express();

//To serve static files such as images, CSS files, and JavaScript files
app.use(express.static('./public'));

// Parse JSON bodies (as sent by API clients)
app.use(express.json());

// Parse URL-encoded bodies (as sent by HTML forms)
app.use(express.urlencoded({ extended: true }));

//Create a new instance of a Workbook class
const wb = new excelgenerate.Workbook();

const heading = ["First Name", "Last name", "Full Name", "Email"];

const data = [
  {
    first_name: "Johns",
    last_name: "Does",
    full_name: "Johns Does",
    email: "johndoess@yopmail.com",
  },
  {
    first_name: "JP",
    last_name: "Smith",
    full_name: "JP Smith",
    email: "jpmorgan@yopmail.com",
  },
  {
    first_name: "Test",
    last_name: "Team",
    full_name: "Test Team",
    email: "test.team@yopmail.com",
  },
];

//creating dynamik tr td for table
let datas = [];
datas.push(`<tr>
<th>${heading[0]}</th>
<th>${heading[1]}</th>
<th>${heading[2]}</th>
<th>${heading[3]}</th>
</tr>`);
data.map((value) => {
  datas.push(`<tr>
        <td>${value.first_name}</td>
        <td>${value.last_name}</td>
        <td>${value.full_name}</td>
        <td>${value.email}</td>
    </tr>`);
});

//remove , from array
datas = datas.join("");

app.get("/", (req, res) => {
  try {
    res.send(`
    <style>
        table {
            font-family: arial, sans-serif;
            border-collapse: collapse;
            width: 100%;
        }
       
        td, th {
            border: 1px solid #dddddd;
            text-align: left;
            padding: 8px;
        }
    </style>
    <div style="margin:100px">
        <form action="/convert" method="post">
            <div class="form-group">
            <input type="submit" value="Convert To Excel!" class="btn btn-default">
            <table>
                ${datas}
            </table>            
            </div>
        </form>
    </div>
    `);
  } catch (error) {
    throw error;
  }
});

app.post("/convert", (req, res) => {
    try {
      // Add Worksheets to the workbook
      const ws = wb.addWorksheet("Sheet 1");
 
      // Create a reusable style
      var style = wb.createStyle({
        font: {
          size: 12,
        },
      });
 
      for (let index = 1; index < 5; index++) {
        ws.column(index).setWidth(25);
        ws.cell(1, index)
          .string(heading[index - 1])
          .style(style)
          .style({ font: { size: 14 } });
      }
 
      for (let index = 0; index < data.length; index++) {
        ws.cell(index + 2, 1)
          .string(data[index].first_name)
          .style(style);
        ws.cell(index + 2, 2)
          .string(data[index].last_name)
          .style(style);
        ws.cell(index + 2, 3)
          .string(data[index].full_name)
          .style(style);
        ws.cell(index + 2, 4)
          .string(data[index].email)
          .style(style);
      }
 
      wb.write("public/files/Excel.xlsx");
      let fname = "Excel.xlsx";
 
      res.send(`<div style="margin:100px">
      <a href="ms-excel:ofe|u|file:///E:/javascript-jquery/multiselect-master/${fname}">
            <input type="button" value="Open In App" class="btn btn-default">
      </a>
      <br>
      <br>
      <a href="/files/${fname}" target="_blank">
        <input type="button" value="Download" class="btn btn-default">
      </a>
      </div>`);
    } catch (e) {
      throw e;
    }
  });

app.listen(3000, () => {
  console.log(`App running at http://localhost:3000`);
});
  1. We are using excel4node npm to convert data in Excel file from Node.js.
  2. we are using app.use(express.static('./public')); to get files from folder.

Comments

Post a Comment