Modify an existing xlsm file #578

New Issue

2017-03-03T14:40:15Z

mca22120 commented

2017-03-03 14:40:15 +00:00

(Migrated from github.com)

Hello, I'm able to create a file but I don't succeed in modifying a file.
Could you help me please ?
You can find my code here.
My aim is to add datas in an existing file but with this code I just put datas in a new file...

var XLSX = require('XLSX')

/function datenum(v, date1904) {
if(date1904) v+=1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}/

		if(typeof cell.v === 'number') cell.t = 'n';
		/*else if(typeof cell.v === 'boolean') cell.t = 'b';
		else if(cell.v instanceof Date) {
			cell.t = 'n'; cell.z = XLSX.SSF._table[14];
			cell.v = datenum(cell.v);
		}
		else cell.t = 's';*/
		
		ws[cell_ref] = cell;
	}
}
if(range.s.c < 1) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;

}

/* original data */
var data = 1,2,3
var ws_name = "SheetJS";

function Workbook() {
if(!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}

var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;

/* write file */
XLSX.writeFile(wb, 'test.xlsm');

Hello, I'm able to create a file but I don't succeed in modifying a file. Could you help me please ? You can find my code here. My aim is to add datas in an existing file but with this code I just put datas in a new file... var XLSX = require('XLSX') /*function datenum(v, date1904) { if(date1904) v+=1462; var epoch = Date.parse(v); return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000); }*/ function sheet_from_array_of_arrays(data, opts) { var ws = {}; var range = {s: {c:1, r:1}, e: {c:0, r:0 }}; for(var R = 0; R != data.length; ++R) { for(var C = 0; C != data[R].length; ++C) { if(range.s.r > R) range.s.r = R; if(range.s.c > C) range.s.c = C; if(range.e.r < R) range.e.r = R; if(range.e.c < C) range.e.c = C; var cell = {v: data[R][C] }; if(cell.v == null) continue; var cell_ref = XLSX.utils.encode_cell({c:C,r:R}); if(typeof cell.v === 'number') cell.t = 'n'; /*else if(typeof cell.v === 'boolean') cell.t = 'b'; else if(cell.v instanceof Date) { cell.t = 'n'; cell.z = XLSX.SSF._table[14]; cell.v = datenum(cell.v); } else cell.t = 's';*/ ws[cell_ref] = cell; } } if(range.s.c < 1) ws['!ref'] = XLSX.utils.encode_range(range); return ws; } /* original data */ var data = [[1,2,3]] var ws_name = "SheetJS"; function Workbook() { if(!(this instanceof Workbook)) return new Workbook(); this.SheetNames = []; this.Sheets = {}; } var wb = new Workbook(), ws = sheet_from_array_of_arrays(data); /* add worksheet to workbook */ wb.SheetNames.push(ws_name); wb.Sheets[ws_name] = ws; /* write file */ XLSX.writeFile(wb, 'test.xlsm');

johnothetree commented

2017-03-27 21:28:18 +00:00

(Migrated from github.com)

I'm finding the same issue, it doesn't write the information to the existing workbook, but makes a new one (and with my experience, the xlsm ends up not having the macros still attached)

SheetJSDev commented

2017-03-27 21:32:45 +00:00

(Migrated from github.com)

The code in @mca22120 is making a new workbook. Read/modify/write workflow is limited to the features that we can read and can write (like values, formulae, merge cells). If you want to read and change values, you have to read the file first (e.g. XLSX.readFile) and tinker with that object before saving.

The code in @mca22120 is making a new workbook. Read/modify/write workflow is limited to the features that we can read and can write (like values, formulae, merge cells). If you want to read and change values, you have to read the file first (e.g. `XLSX.readFile`) and tinker with that object before saving.

manikmi commented

2018-06-06 11:22:32 +00:00

(Migrated from github.com)

I too have a similar requirement. I want to keep the excel file (with formulae and charts, which get data from named ranges) stored on the server as a template, load the excel into memory, push the data from the DB to the named ranges and download updated file to client.

Is it possible using xlsx?

I too have a similar requirement. I want to keep the excel file (with formulae and charts, which get data from named ranges) stored on the server as a template, load the excel into memory, push the data from the DB to the [named ranges](http://www.contextures.com/xlNames01.html) and download updated file to client. Is it possible using xlsx?

👍 1

SheetJSDev commented

2019-08-04 03:11:29 +00:00

(Migrated from github.com)

Template data editing is offered in the Pro compendium (our paid offering). The Community version is primarily focused on data preservation.

https://gist.github.com/SheetJSDev/24b8acd317d01999d721b38de7c53021 is our general commentary.

Template data editing is offered in the Pro compendium (our paid offering). The Community version is primarily focused on data preservation. https://gist.github.com/SheetJSDev/24b8acd317d01999d721b38de7c53021 is our general commentary.

Sign in to join this conversation.