For loop for writing an excel file using xlsx?

Question

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

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

Can you provide us with some test data (rather than a screenshot) that we can copy and paste to better understand the issue and test possible solutions? You can share datasets with `dput(YOUR_DATASET)` or smaller samples with `dput(head(YOUR_DATASET))`. (See [this answer](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example#5963610) for detailed instructions.) — ktiu, Jun 04 '21 at 16:22
Can you give an example of what the output would look like? Would each spreadsheet just contain the row for the specified type? — Addison, Jun 04 '21 at 16:59
It would just be the relevant columns and rows associated with the STR_NBR — Jaskeil, Jun 04 '21 at 17:00
@Jaskeil If the values in the MVNDR have `/` in them that could cause problems when trying to save the data. Could you use dput on the dataframe and add the result to the question so I can replicate it and investigate further? — norie, Jun 07 '21 at 13:16
Sure, well we dont need to use that column. You can just use the number let me update the question. — Jaskeil, Jun 07 '21 at 13:19

score 3 · Accepted Answer · answered Jun 04 '21 at 17:05

3

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"))})

answered Jun 04 '21 at 17:05

norie

9,609
2
11
18

When I applied to my data frame I had the following issue: – Jaskeil Jun 07 '21 at 12:52
`Error in saveWorkbook(wb, file, password = password) : java.lang.NoSuchMethodError: In addition: Warning messages: 1: In if (type == "xls") { : the condition has length > 1 and only the first element will be used 2: In if (type == "xlsx") { : the condition has length > 1 and only the first element will be used` – Jaskeil Jun 07 '21 at 12:52
What code is giving the error? Is there anything 'different' about the data? – norie Jun 07 '21 at 13:06
Thanks for the response, the code is the following: `lapply(group_split(d22_open_quotes), function(x){write.xlsx(x,paste0(x$MVNDR, ".xlsx"))})` where I attempt to print out a new spreadsheet for every mvndr. Let me also update the question wth a data frame – Jaskeil Jun 07 '21 at 13:08
I reframed the question so that it is more clear and the desired output is more clear as well https://stackoverflow.com/questions/67873269/iterating-over-large-dataframe-to-write-individual-excel-spreadsheets – Jaskeil Jun 07 '21 at 14:13

For loop for writing an excel file using xlsx?

1 Answers1