Loosing Styles when an XLSM file edited using Base64 data #831

New Issue

2017-10-05T12:15:38Z

sathish804 commented

2017-10-05 12:15:38 +00:00

(Migrated from github.com)

Hi All,

I am trying to edit an xlsm file which has some styling with Macos. I am extracting the base64 data of the excel and reading it into workbook variable and then editing it with SheetJS and then writing it back to .xlsm.

But this approach is causing 2 issues for me.

Loosing all the styles of the Work Book
The content existing before edit in the Sheet is lost.

Below are my 2 functions which I am using to read and write the xlsm file.

Attached ZIP file have both Input and out xlsm files.

Test1.xlsm (Input)
Test3.xlsm (Output)

xlsm.zip

Please advise me how can I get the styles retained from an existing .xlsm file.

function getExcel() {
	var data = document.getElementById('base64Data').value.trim();
	var sheetData = document.getElementById('excelData').value.trim();
	var sheetName = document.getElementById('sheetName').value.trim();
	if (sheetName.length > 0) {
		var workbook = XLSX.read(data, {
			type : 'base64',
			cellStyles : true,
			bookVBA : true,
			raw:true,
			cellNF: true
		});
		var sheet_name_list = workbook.SheetNames;
		if (isSheetExists(sheetName, sheet_name_list)) {
			var worksheet = workbook.Sheets[sheetName];
			worksheet = sheet_from_array_of_arrays(sheetData,worksheet);
			workbook.Sheets[sheetName] = worksheet;
			var wbout = XLSX.write(workbook, {
				bookType : 'xlsm',
				bookSST : true,
				type : 'binary',
				cellStyles : true,
				bookVBA : true
			});
			saveAs(new Blob([ s2ab(wbout) ], {
				type : "application/octet-stream"
			}), "test.xlsm")
		} else {
			alert("No Sheet Found with Name --> " + sheetName)
		}
	} else {
		alert("Sheet Name is Field is Empty")
	}
}`

```

```
/*Function to create sheet from data*/
function sheet_from_array_of_arrays(data, ws,opts) {
	//var ws = {};
	var range = {
		s : {
			c : 10000000,
			r : 10000000
		},
		e : {
			c : 0,
			r : 2
		}
	};
	var arrData = data.split("~~");
	for (var R = 1; R != arrData.length; ++R) {
		var arrData1 = arrData[R - 1].split("^^");
		for (var C = 0; C != arrData1.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 : arrData1[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 < 10000000)
		ws['!ref'] = XLSX.utils.encode_range(range);
	return ws;
}
```
Sample data used to populate in the Sheet3

`Test^^Test1^^Test3~~1:blala^^2^^3~~1^^2^^3~~1^^2^^3~~"1:blala"^^2^^3~~1^^2^^3~~1^^2^^3~~1^^2^^3~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6`

Hi All, I am trying to edit an xlsm file which has some styling with Macos. I am extracting the base64 data of the excel and reading it into workbook variable and then editing it with SheetJS and then writing it back to .xlsm. But this approach is causing 2 issues for me. 1) Loosing all the styles of the Work Book 2) The content existing before edit in the Sheet is lost. Below are my 2 functions which I am using to read and write the xlsm file. Attached ZIP file have both Input and out xlsm files. 1) Test1.xlsm (Input) 2) Test3.xlsm (Output) [xlsm.zip](https://github.com/SheetJS/js-xlsx/files/1359236/xlsm.zip) Please advise me how can I get the styles retained from an existing .xlsm file. ```` function getExcel() { var data = document.getElementById('base64Data').value.trim(); var sheetData = document.getElementById('excelData').value.trim(); var sheetName = document.getElementById('sheetName').value.trim(); if (sheetName.length > 0) { var workbook = XLSX.read(data, { type : 'base64', cellStyles : true, bookVBA : true, raw:true, cellNF: true }); var sheet_name_list = workbook.SheetNames; if (isSheetExists(sheetName, sheet_name_list)) { var worksheet = workbook.Sheets[sheetName]; worksheet = sheet_from_array_of_arrays(sheetData,worksheet); workbook.Sheets[sheetName] = worksheet; var wbout = XLSX.write(workbook, { bookType : 'xlsm', bookSST : true, type : 'binary', cellStyles : true, bookVBA : true }); saveAs(new Blob([ s2ab(wbout) ], { type : "application/octet-stream" }), "test.xlsm") } else { alert("No Sheet Found with Name --> " + sheetName) } } else { alert("Sheet Name is Field is Empty") } }` ``` ``` /*Function to create sheet from data*/ function sheet_from_array_of_arrays(data, ws,opts) { //var ws = {}; var range = { s : { c : 10000000, r : 10000000 }, e : { c : 0, r : 2 } }; var arrData = data.split("~~"); for (var R = 1; R != arrData.length; ++R) { var arrData1 = arrData[R - 1].split("^^"); for (var C = 0; C != arrData1.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 : arrData1[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 < 10000000) ws['!ref'] = XLSX.utils.encode_range(range); return ws; } ``` Sample data used to populate in the Sheet3 `Test^^Test1^^Test3~~1:blala^^2^^3~~1^^2^^3~~1^^2^^3~~"1:blala"^^2^^3~~1^^2^^3~~1^^2^^3~~1^^2^^3~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6~~4^^5^^6`

sathish804 commented

2017-10-06 09:03:35 +00:00

(Migrated from github.com)

I was doing some more investigation on the style info.

From Console, I see the style info on the sheet object generated but while writing its being lost.

@SheetJSDev : Kindly help me with this issue.

I was doing some more investigation on the style info. From Console, I see the style info on the sheet object generated but while writing its being lost. @SheetJSDev : Kindly help me with this issue. ![image](https://user-images.githubusercontent.com/14090242/31270770-fa3580ea-aa85-11e7-89d5-b7efc9b58135.png)

sathish804 commented

2017-10-10 10:15:28 +00:00

(Migrated from github.com)

Can anyone please help me with this issue?

molomby commented

2018-02-23 06:40:13 +00:00

(Migrated from github.com)

I've been having similar problems. It seems this lib doesn't yet support styles properly..? There's a branch to add style support and, fortunately, a fork and package with it working right now. You might be able to drop it into your project as a replacement.

I've been having similar problems. It seems this lib doesn't yet support styles properly..? There's a [branch](https://github.com/SheetJS/js-xlsx/pull/263) to add style support and, fortunately, a [fork](https://github.com/protobi/js-xlsx) and [package](https://www.npmjs.com/package/xlsx-style) with it working right now. You might be able to drop it into your project as a replacement.

SheetJSDev commented

2018-05-20 17:49:01 +00:00

(Migrated from github.com)

We offer this in the Pro compendium. Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source. We have a longer comment in a gist.

We offer this in the [Pro compendium](https://sheetjs.com/pro). Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source. [We have a longer comment in a gist.](https://gist.github.com/SheetJSDev/24b8acd317d01999d721b38de7c53021)

Sign in to join this conversation.