javascriptexcelsheetjs

How to change the type of columns of excel files for exported SheetJS xlsx?

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 :

enter image description here

To fix it in excel, I should enable modification, try to edit then use enter to let it be recognized as money cell :

enter image description here

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?

Solution

  • 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)

    1. Get the cell type. After making the real excel file (without issue) you can use:
    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\\ "€"'.

    1. For each <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>
    1. Use same code to export the content, and everything will works.