I'm using the write_xlsx command to export data from R to excel,
Here is the data frame that I have,
>df
X1
A 76
B 78
C 10
Using ,
write_xlsx(df, "../mydata.xlsx")
gives the following output in excel,
>df
1 76
2 78
3 10
The column names appear in the xlsx file but the index of each row isn't printed. Is there any way to print the row index in the excel file?
6 Answers
If you want to use the write_xlsx() function (from the writexl package), then you can simply make the row names into the first column of the data frame with the cbind() function:
mtcars1 <- cbind(" "=rownames(mtcars), mtcars)
writexl::write_xlsx(mtcars1, "mtcars1.xlsx")
I've used " "= so the header of column A will appear blank (it will be a space). It can be easily swapped to some other name (e.g., Model=) if desired.
If you have a list of data frames, this can be easily adapted to create a multiple sheet file:
mtcars2 <- list(Sheet1=mtcars[1:5, ], Sheet2=mtcars[6:10, ])
mtcars2_1 <- lapply(mtcars2, function(x) cbind(" "=rownames(x), x))
writexl::write_xlsx(mtcars2_1, "mtcars2.xlsx")
Perhaps the best function for this is write.xlsx() from the openxlsx package. rowNames=TRUE or row.names=TRUE writes row names to column A.
openxlsx::write.xlsx(mtcars, "mtcars.xlsx", rowNames=TRUE)
It works for a list of data frames too. Sheet names match the names of the objects in the list (e.g., first_sheet and second_sheet).
mtcars2 <- list(first_sheet=mtcars[1:5, ], second_sheet=mtcars[6:10, ])
openxlsx::write.xlsx(mtcars2, "mtcars2.xlsx", rowNames=TRUE)
Use below function with argument row.names = TRUE
library(xlsx)
write.xlsx(df, "../mydata.xlsx", sheetName = "Sheet1", col.names = TRUE, row.names = TRUE)
The question is for write_xl function which belongs to writexl library.
This library has no java dependency and works very fast, my favourite library for writing into excel. Also about the memory usage, it's far better than the others.
Imho, the best solution is using the built-in base R codes rather than an excel library.
So, adding a rowindex column to the data file is a better way:
df$rowindex <- as.numeric(rownames(df))
df <- df[,c("rowindex","X1")] #to fix the column order
Additional info about write_xl
To provide a name to the sheet:
write_xlsx( list( name_of_the_sheet = df ), name_of_the_file )
I apologize!
It can be used (not a very elegant solution, but works):
mtcars1<-data.frame(rownames(mtcars),mtcars)
mtcars1 has one additional column (the first one) formed by the row names of mtcars, and the same content of mtcars from the 2nd column to the 5th.
write_xlsx(mtcars1,"mtcars1.xlsx")
> dim(mtcars)
[1] 32 11
> dim(mtcars1)
[1] 32 12
Important first to
install.packages("writexl")
library(writexl)
You can simply add column names to df, using colnames("name_col1","name_col2", etc) before using write_xlsx.
