Installation<\/h3>\nSoftware Requirements<\/h4>\n\n- R, version 2.10.0 or higher<\/li>\n
- Java Runtime Environment (JRE), version 6.0 or higher<\/li>\n<\/ul>\n
if you don’t have JRE on your computer, you have to install it :
\non linux \/ubuntu use this command : apt-get install openjdk-6-jdk<\/p>\n
Package Installation<\/h4>\n
XLConnect can be easily installed by using the install.packages() command in your R session:<\/p>\n
Code R :<\/p>\n
\ninstall.packages<\/a>(\"XLConnect\")<\/pre>\n<\/div>\nWriting Excel files<\/h3>\nBasic functions<\/h4>\n
Code R :<\/p>\n
\n\u00a0\n#loading the package\nrequire<\/a>(XLConnect)\n\n#creating an Excel workbook. Both .xls and .xlsx file formats can be used.\nwb <- loadWorkbook(\"XLConnectExample1.xlsx\", create = TRUE)\n\n#creating sheets within an Excel workbook\ncreateSheet(wb, name = \"chickSheet\")\n\n#writing into sheets within an Excel workbook : \n#writing ChickWeight data frame into chickSheet\nwriteWorksheet(wb, ChickWeight<\/a>, sheet = \"chickSheet\", startRow = 3, startCol = 4)\n\n#saving a workbook to an Excel file :\n#saves a workbook to the corresponding Excel file and writes the file to disk.\nsaveWorkbook(wb)<\/pre>\n<\/div>\n![\"\"](\"http:\/\/www.sthda.com\/upload\/write_execel_with_r_1.png\")
<\/p>\n
Writing sheet with one call<\/h4>\n
The four lines of code presented in the previous example can be replaced with a single call of the
\nwriteWorksheetToFile()<\/strong>\u00a0function:<\/p>\nCode R :<\/p>\n
\n\u00a0\nrequire<\/a>(XLConnect)\nwriteWorksheetToFile(\"XLConnectExample2.xlsx\", data<\/a> = ChickWeight<\/a>, \n sheet = \"chickSheet\", startRow = 3, startCol = 4)<\/pre>\n<\/div>\nwriteWorksheetToFile()<\/strong>\u00a0loads the workbook, creates the sheet and finally saves the workbook. When you only need to write one sheet into an Excel file, this is probably the better choice. If you need to write more sheets, however, using the functions presented in the previous example will be more efficient. This is because calling\u00a0writeWorksheetToFile()\u00a0multiple times will open, write and close the Excel file with each call. Using the functions in the first example will, in contrast, allow you to open the workbook, do multiple operations on it and only then close it.<\/p>\nReading from an Excel sheet<\/h3>\n
For this purpose, we will use the file created in the above example. We set the endRow argument to 10, to limit the result. We set the rest of arguments specifying boundaries as 0, so that they are automatically determined.
\nPlease note, that alternatively to setting the sheet argument to the name of the sheet we want to read from, “chickSheet”, we could also specify it with the sheet index, as sheet = 1.<\/p>\n
Basic functions<\/h4>\n
Code R :<\/p>\n
\n\u00a0\n#loading the package\nrequire<\/a>(XLConnect)\n\n#Loading an Excel workbook. Both .xls and .xlsx file formats can be used.\nwb = loadWorkbook(\"XLConnectExample1.xlsx\", create = TRUE)\n\n#reading worksheets of an Excel workbook\ndata<\/a> = readWorksheet(wb, sheet = \"chickSheet\", startRow = 0, endRow = 10,\nstartCol = 0, endCol = 0)\n\n#print data\ndata<\/a><\/pre>\n<\/div>\n weight Time Chick Diet\n1 42 0 1 1\n2 51 2 1 1\n3 59 4 1 1\n4 64 6 1 1\n5 76 8 1 1\n6 93 10 1 1\n7 106 12 1 1<\/pre>\nReading from an Excel sheet with one call<\/h4>\n
Code R :<\/p>\n
\n\u00a0\nrequire<\/a>(XLConnect)\ndata<\/a> = readWorksheetFromFile(\"XLConnectExample1.xlsx\", sheet = \"chickSheet\", \nstartRow = 0, endRow = 10, startCol = 0, endCol = 0)<\/pre>\n<\/div>\nAdds an image to a worksheet using a named region<\/h3>\n
Code R :<\/p>\n
\n\u00a0\n# Load workbook (create if not existing)\nwb = loadWorkbook(\"add_image_to_excel.xls\", create=TRUE)\n\n# Create a sheet named 'boxplot'\ncreateSheet(wb, name = \"boxplot\")\n\n# Create a named region called 'boxplot' referring to the sheet\n# called 'boxplot' \ncreateName(wb, name = \"boxplot\", formula<\/a> = \"boxplot!$B$2\")\n\n# Create R plot to a png device\npng<\/a>(filename = \"boxplot.png\", width = 800, height = 600)\nboxplot<\/a>(count ~ spray, data<\/a> = InsectSprays<\/a>, col<\/a> = \"lightgray\")\ndev.off<\/a>()\n\n# Write image to the named region created above\naddImage(wb, filename = \"boxplot.png\", name=\"boxplot\", originalSize = TRUE)\n\n# Save workbook (this actually writes the file to disk)\nsaveWorkbook(wb)<\/pre>\n<\/div>\nAs a result, we obtain the following graph, written into Excel file
\n![\"\"](\"http:\/\/www.sthda.com\/upload\/add_graph_to_excel.png\")
<\/p>\n
<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"XLConnect is a package that allows for reading, writing and manipulating Microsoft Excel files from within R. It does not require any installation of Microsoft… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-398","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/398","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=398"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/398\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=398"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- \n
- R, version 2.10.0 or higher<\/li>\n
- Java Runtime Environment (JRE), version 6.0 or higher<\/li>\n<\/ul>\n
if you don’t have JRE on your computer, you have to install it :
\non linux \/ubuntu use this command : apt-get install openjdk-6-jdk<\/p>\nPackage Installation<\/h4>\n
XLConnect can be easily installed by using the install.packages() command in your R session:<\/p>\n
Code R :<\/p>\n
\ninstall.packages<\/a>(\"XLConnect\")<\/pre>\n<\/div>\n
Writing Excel files<\/h3>\n
Basic functions<\/h4>\n
Code R :<\/p>\n
\n\u00a0\n#loading the package\nrequire<\/a>(XLConnect)\n\n#creating an Excel workbook. Both .xls and .xlsx file formats can be used.\nwb <- loadWorkbook(\"XLConnectExample1.xlsx\", create = TRUE)\n\n#creating sheets within an Excel workbook\ncreateSheet(wb, name = \"chickSheet\")\n\n#writing into sheets within an Excel workbook : \n#writing ChickWeight data frame into chickSheet\nwriteWorksheet(wb, ChickWeight<\/a>, sheet = \"chickSheet\", startRow = 3, startCol = 4)\n\n#saving a workbook to an Excel file :\n#saves a workbook to the corresponding Excel file and writes the file to disk.\nsaveWorkbook(wb)<\/pre>\n<\/div>\n
<\/p>\nWriting sheet with one call<\/h4>\n
The four lines of code presented in the previous example can be replaced with a single call of the
\nwriteWorksheetToFile()<\/strong>\u00a0function:<\/p>\nCode R :<\/p>\n
\n\u00a0\nrequire<\/a>(XLConnect)\nwriteWorksheetToFile(\"XLConnectExample2.xlsx\", data<\/a> = ChickWeight<\/a>, \n sheet = \"chickSheet\", startRow = 3, startCol = 4)<\/pre>\n<\/div>\n
writeWorksheetToFile()<\/strong>\u00a0loads the workbook, creates the sheet and finally saves the workbook. When you only need to write one sheet into an Excel file, this is probably the better choice. If you need to write more sheets, however, using the functions presented in the previous example will be more efficient. This is because calling\u00a0writeWorksheetToFile()\u00a0multiple times will open, write and close the Excel file with each call. Using the functions in the first example will, in contrast, allow you to open the workbook, do multiple operations on it and only then close it.<\/p>\n
Reading from an Excel sheet<\/h3>\n
For this purpose, we will use the file created in the above example. We set the endRow argument to 10, to limit the result. We set the rest of arguments specifying boundaries as 0, so that they are automatically determined.
\nPlease note, that alternatively to setting the sheet argument to the name of the sheet we want to read from, “chickSheet”, we could also specify it with the sheet index, as sheet = 1.<\/p>\nBasic functions<\/h4>\n
Code R :<\/p>\n
\n\u00a0\n#loading the package\nrequire<\/a>(XLConnect)\n\n#Loading an Excel workbook. Both .xls and .xlsx file formats can be used.\nwb = loadWorkbook(\"XLConnectExample1.xlsx\", create = TRUE)\n\n#reading worksheets of an Excel workbook\ndata<\/a> = readWorksheet(wb, sheet = \"chickSheet\", startRow = 0, endRow = 10,\nstartCol = 0, endCol = 0)\n\n#print data\ndata<\/a><\/pre>\n<\/div>\n
weight Time Chick Diet\n1 42 0 1 1\n2 51 2 1 1\n3 59 4 1 1\n4 64 6 1 1\n5 76 8 1 1\n6 93 10 1 1\n7 106 12 1 1<\/pre>\n
Reading from an Excel sheet with one call<\/h4>\n
Code R :<\/p>\n
\n\u00a0\nrequire<\/a>(XLConnect)\ndata<\/a> = readWorksheetFromFile(\"XLConnectExample1.xlsx\", sheet = \"chickSheet\", \nstartRow = 0, endRow = 10, startCol = 0, endCol = 0)<\/pre>\n<\/div>\n
Adds an image to a worksheet using a named region<\/h3>\n
Code R :<\/p>\n
\n\u00a0\n# Load workbook (create if not existing)\nwb = loadWorkbook(\"add_image_to_excel.xls\", create=TRUE)\n\n# Create a sheet named 'boxplot'\ncreateSheet(wb, name = \"boxplot\")\n\n# Create a named region called 'boxplot' referring to the sheet\n# called 'boxplot' \ncreateName(wb, name = \"boxplot\", formula<\/a> = \"boxplot!$B$2\")\n\n# Create R plot to a png device\npng<\/a>(filename = \"boxplot.png\", width = 800, height = 600)\nboxplot<\/a>(count ~ spray, data<\/a> = InsectSprays<\/a>, col<\/a> = \"lightgray\")\ndev.off<\/a>()\n\n# Write image to the named region created above\naddImage(wb, filename = \"boxplot.png\", name=\"boxplot\", originalSize = TRUE)\n\n# Save workbook (this actually writes the file to disk)\nsaveWorkbook(wb)<\/pre>\n<\/div>\n
As a result, we obtain the following graph, written into Excel file
\n<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"XLConnect is a package that allows for reading, writing and manipulating Microsoft Excel files from within R. It does not require any installation of Microsoft… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-398","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/398","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=398"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/398\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=398"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}