How to write merged Cells #92

New Issue

2014-07-30T18:35:34Z

longweiquan commented

2014-07-30 18:35:34 +00:00

(Migrated from github.com)

First of all, thank you for sharing this great library. I have a question about how to create the merged cells while writing files. I found the attribute Sheets['!merges'] while reading the files with cells, the same attributes don't work for writing.

SheetJSDev commented

2014-07-30 18:52:27 +00:00

(Migrated from github.com)

@longweiquan thanks for reaching out!

Right now merged cells are not being written. I would accept a contribution :) The code would look like this:

function write_ws_xml_merges(merges) {
    if(merges.length == 0) return "";
    var o = '<mergeCells count="' + merges.length + '">';
    for(var i = 0; i != merges.length; ++i) o += '<mergeCell ref="' + encode_range(merges[i]) + '"/>'
    return o + '</mergeCells>';
}
/* Add this line in write_ws_xml, just before if(o.length>2) { o[o.length] = ('</worksheet>'); o[1]=o[1].replace("/>",">"); } */
    if(ws['!merges'] !== undefined && ws['!merges'].length > 0) o[o.length] = (write_ws_xml_merges(ws['!merges']));

I have not added it yet because, to be honest, I'm not particularly happy with the current representation of merged cells. It's strange because certain operations are not well-defined (for example, if you merge A1:B2, what should happen if you try to access cell A2, B1, or B2?).

@clayzermk1 @mchapman any thoughts on the issue of merged cells representation?

@longweiquan thanks for reaching out! Right now merged cells are not being written. I would accept a contribution :) The code would look like this: ``` function write_ws_xml_merges(merges) { if(merges.length == 0) return ""; var o = '<mergeCells count="' + merges.length + '">'; for(var i = 0; i != merges.length; ++i) o += '<mergeCell ref="' + encode_range(merges[i]) + '"/>' return o + '</mergeCells>'; } /* Add this line in write_ws_xml, just before if(o.length>2) { o[o.length] = ('</worksheet>'); o[1]=o[1].replace("/>",">"); } */ if(ws['!merges'] !== undefined && ws['!merges'].length > 0) o[o.length] = (write_ws_xml_merges(ws['!merges'])); ``` I have not added it yet because, to be honest, I'm not particularly happy with the current representation of merged cells. It's strange because certain operations are not well-defined (for example, if you merge A1:B2, what should happen if you try to access cell A2, B1, or B2?). @clayzermk1 @mchapman any thoughts on the issue of merged cells representation?

longweiquan commented

2014-07-30 19:26:11 +00:00

(Migrated from github.com)

@SheetJSDev, thank you for your rapid reply.

Suggestion, maybe you can keep the same behavior as Excel do. When cells are merged in Excel, only the top-left cell is left and the others are deleted.

This concern the manipulation of the Sheets object, maybe you can even write some utils for merging cells to avoid wrong usage.

@SheetJSDev, thank you for your rapid reply. Suggestion, maybe you can keep the same behavior as Excel do. When cells are merged in Excel, only the top-left cell is left and the others are deleted. This concern the manipulation of the Sheets object, maybe you can even write some utils for merging cells to avoid wrong usage.

clayzermk1 commented

2014-07-31 16:19:55 +00:00

(Migrated from github.com)

@SheetJSDev I think what @longweiquan said about being consistent with Excel is a rational thing to do. Shouldn't be that bad to check for an existing merge range in a proposed merge range, remove the existing range, and then create the proposed range (did I interpret that correctly?).

longweiquan commented

2014-07-31 18:49:54 +00:00

(Migrated from github.com)

@clayzermk1， thank you for the interpretation :), that's exactly what I mean.

Suncatcher commented

2017-11-13 13:21:27 +00:00

(Migrated from github.com)

So the merging is still not possible?

reviewher commented

2017-11-13 18:45:38 +00:00

(Migrated from github.com)

Merging was added in 0.7.10. https://github.com/SheetJS/js-xlsx/issues/416#issuecomment-286932882 has an example in nodejs. https://jsfiddle.net/w8t0g8xn/ is a fiddle