CODEWITHSUNDEEP
ropenxlsx
deschen
deschen

Reputation: 10996

openxlsx: Copying the same style to a new column added to a worksheet

I'm working with the openxlsx package and am cloning a worksheet in a workbook object. I then add new data to this cloned worksheet.

How can I apply the same styles to this added data as currently exists on this sheet?

E.g. assume that the old worksheet consists of data in columns 1:3 that was formatted as percentages, has some underlinings, bolding, font sizes etc. I now just want to make sure that the same format/style from column 3 is applied to column 4 (that's where I add my new data).

I found the getStyle, creatStyle and replaceStyle functions in openxlsx, but don't see any option to grab and apply a certain style (because getStyle returns a list, but it doesn't tell which list element belongs to which worksheet or column).

My current workflow (although probably not relevant/helpful):

old_wb <- loadWorkbook(file.choose())

new_wb <- old_wb
cloneWorksheet(new_wb, "new", "old worksheet")
writeData(new_wb,
          sheet = "new",
          x = c(1:3),
          startRow = 3,
          startCol = 4)

saveWorkbook(new_wb, file = "test.xlsx", overwrite = TRUE)

Upvotes: 3

Views: 1951

Answers (4)

Jan Marvin
Jan Marvin

Reputation: 969

This is one of the many things improved in openxlsx2. In the upcoming release 0.3 you can simply run the following code. This will load a workbook, get the styles from a range of cells. Clone the worksheet, apply the style to a range of cells, add data and open the sheet.

library(openxlsx2)

wb <- wb_load(system.file("extdata", "oxlsx2_sheet.xlsx", package = "openxlsx2"))
styles <- wb_get_cell_style(wb = wb, dims = "A12:G15")

wb <- wb %>% 
  wb_clone_worksheet(new = "Clone1") %>% 
  wb_set_cell_style(dims = "A22:G25", style = styles) %>% 
  wb_add_data(x = matrix(1, 1, 5), dims = "B22:G22", colNames = FALSE) %>%
  wb_open() # or wb_save("file.xlsx")

In openxlsx2 there are no style objects. Therefore the styles of imported xlsx workbook are untouched when modifying the workbook. While you asked to clone a sheet, that's improved as well. It's now even possible to clone sheets with charts and pivot tables (lacking slider support).

Upvotes: 1

Michael Dewar
Michael Dewar

Reputation: 3258

From studying @Ljupcho Naumov's great answer, I learned that if you simply want the cell locations of the styles from openxlsx::getStyle then you can use

old_wb$styleObjects

which returns a list of the styles as well as the sheet/rows/columns. You can check that the style objects are the same with

all.equal(purrr::map(old_wb$styleObjects, "style"), getStyles(old_wb))

Upvotes: 1

Ljupcho Naumov
Ljupcho Naumov

Reputation: 465

You can use this function which combines the capabilities of the openxlsx and tidyxl packages. It will give you the style objects associated with all cells in a given excel, as well as all other content like numeric, character and formulas. You can then directly apply the style to other cells or in a different workbook.

read_excel_template <- function(excel_path) {
   cells <- tidyxl::xlsx_cells(excel_path)
   template <- openxlsx::loadWorkbook(excel_path)
   
   map_df(.x = enframe(template$styleObjects)$value, 
                   .f = ~ tibble(
                     style = c(.x$style),
                     sheet = .x$sheet,
                     row = .x$rows,
                     col = .x$cols
                   )) %>% 
      full_join(cells, by=c("sheet", "row", "col")) %>% 
      relocate(style, .after = address)
}

Upvotes: 4

Jonni
Jonni

Reputation: 922

The getStyles() function returns just the list of style objects present in the workbook, not location of where styles are used. It also orders the styles and can be viewed using getStyles(wb) %>% View()

If you would want to apply an existing style after writing the new column you would have to add the style to that column with addStyle(). You could use the getStyle() though to easily pull over styles and layer them as well.

Below I have 4 styles. Note the the big_purple_text will ultimately be the fourth style listed once the workbook is loaded. I apply these 4 styles to the columns in the iris data set. Save it. And then load it as iris_wb.

library(openxlsx)
wb <- createWorkbook()
bold_blue_text <- createStyle(textDecoration = "bold", fontColour = "blue")
red_italic_text <- createStyle(textDecoration = "italic", fontColour = "red")
big_purple_text <- createStyle(fontSize = 18, fontColour = "purple")
underline_text <- createStyle(textDecoration = "underline")
addWorksheet(wb, "Iris")
writeData(wb, "Iris", iris)

addStyle(wb, "Iris", bold_blue_text, cols = 1, rows = 1:dim(iris)[1],gridExpand = TRUE)
addStyle(wb, "Iris", red_italic_text, cols = 2, rows = 1:dim(iris)[1],gridExpand = TRUE)
addStyle(wb, "Iris", big_purple_text, cols = 3:4, rows = 1:dim(iris)[1],gridExpand = TRUE)
addStyle(wb, "Iris", underline_text, cols = 5, rows = 1:dim(iris)[1],gridExpand = TRUE)

saveWorkbook(wb, file = "C:\\Users\\Desktop\\test.xlsx", overwrite = TRUE)

openXL(wb)

iris_wb <- loadWorkbook("C:\\Users\\Desktop\\test.xlsx")

This will show only the 4 styles loaded, using View you can explore more how the ordering of the styles are listed

getStyles(iris_wb)

getStyles(iris_wb) %>% View() #Here you can view the ascending order of the style types, note that the fontdecoration is above fontsize. This is why big_purple_text is listed 4th

#Write new data to existing sheet
writeData(iris_wb, "Iris", mtcars, startCol = 6)

#add styles from existing styles in loaded workbook
addStyle(iris_wb, "Iris", getStyles(iris_wb)[[1]], cols = 6, rows = 1:10)
addStyle(iris_wb, "Iris", getStyles(iris_wb)[[3]], cols = 7:8, rows = 1:20, gridExpand = TRUE)
addStyle(iris_wb, "Iris", getStyles(iris_wb)[[4]], cols = 8:10, rows = 10:20, gridExpand = TRUE, stack = TRUE) # Should retain underline
openXL(iris_wb)

Upvotes: 0

Related Questions