Feature Request: Add Group and Ungroup columns & rows

[jonekeat]

Hi @ycphs

I have a problem to output a data frame to xlsx with certain styling like grouping several columns based on some conditions.

I think it will be great if openxlsx can have functionality to do grouping & ungrouping columns & rows, while user need to do some manipulation on data frame.

Currently I am doing it in a VBscript, and call it from R, which hard to trace bugs.

Here is the functionality as discussed, under 'Data' tab in excel

[JoshuaSturm]

I've been working on this for the past while, and I currently have two separate working functions - groupColumns and groupRows. I'm going to try to merge them into a single function, continue testing, and submit a PR when I'm done.

[aecoleman]

@JoshuaSturm I think having two functions isn't necessarily a bad thing. It's consistent with the way you set the size of columns/rows in openxlsx via the setColWidths and setRowHeights functions. I think it makes sense for groupColumns and groupRows to act similarly.

Having said that, it could be beneficial to have a single function behind the scenes that is actually doing the work and uses an argument to determine whether it's operating on rows or columns.

Are these functions included in any of the branches on your GitHub? I'd love to take a look at them!

[FrancoisR95]

Many thanks @JoshuaSturm , this will be a very useful functionality, indeed.

Also it would be great to include, as an option, the possibility to collapse some groups of lines or columns. This functionality would certainly fix at the same time the issue already described two years ago "Collapsed rows / columns are lost" : awalker89/openxlsx#383

[JoshuaSturm]

@aecoleman Thanks for the feedback. You're right, and after thinking about it more, I kept the two functions separate. It is more consistent with the rest of the API, as well as the current code structure (rows and columns are handled separately).
I've actually had more work and less time during this quarantine, so I haven't really been able to work on this since I last posted, but I did have a working sample before the hiatus. I'll upload and link you to a dev branch either this weekend or next week.

@FrancoisR95 I added the ability to collapse rows/columns, and, from what I remember when I last worked on it, preferences were being preserved.

Thanks!

[aecoleman]

@JoshuaSturm I know you said that you were busy, but just wanted to give you a nudge and say that I'm really looking forward to taking a look at this!

[JoshuaSturm]

Hi, @aecoleman.

Thanks for the reminder. My current progress is saved here: https://github.com/JoshuaSturm/openxlsx/tree/add-groups
I'm trying to reconcile the hidden argument, which can conflict with the one in setColWidths. Perhaps we should remove it from both, and make it its own function.

I also haven't yet tested on multiple worksheets or loading workbooks. Please let me know of any bugs you come across. Thanks!

[FrancoisR95]

@JoshuaSturm If you have a name conflict for the hidden argument, wouldn't this be a solution: to simply name this argument collapsed in the context of grouped columns or rows?

[JoshuaSturm]

@FrancoisR95 Thanks for the suggestion. The issue wasn't so much the names, but that having conflicting values would lead to errors. For example, setColWidths(wb, sheet = 1, cols = 2, widths = "18", hidden = TRUE) and then groupColumns(wb, sheet = 1, cols = 2, hidden = FALSE).

To keep the changes to the underlying structure minimal, I decided that setting the hidden argument in one function will update it in the other (if it exists), keeping the most recent value.

I'll probably open a PR tomorrow.

[FrancoisR95]

Thank you @JoshuaSturm for the explanations. This seems a good solution which you describe, that "setting the hidden argument in one function will update it in the other (if it exists), keeping the most recent value".