2

Using below image to showcase what I am trying to do. So basically I have 2 workbook with 2 sheets at a location. I want to read them in R & perform two operation (Change 0 to NA & get average of each column) then export them back in the same manner to same location (2 workbook & 2 worksheet).

Details on Operation post import both workbook & worksheet are a) Convert all 0 to NA in all the sheets & b) then creating a average row at the end of each column. (Yellow highlighted in above image)

Sample input to output requirement

In the above image Yellow highlighted in the output are the mean values of each column (post converting 0 to NA)

I tried creating below code but the issue was I am not sure how to export multiple worksheet back to the same workbook basically in the order that I imported them.

library(tidyverse)
library(readxl)

path <-  "C:/Users/tme/Desktop/"
file_name <- list.files(pattern = "*.xlsx")

for (i in seq_along(file_name)){
  sheet_nm <- excel_sheets(file_name[i])
  for (j in seq_along(sheet_nm)){
    assign(x = paste0(file_name[i],"_",sheet_nm[j]), value = read_xlsx(path = file_name[i], sheet = sheet_nm[j],col_names=FALSE), envir = .GlobalEnv)
  }
}
Julien
  • 1,613
  • 1
  • 10
  • 26
Vaibhav Singh
  • 1,159
  • 1
  • 10
  • 25
  • 2
    As `readxl` can only read Excel files, you need to pick some other library for writing. https://readxl.tidyverse.org/index.html#other-relevant-packages lists and describes few options (openxlsx, writexl, tidyxl) – margusl Jun 26 '22 at 09:41
  • Perhaps https://stackoverflow.com/questions/66798541/export-multiple-data-frames-to-multiple-excel-worksheets helps or https://stackoverflow.com/questions/27713310/easy-way-to-export-multiple-data-frame-to-multiple-excel-worksheets – stefan Jun 26 '22 at 10:58

0 Answers0