Merged cell handling #41

New Issue

2014-01-03T01:45:03Z

clayzermk1 commented

2014-01-03 01:45:03 +00:00

(Migrated from github.com)

First, thank you for this library. Second, if I'm in the wrong place to be asking questions, I apologize.

I'm working with documents that contain merged cells. I'd like to iterate over rows and for a particular cell grab the value in a column header above it. The column headers are in merged cells.

Based on the demo, the sheet.xml files, and relevant sections of the spec (29500-1 SS 18.3.1.55 and A.2 ln. 2293 through ln. 2298), it seems the merge cell value is always in the top left cell and subsequent cells in the merge range are undefined.

Are there any helper attributes on the JS object of a merged cell (obj.mergeValue or obj.isMergeCell) or is that something I'll have to handle myself?

Cheers and thank you for your time! 🍻

First, thank you for this library. Second, if I'm in the wrong place to be asking questions, I apologize. I'm working with documents that contain merged cells. I'd like to iterate over rows and for a particular cell grab the value in a column header above it. The column headers are in merged cells. Based on the demo, the `sheet.xml` files, and relevant sections of the spec (29500-1 SS 18.3.1.55 and A.2 ln. 2293 through ln. 2298), it seems the merge cell value is always in the top left cell and subsequent cells in the merge range are `undefined`. Are there any helper attributes on the JS object of a merged cell (`obj.mergeValue` or `obj.isMergeCell`) or is that something I'll have to handle myself? Cheers and thank you for your time! :beers:

SheetJSDev commented

2014-01-03 02:14:44 +00:00

(Migrated from github.com)

if I'm in the wrong place to be asking questions, I apologize.

There's never a wrong place to ask questions :) It really helps (clearly, given that you stumbled upon an interesting case), and it's really cool to see that others are using this library.

it seems the merge cell value is always in the top left cell and subsequent cells in the merge range are undefined.

This is consistent with what excel does when saving to CSV. But that's clearly not the right approach.

Are there any helper attributes on the JS object of a merged cell (obj.mergeValue or obj.isMergeCell) or is that something I'll have to handle myself?

Right now there is nothing, but it is easy to add. What would make the most sense for the API? My thought at the moment is to make A1, B1, A2, B2 all resolve to the same object (where the merge range is clearly noted in a field like obj.range).

The CSV writer would act like Excel, and I imagine the row object array would work the same way.

> if I'm in the wrong place to be asking questions, I apologize. There's never a wrong place to ask questions :) It really helps (clearly, given that you stumbled upon an interesting case), and it's really cool to see that others are using this library. > it seems the merge cell value is always in the top left cell and subsequent cells in the merge range are undefined. This is consistent with what excel does when saving to CSV. But that's clearly not the right approach. > Are there any helper attributes on the JS object of a merged cell (obj.mergeValue or obj.isMergeCell) or is that something I'll have to handle myself? Right now there is nothing, but it is easy to add. What would make the most sense for the API? My thought at the moment is to make A1, B1, A2, B2 all resolve to the same object (where the merge range is clearly noted in a field like `obj.range`). The CSV writer would act like Excel, and I imagine the row object array would work the same way.

👍 1

clayzermk1 commented

2014-01-03 18:25:44 +00:00

(Migrated from github.com)

There's never a wrong place to ask questions :)

If only other repo owners shared your sentiments 😀

My thought at the moment is to make A1, B1, A2, B2 all resolve to the same object (where the merge range is clearly noted in a field like obj.range).

I also think that would be ideal!

The CSV writer would act like Excel, and I imagine the row object array would work the same way.

So the output would still show undefined right? As it currently does? I think that is the way to go, can't be breaking things. Duplicating the cell values would just look weird anyway.

> There's never a wrong place to ask questions :) If only other repo owners shared your sentiments :grinning: > My thought at the moment is to make A1, B1, A2, B2 all resolve to the same object (where the merge range is clearly noted in a field like obj.range). I also think that would be ideal! > The CSV writer would act like Excel, and I imagine the row object array would work the same way. So the output would still show `undefined` right? As it currently does? I think that is the way to go, can't be breaking things. Duplicating the cell values would just look weird anyway.

clayzermk1 commented

2014-01-04 01:18:10 +00:00

(Migrated from github.com)

I'm working on a PR. Should have it done tomorrow.

SheetJSDev commented

2014-01-04 04:39:19 +00:00

(Migrated from github.com)

@clayzermk1 sweet :)

https://github.com/SheetJS/test_files/blob/master/merge_cells.xls and https://github.com/SheetJS/test_files/blob/master/merge_cells.xlsx have vertical, horizontal, and rectangular merges.

@clayzermk1 sweet :) https://github.com/SheetJS/test_files/blob/master/merge_cells.xls and https://github.com/SheetJS/test_files/blob/master/merge_cells.xlsx have vertical, horizontal, and rectangular merges.

clayzermk1 commented

2014-01-04 07:01:22 +00:00

(Migrated from github.com)

Got thrown off by the holiday, meant Monday ;-)

Awesome, I'll definitely have some questions about the tests.
On Jan 3, 2014 8:39 PM, "SheetJSDev" notifications@github.com wrote:

@clayzermk1 https://github.com/clayzermk1 sweet :)

https://github.com/SheetJS/test_files/blob/master/merge_cells.xls and
https://github.com/SheetJS/test_files/blob/master/merge_cells.xlsx have
vertical, horizontal, and rectangular merges.

—
Reply to this email directly or view it on GitHubhttps://github.com/SheetJS/js-xlsx/issues/41#issuecomment-31571352
.

Got thrown off by the holiday, meant Monday ;-) Awesome, I'll definitely have some questions about the tests. On Jan 3, 2014 8:39 PM, "SheetJSDev" notifications@github.com wrote: > @clayzermk1 https://github.com/clayzermk1 sweet :) > > https://github.com/SheetJS/test_files/blob/master/merge_cells.xls and > https://github.com/SheetJS/test_files/blob/master/merge_cells.xlsx have > vertical, horizontal, and rectangular merges. > > — > Reply to this email directly or view it on GitHubhttps://github.com/SheetJS/js-xlsx/issues/41#issuecomment-31571352 > .

clayzermk1 commented

2014-01-06 20:23:29 +00:00

(Migrated from github.com)

clayzermk1/js-xlsx@f0a05daa96
Here's my first cut. I'm concerned with what I've written so far. The parsed output lists the merge cell values in the row (exactly how we don't want it). While this works for my use case, I don't like it for the library in general. Thoughts?

My editor removed some trailing whitespace and I removed a pair of extra curly braces that didn't seem to fit with your style. Otherwise, I tried to stick as closely as possible to your code style.

clayzermk1/js-xlsx@f0a05daa969803ae4d2b93ea0cf5eceb9d4d7f53 Here's my first cut. I'm concerned with what I've written so far. The parsed output lists the merge cell values in the row (exactly how we don't want it). While this works for my use case, I don't like it for the library in general. Thoughts? My editor removed some trailing whitespace and I removed a pair of extra curly braces that didn't seem to fit with your style. Otherwise, I tried to stick as closely as possible to your code style.

clayzermk1 commented

2014-01-06 20:45:07 +00:00

(Migrated from github.com)

clayzermk1/js-xlsx@37753d8230
There was a bug with multiple merged cells in a sheet when I tested against the test file you provided.

clayzermk1/js-xlsx@37753d82302f10130edb476147037df908b342a5 There was a bug with multiple merged cells in a sheet when I tested against the test file you provided.

SheetJSDev commented

2014-01-06 21:19:10 +00:00

(Migrated from github.com)

@clayzermk1 clearly I produced a good test file :)

In that file, I see the following (output passed through xmllint --format:

  <mergeCells count="5">
    <mergeCell ref="A1:B2"/>
    <mergeCell ref="C1:C2"/>
    <mergeCell ref="A3:B3"/>
    <mergeCell ref="D1:D2"/>
    <mergeCell ref="A4:B4"/>
  </mergeCells>

To get the ranges, the code would be:

data.match(/<mergeCell ref="([A-Z0-9:]+)"\/>/g).map(function(x) { return x.match(/<mergeCell ref="([A-Z0-9:]+)"\/>/)[1]; })

For the aforementioned XML the output would be [ 'A1:B2', 'C1:C2', 'A3:B3', 'D1:D2', 'A4:B4' ]

As for style: good catch. What editor do you use? I used to use a vim extension that removed blank spaces, but it clashed with other extensions and I ended up getting rid of it (now it is biting me in the rear end)

@clayzermk1 clearly I produced a good test file :) In that file, I see the following (output passed through `xmllint --format`: ``` <mergeCells count="5"> <mergeCell ref="A1:B2"/> <mergeCell ref="C1:C2"/> <mergeCell ref="A3:B3"/> <mergeCell ref="D1:D2"/> <mergeCell ref="A4:B4"/> </mergeCells> ``` To get the ranges, the code would be: ``` data.match(/<mergeCell ref="([A-Z0-9:]+)"\/>/g).map(function(x) { return x.match(/<mergeCell ref="([A-Z0-9:]+)"\/>/)[1]; }) ``` For the aforementioned XML the output would be `[ 'A1:B2', 'C1:C2', 'A3:B3', 'D1:D2', 'A4:B4' ]` As for style: good catch. What editor do you use? I used to use a vim extension that removed blank spaces, but it clashed with other extensions and I ended up getting rid of it (now it is biting me in the rear end)

clayzermk1 commented

2014-01-07 22:04:13 +00:00

(Migrated from github.com)

clayzermk1/jx-xlsx@37753d8230
Fixed a bug in the logic for actual empty cells.

For the aforementioned XML the output would be [ 'A1:B2', 'C1:C2', 'A3:B3', 'D1:D2', 'A4:B4' ]

I went ahead and converted them to range objects. See https://github.com/clayzermk1/js-xlsx/blob/mergeCells/bits/70_xlsx.js#L113 I think we may be on a slightly different page. I was referring to the CSVesque output containing the duplicated values - I don't like how that works/looks.

How would you implement this feature?

What editor do you use?

SublimeText 3 beta. I love it ❤️

clayzermk1/jx-xlsx@37753d82302f10130edb476147037df908b342a5 Fixed a bug in the logic for actual empty cells. > For the aforementioned XML the output would be `[ 'A1:B2', 'C1:C2', 'A3:B3', 'D1:D2', 'A4:B4' ]` I went ahead and converted them to range objects. See https://github.com/clayzermk1/js-xlsx/blob/mergeCells/bits/70_xlsx.js#L113 I think we may be on a slightly different page. I was referring to the CSVesque output containing the duplicated values - I don't like how that works/looks. How would you implement this feature? > What editor do you use? [SublimeText 3 beta](http://www.sublimetext.com/3). I love it :heart:

SheetJSDev commented

2014-01-08 04:20:32 +00:00

(Migrated from github.com)

CSVesque output containing the duplicated values - I don't like how that works/looks.

https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L84 at first blush, the fix would be something like if(C === val.c && R === val.r) row.push(... As for the JSON object, there is no standard, but it makes sense to parallel the CSV behavior

> CSVesque output containing the duplicated values - I don't like how that works/looks. https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L84 at first blush, the fix would be something like `if(C === val.c && R === val.r) row.push(...` As for the JSON object, there is no standard, but it makes sense to parallel the CSV behavior

vjpr commented

2015-08-29 03:02:40 +00:00

(Migrated from github.com)

+1 It would be great to have isMergeCell and mergeValue.

+1 It would be great to have `isMergeCell` and `mergeValue`.

clayzermk1 commented

2015-09-01 20:29:40 +00:00

(Migrated from github.com)

@vjpr I think you should probably turn that into its own issue so it gets the appropriate attention.

@SheetJSDev since it looks like you added this functionality, I think this issue can be safely closed. Thank you for the work!

@vjpr I think you should probably turn that into its own issue so it gets the appropriate attention. @SheetJSDev since it looks like you added this functionality, I think this issue can be safely closed. Thank you for the work!

mpolutta commented

2016-04-05 01:44:46 +00:00

(Migrated from github.com)

Example for write:

var merges = wb.sheets["mysheetId"]['!merges'] = [];
//then push merges into array
merges.push( { s: 'A1', e: 'F1' } );

Example for write: ``` var merges = wb.sheets["mysheetId"]['!merges'] = []; //then push merges into array merges.push( { s: 'A1', e: 'F1' } ); ```

👍 1

polarBearGit commented

2016-05-30 07:17:59 +00:00

(Migrated from github.com)

can you tell me how to set the alignment property of merge cell?

ashish-agarwal24 commented

2016-10-12 09:10:44 +00:00

(Migrated from github.com)

can someone tell me how to write in the merged cell?

👍 5

Krishnakanth94 commented

2018-05-01 06:51:51 +00:00

(Migrated from github.com)

https://github.com/Krishnakanth94/Xlsx-styles-working-code
the above code shows merge cells

alignment should be given to starting cell to merge it will we done!

https://github.com/Krishnakanth94/Xlsx-styles-working-code the above code shows merge cells alignment should be given to starting cell to merge it will we done!

Sign in to join this conversation.