Hướng dẫn js create excel file

Welcome to a tutorial on how to create an Excel file in Javascript. Yes, you read that right. We are referring to “browser Javascript”, and not the server-side NodeJS. The dark days have passed and it is actually possible to generate Excel files using client-side Javascript – Read on for the example!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

TABLE OF CONTENTS

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

QUICK NOTES

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

All right, let us now get into the example of generating an Excel file in Javascript and offering it as a download.

PART 1) THE HTML

create-excel.html

 

Download Demo Excel
 

Yep, that is pretty much everything we need to drive the example.

1. Load SheetJS from CDNJS, this library is required to create the Excel file. Will leave a link in the extras section below to their full documentation.
2. Just a demo button to start generating the Excel file.
3. Click on the demo button to generate the Excel file. Details below.

PART 2) THE JAVASCRIPT

2A) DUMMY DATA

create-excel.html

// (C1) DUMMY DATA
var data = [
  ["Joa Doe", ""],
  ["Job Doe", ""],
  ["Joe Doe", ""],
  ["Jon Doe", ""],
  ["Joy Doe", ""]
];

For the Javascript, we will first start by defining a nested array of dummy data. Don’t think it needs a lot of explanation, this will get “converted” into rows and columns in the Excel file.

2B) CREATE A NEW EXCEL OBJECT

create-excel.html

// (C2) CREATE NEW EXCEL "FILE"
var workbook = XLSX.utils.book_new(),
    worksheet = XLSX.utils.aoa_to_sheet(data);
workbook.SheetNames.push("First");
workbook.Sheets["First"] = worksheet;

This should be pretty self-explanatory for the folks who already know Excel well, but for the sake of those who are new:

• A “workbook” is simply “the entire Excel file”.
• A “worksheet” is a table within a workbook.
• A workbook can contain multiple worksheets.

So what this section is doing:

• workbook = XLSX.utils.book_new() Creates a new Excel workbook.
• worksheet = XLSX.utils.aoa_to_sheet(data) Creates a new worksheet, using the dummy data array above.
• workbook.SheetNames.push("First") and workbook.Sheets["First"] = worksheet Attach the worksheet to the workbook.

2C) CONVERT TO BLOB 

create-excel.html

// (C3) TO BINARY STRING
var xlsbin = XLSX.write(workbook, {
  bookType: "xlsx",
  type: "binary"
});
 
// (C4) TO BLOB OBJECT
var buffer = new ArrayBuffer(xlsbin.length),
    array = new Uint8Array(buffer);
for (var i=0; i

Horray, we now have an Excel object, all that’s left is to “save it as an Excel file on the hard disk”. That is easier said than done… This part got me stuck for an hour, researching “how to convert a binary string into a Blob object”.

• (C3) The library itself did not come with any functions to offer a “save file as”. This is the closest it gets to a “raw file”, getting the Excel file as a raw binary string.
• (C4) The next step that I figured was “parse the binary string into a blob object” and “create an object URL for downloading”. I will spare you guys of the “micro-details”, but it is necessary for the data to be encoded in octet… For the octet stream to work properly, get it?

2D) OFFER AS A FILE DOWNLOAD

create-excel.html

// (C5) "FORCE DOWNLOAD"
var url = window.URL.createObjectURL(xlsblob),
    anchor = document.createElement("a");
anchor.href = url;
anchor.download = "demo.xlsx";
anchor.click();
window.URL.revokeObjectURL(url);
delete anchor;

Finally, we create a temporary object URL for the Excel blob object, and “force a download”. The end.

P.S. The “use Javascript to click on a download link” trick may not work on all browsers due to security restrictions. The safer way is to attach the download link to the page, and let the user manually click on it.

COMPATIBILITY CHECKS

• Arrow Functions – CanIUse
• Array Buffer – CanIUse
• Uint8Array – Can I Use

This example will work on all modern “Grade A” browsers.

EFFICIENCY & PERFORMANCE

Just a quick note that we are holding an entire Excel file in the memory with this example… This will work great for small to medium Excel files, but the massive ones are going to run into performance issues and memory limits. You may want to implement some sort of restrictions on the maximum amount of data, or look at server-side alternatives with streaming.

LINKS & REFERENCES

• SheetJS
• Blob From Javascript Binary String – Stack Overflow
• How to save .xlsx data to file as a blob – Stack Overflow
• Export From MySQL Database To Excel In NodeJS – Code Boxx

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!