Hi everyone,
here is a tutorial for how to export data collection to excel file using code.
To use this tutorial you need:
- Install the package: “xlsx”
- Button element
- html element
- web module - I called it excel.jsw
After installing the xlsx package, use this code for each element:
excel.jsw
const XLSX = require('xlsx');
export function exportExcel(worksheet) {
var wb = XLSX.utils.book_new();
var ws_name = "SheetJS";
/* make worksheet */
var ws_data = worksheet;
var ws = XLSX.utils.aoa_to_sheet(ws_data);
/* Add the worksheet to the workbook */
XLSX.utils.book_append_sheet(wb, ws, ws_name);
return wb;
}
export function createAoA(rowNum,colNum) { //crete array of array for the excel export
var x = new Array(rowNum);
for (var i = 0; i < x.length; i++) {
x[i] = new Array(colNum);
for (var j = 0; j < colNum; j++) {
x[i][j]=0;
}
}
return x;
}
Button element (I called it iconButton2) - this is the code for the web page
import { exportExcel } from 'backend/excel';
import { createAoA } from 'backend/excel';
import wixData from 'wix-data';
$w.onReady(function () {
// TODO: write your page related code here...
});
export async function iconButton2_click(event) {
wixData.query("payments") //change "payments" to your own collection name
.find()
.then((results) => {
if (results.items.length > 0) {
console.log(results);
let colNum = Object.keys(results.items[0]).length;
let rowsNum = results.items.length;
createAoA(rowsNum, colNum).then(result2 => {
for (var i = 0; i < rowsNum; i++) {
result2[i] = Object.values(results.items[i]);
}
console.log(result2, "result 2");
exportExcel(result2).then((excelfile) => {
console.log(excelfile, "excelfile");
$w('#html1').postMessage(excelfile);
})
});
} else {
console.log("can't fint items");
}
})
.catch((err) => {
let errorMsg = err;
});
}
html element
<!DOCTYPE html>
<html>
<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.7/xlsx.js" integrity="sha512-fH2nbzuBSxkg1hpGKgSGlTcI5GQuqzaPTg04xY/W7UE6X8I+UENvr6qrlvX/G2NK7+acVkBznP1qefpMvNfP7A==" crossorigin="anonymous"></script>
</head>
<body>
<script>
var wb = XLSX.utils.book_new();
var ws_name = "SheetJS";
/* make worksheet */
var ws_data = [
["S", "h", "e", "e", "t", "J", "S"],
[1, 2, 3, 4, 5],
[5,4,3,2,1]
];
var ws = XLSX.utils.aoa_to_sheet(ws_data);
/* Add the worksheet to the workbook */
XLSX.utils.book_append_sheet(wb, ws, ws_name);
console.log(wb);
function myFunction(filewb) {
XLSX.writeFile(filewb, 'out.xls');
}
window.onmessage = function(event){
if (event.data) {
wb = event.data;
console.log(wb,"wb");
}
else {
console.log("if error");
}
};
</script>
<button onclick="myFunction(wb)">export to excel</button>
</body>
</html>
let me know if you have any questions.
Enjoy 
THX!