Using write_xlsx in R

Question

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?

Tyrr

I'm assuming you are using writexl::write_xlsx() as that's the only package that I found with a search. Doesn't look like there's an option to include row names in that one. You have two options: include the row names as a column in the data (preferred), or use a different package, e.g. openxlsx::write.xlsx(head(mtcars), "test.xlsx", row.names = TRUE). – Agraphia

Answer 1

Use below function with argument row.names = TRUE

library(xlsx)
write.xlsx(df, "../mydata.xlsx", sheetName = "Sheet1", col.names = TRUE, row.names = TRUE)

Uxorious

Gronowski Could you please which library has to be used for write.xlsx2? I'm trying to install xlsx package via install.packages("xlsx") .I get this error though Error: package or namespace load failed for ‘xlsx’: – Tyrr

It is in xlsx library. Link link – Steele

My mistake you could also use normal write.xlsx with argument row.names= TRUE – Steele

Thanks, I looked at the link and tried installing. But there seems to be a problem in accessing the library(xlsx) Error: .onLoad failed in loadNamespace() for 'rJava', details: – Tyrr

@Tyrr that error is probably because you are using 64 bit R with 32 bit Java (and xlsx depends on Java). Details here: #37735608 – Agraphia

Maybe this will help link – Steele

If you want to use the xlsx package make sure your bits line up with R and Java. I would uninstall Java, and the r packages xlsx, rjava, and xlsxjars. I would then install java and then xlsx (other packages will install when you install xlsx) – Tegucigalpa

@MikkoMarttila Thanks a ton!! That solved my problem – Tyrr

Answer 2

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

Vories

I've added this answer as it uses the package used in the question. It is also an option that doesn't require java. The write.xlsx() function from the openxlsx package is probably the most universally applicable solution though. – Vories

This should be the accepted answer, as this does not change the package. – Misprint

Answer 3

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)

Vories

This option is powerful (freeze panes, header styles, etc.), easy, and doesn't require java, so may be the best solution in some cases. – Vories

Answer 4

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 )

Generalization

Answer 5

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)

Telpher

Answer 6

Dariober has an easy two-line solution here: https://www.biostars.org/p/469010/

It appears write_xlsx doesn't have a parameter row.names. Why don't you just add a column for row names to the dataframe dd before writing it? Like:

dd$row_names <- rownames(dd) 
dd <- dd[, c(ncol(dd), 1:(ncol(dd)-1))] # Make row_names first column

Bluing

Answer 7

You can simply add column names to df, using colnames("name_col1","name_col2", etc) before using write_xlsx.

Telpher

I don't this your answer addresses the question about how to include the data.frame's row.names in the excel output.. – Guenna