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,

Using ,

write_xlsx(df, "../mydata.xlsx")

gives the following output in excel,

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?

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)`. — Mikko Marttila, Aug 23 '18 at 12:06

score 7 · Answer 1 · answered Dec 08 '20 at 04:06

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

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. — Tim Bainbridge, Dec 08 '20 at 04:17
This should be the accepted answer, as this does not change the package. — NurShomik, May 25 '22 at 18:33

Tim Bainbridge · Answer 2 · 2022-05-14T08:48:16.470

4

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)

edited May 14 '22 at 08:48

answered Dec 08 '20 at 04:31

Tim Bainbridge

177
2
9

1

This option is powerful (freeze panes, header styles, etc.), easy, and doesn't require java, so may be the best solution in some cases. – Tim Bainbridge Dec 08 '20 at 04:34

Mikołaj Gronowski · Accepted Answer · 2018-08-23T12:10:23.757

1

Use below function with argument row.names = TRUE

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

edited Aug 23 '18 at 12:10

answered Aug 23 '18 at 11:47

Mikołaj Gronowski

36
3

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’: ` – Natasha Aug 23 '18 at 11:59
It is in xlsx library. Link [link](https://www.rdocumentation.org/packages/xlsx/versions/0.6.1/topics/write.xlsx) – Mikołaj Gronowski Aug 23 '18 at 12:05
My mistake you could also use normal write.xlsx with argument row.names= TRUE – Mikołaj Gronowski Aug 23 '18 at 12:07
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:` – Natasha Aug 23 '18 at 12:10
@Natasha that error is probably because you are using 64 bit R with 32 bit Java (and **xlsx** depends on Java). Details here: https://stackoverflow.com/questions/37735108/r-error-onload-failed-in-loadnamespace-for-rjava – Mikko Marttila Aug 23 '18 at 12:11
1

Maybe this will help [link](https://stackoverflow.com/questions/23209152/cannot-install-libraryxlsx-in-r-and-look-for-an-alternative) – Mikołaj Gronowski Aug 23 '18 at 12:11
3

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) – Mike Aug 23 '18 at 13:08
@MikkoMarttila Thanks a ton!! That solved my problem – Natasha Aug 23 '18 at 13:21

score 0 · Answer 4 · answered Feb 11 '21 at 16:36

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 )

score 0 · Answer 5 · edited Mar 30 '22 at 21:30

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)

score 0 · Answer 6 · answered Jul 15 '22 at 13:45

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

score -1 · Answer 7 · answered Mar 24 '22 at 09:21

-1

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

answered Mar 24 '22 at 09:21

C Lederman

19
4

I don't this your answer addresses the question about how to include the data.frame's row.names in the excel output.. – xilliam Mar 29 '22 at 08:16

Using write_xlsx in R

7 Answers7