strange '@' chatacter is added to IF formula when exporting data #2955

New Issue

lh104 · 2023-05-27T13:10:06Z

lh104 commented

2023-05-27 13:10:06 +00:00

When I export data to excel file, my formula is "IF XXX", but the formula in the exported excel file is "@IF XXX".

data.png

147 KiB

excel.png

54 KiB

sheetjs commented

2023-05-28 00:27:51 +00:00

Use the formula IF(H3<>"",_xlfn.CONCAT(H3," (", G3, ")"), ""). https://jsfiddle.net/quxk7n6j/ is a live example:

    var ws = XLSX.utils.aoa_to_sheet([
      [ 1 ], // A1
      [ 2 ], // A2
      [ {t: "n", v: 3, f: `IF(H3<>"",_xlfn.CONCAT(H3," (", G3, ")"), "")`} ] // A3
    ]);

    var wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSFormula1.xlsx");

When discussing formula expressions, it is important to stress that the formula displayed in Excel is not the same as the formula stored in the file. https://docs.sheetjs.com/docs/csf/features/formulae tries to cover more details.

The _xlfn. prefix is needed for a number of "future functions". The functions that are not considered "future" (the functions that can be used without the _xlfn prefix) are listed in the Ftab variable defined in https://git.sheetjs.com/sheetjs/sheetjs/src/branch/master/modules/64_ftab.ts#L403

A partial list of future functions is available in the documentation at https://docs.sheetjs.com/docs/csf/features/formulae#prefixed-future-functions .

Use the formula `IF(H3<>"",_xlfn.CONCAT(H3," (", G3, ")"), "")`. https://jsfiddle.net/quxk7n6j/ is a live example: ```js var ws = XLSX.utils.aoa_to_sheet([ [ 1 ], // A1 [ 2 ], // A2 [ {t: "n", v: 3, f: `IF(H3<>"",_xlfn.CONCAT(H3," (", G3, ")"), "")`} ] // A3 ]); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSFormula1.xlsx"); ``` When discussing formula expressions, it is important to stress that the formula displayed in Excel is not the same as the formula stored in the file. https://docs.sheetjs.com/docs/csf/features/formulae tries to cover more details. The `_xlfn.` prefix is needed for a number of "future functions". The functions that are not considered "future" (the functions that can be used without the `_xlfn` prefix) are listed in the `Ftab` variable defined in https://git.sheetjs.com/sheetjs/sheetjs/src/branch/master/modules/64_ftab.ts#L403 A partial list of future functions is available in the documentation at https://docs.sheetjs.com/docs/csf/features/formulae#prefixed-future-functions .

sheetjs closed this issue

2023-05-28 00:27:51 +00:00

Sign in to join this conversation.