Data manipulation in Excel

[jpappire]

Hi All,

I need help on below query .kindly advise if anyone knows.

i wanted to copy particular cell in excel and paste in different excel sheet .how to do in r studio.
Also need to know how to copy and paste values in particular cell range from one excel to another excel sheet . it would be grateful if i get answer with small example .

[andresrcs]

Since you only want to modify excel documents, it would make much more sense to use VBA instead of R.

[jpappire]

Thank you for the reply andresrsc.
But we have requirement to do in R , please advise

[jdlong]

check out the openxlsx package. It allows reading and writing of cell ranges:

https://www.rdocumentation.org/packages/openxlsx/versions/4.1.0/topics/read.xlsx

[FJCC]

Here is a simple example.

library(openxlsx)
File <- "Source.xlsx"
wb <- loadWorkbook(file = File)
#Read D7:F10 of the first sheet.
DataFromSource <- read.xlsx(wb, sheet = 1, colNames = TRUE, rows = seq(7, 10), cols = seq(4,6))

wb2 <- loadWorkbook(file = "Target.xlsx")
writeData(wb2, sheet = "Sheet1", x = DataFromSource, startCol = "B", startRow = 15, colNames = TRUE) #
saveWorkbook(wb2, "Target.xlsx", overwrite = TRUE)

[jpappire]

Thanks very much for the answer .. its works .But the column names are dropped and also want to delete data based on date and copy specific one cell value in excel , is it possible? Please advise

[FJCC]

Yes, you can do what you want with the openxlsx package but you need to study the documentation. See https://cran.r-project.org/web/packages/openxlsx/openxlsx.pdf.
Concerning your specific questions and comments:

1. "Column names are dropped". My code read in particular cells. An entire sheet can be read with the read.xlsx() function if you do not specify the rows and cols parameters. Or you can include the row used for column names in the rows parameter. Choose whatever is best for your needs.

2. "Delete data based on date". You can read the value of the cell with the read.xlsx() function and you can convert that value to an R date with the convertToDate() function. A cell value can be deleted with the deleteData() function.

3. "Copy specific one cell value". This can be done with a combination of read.xlsx() and writeData(), as shown in my previous post.

Those are just hints to get you started. Please do look at the package documentation. There are also other packages for handling Excel files. I happen to be using openxlsx at the moment.

[jpappire]

Thank you very much for the guidance , But i couldn't able to get particular row value when i used below code,please suggest where i was wrong in below code.

File <- "D:/Users/Documents/rtest/surveys.xlsx"
wb <- loadWorkbook(file = File)
#mammalss <- read.xlsx2(wb, sheet=1 , colNames = TRUE, rows = 11, cols = 6)

As per above code i was looking 11 row value from column 6 in the file.

[jpappire]

Hi,

The below code reading column names as row names i tried by giving colNames = TRUE & colNames = FALSE also, but its not giving column names as header

DataFromSource <- read.xlsx(wb, sheet = 1, colNames = TRUE, rows = seq(7, 10), cols = seq(4,6))

[FJCC]

There may be a misunderstanding of what colNames = TRUE does. Here are some examples. First I read in the entire sheet with colNames = FALSE. This shows what the data look like in Excel. I then read in the entire sheet but with colNames = TRUE. All of the row numbers shift by one because the first row is used as column names. I then read a subset of the sheet, D7:F10 both with and without using the first row as column headers.

library(openxlsx)
File <- "/home/fjcc/R/Play/Source.xlsx"
wb <- loadWorkbook(file = File)

#Read the whole sheet with the first row Not used as headers
DF1 <- read.xlsx(wb, sheet = 1, colNames = FALSE)
DF1
#>       X1     X2      X3     X4    X5    X6
#> 1  First Second   Third Fourth Fifth Sixth
#> 2     23  28.29 34.7967      1     3    11
#> 3     25  30.75 37.8225      2     4    22
#> 4     26  31.98 39.3354      3     5    33
#> 5     27  33.21 40.8483      4     6    44
#> 6     28  34.44 42.3612      5     7    55
#> 7     28  34.44 42.3612      A     s     d
#> 8     29  35.67 43.8741      1     4     7
#> 9     30   36.9  45.387      2     5     8
#> 10  <NA>   <NA>    <NA>      3     6     9

#Read the whole sheet with the first row used as headers
DF2 <- read.xlsx(wb, sheet = 1, colNames = TRUE)
DF2
#>   First Second   Third Fourth Fifth Sixth
#> 1    23  28.29 34.7967      1     3    11
#> 2    25  30.75 37.8225      2     4    22
#> 3    26  31.98 39.3354      3     5    33
#> 4    27  33.21 40.8483      4     6    44
#> 5    28  34.44 42.3612      5     7    55
#> 6    28  34.44 42.3612      A     s     d
#> 7    29  35.67 43.8741      1     4     7
#> 8    30  36.90 45.3870      2     5     8
#> 9    NA     NA      NA      3     6     9

#Read D7:F10 of the first sheet, use row 7 as headers.
DF3 <- read.xlsx(wb, sheet = 1, colNames = TRUE, rows = seq(7, 10), cols = seq(4,6))
DF3
#>   A s d
#> 1 1 4 7
#> 2 2 5 8
#> 3 3 6 9

#Read D7:F10 of the first sheet, do not use row 7 as headers.
DF4 <- read.xlsx(wb, sheet = 1, colNames = FALSE, rows = seq(7, 10), cols = seq(4,6))
DF4
#>   X1 X2 X3
#> 1  A  s  d
#> 2  1  4  7
#> 3  2  5  8
#> 4  3  6  9