Working on Excel file from R

There are few packages available in R to let you work with (import/export) Excel files from R. This blog page will focus on 'xlsx' package.


'xlsx' package provides a command to import excel file, but this did not work well in my case. If the excel document is in Excel 97-2003 (.xls) format, the following works quite well.

Note: if ODBC installed in Windows is 32-bit, you need to use 32-bit R to do this.

library(RODBC) 

library(xlsx) 

excel.connect<-odbcConnectExcel("file_name.xls") 

Data<-sqlFetch(excel.connect,"Sheet_Name") 

odbcClose(excel.connect)

For exporting data into excel document, the following works well.

For a simple export:

library(xlsx)

wb<-createWorkbook() 

sheet<-createSheet(wb,sheetName="Sheet_Name") 

addDataFrame(dataframe,sheet,row.names=FALSE)  

saveWorkbook(wb,"file_name")

To create multiple worksheets:

wb<-createWorkbook() 

sheet<-createSheet(wb,sheetName="Sheet_Name_1") 

addDataFrame(dataframe_1,sheet,row.names=FALSE)  

saveWorkbook(wb,"file_name")

sheet<-createSheet(wb,sheetName="Sheet_Name_2") 

addDataFrame(dataframe_2,sheet,row.names=FALSE)  

saveWorkbook(wb,"file_name")

For exporting data with formatting:

wb<-createWorkbook() 

sheet<-createSheet(wb,sheetName="Sheet_Name") 

for(i in c(required row numbers){ 

ROW<-createRow(sheet,rowIndex=i)

#populate selected columns (selected respective to different formatting)

for(j in c(required column numbers)){ 

CELL<-createCell(ROW,colIndex=j)[[1,1]]  

#data format written as per the excel specification (go to cell format and custom format for other examples)

cs<-CellStyle(wb,dataFormat=DataFormat("#,##0;[Red]-#,##0"))+
Font(wb,isBold=FALSE)+
Alignment(h="ALIGN_RIGHT",wrapText=TRUE) 

setCellValue(CELL,dataframe[i,j],showNA=FALSE)  

setCellStyle(CELL,cs)
}

#populate remaining columns (selected respective to different formatting)

for(j in c(required column numbers)){ 

CELL<-createCell(ROW,colIndex=j)[[1,1]]   

cs<-CellStyle(wb,dataFormat=DataFormat("$#,##0.00;[Red]-$#,##0.00"))+
Font(wb,isBold=FALSE)+
Alignment(h="ALIGN_RIGHT",wrapText=TRUE) 

setCellValue(CELL,dataframe[i,j],showNA=FALSE)  

setCellStyle(CELL,cs)
}