1

I have two excel files with multiple sheets. The sheet names and their corresponding column names are same of both the files. Only the values in the sheets will differ. I want to compare using R which values are different and want to mark those cells

Swagat Sahu
  • 11
  • 1
  • 2
  • I'm not sure if this is possible from R. Using a macro instead would be the more convenient way. See also here: http://stackoverflow.com/questions/18511249/excel-cell-coloring-using-xlsx – Roman May 23 '16 at 07:36
  • Thanks for the link but yes, it's easy in Macro and have done it but I need it in R specifically, so I need this help. – Swagat Sahu May 23 '16 at 07:43
  • Look at `XLConnect`. – coffeinjunky May 23 '16 at 07:44
  • How do you want to "mark those cells"? Change formatting, for example shade, or make text bold, etc.? – zx8754 May 23 '16 at 08:00
  • Please add what you tried. StackOverflow is no code writing service. – lukeA May 23 '16 at 08:01
  • Yes, marking the cells means change formatting (Ex - Color Red etc). And since I am a beginner to R, I have tried just importing the sheets but don't know how to work going forward of comparison – Swagat Sahu May 23 '16 at 08:57

1 Answers1

2

For the fun of it, here's a quick&dirty example on which you can build up for your specific needs:

wbsCreate <- function(v) {
  wb <- createWorkbook()
  sheet <- createSheet(wb, "Sheet1")
  rows  <- createRow(sheet, rowIndex=1:5)
  cells <- createCell(rows, colIndex=1:5) 
  for (r in 1:5)
    for (c in 1:5)
      setCellValue(cells[[r, c]], value = v[(r-1)*5+c])
  saveWorkbook(wb, tf <- tempfile(fileext = ".xlsx"))
  return(tf)
}

wbsMarkDiff <- function(fn1, fn2) {
  fns <- c(fn1, fn2)
  wb <- lapply(fns, loadWorkbook)  
  cs <- lapply(wb, function(x) CellStyle(x) + 
                 Fill(backgroundColor="red", 
                      foregroundColor="red", 
                      pattern="SOLID_FOREGROUND"))
  sheets <- lapply(wb, getSheets)
  sheetnames <- do.call(intersect, lapply(sheets, names))
  for (sheetname in sheetnames) {
    sheet <- lapply(sheets, "[[", sheetname)
    rows <- lapply(sheet, getRows)
    cells <- lapply(rows, getCells)
    values <- lapply(cells, function(cell) lapply(cell, getCellValue))
    idx <- names(which(!mapply(identical, values[[1]], values[[2]])))
    for (s in 1:2) 
      for (i in idx) 
        setCellStyle(cells[[s]][[i]], cs[[s]])
    for (s in 1:2)
      saveWorkbook(wb[[s]], fns[s])
  }
}

library(xlsx)

# create to excel workbooks (same dimensions per sheet)    
v <- LETTERS[1:25]
tf1 <- wbsCreate(v)
v[c(3,6,9)] <- letters[c(3,6,9)]
tf2 <- wbsCreate(v)

# mark differences     
wbsMarkDiff(tf1, tf2)
shell.exec(tf1) # open file1 on windows
shell.exec(tf2) # open file2 on windows

You get help on each command by using ?, for example ?createWorkbook gives you the help files on that function.

lukeA
  • 53,097
  • 5
  • 97
  • 100