12

I have to write a value to a particular cell (say the D4 cell) in my xlsm file. I can see the option of

XLSX.writeFile(workbook, 'out.xlsx');

in the XLSX package documentation (writing functions)

But I am not seeing anything to write a value to a particular cell (where should the values which needs to be written passed?). Or, it is not as clear as the example provided to read a particular cell value. Would be glad if someone could provide me a simple example of snippet.

This is how I read a particular cell value:

if(typeof require !== 'undefined') XLSX = require('C:\\Program Files\\nodejs\\node_modules\\npm\\node_modules\\xlsx');
var workbook = XLSX.readFile('xlsm');
var first_sheet_name = workbook.SheetNames[0];
var address_of_cell = 'D5';
var worksheet = workbook.Sheets[first_sheet_name];
var desired_cell = worksheet[address_of_cell];
desired_value = (desired_cell ? desired_cell.v : undefined);
console.log('Cell Value is: '+ desired_value);
Ivan Rubinson
  • 3,001
  • 4
  • 19
  • 48
Ashish
  • 161
  • 1
  • 2
  • 10

2 Answers2

30

So to write to a specific cell in a defined sheet - lets say first sheet, you can do:

const XLSX = require('xlsx');

// read from a XLS file
let workbook = XLSX.readFile('test.xls');

// get first sheet
let first_sheet_name = workbook.SheetNames[0];
let worksheet = workbook.Sheets[first_sheet_name];

// read value in D4 
let cell = worksheet['D4'].v;
console.log(cell)

// modify value in D4
worksheet['D4'].v = 'NEW VALUE from NODE';

// modify value if D4 is undefined / does not exists
XLSX.utils.sheet_add_aoa(worksheet, [['NEW VALUE from NODE']], {origin: 'D4'});

// write to new file
// formatting from OLD file will be lost!
XLSX.writeFile(workbook, 'test2.xls');

Hope that helps

Sebastian Hildebrandt
  • 2,661
  • 1
  • 14
  • 20
  • 1
    Hi Sebastian - Thanks a ton. That really did help me a lot. But have few concerns executing it. I want the value to be written in same file which I'm reading (i.e., workbook). continuing it with another comment due to character restriction :) – Ashish Jul 20 '18 at 13:27
  • Below is the code snippet as guided by you. if(typeof require !== 'undefined') XLSX = require('C:\\Program Files\\nodejs\\node_modules\\npm\\node_modules\\xlsx'); var workbook = XLSX.readFile('xlsm1'); var first_sheet_name = workbook.SheetNames[0]; var address_of_cell = 'D5'; var worksheet = workbook.Sheets[first_sheet_name]; desired_cell = worksheet[address_of_cell].v; //desired_value = (desired_cell ? desired_cell.v : undefined); worksheet[address_of_cell].v = 'NEW VALUE from NODE'; XLSX.writeFile(workbook, 'xlsm1'); – Ashish Jul 20 '18 at 13:27
  • Output file Issues: Format has been changed (Ex. Borders/cell format of the entire excel sheet has been lost after new value has been pushed into the particular cell, Tooltip is visible as a constant text box now, Images has been lost). Actually the excel sheet is macro enabled. So, how could I enable the macro content, then write the value into corresponding cell. Would that resolve these issues ? Also, how to select a value from the dropdown list values of a corresponding cell (There are few cells with dropdown values too) ? – Ashish Jul 20 '18 at 13:28
  • What would I like to achieve is: Iterate the logic for required Attribute Names & Sheets: Common file with Attribute Names (Ditto attribute names present in the excel sheet I'm using) and with corresponding values. Read attribute name in the excel sheet and search for corresponding name in common file. Pick its respective value and then write them into the corresponding cell of excel sheet. – Ashish Jul 20 '18 at 13:28
  • Direct filenames do not work for me - https://github.com/SheetJS/sheetjs/issues/1189 – Manny Dec 09 '19 at 06:09
  • worksheet['D4'].v -- what is this v in the code...? – learnNcode May 09 '20 at 14:55
  • @Ashish did you get your answer, i was looking for same, that when file is saved, and you open file and it shows enable editing, that means you need to save file once in order to get the updated data while accessing the file using fetch – Amit Chauhan Sep 21 '22 at 18:41
  • although when you open file it shows you updated/modified data, but when you try to close the file it asks for do you want to save file or not and if not saved, while using fetch method to access file data it will show you old value – Amit Chauhan Sep 21 '22 at 18:42
11

Modify value in D4

worksheet['D4'].v = 'NEW VALUE from NODE';

This will work only if the cell already defined in the file, but sometimes you will want to write to a new undefined cell. so, the solution I found for that is:

modify value in new cell- D4

XLSX.utils.sheet_add_aoa(worksheet, [['NEW VALUE from NODE']], {origin: 'D4'});
Ivan Rubinson
  • 3,001
  • 4
  • 19
  • 48
user254307
  • 111
  • 1
  • 3