Using cell objects in json_to_sheet? #1168

New Issue

2018-07-02T15:32:37Z

mattandrews commented

2018-07-02 15:32:37 +00:00

(Migrated from github.com)

Thanks for this great library and all your hard work – it's really impressive.

I'm trying to export some database records into Excel. I've used json_to_sheet() to output something basic, but I wanted to add a =HYPERLINK() formula for one cell.

Reading the docs, I can see that I need a .f or .l key on the relevant cell object to use a formula. This doesn't work when I use json_to_sheet() though, eg:

let myArray = [
  {
    name: 'bob',
    age: 23,
    homepage: {
      f: '=HYPERLINK("http://www.google.com", "Google")'
    }
  },
  {
    name: 'alice',
    age: 25,
    homepage: {
      l: {
        '.Target': 'http://www.google.com',
        '.Tooltip': 'Google'
      }
    }
  },
];

let worksheet = XLSX.utils.json_to_sheet(myArray);
let workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "SheetJS");

In the outputted sheet, both rows have a blank "homepage" column (the others are fine).

I guess this means the import functions don't allow cell objects. Is my only option here to import the JSON and then traverse the sheet and find my homepage values and manipulate the cell objects from there? It would be great if I could just set these at import time (as outlined above) if at all possible!

Thanks for this great library and all your hard work – it's really impressive. I'm trying to export some database records into Excel. I've used `json_to_sheet()` to output something basic, but I wanted to add a `=HYPERLINK()` formula for one cell. Reading the docs, I can see that I need a `.f` or `.l` key on the relevant cell object to use a formula. This doesn't work when I use `json_to_sheet()` though, eg: ``` let myArray = [ { name: 'bob', age: 23, homepage: { f: '=HYPERLINK("http://www.google.com", "Google")' } }, { name: 'alice', age: 25, homepage: { l: { '.Target': 'http://www.google.com', '.Tooltip': 'Google' } } }, ]; let worksheet = XLSX.utils.json_to_sheet(myArray); let workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, worksheet, "SheetJS"); ``` In the outputted sheet, both rows have a blank "homepage" column (the others are fine). I guess this means the import functions don't allow cell objects. Is my only option here to import the JSON and then traverse the sheet and find my homepage values and manipulate the cell objects from there? It would be great if I could just set these at import time (as outlined above) if at all possible!

SheetJSDev commented

2018-07-02 21:11:07 +00:00

(Migrated from github.com)

As it is currently shaped, cell objects are not passed through. Fortunately its a simple fix!

https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L199-L207 is the block that builds the cell to add. It could be changed as follows:

			if(v && typeof v == 'object' && !(v instanceof Date)){
				ws[encode_cell({c:_C + C,r:_R + R + offset})] = v;
			} else {
				if(typeof v == 'number') t = 'n';
				else if(typeof v == 'boolean') t = 'b';
				else if(typeof v == 'string') t = 's';
				else if(v instanceof Date) {
					t = 'd';
					if(!o.cellDates) { t = 'n'; v = datenum(v); }
					z = o.dateNF || SSF._table[14];
				}
				ws[encode_cell({c:_C + C,r:_R + R + offset})] = cell = ({t:t, v:v}/*:any*/);
				if(z) cell.z = z;
			}

You'd still have to make an actual cell object (with t:'n'), but that's straightforward.

We'll make that change in the next release (or you can send a PR :)

As it is currently shaped, cell objects are not passed through. Fortunately its a simple fix! https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L199-L207 is the block that builds the cell to add. It could be changed as follows: ```js if(v && typeof v == 'object' && !(v instanceof Date)){ ws[encode_cell({c:_C + C,r:_R + R + offset})] = v; } else { if(typeof v == 'number') t = 'n'; else if(typeof v == 'boolean') t = 'b'; else if(typeof v == 'string') t = 's'; else if(v instanceof Date) { t = 'd'; if(!o.cellDates) { t = 'n'; v = datenum(v); } z = o.dateNF || SSF._table[14]; } ws[encode_cell({c:_C + C,r:_R + R + offset})] = cell = ({t:t, v:v}/*:any*/); if(z) cell.z = z; } ``` You'd still have to make an actual cell object (with `t:'n'`), but that's straightforward. We'll make that change in the next release (or you can send a PR :)

mattandrews commented

2018-07-03 12:13:57 +00:00

(Migrated from github.com)

Done! Check out https://github.com/SheetJS/js-xlsx/pull/1169

Also, with regards to "You'd still have to make an actual cell object", you mean when I'm actually calling json_to_sheet, right? (eg. see my updated example code in the above PR)

Done! Check out https://github.com/SheetJS/js-xlsx/pull/1169 Also, with regards to "You'd still have to make an actual cell object", you mean when I'm actually calling `json_to_sheet`, right? (eg. see my updated example code in the above PR)

Sign in to join this conversation.