5.2 Basic functions: write & read an Excel sheet

5.2.1 Writing Excel files

# Load Excel workbook - either .xls or .xlsx. Create if not existing
wb <- loadWorkbook("xlconnect-demo/XLConnectDemo.xlsx", create = TRUE)

# Create a Sheet within an Excel workbook
createSheet(wb, name = "CHF")

# Write into a sheet
# Write Swiss Francs data frame into CHF sheet
writeWorksheet(wb, swissfranc, sheet = "CHF", startRow = 2, startCol = 2)

# Save workbook - this actually writes the file
saveWorkbook(wb)

The steps above can be executed in a single call using the writeWorksheetToFile() function.

# Write and save a data frame into a specific sheet of a workbook
writeWorksheetToFile("xlconnect-demo/XLConnectDemo.xlsx", 
                     data = swissfranc, sheet = "OneCall", 
                     startRow = 2, startCol = 2)

The data frame can also be written in a named region using the single call writeNamedRegionToFile() function. This will create the sheet (if necessary) and named region name based on the formula argument and call writeNamedRegion instead of writeWorksheet.

# Write and save a data frame into a specific named region of a workbook
writeNamedRegionToFile("xlconnect-demo/XLConnectDemo.xlsx", 
                       data = swissfranc, name = "SwissFrancName", 
                       formula = "NamedRegionEx!$B$2")

5.2.2 Reading from Excel files

# Read data from a specific sheet of a workbook
dataFromExcel <- readWorksheet(wb, sheet = "CHF")

# Read data from a specific location in a sheet of a workbook
dataFromExcel <- readWorksheet(wb, 
                               sheet = "CHF", 
                               startRow = 2, endRow = 10,
                               startCol = 2, endCol = 5, 
                               header = TRUE)

The steps above can be executed in a single call using the readWorksheetFromFile() function.

# Read data from a specific location in a sheet of a workbook
dataFromExcel <- readWorksheetFromFile("xlconnect-demo/XLConnectDemo.xlsx", 
                                       sheet = "OneCall", 
                                       startRow = 2, endRow = 10, 
                                       startCol = 2, endCol = 5, 
                                       header = TRUE)

The data can also be read from an existing named region using the single call readNamedRegionFromFile() function.

# Read data from a specific named region of a workbook
dataFromExcel <- readNamedRegionFromFile("xlconnect-demo/XLConnectDemo.xlsx", 
                                         name = "SwissFrancName", 
                                         header = TRUE)

It can be useful to retrieve all the sheets of a workbook or all existing named regions.

# Get all sheet names of a workbook
allSheets <- getSheets(wb)

# Get all named regions of a workbook
allnr <- getDefinedNames(wb)