I am doing

 XLSX.utils.table_to_book(document.getElementById(table), { sheet: "Sheet JS", raw: false }); 

Table as an HTML Table. However, one of my columns contains USD ($4.56) but is being read as General type in excel and not as currency. How can i use XLSX.utils.table_to_book and keep certain columns as currency?

I am doing

 XLSX.utils.table_to_book(document.getElementById(table), { sheet: "Sheet JS", raw: false }); 

Table as an HTML Table. However, one of my columns contains USD ($4.56) but is being read as General type in excel and not as currency. How can i use XLSX.utils.table_to_book and keep certain columns as currency?

• javascript
• js-xlsx

Share Improve this question Follow asked Oct 31, 2018 at 14:36 Steve ShortSteve Short 9511 gold badge11 silver badge1313 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 2

To change a cell's number format, set the z property of the cell to the format you want to use. For example:

ws["A2"].z = "$0.00";

The formatted text is generally stored in the w field as opposed to the v field but isn't automatically updated when you change the number format. You can do it manually:

delete ws["A2"].w; // delete old formatted text if it exists
XLSX.utils.format_cell(ws["A2"]); // refresh cell

Example in the snippet

var tbl = document.getElementById('sheetjs');
var wb = XLSX.utils.table_to_book(tbl);


var ws = wb.Sheets["Sheet1"]; //  get the current sheet
console.log(ws["A2"].v); //  default v value '4.56'

ws["A2"].z = "$0.00"; //  format the cell

delete ws["A2"].w; // delete old formatted text if it exists
XLSX.utils.format_cell(ws["A2"]); // refresh cell

console.log(ws["A2"].w); // new formatted cell '$4.56'

<script src="https://cdnjs.cloudflare./ajax/libs/xlsx/0.14.0/xlsx.js"></script>

<table id="sheetjs">
<tr><td>S</td><td>h</td><td>e</td><td>e</td><td>t</td><td>J</td><td>S</td></tr>
<tr><td>$4.56</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td></tr>
<tr><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td></tr>
</table>