Export ARRAY Data With Merge Cells To Excel Spreadsheet

 

const express = require("express");

const XLSX = require("xlsx");

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 }));

var data = [

  ["id", "items"],

  ["","name"],

  ["nick","ball"],

  ["nick","phone"],

  ["jack","pen"],

  ["jack","doll"]

];

app.get("/", (req, res) => {

  try {

    var merge = [{ s: {r:2, c:0}, e: {r:3, c:0} },{ s: {r:4, c:0}, e: {r:5, c:0} }];

   

    var ws = XLSX.utils.aoa_to_sheet(data);

    if(!ws['!merges']) ws['!merges'] = [];

    for (const iterator of merge) {

      ws['!merges'].push(iterator);

    }

    var wb = XLSX.utils.book_new();

    XLSX.utils.book_append_sheet(wb, ws, "sheet1");

    // Generate buffer

    XLSX.write(wb, { bookType: "xlsx", type: "buffer" });

    // Binary string

    XLSX.write(wb, { bookType: "xlsx", type: "binary" });

    XLSX.writeFile(wb, "studentsData.xlsx");

    res.send(`yoo`);

  } catch (error) {

    throw error;

  }

});

app.listen(3000, () => {

  console.log(`App running at http://localhost:3000`);

});