-1

I am using an excel workbook with 32 sheets. However, when I try to put sheet thirty-two at the front of sheet 1 in the workbook using worksheet order it gives an error. Please find the error attached below.

worksheetOrder(wb)<-c(32,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)

enter image description here

Peter
  • 11,500
  • 5
  • 21
  • 31
nesha
  • 5
  • 4
  • 2
    Please provide more context and framing for the question. The documentation for `openxlsx::worksheetOrder` states: "This function does not reorder the worksheets within the workbook object, it simply shuffles the order when writing to file." So it would be helpful to see the code that builds the workbook as well as the code that saves the workbook with the 32 sheets. – Peter Dec 12 '21 at 08:43

1 Answers1

1

Here's a solution using readxl package but first let's create a test dataset with 4 sheets.

data <- list(head(iris), head(mtcars), head(cars), head(airquality))
writexl::write_xlsx(data, 'test.xlsx')

Now, in your working directory you should have an excel file with 4 sheets in it. 1st sheet has iris dataset, 2nd has mtcars, 3rd one has cars and 4th airquality.

Solution -

library(readxl)

#Order in which you want the sheets to be in
req_order <- c(4, 2, 3, 1)
#Read the sheet names
sheets <- excel_sheets('test.xlsx')
#Read the data in a list
read_data <- lapply(sheets, function(x) read_xlsx('test.xlsx', x))
#Rearrange the data as per `req_order`
read_data <- read_data[req_order]
#Write the data to a new file with the new order. 
writexl::write_xlsx(read_data, 'new_test.xlsx')

You can now find a new excel workbook with 4 sheets where the order has changed to airquality, mtcars, cars and iris as mentioned in req_order.

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213