Outline settings

[klar-C]

Hi - is there a way to change the outline settings for grouping?

I haven't found an option for that in the reference.

[klar-C]

Hi - I wanted to follow up on this one.

Is there maybe a way to hack it? Any direction would be helpful. I could try to use the reticulate/python route but that comes with other headaches. Hence would be awesome to get some pointers as to how to code it.

[JanMarvin]

Changing the first two is simple, that is just some xml <outlinePr showOutlineSymbols="1" summaryBelow="false" summaryRight="false" />. It wont add subtotals, it simply changes the direction of the last cell. If you want Excel to show dynamic subtotals, you could add them the same way Excel does and with formula SUBTOTAL(). For the styles I don't know, I don't even know what this is supposed to do and I had to research all of this because a certain someone did not provide anything useful besides a simple screenshot.

[JanMarvin]

The outlinePr XML goes to wb$worksheets[[sheet]]$sheetPr it should be applied when saving. It's a per sheet definition therefore it must be applied to all sheets where it is desired.

[klar-C]

Okay thanks. Apologies for not being exact in my question. I was actually talking about the first two options as well - I've never used the third one in the screenshot either.

I will try this out. Thanks so much!

[JanMarvin]

No worries, it's just that we could have solved this a lot faster if you would have provided the requested steps to reproduce your issue. Let me know if this works, I've tested it, but not with openxlsx tbh. The help pages are a bit short on examples and tonight I wasn't in the mood to figure out how to create groupings ;)

[klar-C]

Okay this seems to have worked fine:

wb$worksheets[[1]]$sheetPr <- '<outlinePr showOutlineSymbols="1" summaryBelow="false" summaryRight="false" />'

Question though: Does that have any side-effects I might not be thinking of?

[JanMarvin]

No, I don't think so. That's what openxlsx does under the hood. Modifying and applying XML strings. If you want to, you could prepare a pull request to add this to the package. Glad that it works!

[klar-C]

Okay got it. I never looked at the actual library code but I assume that sending something to sheetPr adds the xml to the existing one?

Reason I was asking is that sheetPr seemed like a very high level attribute. And if I assign the string to it I might overwrite something else at the same time.

[JanMarvin]

Well you could look at the previous content of the field, you need outlinepr node and sheetpr should have only one outlinepr node.

[JanMarvin]

The remaining fields of sheetPr are here (select Properties in the drop down to see a full list). There are a few, nothing critical, but you might want to have a look.

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.