'Export data from R to Excel

I am writing codes to export database from R into Excel, I have been trying others codes including:

write.table(ALBERTA1, "D:/ALBERTA1.txt", sep="\t")
write.csv(ALBERTA1,":\ALBERTA1.csv")
your_filename_in_R = read.csv("ALBERTA1.csv")
your_filename_in_R = read.csv("ALBERTA1.csv")
write.csv(df, file = "ALBERTA1.csv")
your_filename_in_R = read.csv("ALBERTA1.csv")
write.csv(ALBERTA1, "ALBERTA1.csv")
write.table(ALBERTA1, 'clipboard', sep='\t')
write.table(ALBERTA1,"ALBERTA1.txt")
write.table(as.matrix(ALBERTA2),"ALBERTA2.txt")
write.table(as.matrix(vecm.pred$fcst$Alberta_Females[,1]), "vecm.pred$fcst$Alberta_Females[,1].txt")
write.table(as.matrix(foo),"foo.txt")
write.xlsx(ALBERTA2, "/ALBERTA2.xlsx")
write.table(ALBERTA1, "D:/ALBERTA1.txt", sep="\t").

Other users of this forum advised me this:

write.csv2(ALBERTA1, "ALBERTA1.csv")
write.table(kt, "D:/kt.txt", sep="\t", row.names=FALSE)

enter image description here

You can see on the pictures the outcome I have got from this codes above. But this numbers can't be used to make any further operations such as addition with other matrices.

Has someone experienced this kind of problems?

r excel r-faq

Solution 1:^[1]

Another option is the openxlsx-package. It doesn't depend on java and can read, edit and write Excel-files. From the description from the package:

openxlsx simplifies the the process of writing and styling Excel xlsx files from R and removes the dependency on Java

Example usage:

library(openxlsx)

# read data from an Excel file or Workbook object into a data.frame
df <- read.xlsx('name-of-your-excel-file.xlsx')

# for writing a data.frame or list of data.frames to an xlsx file
write.xlsx(df, 'name-of-your-excel-file.xlsx')

Besides these two basic functions, the openxlsx-package has a host of other functions for manipulating Excel-files.

For example, with the writeDataTable-function you can create formatted tables in an Excel-file.

Solution 2:^[2]

Recently used xlsx package, works well.

library(xlsx)
write.xlsx(x, file, sheetName="Sheet1")

where x is a data.frame

Solution 3:^[3]

writexl, without Java requirement:

# install.packages("writexl")
library(writexl)
tempfile <- write_xlsx(iris)

Solution 4:^[4]

The WriteXLS function from the WriteXLS package can write data to Excel.

Alternatively, write.xlsx from the xlsx package will also work.

Solution 5:^[5]

One could also use the readODS package. Granted it doesn't produce an .xlsx, but Excel can read Open Document Spreadsheet (ODS) / LibreOffice files too.

require(readODS)

tmp = file.path(tempdir(), 'iris.ods')
write_ods(iris, tmp)

Solution 6:^[6]

Here is a way to write data from a dataframe into an excel file by different IDs and into different tabs (sheets) by another ID associated to the first level id. Imagine you have a dataframe that has email_address as one column for a number of different users, but each email has a number of 'sub-ids' that have all the data.

data <- tibble(id = c(1,2,3,4,5,6,7,8,9), email_address = c(rep('[email protected]',3), rep('[email protected]', 3), rep('[email protected]', 3)))

So ids 1,2,3 would be associated with [email protected]. The following code splits the data by email and then puts 1,2,3 into different tabs. The important thing is to set append = True when writing the .xlsx file.


temp_dir <- tempdir()

for(i in unique(data$email_address)){
    
  data %>% 
    filter(email_address == i) %>% 
    arrange(id) -> subset_data
  
  for(j in unique(subset_data$id)){
    write.xlsx(subset_data %>% filter(id == j), 
      file = str_c(temp_dir,"/your_filename_", str_extract(i, pattern = "\\b[A-Za-z0- 
       9._%+-]+"),'_', Sys.Date(), '.xlsx'), 
      sheetName = as.character(j), 
      append = TRUE)}
 
  }

The regex gets the name from the email address and puts it into the file-name.

Hope somebody finds this useful. I'm sure there's more elegant ways of doing this but it works.

Btw, here is a way to then send these individual files to the various email addresses in the data.frame. Code goes into second loop [j]

  send.mail(from = "[email protected]",
            to = i,
          subject = paste("Your report for", str_extract(i, pattern = "\\b[A-Za-z0-9._%+-]+"), 'on', Sys.Date()),
          body = "Your email body",
          authenticate = TRUE,
          smtp = list(host.name = "XXX", port = XXX,
                      user.name = Sys.getenv("XXX"), passwd = Sys.getenv("XXX")),
          attach.files = str_c(temp_dir, "/your_filename_", str_extract(i, pattern = "\\b[A-Za-z0-9._%+-]+"),'_', Sys.Date(), '.xlsx'))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Jaap
Solution 2
Solution 3	lukeA
Solution 4	zx8754
Solution 5	andschar
Solution 6

'Export data from R to Excel

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]