General Utility Functions - soares-marcio/js-xlsx GitHub Wiki

Updating Worksheet Range

set_sheet_range changes a sheet's range given a general target spec that can include only the start or end cell:

/* given the old range and a new range spec, produce the new range */
function change_range(old, range) {
	var oldrng = XLSX.utils.decode_range(old), newrng;
	if(typeof range == "string") {
		if(range.charAt(0) == ":") newrng = {e:XLSX.utils.decode_cell(range.substr(1))};
		else if(range.charAt(range.length - 1) == ":") newrng = {s:XLSX.utils.decode_cell(range.substr(0, range.length - 1))};
		else newrng = XLSX.utils.decode_range(range);
	} else newrng = range;
	if(newrng.s) {
		if(newrng.s.c != null) oldrng.s.c = newrng.s.c;
		if(newrng.s.r != null) oldrng.s.r = newrng.s.r;
	}
	if(newrng.e) {
		if(newrng.e.c != null) oldrng.e.c = newrng.e.c;
		if(newrng.e.r != null) oldrng.e.r = newrng.e.r;
	}

	return XLSX.utils.encode_range(oldrng);
}

/* call change_sheet and modify worksheet */
function set_sheet_range(sheet, range) {
  sheet['!ref'] = change_range(sheet['!ref'], range);
}

For the more general case of recalculating the worksheet range:

function update_sheet_range(ws) {
  var range = {s:{r:20000000, c:20000000},e:{r:0,c:0}};
  Object.keys(ws).filter(function(x) { return x.charAt(0) != "!"; }).map(XLSX.utils.decode_cell).forEach(function(x) {
    range.s.c = Math.min(range.s.c, x.c); range.s.r = Math.min(range.s.r, x.r);
    range.e.c = Math.max(range.e.c, x.c); range.e.r = Math.max(range.e.r, x.r);
  });
  ws['!ref'] = XLSX.utils.encode_range(range);
}

Adding a cell to a range

function range_add_cell(range, cell) {
	var rng = XLSX.utils.decode_range(range);
	var c = typeof cell == 'string' ? XLSX.utils.decode_cell(cell) : cell;
	console.log(rng, c);
	if(rng.s.r > c.r) rng.s.r = c.r;
	if(rng.s.c > c.c) rng.s.c = c.c;

	if(rng.e.r < c.r) rng.e.r = c.r;
	if(rng.e.c < c.c) rng.e.c = c.c;
	return XLSX.utils.encode_range(rng);
}
range_add_cell("A1:C3","B2")

function add_to_sheet(sheet, cell) {
	sheet['!ref'] = range_add_cell(sheet['!ref'], cell);
}