title: R: Iterating over a df to paste one slice at a time into MS Excel Spreadsheet using lapply and seq_along
body: <p>PROBLEM: I am trying to automate data input into a legacy MS Excel spreadsheet that carries out calculations. I have figure out how to use openxlsx to do this one &quot;slice&quot; of data at a time. I would like to modify the code below to iterate over the entire dataset rather than doing this piece meal.</p>
  <pre><code>#NO ITERATION
  
  #load libraries and define working directory
  if(!require(tidyr)){install.packages(&quot;tidyr&quot;)}
  if(!require(openxlsx)){install.packages(&quot;openxlsx&quot;)}
  setwd(&quot;C:/R/Seq_along&quot;)
  
  #Sample data frame 
  Site &lt;- rep(letters[1:6],each=3)
  Param &lt;- as.factor(rep(c(&quot;X&quot;,&quot;Y&quot;,&quot;Z&quot;),6))                 
  set.seed(71)
  Result &lt;- sample(0:25, 18, replace = TRUE)   
  df &lt;- data.frame(Site,Param,Result)
  df
  str(df)
  
  #Pivot from long to wide format
  df.long &lt;- df %&gt;% pivot_wider(names_from = Site, values_from = Result)
  
  #Define list of working slices and workbook sheets 
  slicelist &lt;- list(c(2:4),c(5:7))
  #sheetlist &lt;- list(c(&quot;Sheet1&quot;),c(&quot;Sheet2&quot;))
  
  #Slice #1
  #Slice df into first slice
  slicecurrent &lt;- slicelist[[1]]
  #sheetcurrent &lt;- sheetlist[[1]]
  df1 &lt;- df.long[,slicecurrent]
  #Loadworkbook and write first slice into sheet 1 of workbook 
  wb1 &lt;- loadWorkbook(&quot;test.xlsx&quot;)
  class(wb1)
  names(wb1)
  writeData(wb1,sheet=&quot;Sheet1&quot;, x = df1, xy = c(2,6), colNames = FALSE, rowNames = FALSE)
  saveWorkbook(wb1,&quot;test.xlsx&quot;, overwrite = TRUE)
  #openXL(&quot;test.xlsx&quot;)
  
  #Slice #2
  #Slice df into first slice
  slicecurrent &lt;- slicelist[[2]]
  #sheetcurrent &lt;- sheetlist[[2]]
  df1 &lt;- df.long[,slicecurrent]
  #Loadworkbook and write first slice into sheet 1 of workbook 
  wb1 &lt;- loadWorkbook(&quot;test.xlsx&quot;)
  class(wb1)
  names(wb1)
  writeData(wb1,sheet=&quot;Sheet2&quot;, x = df1, xy = c(2,6), colNames = FALSE, rowNames = FALSE)
  saveWorkbook(wb1,&quot;test.xlsx&quot;, overwrite = TRUE)
  openXL(&quot;test.xlsx&quot;)
  </code></pre>
  <p><strong>Those data I am using need to be cut into &quot;slices&quot; (using 2 slices here) since the spreadsheet
  can only handle a certain number of data columns at a time (in this case 3). The actual data to be pasted is onto the spreasheet is called, &quot;df.long&quot; and the test spreadsheet is a blank spreadsheet called, &quot;test.xlsx&quot;, that is saved in the working directory.</strong></p>
  <p>Here is an image of the result I am after:
  <a href="https://ibb.co/SJ0xG4Q" rel="nofollow noreferrer">https://ibb.co/SJ0xG4Q</a></p>
  <p>I have tried using lapply in combination with the seq_along function to specify the slices to use and iterate the process. However. rather than pasting the first chunk into sheet1 in the spreadsheet and the second chunk in sheet2 of the spreadsheet like in the no-iteration script, its iterating over all elements of the list and I end up with the same chunk being pasted on both sheet1 and sheet2 (as I have told it to do so).</p>
  <pre><code>
  #load libraries and define working directory
  if(!require(tidyr)){install.packages(&quot;tidyr&quot;)}
  if(!require(openxlsx)){install.packages(&quot;openxlsx&quot;)}
  setwd(&quot;C:/R/Seq_along&quot;)
  
  #Sample data frame 
  Site &lt;- rep(letters[1:6],each=3)
  Param &lt;- as.factor(rep(c(&quot;X&quot;,&quot;Y&quot;,&quot;Z&quot;),6))                 
  set.seed(71)
  Result &lt;- sample(0:25, 18, replace = TRUE)   
  df &lt;- data.frame(Site,Param,Result)
  df
  str(df)
  
  #Pivot from long to wide format
  df.long &lt;- df %&gt;% pivot_wider(names_from = Site, values_from = Result)
  
  
  
  #Define list of working slices and workbook sheets 
  slicelist &lt;- list(c(2:4),c(5:7))
  #sheetlist &lt;- list(c(&quot;Sheet1&quot;),c(&quot;Sheet2&quot;))
  
  lapply(seq_along(slicelist),
       function(i){
  #Slice #1
  #Slice df into first slice
  #slicecurrent &lt;- slicelist[[1]]
  #sheetcurrent &lt;- sheetlist[[1]]
  df1 &lt;- df.long[,slicelist[[i]]]
  #Loadworkbook and write first slice into sheet 1 of workbook 
  wb1 &lt;- loadWorkbook(&quot;test.xlsx&quot;)
  class(wb1)
  names(wb1)
  writeData(wb1,sheet=&quot;Sheet1&quot;, x = df1, xy = c(2,6), colNames = FALSE, rowNames = FALSE)
  saveWorkbook(wb1,&quot;test.xlsx&quot;, overwrite = TRUE)
  #openXL(&quot;test.xlsx&quot;)
  
  #Slice #2
  #Slice df into first slice
  #slicecurrent &lt;- slicelist[[2]]
  #sheetcurrent &lt;- sheetlist[[2]]
  df1 &lt;- df.long[,slicelist[[i]]]
  #Loadworkbook and write first slice into sheet 1 of workbook 
  wb1 &lt;- loadWorkbook(&quot;test.xlsx&quot;)
  class(wb1)
  names(wb1)
  writeData(wb1,sheet=&quot;Sheet2&quot;, x = df1, xy = c(2,6), colNames = FALSE, rowNames = FALSE)
  saveWorkbook(wb1,&quot;test.xlsx&quot;, overwrite = TRUE)
  })
  openXL(&quot;test.xlsx&quot;)
  </code></pre>
  <p>Is there a way I can I replicate the result of the first script using lapply with the seq_along function or should I be using a for loop instead?</p>
  <p>1: <a href="https://i.stack.imgur.com/XSmhT.jpg" rel="nofollow noreferrer">https://i.stack.imgur.com/XSmhT.jpg</a></p>
  <p>2: <a href="https://i.stack.imgur.com/aPGjG.jpg" rel="nofollow noreferrer">https://i.stack.imgur.com/aPGjG.jpg</a></p>
  <p>3: <a href="https://ibb.co/SJ0xG4Q" rel="nofollow noreferrer">https://ibb.co/SJ0xG4Q</a></p>