Export data collection to XLS excel file using code

[WesFarias]

Hey guys, I modified the code to be able to limit the collected columns, the order of the columns, and the formatting of the generated Excel spreadsheet.

Everything is working fine, except for the Excel formatting. Can you assist me with my code?

“excel.jsw”:

const XLSX = require('xlsx'); 

export function exportExcel(worksheet) {
  var wb = XLSX.utils.book_new();
  var ws_name = "Produção GM";
  var ws_data = worksheet;
  var ws = XLSX.utils.aoa_to_sheet(ws_data);

  // Estilos para a primeira linha
  var firstRowStyle = {
  font: { name: "Calibri", sz: 12, bold: true, color: { rgb: "FFFFFF" } },
  fill: { bgColor: { indexed: 31, rgb: "002060" } }
};

  // Aplicar estilos à primeira linha
  var range = XLSX.utils.decode_range(ws['!ref']);
  for (var C = range.s.c; C <= range.e.c; ++C) {
    var cell_address = { c: C, r: 0 }; // r: 0 representa a primeira linha
    var cell_ref = XLSX.utils.encode_cell(cell_address);
    ws[cell_ref].s = firstRowStyle; // Aplicar estilos à célula
  }

  // Estilos para as demais células
  var cellStyle = {
    font: { name: "Calibri", sz: 11, color: { rgb: "000000" } },
    border: {
      top: { style: "thin", color: { rgb: "002060" } },
      bottom: { style: "thin", color: { rgb: "002060" } },
      left: { style: "thin", color: { rgb: "002060" } },
      right: { style: "thin", color: { rgb: "002060" } }
    }
  };

  // Aplicar estilos às demais células
  for (var R = 1; R <= range.e.r; ++R) {
    for (var C = range.s.c; C <= range.e.c; ++C) {
      var cell_address = { c: C, r: R };
      var cell_ref = XLSX.utils.encode_cell(cell_address);
      ws[cell_ref].s = cellStyle; // Aplicar estilos à célula
    }
  }

  // Definir largura das colunas
  ws['!cols'] = [
    { wch: 30 }, // Coluna A: 300px
    { wch: 15 }, // Coluna B
    { wch: 15 }, // Coluna C
    { wch: 15 }, // Coluna D
    { wch: 15 }, // Coluna E
    { wch: 15 }, // Coluna F
    { wch: 15 }, // Coluna G
    { wch: 9 }, // Coluna H: 90px
    { wch: 15 }, // Coluna I
    { wch: 15 }, // Coluna J
    { wch: 40 }, // Coluna K: 400px
    { wch: 12 }, // Coluna L: 120px
    { wch: 12 } // Coluna M: 120px
  ];

  // Adicionar a planilha ao arquivo do Excel
  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 code:

export function excellGM_click(event) {
    var YA = $w("#inicioGM").value.getFullYear();
    var MA = $w("#inicioGM").value.getMonth();
    var DA = $w("#inicioGM").value.getDate();
    var inicio = new Date(YA, MA, DA);
    var YB = $w("#fimGM").value.getFullYear();
    var MB = $w("#fimGM").value.getMonth();
    var DB = $w("#fimGM").value.getDate();
    var fim = new Date(YB, MB, DB + 1);
    let selectGM = $w("#selectGM").value
    let embarcacao = $w("#embarcacaoGM").value.toUpperCase()
    let viagem = $w("#viagemGM").value.toUpperCase()
    let gm;

    if (selectGM == "AMBOS") {
        gm = "GM"
    }
    else {
        gm = selectGM
    }
    
    wixData.query("ProducaoGM")
    .between("data", inicio, fim)
    .contains("guindaste", gm)
    .contains("embarcacao", embarcacao)
    .contains("viagem", viagem)
    .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 => {
                result2[0] = ["Embarcação", "Viagem", "Tipo", "Setor", "Produto", "Cliente", "Data", "Período", "Quantidade", "Toneladas", "Guindasteiro", "Referência", "Guindaste"];
                for (var i = 0; i < rowsNum; i++) {
                    result2[i+1] = [results.items[i].embarcacao, results.items[i].viagem, results.items[i].tipo, results.items[i].setor, results.items[i].produto, results.items[i].cliente, results.items[i].data, results.items[i].periodo, results.items[i].qtde, results.items[i].tons, results.items[i].guindasteiro, results.items[i].empresa, results.items[i].guindaste];
                }
                console.log(result2, "result 2");
                exportExcel(result2).then((excelfile) => {
                    console.log(excelfile, "excelfile");
                    $w('#html1').postMessage(excelfile);
                })
            });
        } else {
            console.log("Sem respostas");
        }
    })
    .catch((err) => {
    let errorMsg = err;
    });
}

HTML Code:

<!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 = "Produção GM";
 /* 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, 'GM Sagres.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>

My spreadsheet should look like this: