R / openxlsx / Finding the first non-empty cell in Excel file

Question

I'm trying to write data to an existing Excel file from R, while preserving the formatting. I'm able to do so following the answer to this question (Write from R into template in excel while preserving formatting), except that my file includes empty columns at the beginning, and so I cannot just begin to write data at cell A1.

As a solution I was hoping to be able to find the first non-empty cell, then start writing from there. If I run read.xlsx(file="myfile.xlsx") using the openxlsx package, the empty columns and rows are automatically removed, and only the data is left, so this doesn't work for me.

So I thought I would first load the worksheet using wb <- loadWorkbook("file.xlsx") so I have access to getStyles(wb) (which works). However, the subsequent command getTables returns character(0), and wb$tables returns NULL. I can't figure out why this is? Am I right in that these variables would tell me the first non-empty cell?

I've tried manually removing the empty columns and rows preceding the data, straight in the Excel file, but that doesn't change things. Am I on the right path here or is there a different solution?

I started on something but it didn't work. What I can offer up is that by "table" they likely mean a Table object in Excel from INSERT >> Table in the ribbon. — , Jan 23 '19 at 14:12
Maybe [tidyxl](https://github.com/nacnudus/tidyxl) could be helpful here. — Stéphane Laurent, Jan 23 '19 at 14:39

score 1 · Accepted Answer · answered Jan 24 '19 at 12:58

As suggested by Stéphane Laurent, the package tidyxl offers the perfect solution here.

For instance, I can now search the Excel file for a character value, like my variable names of interest ("Item", "Score", and "Mean", which correspond to the names() of the data.frame I want to write to my Excel file):

require(tidyxl)
colnames <- c("Item","Score","Mean")
excelfile <- "FormattedSheet.xlsx"
x <- xlsx_cells(excelfile)

  # Find all cells with character values: return their address (i.e., Cell) and character (i.e., Value)
  chars <- x[x$data_type == "character", c("address", "character")]

  starting.positions <- unlist(
    chars[which(chars$character %in% colnames), "address"]
  )
     # returns: c(C6, D6, E6)

R / openxlsx / Finding the first non-empty cell in Excel file

1 Answers1