8

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
  • 2,468
  • 4
  • 14
  • 33
Ashish
  • 121
  • 1
  • 2
  • 9

2 Answers2

17

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,048
  • 1
  • 11
  • 19
  • 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 – Mansi Dec 09 '19 at 06:09
  • worksheet['D4'].v -- what is this v in the code...? – learnNcode May 09 '20 at 14:55
8

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
  • 2,468
  • 4
  • 14
  • 33
user254307
  • 81
  • 1
  • 1