1/* 2 * Copyright (C) 2022 Huawei Device Co., Ltd. 3 * Licensed under the Apache License, Version 2.0 (the "License"); 4 * you may not use this file except in compliance with the License. 5 * You may obtain a copy of the License at 6 * 7 * http://www.apache.org/licenses/LICENSE-2.0 8 * 9 * Unless required by applicable law or agreed to in writing, software 10 * distributed under the License is distributed on an "AS IS" BASIS, 11 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 * See the License for the specific language governing permissions and 13 * limitations under the License. 14 */ 15 16const htmlStr = () => { 17 const html_start = `<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">`; 18 return { 19 uri : 'data:application/vnd.ms-excel;base64,', 20 template_ExcelWorksheet : `<x:ExcelWorksheet><x:Name>{SheetName}</x:Name><x:WorksheetSource HRef="sheet{SheetIndex}.htm"/><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>`, 21 template_ListWorksheet : `<o:File HRef="sheet{SheetIndex}.htm"/>`, 22 template_WorkBook : 23 `MIME-Version: 1.0 24X-Document-Type: Workbook 25Content-Type: multipart/related; boundary="----=_NextPart_dummy" 26 27------=_NextPart_dummy 28Content-Location: WorkBook.htm 29Content-Type: text/html; charset=windows-1252 30 31` + 32 html_start + 33 ` 34<head> 35<meta name="Excel Workbook Frameset"> 36<meta http-equiv="Content-Type" charset="UTF-8" content="text/html; charset=windows-1252"> 37<link rel="File-List" href="filelist.xml"> 38<!--[if gte mso 9]><xml> 39 <x:ExcelWorkbook> 40 <x:ExcelWorksheets>{ExcelWorksheets}</x:ExcelWorksheets> 41 <x:ActiveSheet>0</x:ActiveSheet> 42 </x:ExcelWorkbook> 43</xml><![endif]--> 44</head> 45<frameset> 46 <frame src="sheet0.htm" name="frSheet"> 47 <noframes><body><p>This page uses frames, but your browser does not support them.</p></body></noframes> 48</frameset> 49</html> 50{HTMLWorksheets} 51Content-Location: filelist.xml 52Content-Type: text/xml; charset="utf-8" 53 54<xml xmlns:o="urn:schemas-microsoft-com:office:office"> 55 <o:MainFile HRef="../WorkBook.htm"/> 56 {ListWorksheets} 57 <o:File HRef="filelist.xml"/> 58</xml> 59------=_NextPart_dummy-- 60` 61 } 62} 63 64export class ExcelFormater { 65 static tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'; 66 static base64 = function (s: any) { 67 return window.btoa(unescape(encodeURIComponent(s))); 68 }; 69 70 static format(s: any, c: any): string { 71 return s.replace(/{(\w+)}/g, function (m: any, p: any) { 72 return c[p]; 73 }); 74 } 75 76 static createExcelRow(columns: any[], data: any): string { 77 let rowsXML = ''; 78 rowsXML += '<Row>'; 79 for (let k = 0; k < columns.length; k++) { 80 let dataIndex = columns[k].getAttribute('data-index'); 81 let columnName = columns[k].getAttribute('title'); 82 if (columnName == '') { 83 columnName = dataIndex; 84 } 85 let ctx = { 86 attributeStyleID: '', 87 nameType: 'String', 88 data: data ? data[dataIndex] || '' : columnName, 89 attributeFormula: '', 90 }; 91 rowsXML += this.format(this.tmplCellXML, ctx); 92 } 93 rowsXML += '</Row>'; 94 if (data && data.children != undefined && data.children.length > 0) { 95 data.children.forEach((child: any) => { 96 rowsXML += this.createExcelRow(columns, child); 97 }); 98 } 99 return rowsXML; 100 } 101 102 static addImage(baseStr: string) { 103 return `<Row>${this.format(this.tmplCellXML, { 104 attributeStyleID: '', 105 nameType: 'String', 106 data: `<div><img src="${baseStr}"></img></div>`, 107 attributeFormula: '', 108 })}</Row>`; 109 } 110 111 static testExport(dataSource: { columns: any[]; tables: any[]; sheetName: string }[], fileName: string) { 112 this.tablesToHtmlExcelMultipleSheet(dataSource, fileName); 113 } 114 115 static tablesToHtmlExcelMultipleSheet( 116 dataSource: { columns: any[]; tables: any[]; sheetName: string }[], 117 fileName: string, 118 image?: string 119 ) { 120 let sheets: any[] = []; 121 dataSource.forEach((data) => { 122 sheets.push(this.createTableData(data.columns, data.tables, image)); 123 }); 124 this.tablesToExcelTestSheet(sheets, fileName, dataSource); 125 } 126 127 static createTableData(columns: any[], dataSource: any[], image?: string) { 128 let tableData = ''; 129 let columnDatas = columns.map((column) => { 130 let dataIndex = column.getAttribute('data-index'); 131 let columnName = column.getAttribute('title'); 132 if (columnName == '') { 133 columnName = dataIndex; 134 } 135 return { 136 columnName: columnName, 137 dataIndex: dataIndex, 138 }; 139 }); 140 tableData += this.createTHead( 141 columnDatas.map((item) => { 142 return item.columnName; 143 }) 144 ); 145 let columnDataIndexes = columnDatas.map((item) => item.dataIndex); 146 dataSource.forEach((data, index) => { 147 if (index == 0 && image) { 148 tableData += this.createTableRow(columnDataIndexes, data, image); 149 } else { 150 tableData += this.createTableRow(columnDataIndexes, data); 151 } 152 }); 153 return tableData; 154 } 155 156 static createTHead(columns: any[]) { 157 let header = '<thead>'; 158 columns.forEach((column) => { 159 header += `<td>${column}</td>`; 160 }); 161 header += '</thrad>'; 162 return header; 163 } 164 165 static createTableRow(columns: any[], data: any, image?: any) { 166 let childrenData = ''; 167 if (data.children !== undefined) { 168 data.children.forEach((child: any) => { 169 if (child) { 170 childrenData += this.createTableRow(columns, child); 171 } 172 }); 173 } 174 return `<tr>${columns 175 .map((column) => { 176 return `<td>${(data[column] + '').replace('μ', 'u')}</td>` || ''; 177 }) 178 .join('')}${image ? `<td><div><img src="${image}"></img></div></td>` : ''}</tr>${childrenData}`; 179 } 180 181 static tablesToExcelTestSheet( 182 tables: any[], 183 filename: string, 184 dataSource: { columns: any[]; tables: any[]; sheetName: string }[] 185 ) { 186 const html_start = `<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">`; 187 let {uri,template_ExcelWorksheet,template_ListWorksheet,template_WorkBook} = htmlStr(); 188 let template_HTMLWorksheet = 189 ` 190------=_NextPart_dummy 191Content-Location: sheet{SheetIndex}.htm 192Content-Type: text/html; charset=windows-1252 193 194` + 195 html_start + 196 ` 197<head> 198 <meta http-equiv="Content-Type" charset="UTF-8" content="text/html; charset=windows-1252"> 199 <link id="Main-File" rel="Main-File" href="../WorkBook.htm"> 200 <link rel="File-List" href="filelist.xml"> 201</head> 202<body><table>{SheetContent}</table></body> 203</html>`; 204 let context_WorkBook = { 205 ExcelWorksheets: '', 206 HTMLWorksheets: '', 207 ListWorksheets: '', 208 }; 209 tables.forEach((item, sheetIndex) => { 210 context_WorkBook.ExcelWorksheets += this.format(template_ExcelWorksheet, { 211 SheetIndex: sheetIndex, 212 SheetName: dataSource[sheetIndex].sheetName, 213 }); 214 context_WorkBook.HTMLWorksheets += this.format(template_HTMLWorksheet, { 215 SheetIndex: sheetIndex, 216 SheetContent: item, 217 }); 218 context_WorkBook.ListWorksheets += this.format(template_ListWorksheet, { 219 SheetIndex: sheetIndex, 220 }); 221 }); 222 let link = document.createElement('a'); 223 link.href = uri + this.base64(this.format(template_WorkBook, context_WorkBook)); 224 link.download = filename + '.xls'; 225 link.target = '_blank'; 226 link.click(); 227 } 228} 229