I am trying to export my html table to excel file, using SheetJS library. This is my code:
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.2/xlsx.full.min.js" ></script>
<script>
var a = document.getElementById("new_table");
var fp=XLSX.utils.table_to_book(a,{sheet:"table"});
XLSX.write(fp,{
bookType:'xlsx',
type:'base64'
});
XLSX.writeFile(fp,"table.xlsx");
</script>
My html table has a column called dates where all the dates are in string, i.e., 10/04/2021, 11/05/2021, etc. and another column called ID where all the values are 11 digit string, i.e., 11P02399018, etc.
But in my table.xlsx file, the IDs are shown in scientific notation and the dates are showing as ######. When I hover over the the ##### value, the date is like 04-10-2021, 05-11-2021, etc. Why? How do I fix this?
Moreover in ID such as 01480EI2104, the preceding 0 is removed in the excel file, i.e., it becomes 1480EI2104.
If the dates are showing as ######, that means your columns are not wide enough to fit the content. You could open the file and make those columns wider, or you can do it with code:
ws['!cols'] = wscols
Where ws is the worksheet and wscols is an array of column properties objects, like this: {wch: 18}
See: worksheet-object