2e:null 2f:["$","title",null,{"children":"Export functions | Bildungsplanung Kanton Zürich | Observable"}] 34:T81e5,createXLSX = function (config) { /** * createXLSX creates an xlsx file from a given dataset and additional parameters. * * @param {Object} config A config object * @param {string} [config.filename="Datentabelle.xlsx"] A string with the filename. * @param {Array} [config.columns] Array of all the column titles (optional) * @param {Array} [config.datatypes] Ordered array of the data types of all the collumns (optional, default is String) * @param {string} [config].title Main title of the Dataset (row is left out if not defined or empty string) * @param {string} [config.subtitle] Subtitle for the Dataset (row is left out if not defined or empty string) * @param {string} [config.description] Any additional information on a dataset (row is left out if not defined or empty string) * @param {string} [config.source] Source of the dataset to be displayed at the bottom (row is left out if not defined or empty string) * @param {Boolean} [config.autofilter=false] Turns on autorfilter for every collumn with data if it contains data * @param {Object[]} config.data The data to be included in the xlsx as an array of objects * */ let data = config.data == undefined ? undefined : config.data; let columns = config.columns == undefined ? Object.keys(data[0]) : config.columns; let columnNames = config.columnNames == undefined ? config.columns == undefined ? Object.keys(data[0]) : config.columns : config.columnNames; let datatypes = config.datatypes == undefined ? undefined : config.datatypes; let title = config.title == undefined ? undefined : config.title; let subtitle = config.subtitle == undefined ? undefined : config.subtitle; let description = config.description == undefined ? undefined : config.description; let source = config.source == undefined ? undefined : config.source; let autofilters = config.autofilters == undefined ? false : config.autofilters; let filename = config.filename == undefined ? "Datentabelle.xlsx" : config.filename; //Throw error if data is undefined. if (typeof data == "undefined") { throw new Error("The data for the excel export has not been provided."); } let Datasheets = {}; //create workbook object and define some attributes var workbook = new ExcelJS.Workbook(); workbook.creator = "Bildungsstatistik Kanton Zürich"; workbook.lastModifiedBy = "Bildungsstatistik Kanton Zürich"; workbook.created = new Date(); workbook.modified = new Date(); //workbook.lastPrinted = new Date(); if (typeof datatypes == "undefined") { datatypes = []; for (var i = 0; i < columns.length; i++) { datatypes.push("string"); } } //create worksheet object var worksheet = workbook.addWorksheet("Datenblatt"); var minCellWidth = 10; var maxCellWidth = 100; //Define column widths depending on data Datasheets.cellWidths = []; //console.log(columns) for (i = 0; i < columns.length; ++i) { var cw = d3.max(data, function (d) { let wert = formater(datatypes[i], d[columns[i]]); return getTextWidth(wert, "10pt arial"); }) / 7.025 + 2; Datasheets.cellWidths.push(cw); } //Define width depending on column names, where names are wider than data for (let i = 0; i < columnNames.length; ++i) { let keyarray = columnNames[i].split(" "); let keywidth = 0; for (let j = 0; j < keyarray.length; ++j) { if (keywidth < getTextWidth(keyarray[j], "10pt arial")) { keywidth = getTextWidth(keyarray[j], "10pt arial"); } } var cw = keywidth / 7.025 + 2; if (Datasheets.cellWidths[i] < cw) { Datasheets.cellWidths[i] = cw; } } //Limit column widths with min and max for (i = 0; i < columns.length; ++i) { if (Datasheets.cellWidths[i] < minCellWidth) { Datasheets.cellWidths[i] = minCellWidth; } if (Datasheets.cellWidths[i] > maxCellWidth) { Datasheets.cellWidths[i] = maxCellWidth; } } //Fill array with default width for remaining columns up to 100 for (i = columns.length; i < 100; i++) { Datasheets.cellWidths.push(8.43); } //Create array with summed up column widths Datasheets.cellWidthsSums = [0.0]; for (i = 0; i < Datasheets.cellWidths.length; ++i) { Datasheets.cellWidthsSums.push( Datasheets.cellWidthsSums[i] + Datasheets.cellWidths[i] ); } //Define columns var defineColumns = []; for (i = 0; i < columns.length; i++) { defineColumns.push({ header: createRichText(columnNames[i], 10, false), key: columns[i], width: Datasheets.cellWidths[i] }); } /*for (i=columns.length; i<100; i++) { defineColumns.push({header: "", key: "", width: Datasheets.cellWidths[i], outlineLevel: 0}); }*/ worksheet.columns = defineColumns; //Counter for header rows and total rows (one row is allready defined though column definition) var headerRows = 1; var totalRows = 1; //If title exist splice it in at the top if (typeof title !== "undefined") { if (title != "") { var titlestr = createRichText([title], 20, true); var titlelength = getTextWidth(title, "20pt arial black"); worksheet.spliceRows(headerRows, 0, [titlestr]); ++headerRows; ++totalRows; } } //If subtitle exists splice it in if (typeof subtitle !== "undefined") { if (subtitle != "") { var subtitlestr = createRichText([subtitle], 12, true); var subtitlelength = getTextWidth(subtitle, "12pt arial black"); worksheet.spliceRows(headerRows, 0, [subtitlestr]); var subtitleRow = headerRows; ++headerRows; ++totalRows; } } //Splice in spacer row worksheet.spliceRows(headerRows, 0, [""]); var spacerRow = headerRows; ++headerRows; ++totalRows; //Add actual data to sheet data.forEach(function (d) { var cellContent = []; for (i = 0; i < columns.length; ++i) { switch (datatypes[i]) { case "string": case "year": var cellCT = createRichText(d[columns[i]], 10, false); break; case "date": var cellCT = d3.time.hour.offset(d[columns[i]], 2); break; default: var cellCT = d[columns[i]]; break; } //Wandelt undefined und NaN in "" um. if ((cellCT == undefined) | (cellCT !== cellCT)) { cellCT = ""; } cellContent.push(cellCT); } worksheet.addRow(cellContent); ++totalRows; }); //Add spacer row at the bottom, before remaining meta data worksheet.addRow([""]); ++totalRows; //If description exists add it at the bottom if (typeof description !== "undefined") { if (description != "") { var descriptionstr = createRichText([description], 10, false); worksheet.addRow([descriptionstr]); ++totalRows; var descriptionRow = totalRows; } } //If source exist add it at the bottom if (typeof source !== "undefined") { if (source != "") { var sourcestr = createRichText([source], 10, false); worksheet.addRow(["Datenquelle: " + sourcestr]); ++totalRows; var sourceRow = totalRows; } } //Add spacer row at the bottom, before remaining meta data worksheet.addRow([""]); ++totalRows; var logoRow = totalRows; //Add file source in any case worksheet.addRow(["Bildungsplanung Kanton Zürich"]); ++totalRows; let filesourceRow = totalRows; //Add license in any case worksheet.addRow([ "© 2023. Diese Tabelle ist offen lizenziert nach CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/)." ]); ++totalRows; let licenseRow = totalRows; //Add border to data portion (including header row) for (i = headerRows; i < data.length + headerRows + 1; ++i) { var row = worksheet.getRow(i); row.eachCell({ includeEmpty: true }, function (cell, colNumber) { cell.border = { top: { style: "thin", color: { argb: "FFD9D9D9" } }, left: { style: "thin", color: { argb: "FFD9D9D9" } }, bottom: { style: "thin", color: { argb: "FFD9D9D9" } }, right: { style: "thin", color: { argb: "FFD9D9D9" } } }; }); } //Add font to all rows worksheet.eachRow({ includeEmpty: true }, function (row, rowNumber) { row.font = { name: "Arial", family: 2, size: 10 }; }); //Add formating to metadata rows Datasheets.cellHeights = []; var headerHeight = 0.0; var maxMerge = 20; //Add formating to title row if (typeof titlelength != "undefined") { var firstRow = worksheet.getRow(1); firstRow.font = { name: "Arial Black", family: 2, size: 20, bold: true }; firstRow.alignment = { vertical: "middle", horizontal: "left", wrapText: true }; //Merge cells //default is 8 var merge = 8; //increase merge area as long as the content is bigger, the area is reasonably small and the merged area is not broader then the columns with data and not more than 20 colums wide while ( (Datasheets.cellWidthsSums[merge] < titlelength / 7.025 + 10) & (Datasheets.cellWidthsSums[merge] < 150) & (merge < columns.length) & (merge < 20) ) { ++merge; } //actually merge the cells worksheet.mergeCells(1, 1, 1, merge); //adapt row height firstRow.height = Math.ceil((titlelength / 7.95 + 10) / Datasheets.cellWidthsSums[merge]) * 30 + (title.match(/\n/g) || []).length * 30; Datasheets.cellHeights.push(firstRow.height); headerHeight += firstRow.height; if (merge > maxMerge) { maxMerge = merge; } } var fontSizeSubtitle = 12; //Add formating to subtitle row if (typeof subtitle !== "undefined") { if (subtitle != "") { var secondRow = worksheet.getRow(subtitleRow); secondRow.font = { name: "Arial Black", family: 2, size: fontSizeSubtitle, bold: true }; secondRow.alignment = { vertical: "middle", horizontal: "left", wrapText: true }; //Merge cells //default is 8 var merge = 8; //increase merge area as long as the content is bigger, the area is reasonably small and the merged area is not broader then the columns with data and not more than 20 colums wide while ( (Datasheets.cellWidthsSums[merge] < subtitlelength / 7.95 + 10) & (Datasheets.cellWidthsSums[merge] < 150) & (merge < columns.length) & (merge < 20) ) { ++merge; } //actually merge the cells worksheet.mergeCells(subtitleRow, 1, subtitleRow, merge); //First part adds calculates Rows depending on the content length, the second part adds another line for each linebreak. This may result in to many rows secondRow.height = Math.ceil( (subtitlelength / 7.95 + 10) / Datasheets.cellWidthsSums[merge] ) * 20 + (subtitle.match(/\n/g) || []).length * 20; Datasheets.cellHeights.push(secondRow.height); headerHeight += secondRow.height; if (merge > maxMerge) { maxMerge = merge; } } } worksheet.getRow(spacerRow).height = 15; Datasheets.cellHeights.push(15.0); //Adapt hight of logoRow to fit the logo worksheet.getRow(logoRow).height = 52; //Adapt header row so that text fits in cells var dataHeaderRow = worksheet.getRow(headerRows); dataHeaderRow.alignment = { vertical: "top", horizontal: "left", wrapText: true }; //find max rows let maxRows = 1; for (let i = 1; i <= dataHeaderRow._cells.length; ++i) { maxRows = Math.max( maxRows, Math.ceil( (getTextWidth(dataHeaderRow.getCell(i).value, "10pt arial") / 7.025 + 1) / Datasheets.cellWidths[i] ) ); } //adjust header row heights (add some space for auto filters) dataHeaderRow.height = maxRows * 15; if (autofilters == true) { dataHeaderRow.height = maxRows * 15 + 12; } Datasheets.cellHeights.push(dataHeaderRow.height); //Add cell heights to array for data rows (including spacer row at bottom) for (i = headerRows + 1; i < data.length + headerRows + 2; ++i) { worksheet.getRow(i).height = 15; Datasheets.cellHeights.push(15.0); worksheet.getRow(i).alignment = { vertical: "middle" }; } //Add formating to description row if (typeof description !== "undefined") { if (description != "") { var descRow = worksheet.getRow(descriptionRow); //descRow.font = { name: 'Arial', family: 2, size: 20, bold: true }; descRow.alignment = { vertical: "middle", horizontal: "left", wrapText: true }; //Merge cells var merge = 8; while ( (Datasheets.cellWidthsSums[merge] < getTextWidth(description, "10pt arial") / 7.025 + 10) & (Datasheets.cellWidthsSums[merge] < 90) ) { ++merge; } if (merge > 20) { merge = 20; } worksheet.mergeCells(descriptionRow, 1, descriptionRow, merge); //split the description by line breaks let descriptionarray = description.split(/\n/g); //get the number of rows per array element let descriptionlengths = descriptionarray.map((d) => Math.ceil( (getTextWidth(d, "10pt arial") / 7.25 + 1) / Datasheets.cellWidthsSums[merge] ) ); //sum up the number of rows let descriptionnrows = descriptionlengths.reduce(function (a, b) { return a + b; }); //2 for the margins and 13 for every row (15 for a single row) descRow.height = 2 + descriptionnrows * 13; Datasheets.cellHeights.push(descRow.height); } } //Add formating to source row if (typeof source !== "undefined") { if (source != "") { var sourcRow = worksheet.getRow(sourceRow); sourcRow.alignment = { vertical: "middle", horizontal: "left", wrapText: true }; //Merge cells var merge = 8; while ( (Datasheets.cellWidthsSums[merge] < getTextWidth("Quelle: " + source[0].Content, "10pt arial") / 7.025 + 10) & (Datasheets.cellWidthsSums[merge] < 90) ) { ++merge; } if (merge > 20) { merge = 20; } worksheet.mergeCells(sourceRow, 1, sourceRow, merge); sourcRow.height = Math.ceil( (getTextWidth("Quelle: " + source[0].Content, "10pt arial") / 7.025 + 10) / Datasheets.cellWidthsSums[merge] ) * 15; Datasheets.cellHeights.push(sourcRow.height); } } //Add formating to filesource row let filesourcRow = worksheet.getRow(filesourceRow); filesourcRow.alignment = { vertical: "middle", horizontal: "left", wrapText: true }; //Add formating to license row let licensRow = worksheet.getRow(licenseRow); licensRow.alignment = { vertical: "middle", horizontal: "left", wrapText: true }; //Merge cells var merge = 8; while ( (Datasheets.cellWidthsSums[merge] < getTextWidth("Fachstelle Statistik des Kantons Zug", "10pt arial") / 7.025 + 10) & (Datasheets.cellWidthsSums[merge] < 90) ) { ++merge; } if (merge > 20) { merge = 20; } worksheet.mergeCells(filesourceRow, 1, filesourceRow, merge); worksheet.mergeCells(licenseRow, 1, licenseRow, merge); filesourcRow.height = Math.ceil( (getTextWidth("Fachstelle Statistik des Kantons Zug", "10pt arial") / 7.025 + 10) / Datasheets.cellWidthsSums[merge] ) * 15; Datasheets.cellHeights.push(filesourcRow.height); //Create array with summed up cell heights Datasheets.cellHeightsSums = [0.0]; for (i = 0; i < Datasheets.cellHeights.length; ++i) { Datasheets.cellHeightsSums.push( Datasheets.cellHeightsSums[i] + Datasheets.cellHeights[i] ); } function calculateWidthinCells(width) { var result = [0, 0]; while (Datasheets.cellWidthsSums[result[0]] < width) { ++result[0]; } --result[0]; result[1] = Math.round( (width - Datasheets.cellWidthsSums[result[0]]) * 7.025 ); return result; } function calculateHeightinCells(height) { var result = [0, 0]; while (Datasheets.cellHeightsSums[result[0]] < height) { ++result[0]; } --result[0]; result[1] = Math.round( (height - Datasheets.cellHeightsSums[result[0]]) * 1.27 ); return result; } // add image to workbook by base64 var logo = "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAN8AAABFCAYAAADKBDMJAAAABGdBTUEAALGOfPtRkwAAACBjSFJNAACHDwAAjA8AAP1SAACBQAAAfXkAAOmLAAA85QAAGcxzPIV3AAAKOWlDQ1BQaG90b3Nob3AgSUNDIHByb2ZpbGUAAEjHnZZ3VFTXFofPvXd6oc0wAlKG3rvAANJ7k15FYZgZYCgDDjM0sSGiAhFFRJoiSFDEgNFQJFZEsRAUVLAHJAgoMRhFVCxvRtaLrqy89/Ly++Osb+2z97n77L3PWhcAkqcvl5cGSwGQyhPwgzyc6RGRUXTsAIABHmCAKQBMVka6X7B7CBDJy82FniFyAl8EAfB6WLwCcNPQM4BOB/+fpFnpfIHomAARm7M5GSwRF4g4JUuQLrbPipgalyxmGCVmvihBEcuJOWGRDT77LLKjmNmpPLaIxTmns1PZYu4V8bZMIUfEiK+ICzO5nCwR3xKxRoowlSviN+LYVA4zAwAUSWwXcFiJIjYRMYkfEuQi4uUA4EgJX3HcVyzgZAvEl3JJS8/hcxMSBXQdli7d1NqaQffkZKVwBALDACYrmcln013SUtOZvBwAFu/8WTLi2tJFRbY0tba0NDQzMv2qUP91829K3NtFehn4uWcQrf+L7a/80hoAYMyJarPziy2uCoDOLQDI3fti0zgAgKSobx3Xv7oPTTwviQJBuo2xcVZWlhGXwzISF/QP/U+Hv6GvvmckPu6P8tBdOfFMYYqALq4bKy0lTcinZ6QzWRy64Z+H+B8H/nUeBkGceA6fwxNFhImmjMtLELWbx+YKuGk8Opf3n5r4D8P+pMW5FonS+BFQY4yA1HUqQH7tBygKESDR+8Vd/6NvvvgwIH554SqTi3P/7zf9Z8Gl4iWDm/A5ziUohM4S8jMX98TPEqABAUgCKpAHykAd6ABDYAasgC1wBG7AG/iDEBAJVgMWSASpgA+yQB7YBApBMdgJ9oBqUAcaQTNoBcdBJzgFzoNL4Bq4AW6D+2AUTIBnYBa8BgsQBGEhMkSB5CEVSBPSh8wgBmQPuUG+UBAUCcVCCRAPEkJ50GaoGCqDqqF6qBn6HjoJnYeuQIPQXWgMmoZ+h97BCEyCqbASrAUbwwzYCfaBQ+BVcAK8Bs6FC+AdcCXcAB+FO+Dz8DX4NjwKP4PnEIAQERqiihgiDMQF8UeikHiEj6xHipAKpAFpRbqRPuQmMorMIG9RGBQFRUcZomxRnqhQFAu1BrUeVYKqRh1GdaB6UTdRY6hZ1Ec0Ga2I1kfboL3QEegEdBa6EF2BbkK3oy+ib6Mn0K8xGAwNo42xwnhiIjFJmLWYEsw+TBvmHGYQM46Zw2Kx8lh9rB3WH8vECrCF2CrsUexZ7BB2AvsGR8Sp4Mxw7rgoHA+Xj6vAHcGdwQ3hJnELeCm8Jt4G749n43PwpfhGfDf+On4Cv0CQJmgT7AghhCTCJkIloZVwkfCA8JJIJKoRrYmBRC5xI7GSeIx4mThGfEuSIemRXEjRJCFpB+kQ6RzpLuklmUzWIjuSo8gC8g5yM/kC+RH5jQRFwkjCS4ItsUGiRqJDYkjiuSReUlPSSXK1ZK5kheQJyeuSM1J4KS0pFymm1HqpGqmTUiNSc9IUaVNpf+lU6RLpI9JXpKdksDJaMm4ybJkCmYMyF2TGKQhFneJCYVE2UxopFykTVAxVm+pFTaIWU7+jDlBnZWVkl8mGyWbL1sielh2lITQtmhcthVZKO04bpr1borTEaQlnyfYlrUuGlszLLZVzlOPIFcm1yd2WeydPl3eTT5bfJd8p/1ABpaCnEKiQpbBf4aLCzFLqUtulrKVFS48vvacIK+opBimuVTyo2K84p6Ss5KGUrlSldEFpRpmm7KicpFyufEZ5WoWiYq/CVSlXOavylC5Ld6Kn0CvpvfRZVUVVT1Whar3qgOqCmrZaqFq+WpvaQ3WCOkM9Xr1cvUd9VkNFw08jT6NF454mXpOhmai5V7NPc15LWytca6tWp9aUtpy2l3audov2Ax2yjoPOGp0GnVu6GF2GbrLuPt0berCehV6iXo3edX1Y31Kfq79Pf9AAbWBtwDNoMBgxJBk6GWYathiOGdGMfI3yjTqNnhtrGEcZ7zLuM/5oYmGSYtJoct9UxtTbNN+02/R3Mz0zllmN2S1zsrm7+QbzLvMXy/SXcZbtX3bHgmLhZ7HVosfig6WVJd+y1XLaSsMq1qrWaoRBZQQwShiXrdHWztYbrE9Zv7WxtBHYHLf5zdbQNtn2iO3Ucu3lnOWNy8ft1OyYdvV2o/Z0+1j7A/ajDqoOTIcGh8eO6o5sxybHSSddpySno07PnU2c+c7tzvMuNi7rXM65Iq4erkWuA24ybqFu1W6P3NXcE9xb3Gc9LDzWepzzRHv6eO7yHPFS8mJ5NXvNelt5r/Pu9SH5BPtU+zz21fPl+3b7wX7efrv9HqzQXMFb0ekP/L38d/s/DNAOWBPwYyAmMCCwJvBJkGlQXlBfMCU4JvhI8OsQ55DSkPuhOqHC0J4wybDosOaw+XDX8LLw0QjjiHUR1yIVIrmRXVHYqLCopqi5lW4r96yciLaILoweXqW9KnvVldUKq1NWn46RjGHGnIhFx4bHHol9z/RnNjDn4rziauNmWS6svaxnbEd2OXuaY8cp40zG28WXxU8l2CXsTphOdEisSJzhunCruS+SPJPqkuaT/ZMPJX9KCU9pS8Wlxqae5Mnwknm9acpp2WmD6frphemja2zW7Fkzy/fhN2VAGasyugRU0c9Uv1BHuEU4lmmfWZP5Jiss60S2dDYvuz9HL2d7zmSue+63a1FrWWt78lTzNuWNrXNaV78eWh+3vmeD+oaCDRMbPTYe3kTYlLzpp3yT/LL8V5vDN3cXKBVsLBjf4rGlpVCikF84stV2a9021DbutoHt5turtn8sYhddLTYprih+X8IqufqN6TeV33zaEb9joNSydP9OzE7ezuFdDrsOl0mX5ZaN7/bb3VFOLy8qf7UnZs+VimUVdXsJe4V7Ryt9K7uqNKp2Vr2vTqy+XeNc01arWLu9dn4fe9/Qfsf9rXVKdcV17w5wD9yp96jvaNBqqDiIOZh58EljWGPft4xvm5sUmoqbPhziHRo9HHS4t9mqufmI4pHSFrhF2DJ9NProje9cv+tqNWytb6O1FR8Dx4THnn4f+/3wcZ/jPScYJ1p/0Pyhtp3SXtQBdeR0zHYmdo52RXYNnvQ+2dNt293+o9GPh06pnqo5LXu69AzhTMGZT2dzz86dSz83cz7h/HhPTM/9CxEXbvUG9g5c9Ll4+ZL7pQt9Tn1nL9tdPnXF5srJq4yrndcsr3X0W/S3/2TxU/uA5UDHdavrXTesb3QPLh88M+QwdP6m681Lt7xuXbu94vbgcOjwnZHokdE77DtTd1PuvriXeW/h/sYH6AdFD6UeVjxSfNTws+7PbaOWo6fHXMf6Hwc/vj/OGn/2S8Yv7ycKnpCfVEyqTDZPmU2dmnafvvF05dOJZ+nPFmYKf5X+tfa5zvMffnP8rX82YnbiBf/Fp99LXsq/PPRq2aueuYC5R69TXy/MF72Rf3P4LeNt37vwd5MLWe+x7ys/6H7o/ujz8cGn1E+f/gUDmPP8usTo0wAAAAlwSFlzAAAOxAAADsQBlSsOGwAAHY1JREFUeF7tnQm8D9UXwO97LZb0p1Vpk1K0q2hPtKeVRIhoVWlVWrSSNu2KqKi8qHhp31VKWhQtZIkWKm2KlIQ3//s9vzm/d3/zm/kt7z3ynvm+z7yZubP8Zrnn3nPPOfdOgWcxMTExK51yC9+oUaNMgwYNzF9//WVmzZplpkyZYubMmWMWLlxoCgsLzRZbbGFat25tDj30UP+ImJgYKLfwFRQUmAMPPND873//MzvssINp0qSJ2WOPPUzt2rXN0qVLzfTp081FF11kPvvsMzNhwgSz9957+0eWwiVwnpiY1YlyCd/dd99t7r//fhEwFwSspKREhG/y5MnmzDPPNNWqVTPTpk0zX3/9tfn222/NaaedZq655hqz6aab+kfFxKxelEn4Xn31VXPEEUeYunXrmh9//NFPTfDEE0+Y4uJimQ8cONCsv/76ZubMmaZ3796y/bnnnpMasmXLllLb1alTR4SxevXqZsCAAaZNmzayX0xMVadMwnf77bebzz//3AwbNsxPKWX06NHm9ddfF8GDxo0bmy+//FKWAUE87LDDROgmTZrkpxqzZMkSEcByVMQxMZWKMqud1FqolWuuuaafUgqGFtRO+O6778yWW24pywo/iQD37NnTTzHmrLPOMh9++GGKQMbEVGXKJXzBQ1kn/eeffzZt27Y1Xbt2Naeeeqq/tZRffvnFbLTRRv5aKbvttpu0EWNiVgfKJHyffvqpOfjgg82vv/7qp+SGCie14vXXX29+//13s/XWW5t9991Xth1//PFm3rx5ZtGiRaZWrVr+UTExVRSELx969erlVatWzZs7d66fEo4VMH8pFStw3lprreVZFdP7/vvvvY8++sjbfffdvQYNGnh///237GPbhN5vv/0myzExVZVCXwZzZuONNzZHHXWUeeWVV/yUcKjhaNcFwe3w1FNPybb27dubm266SVRNrKf4AXHaY03Nhr12fykmpnKSJnzZMvXFF19sfvjhBzNixAg/JZw///xTol6CYNW88sorzciRI824ceNEmPfZZx9pHz788MPmzjvvNC+++KK4KDJdC8IdE1OZSRM+zdTnnnuuadSokUSv7LrrriIsCkKCpTMT6667rkS1LFiwwFg10081ZtCgQebff/8122+/vWnevLnZZJNNTNOmTSX8bPjw4Wb8+PHmyCOPlH2DAvb4448bq6JKuNqQIUP81JiYykmowYVM/+abb5qDDjrIfPLJJ5Lhr776ajN27FgRDmqvDTfcUGo3jC747IIuB1wMW221lQgV/rvNNttMHPJjxozx90gFAceQE0VRUZF57bXX5Pf4Xds+FEf9Lbfc4u8RE1O5SKv56tWrJ2rlfvvtJ0L4zDPPmG222UbiNRGku+66S0LFsEjCfffdlxQ8lWPm+PaY03575JFHzFdffSXxnpyHWhWB5vwbbLCBeeCBB6QdmYm+ffuae++9V2q9jh07iuDdeuutoqJCSBkSE7NqYzNtkpkzZ3r77ruvLO+1117evHnzZBnWW289mdeqVUvmJ554ovfss896VrA8WyNJWi7wk88//7w3e/ZsWbeqpqRlwwqtV1xcLMsdOnTwbLvRs7WgZ9VX74MPPpD0mJjKREqut6qlN3r0aFnec889Za4MHDhQtuEGWLhwoTd//nxxGcBuu+0m8z59+sg8DHU92PacV1hY6FnVUdbBqrH+UjR6PcuXL/fuvPNOWe7SpYtXo0YNWY6JqWykCF+bNm28WbNmyfLHH3+cIoDTpk3zrLro9evXL1nT4ZvDV2fbdFJT5opVXaW2W3fddb2HHnrIT00HgVWhtWqszMGqsDKfNGmSd/LJJ8tyTExlI6XN988//xhbk8gybTKrzkm7zO4nESfLli0T44liBcd069bNLF682Lz//vuSRmhZNi644AI5JwYWjDj8xrHHHivndyGdiXYjRh2FNihwLEahmJjKSIrwYUF03QIYTXr16iUCQF884jHpoYClE9jX1nyyjPDhQqArUTYQPCZCy3AvsExXIs53zz33+HsZ+U36+z322GOhllB6ze+yyy7+WkxMJcNm/KRq17hxY5krO+64o7/keVdccYU3btw4MXAAoWAcrseecsopYrAZMmSIrJeVtddeW9TS1q1be1tssYW08Vz096B27dr+UkxM5SNF+Mj4Luuss46/5Hm2BpK5Wj1h8ODBIoBMagTZaaedZJ4vX3zxhQgvcL7evXvLchRYZW+88UZ/LSam8pF0suNro4Msvcnfeecdc91114lKR7gX6qStBc3LL78soWGolhyGOgr4/egmxDgu9EwgfhO/HQ5xVVEzoefCh4eKeccdd4gjH99eENReesLj47v00ktTriNfZs353ox78TmzeHmi7+GKwhZuosLTrs0F9qdPpBJ1j+W591WRst5P2HHZzhXc/l88SxE+xlVhBDKGeCBihOgUBGrGjBmy0znnnCOCgYBa9VOMMQrtRELJrHoo6wgNxhnCz6wqKsKUDwg5bTliO+fPn++nJqBdSMHAsBNqGCoPAx4pMhfc0N+M7H22+X7BYqnC3cevLyTXFxW2P3Pasuedd16yg3EuvPfee2IA43lquN0333wjwQqck22tWrWS9IqEa4ew+ysvBETUrFnTXyuFvEOM78477yzrFPZgNbGU5UzQPY3O2ECASC5d0l566SUJENl2223F/rDSsQ8b4fOuvfZaaddNnz6dpBT83ZJzF5zsOL2hY8eOMrcPWJzwF154oaxnguNtremveV79+vVlji/QBbdHJj9iWRg6vMgzx1zp9ZrjJ6xAeCb5gMrP89Znvnjx4uQ604QJEyQ9CrdtnI3bbrvNO+mkk8SVBPkcmw/u9Qen5s2b+3uV7od7S5ezYQUpuS/d1XJB97calJ+ycgm9Kx6+vgBejK0NvTFjxiRfjkv//v09W6rLsi1xZI4j3qqOspzLi8RYQ78+4NgDDzxQMivnBgoE9+VUFCJ8x/b0zNP/eOd8vMimlHjLS1INPBVFvsEAGJs0cwABDbqumct9T2Fke/a6vWXLlnJet42fy3uLIupYvf6w6ZhjjvH38rybbrrJ69mzp/T9vOyyyzzbBPK3RPPTTz9JIMj999/v/fHHH35qZvS3K7pQz5WsRQoXBwgDpW+Qzz77LHkT7du391M9b+rUqf5SKmEvRh30CJ3+ns5xqOP4D9aEFUFS+F5a6pnHf/O6f7zQpiauz2ZrmVcU5RG+tm3bJpc1wMF9jrY5IAYo2+b2bDvd69Gjh7/F84qKiryjjz7aO//88z2rsnvbbLONZ9vM3sSJE2U7UU1WxZdzI+DHHXechO0BGZrfRhtp1qyZd++990o6fP75555Ve+XcVh32mjZtKteMES4Xhg4dmrwnq/r5qZ53+umne507dxZBghEjRsi65q05c+ZIlBS/S4du8g7XQcQV+7Rr1y6lozfWdyKwNttsM9HQFi2ikE2gv3/99dd73bp18+rWreudccYZ/tYVT0bhw4JJDCUxng0bNvRT0+Fh8AIaNWok6z/++KO4C1x42bat5q+VYtuGKRbSrl27SqwmDwt4OJ06dcpZlciHFOEbPd8zRb96Z0sNuDwhfOUo/YOUR/h0cqN8FGoI3Y6qr8uokYDQaVpwgu222y4tnagl2+5PS2eigIRXXnkldDuTba/KPlGMGjUqZX8XLO6kqaaDQLn7YRXXdQpk5sQbuyrqp59+KvtSkGiaO2k4Y9g2prPPPlu2r2gyCh8XAgQ1v//++7KcCYaXUAg9C3L88cf7S573ww8/yJzz8oBd1wJtTx4cUEoPGDBAlintM6lDmbaFkSZ8xSqAxJ1aFdT+VRQVIXxMN9xwg79HAgo1nqGqWvvvv7/sRw0HtGf02PHjx4tKp+uEEAK1COsbb7yxrMPee+8taRtssIGsjxw5MnkcQ3zg89V11ELOpet9+/aVY1z03XANuh/Tzz//LOlKnTp1JP3www+XdeJ3dV9Ao9J1hI623ssvvyzNFk2fMWOG5C9dJ5Af9NzqotLtGkZJDcx6Wd1l+ZLWpUjBlE8YGNgHa2z1LstRWBVFLFYKbgfG7wTcF2AziMwBNwRWPKx6uDhwUdiSW7Y9+uijMrI1nW1tW0C6IAEWONcKRzgaI2bT1Yjl8ljoCgp4FPb46oVm0LR/TfdP/jKFKbbP/w5G9lZYtpnfXzNm8803l/dE+B33/+6770q666pQrGqa8s0M7QqGRRWsgMgcNFxQLaqMRqe88cYbKdZHqyqmWMDDLJNcG9ZaLJEKI52HjWIHa6yxhsz1GsOgczXDj1hBlRBHhd/Xrmag94BF1OZ5cZe56EDNVguQOdbplUGk8BFWRt89hv5T4YkCHx+dZXkpgI/QltxJvxYD6QIZggcAmJXxFzKI7iGHHCL9/HjBnIeQNTIKfr6bb75Z9g/CCNh0wOU8vFD2f/bZZ2UbDzhf5Bgp9xDAAhHAMz9mGIwSu81myvxPWWEw0hv9JhV8hgojARCDa0tvuQfbZpH0qIIIF0UQdRO5hSMjDQCjEQDvSbE1Q0ocLu6mbND/0zXnIzBcN35kxvOhEAW9Fo0RJsQwCjfe14VrU7cF6LkoyHFlBfOUFj7uPa0U7AtLg4Y1jVyI2CUF2nL2Zcu+TAcffLCkU81j9dRqnXOq+ppNRRw7dqxnS3RRhZo0aeLZEt3fkujCZGtmb8GCBaKS2pcn7U6sdvlQqnYu88woq3LK9HtC/WQa/qt3xsRE16fyWkHzVTvr1auXfJ4KBgFNUwuzrtMmHzRoUMo6uG1CwOii65MnT5Y0DBGaxkQbn76aum4zcnIZowxYrSaZpu4pXUe1DcL70+1RE4S1QXWCKVOmJNfJI8rbb7+dTMcYBK67BnuELmvPHV232oSso6qzjtFqZRBa8/Xo0UN6nzPeim24+6nh2HNISYzKwjLOTSJPAAfpVVddZaw+LutU6wwFAdlUxBYtWkjNizqLCkQANs5QPsLC4E38BqotwQH8Pj0duAZGRsufElOQVNPs86f24zXUWMMMmb7U1oB/msKVrIESuBBUHakpgAAItJG5c+cma3tqJr4GdcIJJ8g62ggEVcAwdZSh/RmwmG22kJA0aomhQ4fK8TQL4IADDkg6vd33F3yXYWqbptErhut3J1RMNB5Ae1JVk1pdv/Hh9qZR3Htxr4F8CNS21LBADcpICjRzyDMu+oyssMo8LBBgRZA2hgvtu5NPPlmiW3gR6OWumpMNHkLglCmwHdWCB8d+un/wBeYC48kwTgwPj8Ge6F40ePDgnELaYFjR46brk5OM6d7P6k7Bkda4B3tNXNbi5eb07auZIXvUtNea2u7MFV5oLurZyqasz35VorLeQ1oxiKGlXz+bGS20D/IRPNqHjHaWCULXKOm++OILeWDUrszDBDYszYW2HgYHwt4Ya+bpp5/OWfDSsO06rkNeIr+rtR//ahSaB6cvMadP/NtuZ/OKjQVdmVTGTBukst5DmvChtmF1xBiST+zgW2+9Jaqq1b39lHAwxCBU2iDu3r27zHmAxIKqWqMdeXOBeEdGWbvssstkPZvQpmN/pyBREyePlZ/2hZCV6oXmoRlLzWkfLfKvi235/k5MTClpwqeZD5Uu7CuyLmqaxrxNG41aLVfYn0xMb3htZ1x77bUygjUwNqhrLg4DlZhzYK2i/cOXjiBXoU2De2cKHq8yZpsdD8+0AjgxoaLaVm7ptpiYPElr86ES0iYjCp9aCpdDJqgh27dvLw38fFQ+BBWVFsHDZYAxBTMwPhjGDKUWmzNnjmyjwa9R/LTrOIa2HY1ovgNf1gZyos032Va/NxrD6Noqc/pI0oTYppO2uMR0224t89CetaSwSvgIM7Oqtvli/jvSco3WZrSnXnjhBVmOgu8qIHhkwHzbWjiKqaFQO7FmMk4LNRjnwi+INZNBdnG04/djf5ztjIA9e/Zs8XuxvfyWqZDaCwFTwUMQRRj9NBarF0gN2O2jv+QeYmLKQlrNR2bSJJYXLlwoZu8guAAwBQcOzwoChkDRpmPAXCJdssFvhGVy9zrLQtLaebZVdRcnBgFG0HA7JO+LOef30+1CYhtptgbs3HBt80jTmqbEJhVmqAHzrfkoXCgI1ZzOb6KR8FVfvvYLfAsDlwP3zzfueZ44rdmPwAXcB0F47jrqOH0zV1UYywenN/fAAMloWDQtGjZsmHOn5DA4B64qnieDf2WqNHBV8GxxQbjPOttxOWMvIgVi+ojjA3Wk2raXrAOObbp5kK7xmZmgrx5OcCBeDyctkfaM26kxhRrHCTbD+UsrnhQn++jfwyd1vtvlguI/ZEpswxFv50W/ep0/8HtDlBCQHY5Vlf2l3OC5RE2XX3657MPAw5qm/ft0PSy+EoJO91UVvUZ6KQDDl7BOrG95IC/ruaOC9a3Qe2eddZbso70g3GedLXA8V9KK6gcffFBUSeADmMRY4ihH4pkILaKEtcfKyGJRsB1wkuIGwHHKsBTEcHbo0EGc8fScp3Qh5o9zY2BhvkrB9QSvyd6aXCfVXY1C8+ispaaLVUHZL7Fn4t4rAmo+rM/EMCoaHkW4Fv5YHOzNmjWTNCXolFYNoyJKbA0RDBL2VaogfGcj6nhwYzRBHd+0+2nfM1xkGGFaBU0VzYeKG3QQFTeKxR73FejvZ3rWEBx1IQw0EiYlTfgwemDU0G8n1K9fX8z43AQThhI16WdChYhxX/D9oULwvXW9cD0fN8f3+VCHMKgwXMV/Ai9JJ4dkYZCSzrKm23m1AvPozH9NZwTQbrO1X2JbBWBLfHH/MOTB/vvv76cmQIUiaghVLOojM7SXuQcCmJlT4LkQP0t68j4tun7bbbelrGOBZs4QH8w1cgk4PwUq6fiK9RjePzCcCOtEJenxffr0kW1AG5801HMK+GDhQaQKUUwU3EAwNPtTKBHQTz4qLi6WbdgG2EZBo1E7OiSKC9dL84l9mXiOEydOlEpHIZ3AdSqJsGfNeELsQ4XEHHeZQkQQaUSJWS1PhJ1JP5Ue2kjB2siLJXA1zH3AGCpRAc+Kljjsy0PmItC1rdoj6awzAfsilMwZv0VDjVYupZnPRe8DWJZ1e906twuJeY0C85gVwFM+XJRo+7FvBdSAlN5kOtrXuHQI09J34oZXaS8ARZ8tmQU4DuMW7T0XwruiCIam0a7UyH/QXigIgtas/IZrIdfzd+7cWebUXnyXEeihQW1JVFX//v0lDRcUtUMwAFzvT+cqnHykFUs7z4eCm+9HapA2y/S24FwaKO4+JwSK4wCBp1YLq73dtjfoOXg+DOhFgUFIJhAGqQWEutAoPPGDa02LPxx/dqjwAdHsfAOP8C1eJCojPQf4lh61IUaTTOjL5we5CCLLo1Qe3Rfw3SGIuB5WLr4QhaEC5kIakM4iNWD1AjPcqqCdrACaAi9CnPMH6y4lNDAwFV14wH1uQTSDqBrH9xXJlHpsLgTPT48J3g9NB8AVBPqVYtL5DaKXlOA56EaG8YQMSQFGjUWNqqBWkqGDaObX87nn5TxYvgGDExAiiUGJ31IQDBUIcLtBafwq+Zr4T4Vz02XN7fHBObDQ6zcqCfLAQKSRYfrhWFdgEXTtpgXUxJHCx4/SxYc5L5Dh/Kh+9QeCpWIUqJuMKhXVbysMwsQ4P98EBK5hxVNaE9sfTMwhbNmfs38wcxEJU/TVEtPxg0QbpLy1n36hl0k/UIpbBvUxk5uF69KuMkAYHlCLlhUNSFbrtwq4tmPwC4NrC1DXFddLQUymw5JL3tJrJI8Agc/5orVJEASAPKc1P6BSRu3vqsCuBd59hgpCpSO1g94vtaeLCp9WOsHaM1L43ExFFY/pul27dtIZk9AwtyTIBj0ZiJxHvXDJJFRTp05NjtuZlsErGmI17U94fkaxP5iYA8u6Hpizvxwjq9R09nFyT9XXMI/P/td0+mARIi37+jvlDQUfkf4Yo1SdAlStTM8FgXBVSu1kGwz/04yvoPZHofuqQCkaKoixDnXqkksukXUgw3Et7EMbkXeOEKigkemxMwA+3HyJKoD4zLgWWqh81E70ksHgo9BzQ+0Xbodl9/7C1HKeg94zaE2pPUwUPU9wrkQKXyboQTBhwgR/LRrVn9Hn0Ynp8EmGQa1UQ0Amzj///JSHskKgfUYJTqlUyFyX7cQ2XaZPkZvGMWv4ywWJ4zzZh2U7r7GmKfp6menwUULty3KrkSB8FHwYo7StBPia3PZJ8MVqjCyNfkAg6tatmzSAKG7Pct6HW3oHBVPXVd3Sde3GxbWSWYcNGybrQAmPJZCmSpMmTaRdhj1BBQ1DBO9ZoW3q5gv9La2B9L40Pdg2pAAAukNpkwkDojZj3Hu6++67U75srL383c4EaGAILcYZhfuk7cv9AM8QrUB/WwtJvWadu++IZZtz8ocSg+iWbFDaAF+65aYphWi7YE1CDcIqBlE1II10VKwVxXIeBi9xqVWbGLVaJru8zF8uIV0nf5uk6b72ujVd58uY/O1rFpgRM/4xXW0Tppax63lA2xoHMz38mXjRPDfC/mg/ABmbDIYZXGsArG+oWmq0QmBVADEuYEzD+KBqKJZojYnlHFie1bLIV4qBfTlG1UqWmTT2l2vgHRKXi+C5AkFGRf2j5qXQpklB25BrRmj1HNTuCB75hHxx4YUXyjWo0HD/3JcWDvw+ArDPPvvIuoJRiGvg/tUwReGlQovKjKDw+yoUFPA8N+wcFBT8lvZjRcgpKBDC4LPGC0AfStZxybH9ySefTHYW4Fq5Th2ChfYtxzPRbkyLcMkVSpRshxImRgkdpU5wDkpi7fAYBPM66mqwOq8onhk73lxySltTa63ClCDpRMmLysKKJCW3JYlKVxKnkPnCJQWmsRWgl55/RjZVNYi4Ic4W4SAkEfsAXcUAn17UcA+rPQhfWSDiQLvjR2FVHa9Fixb+WjqMKqWjY4VhS6msIzPH/PdEDRGRy4jlqzNlUjsBa5VrOnWx55U5NR8qahRUv67H3wWVk0YtaomeL2bVhDYP78gWpmLJxKSPf1LN/jHhlFntRD/HMuY6XYNkU03Zjmrphk4BOnrQLxITU9XIWPOp4OD4JJKcEg4jAKE0WImCgucKGtYtd/Al3YZZlgYtgkfNiODpNuY0RKktY8GLqfLYDJ8RhmqrWbNmypeEMqEjCm+77bZ+SimtWrXyDjroIBlROAjfGygsLJTRkGNiVgeytvkw5WKxyhQNT8gRMZvUZoTn4BB2B1kF1EjM2Ji63agDTMCYkwmspv1HREdMzOpApPAxSjLxnKiG6ldywaGIwQSBI4ocn4YVZonhJH7PBYc6PiZiFOle5MK5CbZlLJeYmNWJSOFDgBAMwoC0a4kLDlXSETi+yaDdJMLAGUnNiYDRM96Fmg4/nw6HzvnCiEqPiam02Ewdia3dpP0G9GB34ctCfDgzGyV+z/S5c+fKN9wgrJd12KXg4ysuLvbXYmKqFpE1H+NVENhs95H2Gz4cFyyZGtOXCdRSmDdvnsTH0SFUv2ak8XlAoDb70oO4U6dOEhJFyBHphPYQjAtcT0xMVSCjn48YNcKFcBu4Qb0KwpKPMBBgTfuOOX24unTpIj2bXRBogm9pJ2KgId6OLhvENBInGBNTVcjqZCcYlrg9AmMxsBCwyiFqaEEw9GMW1I50BSLWLxjZoscgzPR8Jvo9UxcS+mMRkE2ALQG9RKYT51mWPl8xMasiWYWPjI/AITjajUgFCai56BGMQYVh7ei2QjQ531xjYCTFPQaI9KaTLVbVYLchBsPRyHGi7jHmEK2e5VJjYioVOYWX0QETAaHjI8IQFCSFSBi20VOBmo3uGW4H0DDYDx9f2BiTuDnwGeKiYPiDjh07Rv52TExlI6uTHVA9CQmjLUZtFZb5UREREgboQe0ksFa/apoJ+lRxbg0no6cx56H2pC8fbT3WGZMDYsGLqSrkHVhN5s90CFEqxIAy9CBqKoHT2UBw2Z/2Ih1EsW4SuA1hYy/GxFQFchY+dkPwGNqNr5+6Xf9dGE6OzpMYaRjRyR0hqjzo78fEVBXyrvkAIaCmok8ftRS1HRM9zvl6EH48xgzRCJhYaGJi0imT8AEDf+KHQ+gQLkapwniCwLENR3kseDExURjzf3EKxYkFGsRoAAAAAElFTkSuQmCC"; var imageId1 = workbook.addImage({ base64: logo, extension: "png" }); //Position fixed from left var headerWidth = Math.max(60, Datasheets.cellWidthsSums[maxMerge]); var imageWidthStart = calculateWidthinCells(headerWidth); var imageWidthEnd = calculateWidthinCells(headerWidth + 35); var imageHeightEnd = calculateHeightinCells(79); var imageWidthEnd2 = calculateWidthinCells(33.1); worksheet.addImage(imageId1, { tl: { col: 0, row: logoRow - 1 }, ext: { width: 223, height: 69 } }); worksheet.views = [ { state: "frozen", xSplit: 0, ySplit: headerRows, activeCell: "A1", showGridLines: false } ]; //Seitendefinitionen fürs Drucken worksheet.pageSetup.paperSize = 9; if (Datasheets.cellWidthsSums[columns.length] <= 95) { worksheet.pageSetup.orientation = "portrait"; } else { worksheet.pageSetup.orientation = "landscape"; } worksheet.pageSetup.scale = 78; //console.log(autofilters); if (autofilters == true) { worksheet.autoFilter = { from: { row: headerRows, column: 1 }, to: { row: headerRows, column: columns.length } }; } for (i = 0; i < datatypes.length; i++) { switch (datatypes[i]) { case "int": case "integer": worksheet.getColumn(i + 1).numFmt = "#,##0"; break; case "float": worksheet.getColumn(i + 1).numFmt = "#,##0.0"; break; case "float2": worksheet.getColumn(i + 1).numFmt = "#,##0.00"; break; case "float6": worksheet.getColumn(i + 1).numFmt = "#,##0.000000"; break; case "percentage": case "percent": worksheet.getColumn(i + 1).numFmt = "0.0%"; break; case "chf": worksheet.getColumn(i + 1).numFmt = '"Fr." #,##0.00'; break; case "miochf": worksheet.getColumn(i + 1).numFmt = '#,##0.00 "Mio Fr."'; break; case "floatha": worksheet.getColumn(i + 1).numFmt = '#,##0.00 "ha"'; break; case "dateyear": worksheet.getColumn(i + 1).numFmt = "yyyy"; break; case "date": worksheet.getColumn(i + 1).numFmt = "dd.mm.yyyy"; break; case "week": worksheet.getColumn(i + 1).numFmt = '"Woche vom" dd.mm.yyyy'; break; case "datemonthyear": worksheet.getColumn(i + 1).numFmt = "mmm yyyy"; break; case "terrajoule": worksheet.getColumn(i + 1).numFmt = '#,##0 "TJ"'; break; } } if (true) { var buff = workbook.xlsx.writeBuffer().then(function (xlsxdata) { var blob = new Blob( [ xlsxdata ] /*, {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}*/ ); saveAs(blob, filename); }); } }35:T65f,createPNG = function (config) { /** * createPNG creates an png file from a given element id on the page and additional parameters. * * @param {Object} config A config object * @param {string} config.containerId html-id of an element to be exported with all it's childern. * @param {string} [config.filename="Grafik.png"] Name of the generated png-file */ let containerId = config.containerId == undefined ? undefined : config.containerId; let filename = config.filename == undefined || config.filename == "undefined.png" ? "Grafik.png" : config.filename; //append .png if needed filename = filename.endsWith(".png") ? filename : filename + ".png"; let node = document.getElementById(containerId); node.classList.add("observable-print"); const filter = (node) => { const exclusionClasses = ["dropdown", "noprint"]; if (node.classList != undefined) { return !exclusionClasses.some((classname) => node.classList.contains(classname) ); } else { return true; } }; htmlToImage .toPng(node, { quality: 1, filter: filter, cacheBust: true }) .then(function (dataUrl) { var link = document.createElement("a"); link.download = filename; link.href = dataUrl; link.click(); node.classList.remove("observable-print"); }); /*htmlToImage.toJpeg(node, { quality: 1, filter: filter, backgroundColor: "#ffffff", cacheBust: true}) .then(function (dataUrl) { var link = document.createElement('a'); link.download = 'my-image-name.jpeg'; link.href = dataUrl; link.click(); });*/ }36:T57a,createSVG = function (config) { /** * createSVG creates an svg-file from a given element id on the page and additional parameters. * * @param {Object} config A config object * @param {string} config.containerId html id of an element to be exported with all it's childern. * @param {string} [config.filename="Grafik.svg"] Name of the generated svg-file, should end with .svg (optional, default is "Grafik.svg") */ let containerId = config.containerId == undefined ? undefined : config.containerId; let filename = config.filename == undefined || config.filename == "undefined.svg" ? "Grafik.svg" : config.filename; //append .svg if needed filename = filename.endsWith(".svg") ? filename : filename + ".svg"; let node = document.getElementById(containerId); node.classList.add("observable-print"); const filter = (node) => { const exclusionClasses = ["dropdown", "noprint"]; if (node.classList != undefined) { return !exclusionClasses.some((classname) => node.classList.contains(classname) ); } else { return true; } }; htmlToImage .toSvg(node, { filter: filter, cacheBust: true }) .then(function (dataUrl) { var link = document.createElement("a"); link.download = filename; link.href = dataUrl; link.click(); node.classList.remove("observable-print"); }); }37:T1173,createEmbedCode = function (uid, type = "javascript") { /** * createEmbedCode creates a string of code that can be used to embed a chart in a website. This function works only if called from a an embeded chart, since it scrapes the embed code from there. * * @param {string} uid The uid of the element that needs to be embeded. * @param {string('javascript', 'iframe')} [type="javascript"] The type of embed code to be generated. The options are 'javascript' (default) or 'iframe'. */ let parent = d3 .select("#dropdowncontainer" + uid) .select(function () { return this.parentNode; }) .select(function () { return this.parentNode; }) .select(function () { return this.parentNode; }) .select(function () { return this.parentNode; }); //Catch if the script element does not exist let script = parent.select("script[type=module]"); if (script.empty()) { return "embed code generation works only if the chart is embeded somewhere using the javascript runtime"; } script = parent.select("script[type=module]").text(); let regex = /api\.observablehq\.com\/(.+)\.js/gm; let m; let urlslug; while ((m = regex.exec(script)) !== null) { // This is necessary to avoid infinite loops with zero-width matches if (m.index === regex.lastIndex) { regex.lastIndex++; } urlslug = m[1]; // The result can be accessed through the `m`-variable. /*m.forEach((match, groupIndex) => { console.log(`Found match, group ${groupIndex}: ${match}`); });*/ } let regexcells = /name === "(.+)"\) return new/gm; let cells = []; while ((m = regexcells.exec(script)) !== null) { // This is necessary to avoid infinite loops with zero-width matches if (m.index === regex.lastIndex) { regex.lastIndex++; } cells.push(m[1]); // The result can be accessed through the `m`-variable. /*m.forEach((match, groupIndex) => { console.log(`Found match, group ${groupIndex}: ${match}`); });*/ } let regexhiddencells = /name === "(.+)"\) return true/gm; let hiddencells = []; while ((m = regexhiddencells.exec(script)) !== null) { // This is necessary to avoid infinite loops with zero-width matches if (m.index === regex.lastIndex) { regex.lastIndex++; } hiddencells.push(m[1]); // The result can be accessed through the `m`-variable. /*m.forEach((match, groupIndex) => { console.log(`Found match, group ${groupIndex}: ${match}`); });*/ } let hex = randomHex(4); let code = ``; if (type == "javascript") { for (let cell of cells) { code = code + `

`; } code = code + `

Quelle: Kanton Zürich

`; } if (type == "iframe") { /*code = code + ``;*/ code = ``; } return code; }38:T8e4,//function to create rich text object, if text provided contains html tags (still very bassic, only one layer supported and only subscript, superscript, italic and bold.) function createRichText(html, size, bold) { var tmp = document.createElement("DIV"); tmp.innerHTML = html; if (tmp.childNodes.length == 0) { var rT = ""; } else if ((tmp.childNodes.length == 1) & (tmp.childNodes[0].nodeType == 3)) { var rT = tmp.childNodes[0].nodeValue; } else { var rT = {}; rT.richText = []; for (let node = 0; node < tmp.childNodes.length; ++node) { if (tmp.childNodes[node].nodeType == 3) { rT.richText.push({ font: { name: "Arial", family: 2, size: size, bold: bold }, text: tmp.childNodes[node].nodeValue }); } if (tmp.childNodes[node].nodeType == 1) { if (tmp.childNodes[node].nodeName == "SUB") { rT.richText.push({ font: { name: "Arial", family: 2, size: size, bold: bold, vertAlign: "subscript" }, text: tmp.childNodes[node].innerText }); } else if (tmp.childNodes[node].nodeName == "SUP") { rT.richText.push({ font: { name: "Arial", family: 2, size: size, bold: bold, vertAlign: "superscript" }, text: tmp.childNodes[node].innerText }); } else if (tmp.childNodes[node].nodeName == "I") { rT.richText.push({ font: { name: "Arial", family: 2, size: size, bold: bold, italic: "true" }, text: tmp.childNodes[node].innerText }); } else if (tmp.childNodes[node].nodeName == "B") { rT.richText.push({ font: { name: "Arial", family: 2, size: size, bold: true }, text: tmp.childNodes[node].innerText }); } else { rT.richText.push({ font: { name: "Arial", family: 2, size: size, bold: bold }, text: tmp.childNodes[node].innerText }); } } } } //console.log(rT); return rT; }39:T8ca,//not shure if they are still needed polyfills= { //Polyfill for Object.assign (for IE) if (typeof Object.assign != 'function') { Object.assign = function(target) { 'use strict'; if (target == null) { throw new TypeError('Cannot convert undefined or null to object'); } target = Object(target); for (var index = 1; index < arguments.length; index++) { var source = arguments[index]; if (source != null) { for (var key in source) { if (Object.prototype.hasOwnProperty.call(source, key)) { target[key] = source[key]; } } } } return target; }; } //Polyfill for String.startsWith if (!String.prototype.startsWith) { (function() { 'use strict'; // needed to support `apply`/`call` with `undefined`/`null` var defineProperty = (function() { // IE 8 only supports `Object.defineProperty` on DOM elements try { var object = {}; var $defineProperty = Object.defineProperty; var result = $defineProperty(object, object, object) && $defineProperty; } catch(error) {} return result; }()); var toString = {}.toString; var startsWith = function(search) { if (this == null) { throw TypeError(); } var string = String(this); if (search && toString.call(search) == '[object RegExp]') { throw TypeError(); } var stringLength = string.length; var searchString = String(search); var searchLength = searchString.length; var position = arguments.length > 1 ? arguments[1] : undefined; // `ToInteger` var pos = position ? Number(position) : 0; if (pos != pos) { // better `isNaN` pos = 0; } var start = Math.min(Math.max(pos, 0), stringLength); // Avoid the `indexOf` call if no match is possible if (searchLength + start > stringLength) { return false; } var index = -1; while (++index < searchLength) { if (string.charCodeAt(start + index) != searchString.charCodeAt(index)) { return false; } } return true; }; if (defineProperty) { defineProperty(String.prototype, 'startsWith', { 'value': startsWith, 'configurable': true, 'writable': true }); } else { String.prototype.startsWith = startsWith; } }()); } }3a:[] 32:{"id":"afb9e5e77509934c","slug":"export-functions","title":"Export functions","thumbnail":"49e3b8e7b5c25c76853ac31b4ed41e895e2facb91573182f4e68e995d0ccb899","access_level":"public","create_time":"$D2021-08-23T08:04:25.949Z","update_time":"$D2024-11-20T12:51:44.108Z","view_time":null,"first_public_time":"$D2021-11-08T14:22:10.465Z","has_unpublished_edits":null,"fork":false,"stars":3,"starred":false,"owner":{"type":"team","id":"480a316ccb4f6253","login":"bildungsplanungzh","name":"Bildungsplanung Kanton Zürich","avatar_url":"https://avatars-next.observableusercontent.com/2cb1f8851e077d6126eed5b60e134287f21a1bc0897970ba693f8aa5b3dc3bb3","tier":"pro_2024"},"version":943,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"ae2fb568d64d2eb1","login":"resmartizh","name":"Res Marti","avatar_url":"https://avatars-next.observableusercontent.com/69661c8e64ee6b7b99aba46691979edfa94971eb1e410861d738d2401ebe034e","tier":null}],"members":[{"type":"team","id":"480a316ccb4f6253","login":"bildungsplanungzh","name":"Bildungsplanung Kanton Zürich","avatar_url":"https://avatars-next.observableusercontent.com/2cb1f8851e077d6126eed5b60e134287f21a1bc0897970ba693f8aa5b3dc3bb3","tier":"pro_2024","role":"editor"},{"type":"individual","id":"ae2fb568d64d2eb1","login":"resmartizh","name":"Res Marti","avatar_url":"https://avatars-next.observableusercontent.com/69661c8e64ee6b7b99aba46691979edfa94971eb1e410861d738d2401ebe034e","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":2,"collection":null,"type":"notebook","body":{"title":"Export functions","cells":[{"id":0,"value":"# Export functions","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":913,"value":"toc({ selector: \"h2,h3,h4,h5,h6\", heading: \"Inhaltsverzeichnis\" })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":10,"value":"md `## Beispieldaten`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":257,"value":"datenset=[{\"test\":11,\"coll2\":12,\"columntotestbroadcolumntitles\":13},\n {\"test\":21,\"coll2\":22,\"columntotestbroadcolumntitles\":23}]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":326,"value":"## CSV export","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":434,"value":"### Funktion für den Export von CSV-Dateien","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":294,"value":"createCSV = function (config) {\n /**\n * createCSV function generates and returns a csv to the browser calling the function.\n *\n * @param {Object} config A config object\n * @param {string} [config.filename=\"daten.csv\"] A string with the filename.\n * @param {Object[]} config.data The data to be included in the csv as an array of objects\n *\n */\n\n //define default for filename if it is not defined.\n var filename = config.filename == undefined ? \"daten.csv\" : config.filename;\n //append .csv if needed\n filename = filename.endsWith(\".csv\") ? filename : filename + \".csv\";\n //if we want to feed d3 objects:\n //var data = data.top(Infinity);\n var blob = new Blob([d3.csvFormat(config.data)], {\n type: \"text/csv;charset=utf-8\"\n });\n //console.log(d3.csvFormat(data, [\"Bezirk\", \"2012\"]));\n saveAs(blob, filename);\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":439,"value":"### Beispiel zur Verwengung","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":645,"value":"csvConfig=({filename:\"test.csv\",\n data: datenset})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":302,"value":"viewof buttonCsv = Inputs.button(\"Download CSV\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":305,"value":"//uncomment to see the number of clicks\n//buttonCsv","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":309,"value":"runDownloadCsv = {\n if (buttonCsv>=1) {\n createCSV(csvConfig)\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":331,"value":"## Excel export","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":445,"value":"### Funktion für den Export von Excel-Datein","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":53,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":449,"value":"### Beispiel zur Verwendung\n\nKonfigurationsobjekt für den Export einer Excel-Datei","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":394,"value":"xlsxConfig=({data: datenset,\n columns:['test','coll2','columntotestbroadcolumntitles'],\n datatypes:[\"integer\",\"integer\",\"integer\"],\n title:\"Testtitel\", \n subtitle:\"Testuntertitel\",\n description:\"Testbeschreibung\",\n source:\"Testquelle\",\n autofilters:true,\n filename: \"test.xlsx\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":105,"value":"viewof buttonXlsx = Inputs.button(\"Download xlsx\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":116,"value":"//buttonXlsx","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":109,"value":"runDownloadXlsx = {\n if (buttonXlsx>=1) {\n createXLSX(xlsxConfig)\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":537,"value":"## PNG export","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":542,"value":"### Funktion für den Export von PNG-Dateien","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":547,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":556,"value":"### Beispiel zur Verwendung\n\nKonfigurationsobject für den Export einer PNG-Datei.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":560,"value":"pngConfig=({containerId: \"testcontainer\",\n filename: \"test.png\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":599,"value":"viewof buttonPng = Inputs.button(\"Download png\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":603,"value":"runDownloadPng = {\n if (buttonPng>=1) {\n console.log(pngConfig)\n createPNG(pngConfig)\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":620,"value":"## SVG export","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":624,"value":"Funktion für den Export als SVG. Sollte auch die Formatierung von HTML-Elementen übernehmen.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":628,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":631,"value":"### Beispiel zur Verwendung\n\nKonfigurationsobject für den Export einer SVG-Datei.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":637,"value":"svgConfig=({containerId: \"testcontainer\",\n filename:\"test.svg\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":639,"value":"viewof buttonSvg = Inputs.button(\"Download svg\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":642,"value":"runDownloadSvg = {\n if (buttonSvg>=1) {\n createSVG(svgConfig)\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":719,"value":"## Embed Code generieren","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":714,"value":"$37","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":565,"value":"container = html`

Container to be exported

${chart}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":494,"value":"### Zusätzlich benötigte Funktionen","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":25,"value":"//function for calculation text width in pixel\nfunction getTextWidth(text, font) {\n// re-use canvas object for better performance\n var canvas = getTextWidth.canvas || (getTextWidth.canvas = document.createElement(\"canvas\"));\n var context = canvas.getContext(\"2d\");\n context.font = font;\n var metrics = context.measureText(strip(text));\n return metrics.width;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":28,"value":"//function to remove all html-tags (currently only used in getTextWidth)\nfunction strip(html)\n{\n\tvar tmp = document.createElement(\"DIV\");\n\tif (html instanceof Date) {\n\t\thtml=html.toISOString().slice(0,10).replace(/-/g,\".\");\n\t}\n\ttmp.innerHTML = html;\n\treturn tmp.textContent || tmp.innerText || \"\";\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":30,"value":"$38","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":19,"value":"$39","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":39,"value":"import { germanFormater, formater } from \"@bildungsplanungzh/general\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":561,"value":"import {chart} from \"@d3/contours\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":337,"value":"## TXT export (unused)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":176,"value":"blob = new Blob([\"Hello, world!\"], {type: \"text/plain;charset=utf-8\"});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":178,"value":"//uncomment to generate txt-File\n//{saveAs(blob, \"hello world.txt\");}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":341,"value":"## Libraries","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":78,"value":"//jquery is not needed here\n//$ = require('jquery')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":99,"value":"d3 = require(\"d3@7\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":6,"value":"ExcelJS = require('exceljs@4.3.0/dist/exceljs.min.js')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":194,"value":"saveAs = require('https://bundle.run/file-saver@2.0.5')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":551,"value":"htmlToImage= require('html-to-image@1.9.0/dist/html-to-image.js')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":722,"value":"import { randomHex } from \"@mbostock/random-hex-string\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":915,"value":"import { toc } from \"@harrystevens/toc\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":927,"value":"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W3a","stdlib":"1","titling":"auto"}} 33:"$32:body"