How to append (%) percentage sign without multiplying in XLSX JS

Question

In XLSX, unable to append a cell value with % symbol. While trying, it's multiplying the cell value by 100.

Input: ["Ball", 1000, 201.72].

Expected output in XLSX: ["Ball", 1000, 201.72%]

Current output: ["Ball", 1000, 20172.00%]

function test(){
  var itemArr = [
    ["Ball", 1000, 201.72],
    ["Bell", 800, 0.7],
    ["Bun", 1500, 1.7612]
  ];
  var arrayOfArray = [];

  for (var i = 0; i < itemArr.length; i++) {
    var row = [];
    for (var j = 0; j < itemArr[i].length; j++) {
      var obj = {};
      obj.v = itemArr[i][j];
      if (j > 0) obj.t = "n";
      else obj.t = "s";
      if (j == 2) obj.z = "#,##0.00%";
      else if (j == 1) obj.z = "#,##0.00";
      row.push(obj);
    }
    arrayOfArray.push(row);
  }
  var ws = XLSX.utils.aoa_to_sheet(arrayOfArray);
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  return XLSX.writeFile(wb, "test.xlsx");
}

Here's the sample codepen: https://codepen.io/satishvarada/pen/yLMajKe

score 1 · Accepted Answer · answered May 19 '21 at 06:05

This uses a number formatter similar to Excel's so whenever you use the percentage sign it automatically multiplies by 100.

Via SheetJS documentation

The rules are slightly different from how Excel displays custom number formats. In particular, literal characters must be wrapped in double quotes or preceded by a backslash. For more info, see the Excel documentation article Create or delete a custom number format or ECMA-376 18.8.31 (Number Formats)

You can add a percentage literal by adding quotes around it

if (j == 2) obj.z = '#,#0.00"%"';

Codepen: https://codepen.io/JonathanSouthern/pen/PopbbZr

score -1 · Answer 2 · answered May 19 '21 at 06:01

Remove the "%" from the format and append it to the value afterwards. Here is the relevant change:

if (j == 2) {
   obj.z = "#,##0.00";
   obj.v += '%';
}

Updated code:

document.getElementById("test").addEventListener("click", function () {
  var itemArr = [
    ["Ball", 1000, 201.72],
    ["Bell", 800, 0.7],
    ["Bun", 1500, 1.7612]
  ];
  var arrayOfArray = [];

  for (var i = 0; i < itemArr.length; i++) {
    var row = [];
    for (var j = 0; j < itemArr[i].length; j++) {
      var obj = {};
      obj.v = itemArr[i][j];
      if (j > 0) obj.t = "n";
      else obj.t = "s";
      if (j == 2) {
        obj.z = "#,##0.00"; // No percent sign here
        obj.v += '%'; // Append it afterwards
      }
      else if (j == 1) obj.z = "#,##0.00";
      row.push(obj);
    }
    arrayOfArray.push(row);
  }
  var ws = XLSX.utils.aoa_to_sheet(arrayOfArray);
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  return XLSX.writeFile(wb, "test.xlsx");
});

https://codepen.io/km0ser/pen/dyvOOPj

How to append (%) percentage sign without multiplying in XLSX JS

2 Answers2