Dependent data validation in R

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Dependent data validation in R

Manish Mukherjee
Hi All ,
I need help in creating dependent data validation in R while creating an excel workbook
So i have two columns one has country and other states . I have to create the excel workbook in such a way that when i select the one specific country , the corresponding states appear in the next column . Something similar to dependent input in shiny ,
Here is the code what i have written

wb <- createWorkbook()
addWorksheet(wb, "Input")
addWorksheet(wb, "list")

df = data.frame("Country"=c("India","US"))
df1=data.frame("IndiaStates"=c("tamilnadu","Andhra pradesh"))
df2=data.frame("USStates"=c("Texas","California"))

# Add drop-down values "
writeData(wb, sheet = "list", x = df, startCol = 1)
writeData(wb, sheet = "list", x = df1, startCol = 2)
writeData(wb, sheet = "list", x = df2, startCol = 3)
#Add drop-downs to the column
dataValidation(wb, "input", col = 1, rows = 2:5, type = "list", value =
                 "'db'!$A$2:$A$3")

dataValidation(wb, "input", col = 2, rows = 2:5, type = "list", value =
                 "'db'!$b$2:$b$3")

dataValidation(wb, "input", col = 3, rows = 2:5, type = "list", value =
                 "'db'!$c$2:$c$3")

saveWorkbook(wb, "dataValidationExample.xlsx", overwrite = TRUE)


The states should be in single column in exported excel workbook with dependent data validation


Thanks, and Regards

Manish Mukherjee



        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Dependent data validation in R

Jeff Newmiller
Why are you telling us all of this? Do you have a question about the R language? Dragging in stuff about Excel makes it seem like you just want us to do your work for you.

Try to be more specific about what your difficulty is with R. Here are some links that may help:

[1] http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example

[2] http://adv-r.had.co.nz/Reproducibility.html

[3] https://cran.r-project.org/web/packages/reprex/index.html (read the vignette)


On June 6, 2020 2:42:49 AM PDT, Manish Mukherjee <[hidden email]> wrote:

>Hi All ,
>I need help in creating dependent data validation in R while creating
>an excel workbook
>So i have two columns one has country and other states . I have to
>create the excel workbook in such a way that when i select the one
>specific country , the corresponding states appear in the next column .
>Something similar to dependent input in shiny ,
>Here is the code what i have written
>
>wb <- createWorkbook()
>addWorksheet(wb, "Input")
>addWorksheet(wb, "list")
>
>df = data.frame("Country"=c("India","US"))
>df1=data.frame("IndiaStates"=c("tamilnadu","Andhra pradesh"))
>df2=data.frame("USStates"=c("Texas","California"))
>
># Add drop-down values "
>writeData(wb, sheet = "list", x = df, startCol = 1)
>writeData(wb, sheet = "list", x = df1, startCol = 2)
>writeData(wb, sheet = "list", x = df2, startCol = 3)
>#Add drop-downs to the column
>dataValidation(wb, "input", col = 1, rows = 2:5, type = "list", value =
>                 "'db'!$A$2:$A$3")
>
>dataValidation(wb, "input", col = 2, rows = 2:5, type = "list", value =
>                 "'db'!$b$2:$b$3")
>
>dataValidation(wb, "input", col = 3, rows = 2:5, type = "list", value =
>                 "'db'!$c$2:$c$3")
>
>saveWorkbook(wb, "dataValidationExample.xlsx", overwrite = TRUE)
>
>
>The states should be in single column in exported excel workbook with
>dependent data validation
>
>
>Thanks, and Regards
>
>Manish Mukherjee
>
>
>
> [[alternative HTML version deleted]]
>
>______________________________________________
>[hidden email] mailing list -- To UNSUBSCRIBE and more, see
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.

--
Sent from my phone. Please excuse my brevity.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.