Issue
I need to export an HTML Table to Excel file, but I need to keep the css styles, like background color, borders, etc...
Here a fragment of my table
<div>
<table #excel_table>
<tbody>
<tr class="table-header">
<td> General specifications </td>
</tr>
<tr class="table-tr-spacer"></tr>
<tr>
<td class="table-td-title"> CODE </td>
<td class="table-td-data"> XXXX</td>
</tr>
<tr>
<td class="table-td-title"> MODEL </td>
<td class="table-td-data"> XXXXX </td>
</tr>
<tr class="table-tr-spacer"></tr>
<tr>
<td class="table-td-title"> POWER (kVA) </td>
<td class="table-td-data"> XXX </td>
</tr>
<tr>
<td class="table-td-title"> PHASES </td>
<td class="table-td-data"> X </td>
</tr>
<tr class="table-tr-spacer"></tr>
<tr class="table-tr-spacer"></tr>
<tr class="table-header">
<td> Technical specifications </td>
</tr>
<tr class="table-tr-spacer"></tr>
<tr>
<td> LEFT CORE LEG </td>
<td class="table-td-title center-text"> Voltage (V) </td>
<td class="table-td-title center-text"> Current (A) </td>
<td class="table-td-title center-text"> Number of turns ±2‰ </td>
<td class="table-td-title center-text"> Wire Diameter (mm) </td>
</tr>
<tr>
<td class="table-td-title"> PRIMARY WINDING/S </td>
<td class="table-td-data"> XXX</td>
<td class="table-td-data"> XXX </td>
<td class="table-td-data"> XXX</td>
<td class="table-td-data"> XXX </td>
</tr>
<tr>
<td class="table-td-title"> ELECTRIC SHIELD </td>
</tr>
...
</tbody>
</table>
</div>
I tried with differents plugins, like 'xlsx' or 'tableexport', but them don't keep the style. Here a code using 'xlsx' to export table
@ViewChild('excel_table', { static: false }) excel_table: ElementRef;
generateExcelTable3() {
/* pass here the table id */
const tableData = this.excel_table.nativeElement;
const ws: WorkSheet =utils.table_to_sheet(element);
/* generate workbook and add the worksheet */
const wb: WorkBook = utils.book_new();
utils.book_append_sheet(wb, ws, 'Sheet1');
/* save to file */
writeFile(wb, `${this.trafoItem.code}.xlsx`);
}
Any help?
Thanks in advance
Solution
Exporting HTML tables to Excel with CSS styles is quite challenging because Excel uses its own styling system and it's hard to convert CSS styles directly to Excel's styles. However, there is a workaround using Excel's XML Spreadsheet format which allows to keep the CSS style in the exported Excel file.
Here is an example of how you can do it:
<button (click)="exportAsExcel()">Export as Excel</button>
<div>
<table #excelTable id="excelTableId">
...
</table>
</div>
@ViewChild('excelTable', { static: false }) excelTable: ElementRef;
exportAsExcel() {
const uri = 'data:application/vnd.ms-excel;base64,';
const template = `<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>`;
const base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) };
const format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) };
const table = this.excelTable.nativeElement;
const ctx = { worksheet: 'Worksheet', table: table.innerHTML };
const link = document.createElement('a');
link.download = `${this.trafoItem.code}.xls`;
link.href = uri + base64(format(template, ctx));
link.click();
}
This script will generate a base64-encoded data URI containing the XML Spreadsheet file, which you can then download using a simple link. The CSS styles applied in your HTML table will be preserved in the output Excel file.
Please note that this approach has some limitations. It does not support all CSS properties, and the styles may look different in Excel due to differences between Excel's and browsers' rendering engines.
Also, remember to sanitize any user input that goes into the generated XML to prevent XML injection attacks.
Answered By - Ketan Patel
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.