Hello!
I'm going to get the straight to the point.
I want to download data from a table. Retool does offer the option to download via excel but I was wondering if there was a way to format it before downloading as we need the same format for future use as well.
Thank you in advance!
Hey there @Katunga,
I remember long ago sharing this with the forum:
It provides a way to format an excel file, including formulas. So this may be a good way for you to consistently and programmatically download an excel file.
Hope this helps!
Best,
Miguel
Hi @Katunga , you could try to create a table then rearrange the columns in the table and hide the columns you don't need. Afterwards, you can add a button that exports it to Excel for download.
Thank you for your replies!
@Tess Thank you, but i already knew that I could download an xlsx file 
I wanted to have a very specific format of the excel.
@Al_Christian_Gochangco Its not just about the columns. The Excel should look like this
@MiguelOrtiz I will try your approach, thank you!
So I've been playing around with it a bit. I came up with that code (mostly chat gpt to be fair) and I would like to have the exact same format as in the Screenshot. the code is the following:
const XLSX = window.XLSX;
const tableData = rt_ufind_table.data;
// ---------------------
// Hardcoded values
// ---------------------
const headerNummer = "123456";
const headerBezeichnung = "Musterinstitut";
const hardcodedEinrichtung = "Forschungsverbund Alpha";
const hardcodedDienstadresse = "Beispielstraße 12, 1010 Wien";
const hardcodedFremdpersonal = "Werkvertrag";
// ---------------------
// Date Formatter
// ---------------------
function formatDate(dateStr) {
if (!dateStr) return "";
const d = new Date(dateStr);
if (isNaN(d)) return dateStr;
const day = String(d.getDate()).padStart(2, '0');
const month = String(d.getMonth() + 1).padStart(2, '0');
const year = d.getFullYear();
return `${day}.${month}.${year}`;
}
// ---------------------
// Worksheet Layout
// ---------------------
const layout = [
["1. Einrichtungsstruktur"],
["Nummer", "Bezeichnung Deutsch"],
[headerNummer, headerBezeichnung],
[],
["2. Personalzuordnungen"],
[],
[
"Persönliche Daten:", "", "", "", "", "", "",
"", // Spacer 1 (column H)
"Arbeitsadresse:", "",
"", // Spacer 2 (column K)
"Fremdpersonal:", "", ""
],
[
"Zuname", "Vorname", "akademische Grade / Amtstitel (optional)",
"Geburtsdatum TT.MM.JJJJ", "SV-Nummer (optional)", "u:account (optional)",
"Geschlecht",
"", // Spacer 1
"Einrichtung", "Dienstadresse",
"", // Spacer 2
"Fremdpersonal-Typ", "Von TT.MM.JJJJ", "Bis TT.MM.JJJJ"
]
];
// ---------------------
// Data Rows
// ---------------------
const dataRows = tableData.map(row => [
row.last_name || "",
row.first_name || "",
row.title || "",
formatDate(row.birth_date),
row.social_number || "",
row.u_account || "",
row.gender || "",
"", // Spacer 1
hardcodedEinrichtung,
hardcodedDienstadresse,
"", // Spacer 2
hardcodedFremdpersonal,
formatDate(row.start_date),
formatDate(row.end_date)
]);
const worksheetData = [...layout, ...dataRows];
const worksheet = XLSX.utils.aoa_to_sheet(worksheetData);
// ---------------------
// Merge Ranges (adjusted for spacers)
// ---------------------
worksheet["!merges"] = [
{ s: { r: 0, c: 0 }, e: { r: 0, c: 13 } }, // 1. Einrichtungsstruktur
{ s: { r: 4, c: 0 }, e: { r: 4, c: 13 } }, // 2. Personalzuordnungen
{ s: { r: 6, c: 0 }, e: { r: 6, c: 6 } }, // Persönliche Daten
{ s: { r: 6, c: 8 }, e: { r: 6, c: 9 } }, // Arbeitsadresse
{ s: { r: 6, c: 11 }, e: { r: 6, c: 13 } } // Fremdpersonal
];
// ---------------------
// Column Widths (including spacers)
// ---------------------
worksheet['!cols'] = [
{ wch: 20 }, { wch: 20 }, { wch: 25 },
{ wch: 18 }, { wch: 20 }, { wch: 20 },
{ wch: 15 },
{ wch: 3 }, // Spacer 1
{ wch: 30 }, { wch: 30 },
{ wch: 3 }, // Spacer 2
{ wch: 25 }, { wch: 15 }, { wch: 15 }
];
// ---------------------
// Styling
// ---------------------
for (const cell in worksheet) {
if (!cell.startsWith('!')) {
const cellRef = XLSX.utils.decode_cell(cell);
const row = cellRef.r;
if (row === 0 || row === 4) {
worksheet[cell].s = {
font: { bold: true, sz: 12 },
fill: { fgColor: { rgb: "D9E1F2" } },
alignment: { horizontal: "left", vertical: "center" }
};
}
if (row === 6) {
worksheet[cell].s = {
font: { bold: true, color: { rgb: "FFFFFF" } },
fill: { fgColor: { rgb: "4F81BD" } },
alignment: { horizontal: "center", vertical: "center" }
};
}
if (row === 7) {
worksheet[cell].s = {
font: { bold: true },
fill: { fgColor: { rgb: "B8CCE4" } },
alignment: { horizontal: "center", vertical: "center", wrapText: true }
};
}
}
}
// ---------------------
// Workbook Export
// ---------------------
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Mitarbeiterliste");
workbook.Workbook = { CalcPr: { fullCalcOnLoad: true } };
const excelBuffer = XLSX.write(workbook, {
bookType: 'xlsx',
type: 'binary',
cellStyles: true
});
const s2ab = (s) => {
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
};
const blob = new Blob([s2ab(excelBuffer)], { type: 'application/octet-stream' });
const url = window.URL.createObjectURL(blob);
const fileName = `Mitarbeiterliste_${new Date().toISOString().slice(0, 10)}.xlsx`;
const link = document.createElement('a');
link.href = url;
link.setAttribute('download', fileName);
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
(its for a german form, hence the german titles).
The issue is that I cant colour the cells or find a way to have the spacer columns.
My assumption is that this is due to the open-source version of the library. If thats the case, can you recommend some alternative and if it is not: can you help me with the formatting?
Thank you again in advance!
Have you tried using ExcelJS: GitHub - exceljs/exceljs: Excel Workbook Manager - I've used it several times to export fully-formatted Excel files, including multiple worksheets, hidden columns, filtering etc. I just added it as an external library in the apps that use it.
Thank you that was the solution!