XLwriteOpen: Open a spreadsheet document, while deleting the previous...
In table1xls: Exports Reproducible Summary Tables to Multi-Tab Spreadsheet Files (.xls or .xlsx)

Description Usage Arguments Details Value Note Author(s) See Also Examples

Open a spreadsheet file (.xls or .xlsx), while deleting the previous copy if it exists.

1	XLwriteOpen(path)

path

character: the spreadsheet's full filename, including the extension. Only .xls, .xlsx extensions are allowed.

The XLConnect function loadWorkbook can open existing spreadsheets or create new ones if they don't exist. However, it cannot delete the previous copy when opening the new one – which is the default expected behavior of software such as R. As a result, analysts might inadvertently mix old and new versions of data and analyses, in the same spreadsheet.

This short utility mitigates the risk, by calling unlink first to make sure existing copies are deleted before the new spreadsheet file is opened.

an XLConnect workbook object.

Even though the workbook object is created, and is linked to a specific file name, it will only be saved to disk after saveWorkbook is called. See example. From table1xls version 0.3.0 on, all of the package's spreadsheet-export functions save the file by default. The example also illustrates some of the peculiarities of working with XLConnect, many of which are taken care of when using table1xls functions.

Assaf P. Oron <assaf.oron.at.seattlechildrens.org>

loadWorkbook, saveWorkbook

### Run this example in successive copy-paste batches

## Batch 1: be careful to copy and paste only the first 3 lines 
# *without* the white-space below them.
cat("R will now open a new .xls worksheet for you!\n")
cat("Please enter path and filename, without extension:\n")
filestring<-readLines(n=1)


# R is waiting for you... enter the filename ... then proceed to next batch.

## Batch 2
newPath<-paste(filestring,'xls',sep='.')
blankbook<-XLwriteOpen(newPath)

# If you check to see whether the file exists - it's not there.
# The spreadsheet is only in R's memory. The next batch will save it.

## Batch 3
XLConnect::saveWorkbook(blankbook)
cat("Now there should be a blank file called",newPath, "- Check it out!\n")

## Now: writing into the file and resaving
# Make sure you close the file in case you opened it in Excel.
# We'll just write something silly there now:

## Batch 4
# Excel showed 1 blank sheet. But for R, there are 0 sheets until you create some.
XLConnect::createSheet(blankbook,"one") 
XLConnect::writeWorksheet(blankbook,"Something Sillee!!!",sheet='one') 
XLConnect::saveWorkbook(blankbook)

# Now it's not blank anymore - Check it out... 
# You will notice XLConnect has interpreted the string 
# as a data frame. Data transfer can only occur in the form of
# data frames (except some graphics).
# After closing the file run the last batch, which finally demonstrates 
# what XLwriteOpen itself does (open with overwrite).
# Don't forget to close the .xls file first!


## Batch 5

blankbook2<-XLwriteOpen(newPath)
XLConnect::saveWorkbook(blankbook2)

#### Now the file is blank again - Check it out!
#### All done!