Hi everyone,
as for many users i needed to export some data from my Wix site into excel spreadsheets. Some tutorials are already available for this purpose:
Export data collection to XLS excel file using code
Export any Data Collections to MS Excel using Wix Code (Video)
However, the main limitation of these solutions is the difficulty of customization, with the data being processed within an html element.
The goal of this tutorial is to show an alternative method of creating an excel spreadsheet in javascript within a Wix site, and later downloading it.
Elements and packages used in this tutorial
- Button
- Html element
- Install the npm exceljs package (tested with v4.3.0)
- create/add a Back-end web module (in my case called aModule.jsw)
The first step is to create a function in the web module that generates the custom excel file.
The exceljs library is used for this purpose, which allows to read, manipulate and write spreadsheet data and styles to XLSX and JSON.
In the package's git repository you will find many examples on how to customize your excel sheet.
The following example shows how to add some rows and columns:
aModule.jsw
import * as ExcelJS from "exceljs/dist/exceljs.min.js";
export async function generateExcel(){
const workbook = new ExcelJS.Workbook();
workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();
workbook.properties.date1904 = true;
var worksheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'dob', width: 32 }
];
worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,3,4)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1980,7,12)});
let buffer = await workbook.xlsx.writeBuffer();
return buffer
}This function returns a buffer containing all the data in the excel sheet.
The next step is to insert the code into the html element that allows you to download the file. The file-saver package is used for this purpose, which is imported directly into the html element.
html element code:
<html>
<head>
<script src="https://unpkg.com/file-saver@2.0.5/dist/FileSaver.js"></script>
<script>
window.onmessage = function(event){
if(event.data){
var buffer = event.data.buffer;
const fileExtension = event.data.extension;
const fileType = event.data.type;
const fileName = event.data.filename;
const blob = new Blob([buffer], {type: fileType});
saveAs(blob, fileName + fileExtension);
}
};
</script>
</head>
<body>
</body>
</html>Finally we need to import the web form into the code of the wix page, and send the data to be downloaded to the html element.
wix page code:
import {generateExcel} from 'backend/aModule.jsw'
$w.onReady(function () {
// Write your JavaScript here
// To select an element by ID use: $w('#elementID')
$w('#button1').onClick(async()=>{
let buffer = await generateExcel()
let filename = "export";
let extension = ".xlsx"
let type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
$w('#html1').postMessage({"buffer": Uint8Array.from(buffer.data), "filename": filename, "extension": extension,"type": type})
})
});Bonus
The file-saver package allows you to save different types of files by specifying their extension and type.
An example for exporting a text file is:
$w('#button1').onClick(async()=>{
let buffer = "Hello World!"
let filename = "exportText";
let extension = ".txt"
let type = "text/plain;charset=utf-8";
$w('#html1').postMessage({"buffer": buffer, "filename": filename, "extension": extension,"type": type})
})Please feel free to comment for any improvements or suggestions.
#excel #exceljs #filesaver #npm
Hi everyone, I found the solution.
You need just to change the way to import the exceljs npm module:
Change
import ExcelJS from 'exceljs'as
import * as ExcelJS from "exceljs/dist/exceljs.min.js";in your backend module.
Thanks for the great solution!
Here is a way to implement it universally for any database.
wix page code:
import { generateExcel } from 'backend/aModule.jsw' import wixData from 'wix-data'; export async function button122_click(event) { let tt = await wixData.query("mydata").find() let data = tt.items let buffer = await generateExcel(data) let filename = "export"; let extension = ".xlsx" let type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; $w('#html1').postMessage({ "buffer": Uint8Array.from(buffer.data), "filename": filename, "extension": extension, "type": type }) }aModule.jsw
import ExcelJS from 'exceljs' export async function generateExcel(data){ const workbook = new ExcelJS.Workbook(); workbook.creator = 'Me'; workbook.lastModifiedBy = 'Her'; workbook.created = new Date(1985, 8, 30); workbook.modified = new Date(); workbook.properties.date1904 = true; var worksheet = workbook.addWorksheet('My Sheet'); let cals = [] let item = data[0] for (const key in item) { cals.push({header: key, key:key, width: 15}) } worksheet.columns =cals for (const iterator of data) { worksheet.addRow(iterator) } let buffer = await workbook.xlsx.writeBuffer(); return buffer }