[SOLVED] Set Excel Worksheet Zoom Level from R (openxlsx)

Set Excel Worksheet Zoom Level from R (openxlsx)

If I add a worksheet to an Excel workbook using R I am able to set its zoom level via:

openxlsx::addWorksheet(wb = wb, sheetName="foo", zoom = "bar")

But how do I set the zoom level to an existent sheet? openxlsx provides a similar option to row heights columns width:

openxlsx::setColWidths()
openxlsx::setRowHeights()

but I can't find the option for zoom level setting.

Solution

Somehow we have not implemented a get/set function for sheetViews. The sheetViews xml string contains the zoom value:

"<sheetViews><sheetView workbookViewId=\"0\" zoomScale=\"100\" showGridLines=\"1\" tabSelected=\"1\"/></sheetViews>"

You can set it this way.

# set zoom
set_zoom <- function(x) gsub('(?<=zoomScale=")[0-9]+', x, sV, perl = TRUE)

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Sheet1")

sV <- wb$worksheets[[1]]$sheetViews
wb$worksheets[[1]]$sheetViews <- set_zoom(75)

openXL(wb)