How to control which sheet is visible when a workbook is opened in Excel

[Jeff-White-AZ]

I am using openxlsx2 to modify a workbook with 30 sheets and then save the wb with a different name.
When I open the new workbook in Excel, the last sheet viewed in the original file is always displayed, but I would like the workbook to open to different sheets (e.g., #3).
I have tried:
wb_set_active_sheet(wb, sheet = 3)
wb_set_row_heights(wb, sheet = 3, 1, heights = 95, hidden = FALSE)
The latter was as the last command before wb_save() in the hopes that the last modified sheet would be displayed on opening in Excel.
Is there a way to control this property from openxlsx2?

[JanMarvin]

Hi @Jeff-White-AZ ,

if I understand you correctly, you can try this (select and make the third sheet the active sheet):

library(openxlsx2)

wb <- wb_workbook()

for (i in seq_len(10)) {
  wb$add_worksheet(as.character(i))
}

wb$set_active_sheet(sheet = 3)
wb$set_selected(sheet = 3)

if (interactive()) wb$open()

[Jeff-White-AZ]

Yes! Thank you for the quick response. The script now creates a workbook that opens on the specified sheet (here = 3).

[Jeff-White-AZ]

While Jan's suggestion worked nicely, I see another annoyance. Although the correct sheet is displayed, the tabs with sheet names are displaying much further to the right along the set of 30 sheets.
Is there a way to control the behavior so that the tab corresponding to the active sheet is visible when the wb opens?

[JanMarvin]

Not really sure what you mean. Maybe you can make a screenshot, but at the moment there are no other ways to modify the appearance of the selected sheet.

[JanMarvin]

Ah, I think I know what you mean. We do not provide an API for this atm, but you can alter the XML string manually. You want to modify firstSheet in the bookViews string below:

library(openxlsx2)

wb <- wb_workbook()

for (i in seq_len(30)) {
  wb$add_worksheet()
}

wb$set_active_sheet(sheet = 3)
wb$set_selected(sheet = 3)

wb$workbook$bookViews <- "<bookViews><workbookView xWindow=\"0\" yWindow=\"760\" windowWidth=\"30240\" windowHeight=\"17600\" firstSheet=\"3\" activeTab=\"3\"/></bookViews>"

if (interactive()) wb$open()

[JanMarvin]

Ah, now I look a bit stupid, we provide an API for it. wb_set_active_sheet() is just an internal shortcut calling wb_set_bookview(). This should provide you with what you want. Be aware that wb_set_bookview() accepts only integers not sheet names, because I forgot to implement this. You can get the integer positions using something like this wb$validate_sheet("Sheet 4").

library(openxlsx2)

wb <- wb_workbook()

# add a few sheets
for (i in seq_len(30)) {
  wb$add_worksheet()
}

# select sheet 3 and set the selected sheet(s)
wb$set_selected(sheet = 3)

# modify the bookview
wb$set_bookview(active_tab = 3, first_sheet = 3)

if (interactive()) wb$open()

[Jeff-White-AZ]

Your first solution, 'wb$workbook$bookViews <- "<workbo ...' worked fine with the qualification that it looked like the XML uses numbers for sheets 0, 1, 2, 3. When I tried setting sheets to 1, I got the second sheet.
With the second solution. 'wb$set_bookview(active_tab = 3, first_sheet = 3)', the selected sheet was #3 but the sheet first viewable when I opened the workbook was #4. Thus, what I actually need is the somewhat confusing command:
wb$set_bookview(active_tab = 3, first_sheet = 2)
Some users might consider this a minor bug.
I don't need a specific sheet selected, so I commented out the code 'wb$set_selected(sheet = 3)'. My script still works as expected. Was there a reason you included this command?
No apologies necessary for not realizing that the wb_set_bookview() already existed. I must have seen this but not understood its function. You might change the title from 'Set the workbook position, size and filter' to 'Set the workbook position, size, filter and initial view on opening' or something similar. The descriptive text could be expanded as well to explain that it "controls how the workbook appears upon opening, including which sheet is first displayed."
Warmest regards from Phoenix, AZ,
Jeff W

[Jeff-White-AZ]

I should have tested a bit further.
Using wb$set_bookview(active_tab = 1, first_sheet = 0), the file opens showing the second sheet but tab 1 is shown
Using wb$set_bookview(active_tab = 0, first_sheet = 0), the file opens showing the first sheet and tab 1 is shown

Further tests:
wb$set_bookview(active_tab = 1), the file opens showing the second sheet but tab 1 is shown.

wb$set_bookview(active_tab = 0), the file opens showing the first sheet and tab 1 is shown.

wb$set_bookview(first_sheet = 0), the file opens showing the first sheet and tab 1 is shown.

wb$set_bookview(first_sheet = 1), the file opens showing the first sheet. Tab 2 is shown and tab 1 is hidden (...).

It seems that it is safer to use both the active-tab and first_sheet arguments and remember that the sheets are numbered 0, 1, 2 ...

• Jeff

[JanMarvin]

The selection is necessary as otherwise the first sheet or, in the case of a loaded workbook, the last previously selected sheet is selected too. (Excel allows you to select multiple sheets, and if you change something in the current sheet, it will be applied to all selected sheets. This could surprise users, especially if the first sheet is not visible in the sheet tab).

Yes, I figured there was some zero based integer logic at work, but due to minimal testing I didn't want to implement anything beyond the basics for this function. Now it is released and chances are, that we break exisisting code, if we convert the argument to a zero based integer. For various functions our documentation is still sparse, lacking explanations of what is going on, what works or how functions can be safely combined and examples of how to use the functions. We have already massively expanded the documentation in the last year, but there are still so many things that are not documented.

This function wb_set_bookview() was created by reading the Microsoft documentation of office open xml and just adding all possible arguments. Without much testing and therefore without much documentation. Testing, checking and documenting things takes time, and time is always the biggest problem in hobby projects. So if you want to improve something, please feel free to open a bug report or an issue.

[Jeff-White-AZ]

Jan, Thanks for clarifying the need for selecting the sheet to avoid dangerous behavior (such as deleting rows on all selected sheets, which I have done in the past). I fully sympathize with your issue of not wanting to break existing code. As I progress with openxlsx2, I would be willing to provide more complete documentation for you to consider. What is your preference for ways to provide edits? I've been an editor/associate editor of major agricultural journals, which should mean you wouldn't need to do basic editing for punctuation, clarity, etc. Best regards, Jeff

…

On Fri, Feb 23, 2024 at 12:01 PM Jan Marvin Garbuszus < ***@***.***> wrote: The selection is necessary as otherwise the first sheet or, in the case of a loaded workbook, the last previously selected sheet is selected too. (Excel allows you to select multiple sheets, and if you change something in the current sheet, it will be applied to all selected sheets. This could surprise users, especially if the first sheet is not visible in the sheet tab). Yes, I figured there was some zero based integer logic at work, but due to minimal testing I didn't want to implement anything beyond the basics for this function. Now it is released and chances are, that we break exisisting code, if we convert the argument to a zero based integer. For various functions our documentation is still sparse, lacking explanations of what is going on, what works or how functions can be safely combined and examples of how to use the functions. We have already massively expanded the documentation in the last year, but there are still so many things that are not documented. This function wb_set_bookview() was created by reading the Microsoft documentation of office open xml and just adding all possible arguments. Without much testing and therefore without much documentation. Testing, checking and documenting things takes time, and time is always the biggest problem in hobby projects. So if you want to improve something, please feel free to open a bug report or an issue. — Reply to this email directly, view it on GitHub <#957 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BGFFQ2HVKT723HK6753SJR3YVDRR7AVCNFSM6AAAAABDVXXCUGVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DKNZSGAYDC> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[JanMarvin]

Very cool if you could help with openxlsx2. This package, aside from the time invested, is just a hobby project. I do it because I enjoy working on it for some reason, and so should anyone who contributes. And adding a sentence to a previously undocumented function or correcting and rearranging text blocks are always very valuable contributions.

I asked you because you are working through the actual code/documentation and seeing the package from a user's point of view, whereas I often see it from a programmer's point of view, and they are often contrary.

Preferably these edits would be in the form of Github pull requests, but if you're not familiar with Git/Github, you can also email me with a diff or Word docs in edit mode. And I'm the last person to judge other people's punctuation, especially not in English.

[Jeff-White-AZ]

Hi Jan, I would be glad to help with providing more detailed text and use cases. I'll probably start with Word docs but recognize that it is time I start using GitHub more efficiently. Thanks again for your assistance! - Jeff White

…

On Sat, Feb 24, 2024 at 2:11 AM Jan Marvin Garbuszus < ***@***.***> wrote: Very cool if you could help with openxlsx2. This package, aside from the time invested, is just a hobby project. I do it because I enjoy working on it for some reason, and so should anyone who contributes. And adding a sentence to a previously undocumented function or correcting and rearranging text blocks are always very valuable contributions. I asked you because you are working through the actual code/documentation and seeing the package from a user's point of view, whereas I often see it from a programmer's point of view, and they are often contrary. Preferably these edits would be in the form of Github pull requests, but if you're not familiar with Git/Github, you can also email me with a diff or Word docs in edit mode. And I'm the last person to judge other people's punctuation, especially not in English. — Reply to this email directly, view it on GitHub <#957 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BGFFQ2GEWVWQV26B3SOJQATYVGVDFAVCNFSM6AAAAABDVXXCUGVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DKNZVGY3DG> . You are receiving this because you were mentioned.Message ID: ***@***.***>