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); }); } }