Sheetjs: Write in cell

[271bibi]

Hi, how can I write new text into an existing .xlsx? How can I grab a specific cell? I read the documentation but I can not find an example. Thank you!

[271bibi]

update: I tried this:

const XLS = require('xlsjs');
var workbook = XLS.readFile('test1.xls');
var sheet_name_list = workbook.SheetNames;
var Sheet1A1 = workbook.Sheets[sheet_name_list[0]]['A1'].v;
consolee.log(Sheet1A1);

but I get an error:

TypeError: Cannot read property 'v' of undefined

[f7el]

I would like to accomplish the same thing.

[reviewher]

First: You should be using xlsx: https://www.npmjs.com/package/xlsx -- it now includes everything from xlsjs and much more :)

Second: if the worksheet doesn't have a cell A1, then the sheet won't have that cell object and you will see the undefined error. You should check for the cell:

/* load module */
const XLSX = require('xlsx');

/* read workbook */
const workbook = XLSX.readFile('test1.xls');

/* get the first worksheet */
const sheet_name_list = workbook.SheetNames;
const worksheet = workbook.Sheets[sheet_name_list[0]];

/* find cell A1 */
let address = 'A1';
let Sheet1A1 = worksheet[address];

/* create a stub cell if it doesn't exist */
if(!Sheet1A1) Sheet1A1 = worksheet[address] = {t:'z'};

/* print out the value in A1 */
console.log(Sheet1A1.v);

[MWolman1990]

First: You should be using xlsx: https://www.npmjs.com/package/xlsx -- it now includes everything from xlsjs and much more :)

Second: if the worksheet doesn't have a cell A1, then the sheet won't have that cell object and you will see the undefined error. You should check for the cell:

/* load module */
const XLSX = require('xlsx');

/* read workbook */
const workbook = XLSX.readFile('test1.xls');

/* get the first worksheet */
const sheet_name_list = workbook.SheetNames;
const worksheet = workbook.Sheets[sheet_name_list[0]];

/* find cell A1 */
let address = 'A1';
let Sheet1A1 = worksheet[address];

/* create a stub cell if it doesn't exist */
if(!Sheet1A1) Sheet1A1 = worksheet[address] = {t:'z'};

/* print out the value in A1 */
console.log(Sheet1A1.v);

I tried this example step for step. The program runs fine, but when I open up the excel file, nothing has changed. I added a v property to the object right after the t: 'z' property. I was under the impression this was supposed to set the value. Your example doesn't show how to actually insert a value into the spreadsheet. Can you explain how to do this?

[Suley]

First: You should be using xlsx: https://www.npmjs.com/package/xlsx -- it now includes everything from xlsjs and much more :)
Second: if the worksheet doesn't have a cell A1, then the sheet won't have that cell object and you will see the undefined error. You should check for the cell:

/* load module */
const XLSX = require('xlsx');

/* read workbook */
const workbook = XLSX.readFile('test1.xls');

/* get the first worksheet */
const sheet_name_list = workbook.SheetNames;
const worksheet = workbook.Sheets[sheet_name_list[0]];

/* find cell A1 */
let address = 'A1';
let Sheet1A1 = worksheet[address];

/* create a stub cell if it doesn't exist */
if(!Sheet1A1) Sheet1A1 = worksheet[address] = {t:'z'};

/* print out the value in A1 */
console.log(Sheet1A1.v);

I tried this example step for step. The program runs fine, but when I open up the excel file, nothing has changed. I added a v property to the object right after the t: 'z' property. I was under the impression this was supposed to set the value. Your example doesn't show how to actually insert a value into the spreadsheet. Can you explain how to do this?

Same problems here. Could someone please supplement examples of writing an existing excel file?

[fozcode]

This isn't documented as far as I can see, but the answer is at https://github.com/SheetJS/sheetjs/issues/1358#issuecomment-439239401

You need to supply your own object with minimum a type and a value. So:

sheet.A1 = { t: 'n', v: 123 };  // Create A1 as a number
sheet.B1 = { t: 's', v: 'foo' };  // Create B1 as a string

Edit: this method does not seem to create new rows if the rows do not already exist. To write single cells and create new rows as required I am now using:

XLSX.utils.sheet_add_aoa(sheet, [[123]], {origin: 'A1'});
XLSX.utils.sheet_add_aoa(sheet, [['foo']], {origin: 'B1'});

[miticous]

This isn't documented as far as I can see, but the answer is at #1358 (comment)

You need to supply your own object with minimum a type and a value. So:

sheet.A1 = { t: 'n', v: 123 };  // Create A1 as a number
sheet.B1 = { t: 's', v: 'foo' };  // Create B1 as a string

Edit: this method does not seem to create new rows if the rows do not already exist. To write single cells and create new rows as required I am now using:

XLSX.utils.sheet_add_aoa(sheet, [[123]], {origin: 'A1'});
XLSX.utils.sheet_add_aoa(sheet, [['foo']], {origin: 'B1'});

@fozcode You saved my life !!!

[reviewher]

@fozcode @mam17 What should be added to the documentation? Should it be in the README or in the wiki?

[miticous]

@fozcode @mam17 What should be added to the documentation? Should it be in the README or in the wiki?

You should use it if the column had any one cell with any value when you read the file

sheet.A1 = { t: 'n', v: 123 }; // Create A1 as a number sheet.B1 = { t: 's', v: 'foo' }; // Create B1 as a string

or this if not had

XLSX.utils.sheet_add_aoa(sheet, [[123]], {origin: 'A1'}); XLSX.utils.sheet_add_aoa(sheet, [['foo']], {origin: 'B1'});

Should be added into README

[fozcode]

@reviewher Personally I would add it to the README, in _Working with the Workbook_, alongside the example for _Reading a specific cell_