I want to convert a table into an xlsx file. I'm using SheetJS. It success to get data, create the file and download it.
The issue comes from excel types. For excel, all columns are simple string, and for example 23,0€
isn't recognize as money, and I can't do basic excel calcul as sum
:
To fix it in excel, I should enable modification, try to edit then use enter to let it be recognized as money cell :
My code is here (I can't add direct snippet into question as it require download).
All my excel manipulation:
const worksheet = XLSX.utils.table_to_sheet(document.getElementById("myTable"));
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Worksheet");
XLSX.writeFile(workbook, "Export.xlsx");
How can I change type in SheetJS to let excel recognize values?
I found the answer. I should override set the cell-type myself, but as it's not a general format, it's different. (example of the default one)
var XLSX = require("xlsx");
var wb = XLSX.readFile("export.xlsx", {cellNF: true})
var ws = wb.Sheets[wb.SheetNames[0]]
console.log(ws["A1"].z)
And you will see the type. In my case, it's '#,##0.00\\ "€";[Red]\\-#,##0.00\\ "€"'
.
<td>
, you should add the given format everything as :<td data-v="100" data-t="n" data-z='#,##0.00\ "€";[Red]\-#,##0.00\ "€"'><p>100,00€</p></td>
data-v
will contains the value as, in my case, integer (without €
)data-n
is set as "n" because the value is a numberdata-z
is what we get before: the formatting of the cell