Regelmässiger Historienexport als Excel-Tabelle
-
Hallo,
hier ein recht banaler Anwendungsfall. Ich möchte gerne für einige Datenpunkte auch außerhalb von iobroker bestimmte Kerndaten haben, um damit z.B. in Excel rumspielen zu können.Dabei will ich nicht jedes Datenfragment aus der History-DB pro Datenpunkt haben, sondern mir reicht hier ein Wert pro Tag, i.d.R. der Höchste Zählerstand eines Wertes.
Das ganze will ich einfach automatisch einmal pro Monat zugesendet bekommen per Email.
Als Zusatzfeature, das ich über einen extra Datenpunkt steuern kann, kann das Ding auch sofort ein ganzes Jahr in dieser Form exportieren.
Vielleicht habt Ihr ja ähnliche Anforderungen, dann könnt Ihr hierdrauf aufsetzen.
Ich hoffe, alles ist hinreichend sprechend und auskommentiert, dass es gut lesbar ist.
Das Logging kann man ja leicht entfernen, wenn es nervt.
/** * ================================================================ * 📊 IOBROKER MONATLICHER HISTORIEN-BERICHT ALS EXCEL PER E-MAIL * ================================================================ * * 🎯 ZWECK DES SKRIPTS: * Dieses Skript erstellt automatisch am Anfang jedes Monats eine Excel-Datei * mit allen Tageswerten des VORMONATS für konfigurierte Datenpunkte und sendet sie per E-Mail. * Alternativ kann es auch manuell gestartet werden, dann werden die Daten des AKTUELLEN Monats ausgegeben. * Bei Bedarf kann auch ein Jahresbericht (365 Tage) erstellt werden. * * ✅ WAS ES TUT: * 1. Liest Email-Absender und -Empfänger aus konfigurierbaren Datenpunkten. * 2. Ermittelt automatisch den richtigen Zeitraum (Vormonat, aktueller Monat oder 365 Tage). * 3. Holt für jeden konfigurierten Datenpunkt die Historie des Zeitraums. * 4. Aggregiert die Werte pro Tag (konfigurierbar pro Spalte). * 5. Wendet optional Multiplikator an (z. B. 1000 für Liter statt m³). * 6. Legt fest, wieviele Nachkommastellen verwendet werden. * 7. Kann leere Werte mit 0 auffüllen (optional). * 8. Erstellt eine ECHTE Excel-Datei (.xlsx) mit formatierter Tabelle. * 9. Sendet die Excel-Datei per E-Mail mit konfigurierbarem Betreff. * * Gebastelt 2025 von BertDerKleine * * 🧩 KONFIGURIERBAR: * - Liste der Datenpunkte mit: * - id: Datenpunkt-Name * - agg: 'max|min|avg|sum' * - spaltenName: Anzeigename in Excel * - nachkommastellen: Anzahl der Nachkommastellen (optional, default: 2) * - multiplikator: Faktor für Multiplikation (optional, default: 1) * - fuellenMitNull: Ob leere Werte mit 0 gefüllt werden sollen (optional, default: false) * * 📥 VORAUSSETZUNGEN: * - History-Adapter (history.0, sql.0, etc.) muss aktiv sein * - Email-Adapter (email.0) muss konfiguriert und verbunden sein * - Folgende Konfigurations-Datenpunkte müssen existieren: * - 0_userdata.0.Email-Absender (string) * - 0_userdata.0.Email-Empfaenger (string) * - 0_userdata.0.Email-Betreff (string) * - 0_userdata.0.VollJahresDaten (zahl: 0,1,2,3 Bei 0 läuft das Skript normal, bei 1/2/3 werden die Daten von 1-3 Jahren ausgegeben) * - exceljs-Library muss installiert sein: * → Im ioBroker JavaScript-Adapter in den Instanzeinstellungen unter * "additional npm modules" eintragen: "exceljs" * * 📤 AUSGABE: * - Echte Excel-Datei (.xlsx) mit formatierter Tabelle * - Datum im Format TT.MM.JJJJ * - Zahlen als echte Excel-Zahlen (kein Text) * - Automatische Spaltenbreite * - Abwechselnde Zeilenfarben (Zebra-Stripes) * - Auto-Filter in der Kopfzeile * - Zwei Blätter: Daten + Zusammenfassung * * 🚀 AUTOMATISIERUNG: * Plane dieses Skript am 1. jedes Monats um 00:05 Uhr: * * ================================================================ * 🔧 KONFIGURATION – HIER ANPASSEN! * ================================================================ */ // ✅ exceljs-Library laden (muss installiert sein: exceljs in "additional npm modules") try { const ExcelJS = require('exceljs'); log('✅ ExcelJS erfolgreich geladen!', 'info'); } catch (e) { log(`❌ Fehler beim Laden von ExcelJS: ${e.message}`, 'error'); log('💡 Lösung: ExcelJS in den Instanzeinstellungen des JavaScript-Adapters unter "additional npm modules" hinzufügen', 'error'); return; } const ExcelJS = require('exceljs'); // Liste der zu verarbeitenden Datenpunkte // Jeder Eintrag: { id: '...', agg: 'max|min|avg|sum', spaltenName: '...', nachkommastellen?: number, multiplikator?: number, fuellenMitNull?: boolean } const Datenpunkte = [ { id: 'alias.0.Aussentemperatur', agg: 'max', spaltenName: 'Höchste Aussentemperatur', nachkommastellen: 1, multiplikator: 1, fuellenMitNull: false }, { id: 'alias.0.Aussentemperatur', agg: 'min', spaltenName: 'Niedrigste Aussentemperatur', nachkommastellen: 1, multiplikator: 1, fuellenMitNull: false }, { id: 'alias.0.Regen', agg: 'max', spaltenName: 'Regenmenge', nachkommastellen: 1, multiplikator: 1, fuellenMitNull: true }, { id: 'alias.0.GasH', agg: 'max', spaltenName: 'Gas für Heizung', nachkommastellen: 1, multiplikator: 1, fuellenMitNull: true }, { id: 'alias.0.GasW', agg: 'max', spaltenName: 'Gas für Warmwasser', nachkommastellen: 1, multiplikator: 1, fuellenMitNull: true }, { id: 'statistics.0.save.sumDelta.alias.0.Wasser.day', agg: 'max', spaltenName: 'Wasserverbrauch', nachkommastellen: 0, multiplikator: 1000, fuellenMitNull: true }, // in Liter { id: 'statistics.0.save.sumDelta.alias.0.PV_Erzeugung.day', agg: 'max', spaltenName: 'Strom-PV-Erzeugung', nachkommastellen: 0, multiplikator: 1, fuellenMitNull: false }, { id: 'statistics.0.save.sumDelta.alias.0.Netzbezug.day', agg: 'max', spaltenName: 'Strom-Netzbezug', nachkommastellen: 2, multiplikator: 1, fuellenMitNull: false }, { id: 'statistics.0.save.sumDelta.alias.0.Netzeinspeisung.day', agg: 'max', spaltenName: 'Strom-Netzeinspeisung', nachkommastellen: 2, multiplikator: 1, fuellenMitNull: false }, { id: 'statistics.0.save.sumDelta.0_userdata.0.Verbrauch.day', agg: 'max', spaltenName: 'Strom-Verbrauch', nachkommastellen: 2, multiplikator: 1, fuellenMitNull: false } ]; // Datenpunkte für Email-Konfiguration const EmailAbsenderDatenpunkt = '0_userdata.0.Email-Absender'; const EmailEmpfaengerDatenpunkt = '0_userdata.0.Email-Empfaenger'; const EmailBetreffDatenpunkt = '0_userdata.0.Email-Betreff'; const VollJahresDatenDatenpunkt = '0_userdata.0.VollJahresDaten'; // "YES" oder "NO" // Adapter-Instanzen (anpassen, falls abweichend) const EmailAdapterInstanz = 'email.0'; const HistoryAdapterInstanz = 'history.0'; // z. B. 'sql.0', 'influxdb.0' // Globale Variablen (lokal im Skript) let alleAggregiertenDaten = {}; let sortierteTage = []; /** * ================================================================ * HILFSFUNKTIONEN * ================================================================ */ function formatiereDatumFuerExcel(datum) { const jahr = datum.getFullYear(); const monat = (datum.getMonth() + 1).toString().padStart(2, '0'); const tag = datum.getDate().toString().padStart(2, '0'); return `${tag}.${monat}.${jahr}`; } function bereinigeWerte(werte) { return werte.filter(wert => wert !== null && wert !== undefined && !isNaN(wert)); } // Funktion: Historische Daten abrufen (optimiert für lange Zeiträume) async function holeHistorie(id, start, ende) { // ✅ Schätzung: wie viele Werte könnten es sein? const geschätzteTage = Math.ceil((ende.getTime() - start.getTime()) / (24 * 60 * 60 * 1000)); const geschätzteWerteProTag = 150; // Anpassen je nach deinen Daten const geschätzteAnzahl = geschätzteTage * geschätzteWerteProTag; // ✅ Sicherstellen, dass genug Werte abgerufen werden const count = Math.min(200000, Math.max(50000, geschätzteAnzahl)); // Max 200.000, Min 50.000 return new Promise((resolve, reject) => { sendTo(HistoryAdapterInstanz, 'getHistory', { id: id, options: { start: start.getTime(), end: ende.getTime(), aggregate: 'none', count: count } }, (ergebnis) => { if (!ergebnis || !ergebnis.result || ergebnis.error) { reject(new Error(ergebnis?.error || 'Unbekannter Fehler beim History-Abruf')); } else { resolve(ergebnis.result); } }); }); } // Funktion: Einheit aus Datenpunkt auslesen function leseEinheitAusDatenpunkt(dpId) { try { const obj = getObject(dpId); if (obj && obj.common && obj.common.unit) { return obj.common.unit; } } catch (e) { log(`⚠️ Fehler beim Lesen der Einheit von ${dpId}: ${e.message}`, 'warn'); } return ''; // Falls keine Einheit vorhanden } /** * ================================================================ * HAUPTPROGRAMM: EXCEL EXPORT * ================================================================ */ async function HauptprogrammExcelExport() { try { log('🚀 Starte Monatsbericht...', 'info'); // ✅ Email-Konfiguration aus Datenpunkten lesen const absenderState = getState(EmailAbsenderDatenpunkt); const empfaengerState = getState(EmailEmpfaengerDatenpunkt); const betreffState = getState(EmailBetreffDatenpunkt); const vollJahresState = getState(VollJahresDatenDatenpunkt); if (!absenderState || !absenderState.val) { log(`❌ Email-Absender-Datenpunkt "${EmailAbsenderDatenpunkt}" nicht gefunden oder leer!`, 'error'); return; } if (!empfaengerState || !empfaengerState.val) { log(`❌ Email-Empfänger-Datenpunkt "${EmailEmpfaengerDatenpunkt}" nicht gefunden oder leer!`, 'error'); return; } if (!betreffState || !betreffState.val) { log(`❌ Email-Betreff-Datenpunkt "${EmailBetreffDatenpunkt}" nicht gefunden oder leer!`, 'error'); return; } const emailAbsender = absenderState.val.toString(); const emailEmpfaenger = empfaengerState.val.toString(); const emailBetreff = betreffState.val.toString(); const vollJahresDaten = vollJahresState?.val?.toString().toUpperCase(); // ✅ Neue Jahresausgabe-Steuerung let jahreZurueck = 0; // Standard: normaler Modus if (vollJahresDaten === '1' || vollJahresDaten === '2' || vollJahresDaten === '3') { jahreZurueck = parseInt(vollJahresDaten, 10); } log(`✅ Email von: ${emailAbsender} → an: ${emailEmpfaenger}`, 'info'); log(`✅ Betreff: ${emailBetreff}`, 'info'); log(`✅ Jahresausgabe-Modus: ${jahreZurueck > 0 ? jahreZurueck + ' Jahre' : 'Normal'}`, 'info'); // ✅ Zeitraum berechnen const jetzt = new Date(); let startDatum, endDatum; if (jahreZurueck > 0) { // X Jahre zurück startDatum = new Date(jetzt); startDatum.setFullYear(startDatum.getFullYear() - jahreZurueck); startDatum.setHours(0, 0, 0, 0); endDatum = new Date(jetzt); endDatum.setHours(23, 59, 59, 999); log(`📅 Zeitraum: Letzte ${jahreZurueck} Jahr(e) (${startDatum.toLocaleDateString()} bis ${endDatum.toLocaleDateString()})`, 'info'); } else { // Prüfen, ob automatisch (1. des Monats) oder manuell const istAutomatisch = jetzt.getDate() === 1 && jetzt.getHours() === 0 && jetzt.getMinutes() <= 10; if (istAutomatisch) { // Vormonat startDatum = new Date(jetzt.getFullYear(), jetzt.getMonth() - 1, 1); endDatum = new Date(jetzt.getFullYear(), jetzt.getMonth(), 0); endDatum.setHours(23, 59, 59, 999); log(`📅 Zeitraum: Vormonat (${startDatum.toLocaleDateString()} bis ${endDatum.toLocaleDateString()})`, 'info'); } else { // Aktueller Monat (manueller Start) startDatum = new Date(jetzt.getFullYear(), jetzt.getMonth(), 1); endDatum = new Date(jetzt); endDatum.setHours(23, 59, 59, 999); log(`📅 Zeitraum: Aktueller Monat (${startDatum.toLocaleDateString()} bis heute)`, 'info'); } } // ✅ Für jeden Datenpunkt Historie abrufen + aggregieren alleAggregiertenDaten = {}; // Lokale Variable for (const dp of Datenpunkte) { log(`⏳ Verarbeite Datenpunkt: ${dp.id} → Spalte: "${dp.spaltenName}" (Aggregation: ${dp.agg})`, 'info'); let historieDaten; try { // ✅ Dynamische Anzahl von Werten basierend auf Zeitraum const geschätzteTage = Math.ceil((endDatum.getTime() - startDatum.getTime()) / (1000 * 60 * 60 * 24)); const geschätzteAnzahl = geschätzteTage * 10; // Annahme: max. 10 Werte pro Tag const count = Math.max(20000, geschätzteAnzahl); historieDaten = await holeHistorie(dp.id, startDatum, endDatum); // Kein count mehr } catch (fehler) { log(`⚠️ Fehler beim Abruf von ${dp.id}: ${fehler.message}`, 'warn'); continue; } if (!historieDaten || historieDaten.length === 0) { log(`⚠️ Keine Daten für ${dp.id} gefunden.`, 'warn'); continue; } // Gruppieren nach Tag const gruppiertNachTag = {}; historieDaten.forEach(eintrag => { if (eintrag && eintrag.val !== null && !isNaN(eintrag.val)) { const eintragDatum = new Date(eintrag.ts); const tagSchluessel = `${eintragDatum.getFullYear()}-${(eintragDatum.getMonth() + 1).toString().padStart(2, '0')}-${eintragDatum.getDate().toString().padStart(2, '0')}`; if (!gruppiertNachTag[tagSchluessel]) gruppiertNachTag[tagSchluessel] = []; gruppiertNachTag[tagSchluessel].push(eintrag.val); } }); // Pro Tag aggregieren for (const [tagSchluessel, werte] of Object.entries(gruppiertNachTag)) { const bereinigteWerte = bereinigeWerte(werte); if (bereinigteWerte.length === 0) continue; let aggregierterWert; const aggTyp = (dp.agg || 'max').toLowerCase(); switch (aggTyp) { case 'max': aggregierterWert = Math.max(...bereinigteWerte); break; case 'min': aggregierterWert = Math.min(...bereinigteWerte); break; case 'avg': aggregierterWert = bereinigteWerte.reduce((summe, wert) => summe + wert, 0) / bereinigteWerte.length; break; case 'sum': aggregierterWert = bereinigteWerte.reduce((summe, wert) => summe + wert, 0); break; default: log(`⚠️ Unbekannter Aggregationstyp "${aggTyp}" für ${dp.id}. Nutze 'max'.`, 'warn'); aggregierterWert = Math.max(...bereinigteWerte); } // Multiplikator anwenden aggregierterWert = aggregierterWert * (dp.multiplikator || 1); // Eindeutigen Schlüssel erstellen const eindeutigerSchluessel = `${dp.id}__${dp.agg}__${dp.spaltenName}`; if (!alleAggregiertenDaten[tagSchluessel]) alleAggregiertenDaten[tagSchluessel] = {}; alleAggregiertenDaten[tagSchluessel][eindeutigerSchluessel] = aggregierterWert; } } if (Object.keys(alleAggregiertenDaten).length === 0) { log('❌ Keine Daten für irgendeinen Datenpunkt gefunden!', 'error'); return; } // ✅ Sortierte Tage sortierteTage = Object.keys(alleAggregiertenDaten).sort(); log(`✅ Daten für ${sortierteTage.length} Tage aggregiert.`, 'info'); // ✅ Excel-Datei erstellen const excelBase64 = await erstelleExcelDatei(); log('✅ Excel-Datei mit formatierter Tabelle erfolgreich erstellt.', 'info'); // ✅ Intelligenter Dateiname const zeitstempel = new Date().toISOString().replace(/[:.]/g, '-').slice(0, 19); let dateiname; if (jahreZurueck > 0) { dateiname = `${jahreZurueck}-Jahresbericht_${zeitstempel}.xlsx`; } else { // Prüfen, ob automatisch (1. des Monats) oder manuell const jetzt = new Date(); const istAutomatisch = jetzt.getDate() === 1 && jetzt.getHours() === 0 && jetzt.getMinutes() <= 10; if (istAutomatisch) { dateiname = `Vormonatsbericht_${zeitstempel}.xlsx`; } else { dateiname = `Monatsbericht_${zeitstempel}.xlsx`; } } // ✅ E-Mail senden sendeEmailMitAnhang(excelBase64, dateiname, emailAbsender, emailEmpfaenger, emailBetreff); // ✅ Timer bereinigen (ohne Skript zu beenden) setTimeout(() => { log('✅ Aktuelle Ausführung beendet - Scheduler bleibt aktiv', 'info'); // Timer bereinigen for (let i = 1; i < 1000; i++) { clearTimeout(i); clearInterval(i); } }, 3000); } catch (fehler) { log(`❌ Fehler im Hauptskript: ${fehler.message}`, 'error'); } } // Funktion: Echte Excel-Datei mit formatierter Tabelle erstellen async function erstelleExcelDatei() { try { log('⏳ Erstelle Excel-Datei...', 'info'); // Erstelle ein neues Workbook const workbook = new ExcelJS.Workbook(); // ✅ Dynamischen Tabellennamen erstellen let tabellenName = "Daten"; if (sortierteTage.length > 0) { const erstesDatum = sortierteTage[0]; const [jahr, monat] = erstesDatum.split('-'); tabellenName = `Daten ${jahr}-${monat.padStart(2, '0')}`; } // ✅ Arbeitsblatt erstellen const worksheet = workbook.addWorksheet(tabellenName); // Header-Zeile const ueberschriften = ['Datum', ...Datenpunkte.map(dp => dp.spaltenName)]; worksheet.addRow(ueberschriften); // ✅ Daten sammeln für Tabelle const tabellenDaten = []; // Datenzeilen for (const tagSchluessel of sortierteTage) { const teile = tagSchluessel.split('-').map(Number); const datumObj = new Date(teile[0], teile[1] - 1, teile[2]); const zeile = [formatiereDatumFuerExcel(datumObj)]; for (let dpIndex = 0; dpIndex < Datenpunkte.length; dpIndex++) { const dp = Datenpunkte[dpIndex]; const eindeutigerSchluessel = `${dp.id}__${dp.agg}__${dp.spaltenName}`; let wert = alleAggregiertenDaten[tagSchluessel]?.[eindeutigerSchluessel]; if (wert !== undefined) { // ✅ ACHTUNG: Multiplikator wurde bereits in Hauptfunktion angewendet! // Hier nur noch Nachkommastellen anwenden const nachkommastellen = dp.nachkommastellen !== undefined ? dp.nachkommastellen : 2; wert = parseFloat(wert.toFixed(nachkommastellen)); zeile.push(wert); } else { // Auffüllen mit Null, wenn konfiguriert if (dp.fuellenMitNull) { zeile.push(0); } else { zeile.push(null); } } } worksheet.addRow(zeile); tabellenDaten.push(zeile); } log(`✅ ${sortierteTage.length} Datenzeilen vorbereitet`, 'info'); // ✅ ECHTE EXCEL-TABELLE ERSTELLEN if (tabellenDaten.length > 0) { worksheet.addTable({ name: 'DatenTabelle', ref: 'A1', headerRow: true, totalsRow: false, style: { theme: 'TableStyleMedium2', showRowStripes: true, showColumnStripes: false }, columns: ueberschriften.map(name => ({ name: name, filterButton: true })), rows: tabellenDaten }); } // ✅ Spaltenbreiten und Zahlenformate worksheet.columns.forEach(column => { column.width = 20; }); // Zahlenformate für Daten-Spalten (ab Spalte B) for (let i = 1; i < ueberschriften.length; i++) { const dp = Datenpunkte[i - 1]; // i-1 weil erste Spalte Datum ist const nachkommastellen = dp.nachkommastellen !== undefined ? dp.nachkommastellen : 2; const einheit = leseEinheitAusDatenpunkt(dp.id); const column = worksheet.getColumn(i + 1); // +1 weil Excel-Spalten bei A=1 beginnen column.numFmt = nachkommastellen === 0 ? `0 " ${einheit}"` : `0.${'0'.repeat(nachkommastellen)} " ${einheit}"`; } // ✅ Zweites Blatt: Zusammenfassung const zusammenfassungBlatt = workbook.addWorksheet('Zusammenfassung'); // Überschrift in Zelle A1 const titelZelle = zusammenfassungBlatt.getCell('A1'); titelZelle.value = 'Zusammenfassung'; titelZelle.font = { bold: true, size: 14, color: { argb: 'FFFFFFFF' } }; titelZelle.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF4472C4' } }; // Leerzeile in A2 zusammenfassungBlatt.getCell('A2').value = ''; // Header-Zeile in Zeile 3 const headerZellen = ['A3', 'B3', 'C3', 'D3', 'E3']; const headerWerte = ['Datenpunkt', 'Minimum', 'Maximum', 'Durchschnitt', 'Summe']; for (let i = 0; i < headerZellen.length; i++) { const zelle = zusammenfassungBlatt.getCell(headerZellen[i]); zelle.value = headerWerte[i]; zelle.font = { bold: true }; zelle.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFD9E1F2' } }; } // Daten für Zusammenfassung sammeln und eintragen let aktuelleZeile = 4; // Start ab Zeile 4 for (let i = 0; i < Datenpunkte.length; i++) { const dp = Datenpunkte[i]; // Werte sammeln und bereinigen const werte = []; for (const tag of sortierteTage) { const eindeutigerSchluessel = `${dp.id}__${dp.agg}__${dp.spaltenName}`; let val = alleAggregiertenDaten[tag]?.[eindeutigerSchluessel]; if (val !== undefined && val !== null && !isNaN(val)) { // ✅ ACHTUNG: Multiplikator wurde bereits in Hauptfunktion angewendet! // Hier nur noch Nachkommastellen anwenden const nachkommastellen = dp.nachkommastellen !== undefined ? dp.nachkommastellen : 2; val = parseFloat(val.toFixed(nachkommastellen)); werte.push(val); } } if (werte.length === 0) { log(`🔍 Keine gültigen Werte für ${dp.spaltenName}`, 'warn'); continue; } // Berechnungen const min = Math.min(...werte); const max = Math.max(...werte); const avg = werte.reduce((sum, val) => sum + val, 0) / werte.length; const summe = werte.reduce((sum, val) => sum + val, 0); // Werte direkt in Zellen schreiben zusammenfassungBlatt.getCell(`A${aktuelleZeile}`).value = dp.spaltenName; zusammenfassungBlatt.getCell(`B${aktuelleZeile}`).value = min; zusammenfassungBlatt.getCell(`C${aktuelleZeile}`).value = max; zusammenfassungBlatt.getCell(`D${aktuelleZeile}`).value = avg; zusammenfassungBlatt.getCell(`E${aktuelleZeile}`).value = summe; // Zahlen formatieren for (let col of ['B', 'C', 'D', 'E']) { const zelle = zusammenfassungBlatt.getCell(`${col}${aktuelleZeile}`); const nachkommastellen = dp.nachkommastellen !== undefined ? dp.nachkommastellen : 2; const einheit = leseEinheitAusDatenpunkt(dp.id); zelle.numFmt = nachkommastellen === 0 ? `0 " ${einheit}"` : `0.${'0'.repeat(nachkommastellen)} " ${einheit}"`; } aktuelleZeile++; } // Falls keine Daten gefunden wurden if (aktuelleZeile === 4) { zusammenfassungBlatt.getCell('A4').value = 'Keine Daten verfügbar'; zusammenfassungBlatt.getCell('A4').font = { italic: true, color: { argb: 'FF808080' } }; } // Spaltenbreiten für Zusammenfassung for (let col of ['A', 'B', 'C', 'D', 'E']) { zusammenfassungBlatt.getColumn(col).width = 20; } // ✅ IN BINÄREN BUFFER UMWANDELN const uint8Array = await workbook.xlsx.writeBuffer(); const buffer = Buffer.from(uint8Array); const base64 = buffer.toString('base64'); log('✅ Excel-Datei erfolgreich erstellt', 'info'); return base64; } catch (fehler) { log(`❌ Fehler beim Erstellen der Excel-Datei: ${fehler.message}`, 'error'); log(`❌ Stack: ${fehler.stack}`, 'error'); throw fehler; } } // Funktion: E-Mail senden function sendeEmailMitAnhang(excelBase64, dateiname, absenderAdresse, empfaengerAdresse, betreff) { const text = `Hallo,\n\nIm Anhang findest du den monatlichen Aggregationsbericht als formatierte Excel-Tabelle.\n\nAutomatisch generiert von ioBroker.\nDatum: ${new Date().toLocaleString()}`; sendTo(EmailAdapterInstanz, { from: absenderAdresse, to: empfaengerAdresse, subject: betreff, text: text, attachments: [{ filename: dateiname, content: excelBase64, encoding: 'base64', contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }] }, (fehler, ergebnis) => { if (fehler && typeof fehler === 'object' && fehler.result && fehler.result.includes('250')) { log(`✅ E-Mail erfolgreich gesendet (Server: ${fehler.result})`, 'info'); } else if (fehler) { log(`❌ E-Mail-Fehler: ${JSON.stringify(fehler)}`, 'error'); } else { log(`✅ E-Mail mit Anhang "${dateiname}" gesendet.`, 'info'); } }); } /** * ================================================================ * SCHEDULE + MANUELLEN START * ================================================================ */ // Zeitplanung: 1. Tag jedes Monats um 00:05 Uhr schedule('5 0 1 * *', HauptprogrammExcelExport); // Manueller Start: Nur wenn Skript neu geladen wird und Adapter schon läuft if (existsState('system.adapter.javascript.0.uptime') && getState('system.adapter.javascript.0.uptime').val > 60) { log('🔁 Manueller Start des Skripts erkannt', 'info'); setTimeout(HauptprogrammExcelExport, 2000); // Kleine Verzögerung für sicheres Starten } log('✅ Skript geladen - bereit für manuellen oder zeitgesteuerten Start', 'info'); -
@bertderkleine sagte in Regelmässiger Historienexport als Excel-Tabelle:
try { const ExcelJS = require('exceljs'); log('
ExcelJS erfolgreich geladen!', 'info'); } catch (e) { log(❌ Fehler beim Laden von ExcelJS: ${e.message}, 'error'); log('
Lösung: Führe aus: cd /opt/iobroker && npm install exceljs', 'error'); return; }Frage:
Warum npm ?
Müsste es nicht reichen "exceljs" bei javascript in der Instanz einzutragen.Ansonsten:
Sehr gute Arbeit.
Hat bestimmt sehr viel Zeit gekostet.
Habe auch schon viel mit "exceljs" gemacht. -
@bahnuhr sagte in Regelmässiger Historienexport als Excel-Tabelle:
Frage:
Warum npm ?
Müsste es nicht reichen "exceljs" bei javascript in der Instanz einzutragen.Ehrlicherweise muss ich sagen, ich habs einfach gemacht ohne Nachzudenken. Ich hatte mal ein Skript nachgebaut, wo ich was in die Instanz eintragen sollte und bei dem schien das doch völlig egal zu sein. Später war es bei einem anderen Fall so, dass esnur per Installation auf Kommandozeile klappt, nicht in der Instanz des Adapters.
Ende vom Lied ist, dass ich es gar nicht mehr versucht habe.
Vermutlich werde ich das nochmal umstellen später. -
Respekt! Klasse, das hätte ich schon vor Jahren brauchen können.
Meiner Meinung nach eine wichtige Sache, die auch einen Adapter verdienen würde.
Mittlerweile habe ich es für mich mit python auf Basis der json files im Dateisystem gelöst. Nicht so elegant, nicht so automatisert, nicht so integriert; kommen aber auch xlsx und csv raus. -
@klassisch
Danke. Ich habe jetzt das Skript oben nochmal aktualisiert.
Neuerungen:- Hinweise bzgl. des npm Moduls korrigiert. Jetzt wird empfohlen, exceljs lokal über den Eintrag in der Instanz zu hinterlegen, nicht mehr global per Kommandozeile
- Nachkommastellen pro Spalte konfigurierbar
- optionaler Multiplikator z.B. um von m3 auf Liter zu kommen
- optionales Auffüllen von Leerfeldern mit Nullen
- die Einheitennahmen aus den Datenpunkten werden jetzt in die Zellformatierung übernommen, so dass es netter zu lesen ist
- Probleme mit vielen Daten für den Ganzjahresexport sind behoben
- über den gesonderten DP "0_userdata.0.VollJahresDaten" kann man jetzt 1,2 oder 3 ganze Jahre gesamthaft auswerfen lassen. Solange der DP auf Null steht, läuft das normale monatliche Modell, sonst einfach 1,2 oder 3 eintragen.
Ich denke, jetzt ist es schön rund.
-
@bertderkleine sagte in Regelmässiger Historienexport als Excel-Tabelle:
Ich habe jetzt das Skript oben nochmal aktualisiert.
Neuerungen:Sieht super aus!
- Nachkommastellen pro Spalte konfigurierbar
Warum? Bei meinen Python Scripts habe ich das einfach so in XLS geschrieben wie es ist und in xls dann die Nachbearbeitung gemacht.
- optionaler Multiplikator z.B. um von m3 auf Liter zu kommen
Auch das ist in xls bequemer, denke ich.
- optionales Auffüllen von Leerfeldern mit Nullen
Das habe ich nicht verstanden. Welche Leerfelder?
- Probleme mit vielen Daten für den Ganzjahresexport sind behoben
Gibt es da Beschränkungen mit der Datenmenge oder Rechenleistung?
Ich denke, jetzt ist es schön rund.
Saubere Arbeit, Respekt!
-
@klassisch sagte in Regelmässiger Historienexport als Excel-Tabelle:
- Nachkommastellen pro Spalte konfigurierbar
Warum? Bei meinen Python Scripts habe ich das einfach so in XLS geschrieben wie es ist und in xls dann die Nachbearbeitung gemacht.
Klar geht das, aber jetzt wollte ich es direkt so haben, wie es am Ende aussehen soll. Wenn man mal dran ist ...
- optionaler Multiplikator z.B. um von m3 auf Liter zu kommen
Auch das ist in xls bequemer, denke ich.
Bequemer absolut, aber wie gesagt, ich wollte es jetzt direkt rund haben,
- optionales Auffüllen von Leerfeldern mit Nullen
Das habe ich nicht verstanden. Welche Leerfelder?
Bei manchen Datenpunkten, wie "Regen" gibt es halt an manchen Tagen gar keine Daten. Da kann es besser sein, eine Null statt einer Lücke zu haben. Ist aber eh optional.
- Probleme mit vielen Daten für den Ganzjahresexport sind behoben
Gibt es da Beschränkungen mit der Datenmenge oder Rechenleistung?
Ich habe alte Datenreihen, die amfangs noch viel zu viele Datenänderungen aufgezeichnet hatten, was pro Tag sehr viele Einträge erzeugt. Und das Skript saugt sich ja für das vorgegebene Zeitfenster alles rein. Bei einem Jahr war der Puffer zu schnell voll und spätere Tage blieben leer. Jetzt ist der Puffer größer und es wird in Salamitaktik gearbeitet.
