<!doctype html>
<html lang="de">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width,initial-scale=1" />
<title>Export Tabelle mit ExcelJS</title>
</head>
<body>
<table id="tblTestData">
<caption>Tabelle 1</caption>
<thead>
<tr>
<th class="sub">Name</th>
<th class="sub">Vorname</th>
<th class="sub">Alter</th>
</tr>
</thead>
<tbody>
<tr><td>Müller</td><td>Horst</td><td>50</td></tr>
<tr><td>Lehmann</td><td>Eduard</td><td>64</td></tr>
<tr><td>Meier</td><td>Inge</td><td>71</td></tr>
</tbody>
</table>
<div class="controls">
<button id="btnExport" class="btn" type="button">Tabelle exportieren</button>
</div>
<!-- ExcelJS und FileSaver (CDN) -->
<script src="https://cdn.jsdelivr.net/npm/exceljs/dist/exceljs.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script>
<script>
(function () {
const btn = document.getElementById('btnExport');
// Näherung: 1 Excel-Char ≈ 7 Pixel
function pxToExcelChars(px) {
return Math.ceil(px / 7);
}
// charsForLen mit padding +5
function charsForLen(len, minChars) {
return Math.min(Math.max(len + 5, minChars), 50);
}
// Hilfsfunktion: ARGB aus hex (z.B. "#2f6f9f" -> "FF2F6F9F")
function hexToARGB(hex) {
if (!hex) return 'FFFFFFFF';
const h = hex.replace('#','').toUpperCase();
return (h.length === 6) ? ('FF' + h) : ('FF' + h.slice(-6));
}
btn.addEventListener('click', async function () {
// HTML-Tabelle
const table = document.getElementById('tblTestData');
// Workbook
const workbook = new ExcelJS.Workbook();
// Worksheet mit Bezeichner 'Tabelle1'
const sheet = workbook.addWorksheet('Tabelle1');
// Farben
const headerColor = '#2f6f9f'; // Farbe Header-Zeile
const headerFontColor = '#FFFFFF'; // Farbe Header Schrift
const rowOddColor = '#fbfbfb'; // Farbe gerade Zeile
const rowEvenColor = '#FFFFFF'; // Farbe ungerade Zeile
// Header-Zeile
const headerCells = Array.from(table.tHead.rows[0].cells).map(th => th.textContent.trim());
sheet.addRow(headerCells);
// Datenzeilen aus HTML-Tabelle
const tbody = table.tBodies[0];
const iCols = table.tHead.rows[0].cells.length;
const iRows = tbody.rows.length;
for (let r = 0; r < iRows; r++) {
const cells = Array.from(tbody.rows[r].cells).map(td => td.textContent.trim());
sheet.addRow(cells);
}
// Beispiel: Zellen mergen
//sheet.mergeCells('A1:C1'); // A-C
const headerARGB = hexToARGB(headerColor);
const headerFontARGB = hexToARGB(headerFontColor);
const oddARGB = hexToARGB(rowOddColor);
const evenARGB = hexToARGB(rowEvenColor);
// Setze Arial und Standardformat für alle Zellen
sheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
cell.font = cell.font || {};
cell.font.name = 'Arial';
// Default alignment left for non-specified cells
cell.alignment = cell.alignment || { vertical: 'middle', horizontal: 'left' };
});
});
// Formatierung für Header
for (let c = 1; c <= iCols; c++) {
const cell1 = sheet.getCell(1, c);
cell1.font = Object.assign({}, cell1.font, { name: 'Arial', bold: true, color: { argb: headerFontARGB } });
cell1.alignment = { vertical: 'middle', horizontal: 'center' };
cell1.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: headerARGB } };
}
// für gemergedte Zellen (D1, I1, N1) loopen
// ['D1','I1','N1'].forEach(addr => {
// const cell = sheet.getCell(addr);
// cell...
//});
// Datenzeilen zentrieren
const firstDataRow = 1;
const startCol = 3; // C
const endCol = 3; // C
for (let r = firstDataRow; r <= sheet.rowCount; r++) {
const row = sheet.getRow(r);
for (let c = startCol; c <= endCol; c++) {
const cell = row.getCell(c);
cell.alignment = Object.assign({}, cell.alignment || {}, { horizontal: 'center', vertical: 'middle' });
cell.font = Object.assign({}, cell.font || {}, { name: 'Arial' });
}
}
// alternierende Datenzeilen-Farben
for (let r = 0; r < tbody.rows.length; r++) {
const excelRowIndex = 2 + r; // ab der zweiten Zeile (ohne Header)
const isOdd = (r % 2 === 0); // r=0 -> erste data row -> odd style (#fbfbfb)
const fillColor = isOdd ? oddARGB : evenARGB;
for (let c = 1; c <= iCols; c++) {
const cell = sheet.getCell(excelRowIndex, c);
cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: fillColor } };
}
}
// Spaltenbreiten: orientiert an der Header-Zeile (padding +5)
const headerLens = headerCells.map(h => h ? String(h).length : 0);
function maxLen(startIdx, endIdx) {
let m = 0;
for (let i = startIdx; i <= endIdx; i++) {
if (headerLens[i] > m) m = headerLens[i];
}
return m;
}
// Spaltenbreite
const maxColLen = maxLen(0, iCols - 1); // A(0) .. C(2)
// Mindestbreite 20px in Excel-Char-Einheiten
const minChars = pxToExcelChars(20);
// Spaltenbreite mit Padding (+5 px)
const maxColLenPad = charsForLen(maxColLen, minChars);
const cols = [];
for (let i = 0; i < iCols; i++) {
cols.push({ width: maxColLenPad });
}
sheet.columns = cols;
// Rahmen
const thinBorder = { style: 'thin' };
const fullBorder = { top: thinBorder, left: thinBorder, bottom: thinBorder, right: thinBorder };
// Iteriere über alle Zeilen und Spalten und setze border
for (let r = 1; r <= sheet.rowCount; r++) {
const row = sheet.getRow(r);
for (let c = 1; c <= iCols; c++) {
const cell = row.getCell(c);
cell.border = fullBorder;
}
}
// Datei: Export über Buffer für Kompatibiliät mit allen Browsern
const buf = await workbook.xlsx.writeBuffer();
const filename = `Test.xlsx`;
const blob = new Blob([buf], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
saveAs(blob, filename);
});
})();
</script>
</body>
</html>