CODEWITHSUNDEEP
ropenxlsx
Jonas
Jonas

Reputation: 1

Reading XLSX-file with multiple sheets and special cell-style/formation into R

a coworker for my project is working mainly in excel and he provided me with an xlsx file with more than 400 sheets with a similar structure. Anyway, every sheet has information for more than one individual (max of 12) and for each individual, there are about 50 different measurements (length of special character). But they are equally organized on the sheets so here is not the issue. The issue is that between all this data there are manually inserted mean values added for cases where the measurement could not be done, which I would like to remove. The only way to identify these mean values is based on the font color.

Does someone know how I can loop through all sheets and get not only the cell information but also the style of each cell?

I tried already using the "readxl", "tidyxl" & "openxlsx" packages, but my problem is that I am not able to loop through all sheets using the "tidyxl::xlsx_formats(path = path)" function or the "openxlsx::loadWorkbook" function.

I´m sure that I´m just missing a function or another way to deal with this problem.

Thank´s already for any help :).

So far I was using "readxl::excel_sheets" to loop through all sheets and get the cell information given in each cell but I can´t find a way to get the cell style information too (R version 4.2.2 Patched, packageVersion("readxl") ‘1.4.0’).

Then I tried using "tidyxl::xlsx_formats" which gives me the cell style information but only for the first sheet (packageVersion("tidyxl") ‘1.0.8’). I followed the instructions on their CRAN page.

Also, I tried different approaches like (openxlsx::loadWorkbook) which I found on other issues like "https://stackoverflow.com/questions/62519400/filter-data-highlighted-in-excel-by-cell-fill-color-using-openxlsx".

Upvotes: 0

Views: 149

Answers (1)

Emmanuel Hamel
Emmanuel Hamel

Reputation: 2213

You can consider the following approach which only works on Windows :

library(RDCOMClient)

path_Excel_File <- "D:/Dropbox/Reponses_Stackoverflow/stackoverflow_346/empty_Excel_File.xlsx"

xlApp <- COMCreate("Excel.Application")
xlApp[["Visible"]] <- TRUE
xlWbk <- xlApp$Workbooks()$Open(path_Excel_File)
Sheets <- xlWbk$Sheets()

Mat_Val <- matrix(NA, nrow = 2, ncol = 2)
Mat_Style <- matrix(NA, nrow = 2, ncol = 2)
list_Mat_Val <- list()
list_Mat_Style <- list()

for(l in 1 : 400)
{
  for(i in 1 : 2)
  {
    for(j in 1 : 2)
    {
      Mat_Val[i, j] <- Sheets[[l]]$Cells(i, j)$Value()
      Mat_Style[i, j] <- Sheets[[l]]$Cells(i, j)$Style()$Value()
    }
  }
  
  list_Mat_Val[[l]] <- Mat_Val
  list_Mat_Style[[l]] <- Mat_Style
}

Upvotes: 0

Related Questions