Translate

Friday, 6 February 2015

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














No comments:

Post a Comment