How to Export Excel Files Using JavaScript

Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

Excel export script works on IE7+, Firefox and Chrome.

function fnExcelReport()
{
    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = document.getElementById('headerTable'); // id of table

    for(j = 0 ; j < tab.rows.length ; j++) 
    {     
        tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
        //tab_text=tab_text+"</tr>";
    }

    tab_text=tab_text+"</table>";
    tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
        txtArea1.document.open("txt/html","replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");
    }  
    else                 //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

    return (sa);
}

Just create a blank iframe:

<iframe id="txtArea1" style="display:none"></iframe>

Call this function on:

<button id="btnExport" onclick="fnExcelReport();"> EXPORT </button>

How to save excel workbook using Javascript

Your code is working fine. The issue is, each time your server is restarted, you are recreating a new excel file with same name.

You have to implement some extra logic to check if the file already exists in the server. If so, then don't create the file, just append the new row to the existing file otherwise create a new file and save the data.

I have added below working example:

//importing necessary libraries
const express = require("express");
const morgan = require("morgan");
const Prohairesis = require("prohairesis");
const bodyParser = require("body-parser");
const Excel = require("exceljs");
const fs = require("fs");

const app = express();
const port = process.env.PORT || 4444;

let info = [];

app
  .use(express.static("public"))
  .use(morgan("dev"))
  .use(bodyParser.urlencoded({ extended: false }))
  .use(bodyParser.json())

  .post("/api/user", async (req, res) => {
    res.json(req.body);

    //collecting user data into a javascript string
    const user = req.body;
    const ud = JSON.stringify(user);
    const user_data = JSON.parse(ud);
    console.log(user_data);
    const user_li = [
      user_data.first,
      user_data.email,
      user_data.stdid,
      user_data.pwd,
      user_data.cpwd,
    ];
    console.log(user_li);

    //some simple validation
    for (i in user_li) {
      if (user_data.pwd != user_data.cpwd) {
        console.log("**Password does not match**");
        break;
      }
      if (user_data.pwd == user_data.cpwd) {
        info.push(user_li);
        console.log(info);

        //append row to excel worksheet
        const workbook = new Excel.Workbook();
        // for safety
        try {
          // check if `Login-Db.xlsx` file exists
          if (fs.existsSync("Login-Db.xlsx")) {
            // load existing workbook
            workbook.xlsx.readFile("Login-Db.xlsx").then((workbook) => {
              // get worksheet
              const worksheet = workbook.getWorksheet("Main Db");
              // append rows to worksheet
              worksheet.addRows(info);
              // save workbook
              workbook.xlsx.writeFile("Login-Db.xlsx").then((err) => {
                if (!err) {
                  console.log("Row added to excel file");
                  return;
                }
                // if error, print it
                console.log(err);
              });
            });
          } else {
            // create new worksheet
            const worksheet = workbook.addWorksheet("Main Db");
            // add new rows to worksheet
            worksheet.addRows(info);
            // save workbook
            workbook.xlsx.writeFile("Login-Db.xlsx").then((err) => {
              if (!err) {
                console.log("Row added to excel file");
                return;
              }
              // if error, print it
              console.log(err);
            });
          }
        } catch (error) {
          console.log(error);
        }
        break;
      }
    }
  })

  .listen(port, () => console.log(`Server listening on port ${port}`));

Notes (extra):

• Your row is actually appened when you're doing worksheet.addRows(info);

• use a try/catch before checking the with fs.

• In the docs, it's mentioned that writing to a xlsx file is asynchronous hence await should be used. So use async/await to log the result after safely saving file.

// using async/await

.post("/api/user", async (req, res) => {
  // other code
  await workbook.xlsx.writeFile("Login-Db.xlsx");
}

// or using .then()

workbook.xlsx.writeFile("Login-Db.xlsx").then(() => {
  console.log("File has been written");
});

How to parse Excel (XLS) file in Javascript/HTML5

Below Function converts the Excel sheet (XLSX format) data to JSON. you can add promise to the function.

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script>
var ExcelToJSON = function() {

  this.parseExcel = function(file) {
    var reader = new FileReader();

    reader.onload = function(e) {
      var data = e.target.result;
      var workbook = XLSX.read(data, {
        type: 'binary'
      });

      workbook.SheetNames.forEach(function(sheetName) {
        // Here is your object
        var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
        var json_object = JSON.stringify(XL_row_object);
        console.log(json_object);

      })

    };

    reader.onerror = function(ex) {
      console.log(ex);
    };

    reader.readAsBinaryString(file);
  };
};
</script>

Below post has the code for XLS format Excel to JSON javascript code?

Exporting table to Excel file using javascript

First you need to convert the table to a way that excel can understand it.

Expecting a html code to work out of the box in excel is wrong.

Once you got the data in the right format you should create a Blob and download that, one good lib to help out with saving blob is FileSaver.js

var chunk = '<?xml version="1.0"?>...'
var blob = new Blob([chunk], {type: 'application/vnd.ms-excel'})
saveAs(blob, 'filename.xls') // using FileSaver.js

---

Related Topics