Search code examples
rfor-loopdplyrxlsxopenxlsx

For loop for writing an excel file using xlsx?

I am attempting to write an individual excel spreadsheet for STR_NBR you see in the data below

enter image description here

In other words the logic I think would look as follows:

for (i in seq_along(STR_NBR)) {
  openxlsx::write.xlsx("C:/Users/santi/Documents/R_Scripts/Export_Data_CSV.xlsx", 
                    output_file = sprintf("STR_NBR%s.xlsx", STR_NBR[i])
}

Basically, I am trying to create an individual spreadsheet for every STR_NBR

Example: Say I want to print a spreadsheet for every type column you see below.

  set.seed(42)  ## for sake of reproducibility
    n <- 6
    dat <- data.frame(id=1:n, 
                      date=seq.Date(as.Date("2020-12-26"), as.Date("2020-12-31"), "day"),
                      group=rep(LETTERS[1:2], n/2),
                      age=sample(18:30, n, replace=TRUE),
                      type=factor(paste("type", 1:n)),
                      x=rnorm(n))
    dat
    #   id       date group age   type         x
    # 1  1 2020-12-26     A  27 type 1 0.0356312
    # 2  2 2020-12-27     B  19 type 2 1.3149588
    # 3  3 2020-12-28     A  20 type 3 0.9781675
    # 4  4 2020-12-29     B  26 type 4 0.8817912
    # 5  5 2020-12-30     A  26 type 5 0.4822047
    # 6  6 2020-12-31     B  28 type 6 0.9657529

Update with data from my actual data frame: I am trying to print out a new spreadsheet for every MVNDR column, but grouped not by every row

Quote Date  eSVS Order Nbr  MVNDR
2021-05-24  H6328-206574    60710435
2021-05-27  H8926-157085    60710435
2021-05-24  H1020-178324    60660525
2021-05-24  H1020-178324    60660525
2021-05-27  H0772-64192 60074330
2021-05-27  H0772-64192 60074330
2021-05-27  H0772-64192 60074330
2021-05-25  H6646-240810    60063056
2021-05-25  H6646-240810    60063056
Solution

  • You could use group_by and group_split from the dplyr package to split the data and then use write.xlsx from the xlsx package to create the Excel workbooks.

    The following code uses your example data, you should be able to adjust it for your actual data.

    library(dplyr)
library(xlsx)

set.seed(42)  ## for sake of reproducibility
n <- 6
dat <- data.frame(id=1:n, 
                  date=seq.Date(as.Date("2020-12-26"), as.Date("2020-12-31"), "day"),
                  group=rep(LETTERS[1:2], n/2),
                  age=sample(18:30, n, replace=TRUE),
                  type=factor(paste("type", 1:n)),
                  x=rnorm(n))
dat
#   id       date group age   type         x
# 1  1 2020-12-26     A  27 type 1 0.0356312
# 2  2 2020-12-27     B  19 type 2 1.3149588
# 3  3 2020-12-28     A  20 type 3 0.9781675
# 4  4 2020-12-29     B  26 type 4 0.8817912
# 5  5 2020-12-30     A  26 type 5 0.4822047
# 6  6 2020-12-31     B  28 type 6 0.9657529

dat_grouped <- dat %>% group_by(type)

lapply(group_split(dat_grouped), function(x){write.xlsx(x,paste0(x$type, ".xlsx"))})