Set last active cell in the book

[lz1nwm]

Is it possible to set the last active cell in the book? Basically, an option to select particular cell within a particular worksheet, and after saving the file it is expected that when it is opened that cell will be displayed.

[JanMarvin]

Hi @lz1nwm ,

that's not possible via argument, but you can do it like this:

wb <- wb_workbook()$add_worksheet()
wb$worksheets[[1]]$freezePane <- "<selection activeCell=\"G9\" sqref=\"G9\"/>"
wb$worksheets[[1]]$sheetViews <- "<sheetViews><sheetView tabSelected=\"1\" topLeftCell=\"B2\" workbookViewId=\"0\"/></sheetViews>"
if (interactive()) wb$open()

To set the top left cell, you'd have to modify sheetViews, but I'm typing on the smartphone.

Let me know if this works.

[JanMarvin]

Updated it, now it actually works and added a line to select the topLeftCell as well.

[Jeff-White-AZ]

This is a valuable capability for workbooks that are to be shared with others. I attempted to modify the code to process all sheets in a workbook, so each sheet will open with A1 selected and as the topLeftCell:
sheet_list <- wb_get_sheet_names(wbTemplate)
for (i in length(sheet_list)) {
wbTemplate$worksheets[[i]]$freezePane <- "<selection activeCell="A1" sqref="A1"/>"
wbTemplate$worksheets[[i]]$sheetViews <- "<sheetView tabSelected="i" topLeftCell="A1" workbookViewId="0"/>"
}

It would be nice to see an openxlsx2 function to set the active cell and opening cell position for any/all sheets.

[JanMarvin]

The cells at the top left can be set with wb_set_sheetview(). Setting the active cell would indeed be nice. The problem in this case is that I've always been too lazy to research how the different sheet views work. Excel allows multiple sheet views and multiple selections.

<sheetViews>
  <sheetView tabSelected="1" workbookViewId="0">
    <pane xSplit="2310" ySplit="2070" topLeftCell="C1" activePane="bottomRight"/>
    <selection/>
    <selection pane="bottomLeft" activeCell="A6" sqref="A6"/>
    <selection pane="topRight" activeCell="C1" sqref="C1"/>
    <selection pane="bottomRight" activeCell="E13" sqref="E13"/>
  </sheetView>
</sheetViews>

(Example from ECMA-376 Part 1, p. 1702)

According to the ECMA documentation, such a thing is possible, and all the pane and selection nodes go into our freezePane element. The difficult question, however, is how we identify the correct node to modify (if we have loaded from such a file). The goal should be to load this file, modify it to change the active cell to another and write the file to look similar to the previously loaded file.

[lz1nwm]

It works. Thank you!