1/* DOKUWIKI:include packages/exceljs/exceljs.js */ 2/* DOKUWIKI:include packages/xlsx/xlsx.mjs */ 3/* DOKUWIKI:include packages/xlsx/cpexcel.full.mjs */ 4/* DOKUWIKI:include packages/jszip/jszip.js */ 5/* DOKUWIKI:include packages/xmltojson/xmltojson.js */ 6 7XLSX.set_cptable({ 8 cptable, 9 utils 10}); 11 12function xlsx2dwButtonOnClick() { 13 let input = document.createElement('input'); 14 input.type = 'file'; 15 input.accept = ".xls,.xlsx,.ods"; 16 input.onchange = (e) => parseTableFile(e); 17 input.click(); 18} 19 20function parseTableFile(e) { 21 var file = e.target.files?.[0]; 22 if(!file) 23 throw "File is undefined or unselected."; 24 let fileName = file.name; 25 26 let reader = new FileReader(); 27 reader.onload = async function(e) { 28 let formattedTable; 29 switch(fileName.slice(fileName.lastIndexOf(".")+1).toLowerCase()) { 30 case "xlsx": 31 formattedTable = await getFormattedTableFromXLSX(e.target.result); 32 break; 33 case "xls": 34 formattedTable = await getFormattedTableFromXLS(e.target.result); 35 break; 36 case "ods": 37 formattedTable = await getFormattedTableFromODS(e.target.result); 38 break; 39 default: 40 throw "Wrong file format."; 41 } 42 let text = getTextFromFormattedTable(formattedTable); 43 insertTextToDokuWiki(text); 44 }; 45 reader.readAsArrayBuffer(file); 46} 47 48function formattedTablePostroutine(table) { 49 let maxCellsPerRow = Math.max(...table.map(row => row.length)); 50 table.forEach(row => { 51 while(row.length < maxCellsPerRow) 52 row.push({isEmpty: true}); 53 }); 54 table.forEach(row => { 55 row.forEach((cell, index) => { 56 row[index] = { 57 value: cell?.value ?? "", 58 isEmpty: cell?.isEmpty ?? false, 59 isMerged: cell?.isMerged ?? false, 60 isMergedFirstColumn: cell?.isMergedFirstColumn ?? false, 61 62 isBold: cell?.isBold ?? false, 63 isItalic: cell?.isItalic ?? false, 64 isUnderline: cell?.isUnderline ?? false, 65 isStrike: cell?.isStrike ?? false, 66 alignmentHorizontal: cell?.alignmentHorizontal ?? "left", 67 68 colorFont: (cell?.colorFont && cell.colorFont.length === 7) ? cell.colorFont : "#000000", 69 colorBackground: (cell?.colorBackground && cell.colorBackground.length === 7) ? cell.colorBackground : "#FFFFFF" 70 }; 71 }); 72 }); 73} 74 75// From ODS to XLSX using the xlsx library 76async function getFormattedTableFromODS(file) { 77 // 1. Present the ODS file as a ZIP archive and get the content.xml file from it. let zip = new JSZip(); 78 await zip.loadAsync(file); 79 let tableStringXML = await zip.files["content.xml"].async('text'); 80 81 // 2. Convert XML string to JSON object. 82 let tableJSON = JSON.parse(xmlToJson(tableStringXML)); 83 84 console.log(tableJSON); 85 86 // 3. Get cell styles with the required fields as a map object. 87 let styleMap = new Map(tableJSON["office:document-content"]["office:automatic-styles"]["style:style"].map(style => [style["-style:name"], { 88 isBold: (style["style:text-properties"]?.["-fo:font-weight"] === "bold"), 89 isItalic: (style["style:text-properties"]?.["-fo:font-style"] === "italic"), 90 isUnderline: (style["style:text-properties"]?.["-style:text-underline-style"] === "solid"), 91 isStrike: (style["style:text-properties"]?.["-style:text-line-through-style"] === "solid"), 92 alignmentHorizontal: (style["style:paragraph-properties"]?.["-fo:text-align"] === "start") ? "left" 93 : (style["style:paragraph-properties"]?.["-fo:text-align"] === "end") ? "right" 94 : style["style:paragraph-properties"]?.["-fo:text-align"] ?? "left", 95 colorFont: style["style:text-properties"]?.["-fo:color"] ?? "#000000", 96 colorBackground: style["style:table-cell-properties"]?.["-fo:background-color"] ?? "#FFFFFF", 97 }])); 98 99 // 4. Get the cell data from the JSON object as a two-dimensional array. 100 // To do this, iterate and remake from a non-uniform array 101 // (infinite nesting through the "#item" field is possible) into a two-dimensional one. 102 let table = []; 103 let recursiveCellExportODS = function(row, exportParsedRow) { 104 // a) Check the series for correctness - this is a regular series of non-zero length, 105 // and no indicator at the end of the rows, showing the style at "infinity" 106 // (usually row count over 10^6). 107 if((Number(row["-table:number-rows-repeated"]) || 1) > 10**6) 108 return; 109 row = [].concat(...[row["table:covered-table-cell"], row["table:table-cell"]].map(row => { 110 if(row === undefined) 111 return []; 112 if(!Array.isArray(row)) 113 return [row]; 114 return row; 115 })); 116 if(row.length === 0) 117 return; 118 // b) For each cell in the array. 119 row.forEach(cell => { 120 if(cell["#item"] !== undefined) { 121 // c) "#item" can be any depth, so 122 // recursively call the function on this object. 123 recursiveCellExportODS(cell["#item"], exportParsedRow); 124 } else if( 125 // d) If it's a regular cell, not an indicator at the end of the array (see point a). 126 ((cell["-self-closing"] !== undefined) || (cell["-office:value-type"] !== undefined)) && 127 ((Number(cell["-table:number-columns-repeated"]) || 1) < 10**3) 128 ) { 129 exportParsedRow.push(cell); 130 } 131 }); 132 }; 133 tableJSON["office:document-content"]["office:body"]["office:spreadsheet"]["table:table"]["table:table-row"].forEach((row => { 134 let parsedRow = []; 135 recursiveCellExportODS(row, parsedRow); 136 table.push(parsedRow); 137 })); 138 // 5. Remove extreme empty lines. 139 while((table.length > 0) && (table[table.length-1].length === 0)) 140 table.pop(); 141 142 // 6. Allocate the necessary data for Dokuwiki. 143 let formattedTable = []; 144 table.forEach((row, rowIndex) => { 145 // Существует ли уже такой ряд? Если нет, то внести в массив рядов. 146 // Поскольку номера рядов не уменьшаются, то считаю допустимым 147 // использовать push НОВОГО пустого массива. 148 if(formattedTable[rowIndex] === undefined) 149 formattedTable.push(new Array(0)); 150 row.forEach((cell, columnIndex) => { 151 if(formattedTable[rowIndex]?.[columnIndex] !== undefined) { 152 // a) A cell has already been written at this position as a result of additions. 153 // (see implementation below in "c"). 154 return; 155 } 156 let mergedColumns = Number(cell["-table:number-columns-spanned"]) || 1; 157 let mergedRows = Number(cell["-table:number-rows-spanned"]) || 1; 158 if((mergedColumns === 1) && (mergedRows === 1)) { 159 // b) Single cell. 160 formattedTable[rowIndex][columnIndex] = { 161 ...{ 162 value: cell["text:p"] ?? "", 163 isEmpty: !cell["text:p"], 164 isMerged: false, 165 isMergedFirstColumn: false 166 }, 167 ...(styleMap.get(cell["-table:style-name"]) ?? {}) 168 }; 169 } else { 170 // c) The remaining option is a merged cell. 171 // It is necessary to write the merged cells according to the corresponding indices 172 // to the right and bottom of the main cell. 173 for(let i = 0; i < mergedRows; i++) { 174 if(formattedTable[rowIndex+i] === undefined) 175 formattedTable.push(new Array(0)); 176 for(let j = 0; j < mergedColumns; j++) { 177 formattedTable[rowIndex+i][columnIndex+j] = { 178 ...{ 179 value: (i+j === 0) ? (cell["text:p"] ?? "") : "", 180 isEmpty: (i+j === 0) ? !cell["text:p"] : true, 181 isMerged: true, 182 isMergedFirstColumn: (j === 0) 183 }, 184 ...(styleMap.get(cell["-table:style-name"]) ?? {}) 185 }; 186 } 187 } 188 } 189 }); 190 }); 191 formattedTablePostroutine(formattedTable); 192 return formattedTable; 193} 194 195// From XLS to XLSX using the xlsx library 196async function getFormattedTableFromXLS(file) { 197 /** 198 There should be a function here 199 turns XLS into a formatted table 200 with styles. For now, there will be a challenge already 201 existing function for XLSX after conversion 202 to this format, with the loss of styles. 203 */ 204 let xlsxWorkbook = XLSX.read(file); 205 let xlsxRawTable = XLSX.write(xlsxWorkbook, {type: 'binary', bookType: 'xlsx'}); 206 return await getFormattedTableFromXLSX(xlsxRawTable); 207} 208 209// Works with the ExcelJS library 210async function getFormattedTableFromXLSX(file) { 211 // 1. Open the XLSX table. 212 let workbook = new ExcelJS.Workbook(); 213 await workbook.xlsx.load(file); 214 let worksheet = workbook.worksheets[0]; 215 let formattedTable = []; 216 217 // 1.1. Save from the table today about colors. 218 // This is needed for cases where the color was selected 219 // from the suggested color themes. 220 let themesXML = workbook._themes?.theme1 ?? ""; 221 let themesJSON = JSON.parse(xmlToJson(themesXML)); 222 let colorsJSON = Object.values(themesJSON 223 ?.["a:theme"] 224 ?.["a:themeElements"] 225 ?.["a:clrScheme"] ?? {}) 226 .map((item) => { 227 let color = item["a:srgbClr"]?.["-val"] 228 ?? item["a:sysClr"]?.["-lastClr"] 229 ?? undefined; 230 if(!!color) 231 color = "#" + color; 232 return color; 233 }).slice(1); 234 235 // 1.2. A function to define a color. 236 // It's easier to declare and describe it here, 237 // so as not to write a lot in paragraphs 2a, 2b. 238 function getColorXLSX(cell, type) { 239 switch(type) { 240 case "font": 241 let fontStyle = cell.style.font?.color; 242 if(fontStyle?.argb) 243 return "#" + (cell.style.font.color.argb.slice(2) || "000000"); 244 if(fontStyle?.theme !== undefined) { 245 if(fontStyle.theme === 0) 246 return "#000000"; 247 return colorsJSON[fontStyle.theme]; 248 } 249 return undefined; 250 default: 251 case "background": 252 let fgStyle = cell.style.fill?.fgColor; 253 if(fgStyle?.argb) 254 return "#" + (cell.style.fill?.fgColor?.argb?.slice(2) || "FFFFFF"); 255 if(fgStyle?.theme !== undefined) { 256 if(fgStyle.theme === 0) 257 return "#FFFFFF"; 258 return colorsJSON[fgStyle.theme]; 259 } 260 return undefined; 261 } 262 } 263 264 // 2. Walk through all the cells in the table and extract the necessary data for Dokuwiki. 265 worksheet.eachRow(function(row, rowNumber) { 266 // Fill the table with empty rows 267 while(formattedTable.length < rowNumber-1) 268 formattedTable.push([]); 269 let formattedRow = []; 270 row._cells.forEach(function(cell, colNumber) { 271 // Fill the line with empty cells 272 while(formattedRow.length < colNumber) 273 formattedRow.push({isEmpty: true}); 274 // Consider the cell 275 let formattedCell = {}; 276 if(!cell.isMerged) { 277 // a) If it's a regular cell with data 278 formattedCell = { 279 value: cell.value ?? "", 280 isEmpty: !cell.value?.length, 281 isMerged: false, 282 isMergedFirstColumn: false, 283 284 isBold: cell.style.font?.bold || false, 285 isItalic: cell.style.font?.italic || false, 286 isUnderline: cell.style.font?.underline || false, 287 isStrike: cell.style.font?.strike || false, 288 alignmentHorizontal: cell.style?.alignment?.horizontal || "left", 289 290 colorFont: getColorXLSX(cell, "font"), 291 colorBackground: getColorXLSX(cell, "background") 292 }; 293 } else if((cell?._mergeCount ?? 0) > 0) { 294 // b) If _mergeCount > 0, then this is the main cell 295 formattedCell = { 296 value: cell.value ?? "", 297 isEmpty: false, // To distinguish main from attached on main column 298 isMerged: true, 299 isMergedFirstColumn: true, 300 301 isBold: cell.style.font?.bold || false, 302 isItalic: cell.style.font?.italic || false, 303 isUnderline: cell.style.font?.underline || false, 304 isStrike: cell.style.font?.strike || false, 305 alignmentHorizontal: cell.style?.alignment?.horizontal || "left", 306 307 colorFont: getColorXLSX(cell, "font"), 308 colorBackground: getColorXLSX(cell, "background") 309 }; 310 } else if(formattedTable[rowNumber-2]?.[colNumber]?.isMergedFirstColumn) { 311 // c) If the cell is in the main column 312 formattedCell = { 313 isEmpty: true, 314 isMerged: true, 315 isMergedFirstColumn: true, 316 }; 317 } else { 318 // d) The last option - the cell is not in the main column 319 formattedCell = { 320 isEmpty: true, 321 isMerged: true, 322 isMergedFirstColumn: false, 323 }; 324 } 325 formattedRow.push(formattedCell); 326 }); 327 formattedTable.push(formattedRow); 328 }); 329 formattedTablePostroutine(formattedTable); 330 return formattedTable; 331} 332 333function setStyle(cell) { 334 let styledCell = cell.value; 335 336 if (cell.isBold) styledCell = `**${styledCell}**`; 337 if (cell.isItalic) styledCell = `\/\/${styledCell}\/\/`; 338 if (cell.isUnderline) styledCell = `__${styledCell}__`; 339 if (cell.isStrike) styledCell = `<del>${styledCell}</del>`; 340 341 if (cell.isMerged) { 342 if (cell.isMergedFirstColumn) { 343 if (!cell.isEmpty) return ' ' + styledCell + ' '; 344 else return ':::'; 345 } else { 346 return ''; 347 } 348 } 349 350 if (!cell.value && !cell.isMerged) { 351 return ' '; 352 } 353 354 switch(cell.alignmentHorizontal) { 355 case "left": 356 styledCell = styledCell + ' '; 357 break; 358 case "center": 359 styledCell = ' ' + styledCell + ' '; 360 break; 361 case "right": 362 styledCell = ' ' + styledCell; 363 break; 364 } 365 return styledCell; 366} 367 368// Output styles. 369function getTextFromFormattedTable(formattedTable) { 370 return formattedTable 371 .map(formattedRow => { 372 return "|" + 373 formattedRow 374 .map((cell) => setStyle(cell)) 375 .join("|") + 376 "|"; 377 }).join("\n"); 378} 379 380function insertTextToDokuWiki(text) { 381 let textArea = jQuery('#wiki__text'); 382 let cursorPosition = textArea[0].selectionStart || 0; 383 let sourceText = textArea.val(); 384 textArea.val( 385 sourceText.slice(0, cursorPosition) + 386 text + 387 sourceText.slice(cursorPosition) 388 ); 389 return; 390} 391 392jQuery(document).ready(() => { 393 jQuery('#xlsx2dw_btn').click(() => { 394 xlsx2dwButtonOnClick(); 395 }); 396}); 397