-2

Perhaps there is another package for R that will allow me to delete rows in Excel?

Michael
  • 19
  • 1
  • 1
    what did you try? Maybe you can deliver code where you try to delete a row but it is not working. We could help you with the code then. – ruedi Sep 09 '14 at 19:05

1 Answers1

4

Please take a few minutes to read through this before posting questions in the future. Here's one approach using XLConnect:

library(XLConnect)
## create a 'workbook' object 
## from an .xlsx file 
WB <- loadWorkbook(
  "F:/Studio/DefaultWD/testFile.xlsx")
## read data from the first sheet
wbData <- readWorksheet(
  WB,
  sheet=1)
> wbData
  col1 col2
1    A    1
2    B    2
3    C    3
4    D    4
5    E    5
6    F    6
7    G    7
## clear worksheet 1
clearSheet(WB,sheet=1)
writeWorksheet(
  WB,
  data=wbData[-4,],
  sheet=1,
  header=TRUE)
## save the modified file
saveWorkbook(
  WB,
  file="F:/Studio/DefaultWD/testFile.xlsx")
## read in the modified file
newWB <- loadWorkbook(
  "F:/Studio/DefaultWD/testFile.xlsx")
## row 4 has been removed
> readWorksheet(newWB,sheet=1)
  col1 col2
1    A    1
2    B    2
3    C    3
4    E    5
5    F    6
6    G    7
Community
  • 1
  • 1
nrussell
  • 18,382
  • 4
  • 47
  • 60
  • Thanks for the speedy response. This method clears the whole entire sheet then exports new data. Is there anyway to delete a row without clearing the whole sheet? I have existing data that I do not want to be cleared. – Michael Sep 09 '14 at 20:34
  • That is why I stored the existing data in a `data.frame` called `wbData` before clearing the worksheet. As long as you do this, you can recover the original worksheet data. Alternatively, you could store the original data as above, and instead of clearing & overwriting the original file, make a new `workbook` object with the modified data and save it as a different file. Or, you could just create a new worksheet in the original workbook, and fill it with the modified data. There are a lot of ways to go about this. – nrussell Sep 09 '14 at 20:39
  • Thank you again for your response. The excel sheet I am working on has a lot of formatting and data that storing the data in the worksheet or creating a new workbook is not feasible. What I am trying to do in R, is what I can do in excel - click and highlight a row, right click, and delete. This will move all cells up one in Excel. I suppose that cannot happen using XL Connect? I could create a VBA Macro in Excel. Perhaps R can execute an Excel VBA Macro? – Michael Sep 09 '14 at 20:50
  • I'm sure it can be done using `XLConnect`, and most likely with less effort than writing a VBA macro. I'll take another look at the problem; in the meantime you should probably start reading [this XLConnect walkthrough](http://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf). R may or may not be able to execute macros in Excel, my use of VBA is very minimal so I'm not sure. – nrussell Sep 09 '14 at 20:54
  • Thank you so much for taking the time to answer my question. I really appreciate it. – Michael Sep 09 '14 at 21:07