1 Read or Import Data from Excel Files in R

Download testdata.xlsx file.

Download testdata.xls file.

The .xlsx or .xls file formats are the standard spreadsheet files for Microsoft Excel in Windows and macOS.

To read or import Excel (XLSX or .xlsx) files in R, first install the "readxl" package:

install.packages("readxl")

Then load the package with the following:

library("readxl")

After the installation and loading of the "readxl" package, you can read the XLSX or .xlsx file.

If you set or have the working directory as the folder containing the file, use the line of code below:

read_excel("testdata.xlsx", sheet = 1)

Or specify the full path of where the file is located:

read_excel("C:/Users/Public/Statscodes/Rdata/read-files/testdata.xlsx", sheet = 1)

XLSX File Read in R

Note that the "sheet" argument is set to 1, which is also the default. This can be changed to other sheet numbers such as 2, 3 and so on or even sheet names, such as "Results" with the quotation marks.

To remove the initial messages, you can turn the XLSX input to a dataframe as below:

data.frame(read_excel("testdata.xlsx", sheet = 1))

Image 2 of XLSX File Read in R

To read or import XLS or .xls files in R you can use the following line of code:

read_excel("testdata.xls", sheet = 1)

Or specify the full path:

read_excel("C:/Users/Public/Statscodes/Rdata/read-files/testdata.xls", sheet = 1)

XLS File Read in R

2 Write or Export Data to Excel Files in R

The dataframe named dtfrm will be used:

dtfrm = data.frame(Group = c("A", "B", "B", "C", "D"), 
                   ID = c("A02", "B12", "B15", "C04", "D07"), 
                   Score = c(9, 8, 8, 10, 7), 
                   Position = c(2, 3, 3, 1, 5))
dtfrm

  Group  ID Score Position
1     A A02     9        2
2     B B12     8        3
3     B B15     8        3
4     C C04    10        1
5     D D07     7        5

To write or export data to Excel or .xlsx files in R, first install the "writexl" package:

install.packages("writexl")

Then load the package with the following:

library("writexl")

2.1 Writing or Exporting Data to Single Sheet Excel Files in R

After the installation and loading of the "writexl" package, you can save the XLSX or .xlsx file to the working directory with the following line of code:

write_xlsx(dtfrm, "outdata.xlsx")

Or specify the full file path of where you want to save it:

write_xlsx(dtfrm, "C:/Users/Public/Statscodes/Rdata/write-files/outdata.xlsx")

The output of the single sheet should look like this in Excel:

Excel or XLSX Output Written in R

2.2 Writing or Exporting Data to Multiple Sheets Excel Files in R

To write to multiple sheets in Excel use:

write_xlsx(list(DataSheet = dtfrm, Sheet2 = BOD), "outdata2.xlsx")

The output of the multiple sheets should look like this in Excel:

Excel or XLSX Multiple Sheets Output Written in R