Sheetjs: Merged cell handling

[clayzermk1]

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]

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.

[clayzermk1]

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]

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

[SheetJSDev]

@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]

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" [email protected] 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]

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]

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]

@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]

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. I love it :heart:

[SheetJSDev]

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]

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

[clayzermk1]

@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]

Example for write:

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

[polarBearGit]

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

[ashish-agarwal24]

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

[Krishnakanth94]

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!