npmjasmineprotractorjs-xlsxsheetjs

How to write into a particular cell using xlsx npm package


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);

Solution

  • 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