Hi everyone,
here is a tutorial for how to export data collection to excel file using code.
To use this tutorial you need:
1) Install the package: "xlsx"
2) Button element
3) html element
4) 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 :) 😎
hi everyone,
can someone share code as a example for this portion:
how to make a worksheet for a table like in this html portion says todoo...
/* make worksheet */
var ws_data =[["S","h","e","e","t","J","S"],[1,2,3,4,5],[5,4,3,2,1]];
second thing is this:
All works fine but when i export excel file just this content which i wrote above shown in ms excel file my actual database collection not shown which is filled by visitor and store in the back end(content manger in the database)
how can i fix this issue please help....
Hi, i´m trying hard to implement this functionality but till now i couln´t. I just follow the instrunction, but the excel file never creates. Is it posible to someone to share me a working sample, please,i'm getting a little desperate, because have no experience in coding in java.
Hi all,
Is it possible to export to a pre-defined .xls template with some initial formatting and formulas?
Many thanks,
Luke
Hello guys, need one more help. I am using HTML to export data to excel. However, if some one clicks button twice, data is duplicated in excel. I think HTML needs to be refreshed/reloaded. How to do that?
$w("#html1").postMessage(items);
@arthosmaciel
@Ziv Assor
https://www.wix.com/velo/forum/594503a32558e5005930102e/export-custom-excel-files-using-exceljs-and-file-saver-npm-packages?postId=629cc1f18bf5220012b8ed8e&origin=notification&utm_campaign=d72ae9f2-22de-4ac5-800a-9e42f025df79&utm_source=so&utm_medium=mail&utm_content=6e9c60f7-6e45-421d-ae03-26df954e0fe4&cid=caced9d2-d297-4c8f-862f-5d70e7a75044
Hello I integrated this code which works fine. Thank you I would like to change the appearance of the excel file: change the color or size of the texts, change the width of the columns ... I did not see how to make or where to place this code (excel.jsw?) Thanks for your help
@arthosmacielor @Ziv Assor
How do you do the first step? (i.e. 81) Install the package: "xlsx"). How do you acquire and install this package?
Also, it seems there is now a "sandbox" related security issue--has anyone determined conclusively if this is *not* an issue for premium domain-bearing sites?
Hello Guys, My code (as provided by @Andreas Kviby was working fine and I was able to download excel. It has stopped working now. I can see populated HTML but excel is not getting downloaded. Has Google changed some setting? How to make it work ? Pls help as live site is impacted
@Ziv Assor I deploy your script but its not downloading the file on chrome now due to chrome security update
there is other solution to download the excel file?
Thanks
Basically, I want to have column span in HTML
I am using HTML code shared by @Andreas Kviby
Hi guys, I am able to download excel after exporting data to hidden HTML. I want to go further ... I need to create excel like below. What changes are needed in HTML code
The code only works on Premium-Sites
Does the version of Ziv Assor works on both ?
1) Free-Sites ?
2) Premium-Sites ?
@arthosmaciel
Data export part is done courtesy your help. Now I am moving to next level of refinement
One more ... Can I have space in Key ? Like instead of "DateofPurchase", I would like to show column in excel as "Date of Purchase". Any pointers ?
@arthosmaciel
I can't thank you enough for solving my issue. It worked. I just needed to make few changes like retrieving ITEMS from results. What a relief ? You are star. All the best for your project as well
Hey @Piush Goyal I mostly use discord, I never try to export data the way your try to do, I believe it's possible. The simplest way to combine both into a single collection. If that's not an option, then you'll need code to reach that outcome. To be honest I'm currently working on a code project myself. You should take a look at the loop function, something like this:
export async function combine_data() { let arr = []; let collection_1_data = await wixData.query("collectionId_1").limit(1000).find() for (var i = 0; i < collection_1_data.length; i++) { let value = collection_1_data[i]; let value2 = await wixData.query("collectionId_2").eq("_id", value._id).limit(1).find() let formated_data = { // collection 1 data startup: value.startup, Currency: value.currency, Noofshares: value.noOfShares, PurchaseDate: value.investmentdate, PurchasePrice: value.purchasePrice, // collection 2 data InvestmentAmount: value2[0].investmentAmount, LastValuationDate: value2[0].lastValuationDate, CurrentSharePrice: value2[0].currentSharePrice, CurrentValuation: value2[0].currentValuation, }; arr.push(formated_data); } console.log(arr) }Hope this can spark some ideas. Best of luck to you.
Hey @Piush Goyal I mostly use discord, I never try to export data the way your try to do, I believe it's possible. The simplest way to combine both into a single collection. If that's not an option, then you'll need code to reach that outcome. To be honest I'm currently working on a code project myself. You should take a look at the loop function, something like this:
export async function combine_data() { let arr = []; let collection_1_data = await wixData.query("collectionId_1").limit(1000).find() for (var i = 0; i < collection_1_data.length; i++) { var value = collection_1_data[i]; let value2 = await wixData.query("collectionId_2").eq("_id", value._id).find() let formated_data = { // collection 1 data startup: value.startup, Currency: value.currency, Noofshares: value.noOfShares, PurchaseDate: value.investmentdate, PurchasePrice: value.purchasePrice, // collection 2 data InvestmentAmount: value2.investmentAmount, LastValuationDate: value2.lastValuationDate, CurrentSharePrice: value2.currentSharePrice, CurrentValuation: value2.currentValuation, }; arr.push(formated_data); } console.log(arr) }Hope this can spark some ideas. Best of luck to you.
@arthosmaciel
is there a way to reach out to you directly. You can help me troubleshoot faster
pls help guys