2

  1. Create an .xlsx file with three sheets named: "Test 1", "S&P500 TR" and "SP500 TR". Put some random content in each sheet and save it as "Book1.xlsx".

  2. Run:

    > a <- getSheetNames("Book1.xlsx")
> a
[1] "Test 1"    "S&P500 TR" "SP500 TR"

  3. Now try:

    > read.xlsx("Book1.xlsx", a[2])
Error in read.xlsx.default("Book1.xlsx", a[2]) : 
  Cannot find sheet named "S&P500 TR"
M--
  • 25,431
  • 8
  • 61
  • 93
Gio
  • 21
  • 1
  • 1
  • 2
  • I'm guessing you're out of luck. Your choices are likely to be: (1) rename your sheets; (2) hack the internals of `openxlsx::read.xlsx` to fix the problem; (3) look around for another XLS-reader that works the way you want it to (for example, see alternatives [here](https://www.datacamp.com/community/tutorials/r-tutorial-read-excel-into-r) – Ben Bolker Mar 30 '17 at 16:49
  • Please read this [post](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example#answer-5963610) to know how to provide a reproducible code. I would like to know your OS and R version. – M-- Mar 30 '17 at 17:29
  • 1
    Hi @Masoud it happens on both my win 7 and on my Fedora 25 machines. R versions 3.3.2 and 3.3.3 respectively – Gio Mar 30 '17 at 20:04

3 Answers3

1

First check if you actually type the name S&P500 TR instead of using a[2] that would change anything.

Alternatively, you can use readxl package for importing;

  library(readxl)

  X1 <- read_excel("C:/1.xls", sheet = "S&P500 TR")

This is a spreadsheet that I had and it is the result after it is imported;

 head(X1)
  # A tibble: 6 × 4
  #   Year Month       Community                      ` Average Daily`
  #  <dbl> <chr>         <chr>                                   <dbl>
  # 1  2016   Jan       Arlington                              5.35
  # 2  2016   Jan       Ashland                               1.26
  # 3  2016   Jan       Bedford                                2.62
  # 4  2016   Jan       Belmont                               3.03
  # 5  2016   Jan       Boston                                 84.89
  # 6  2016   Jan       Braintree                              8.16
M--
  • 25,431
  • 8
  • 61
  • 93
  • 1
    Thank you @Masoud. I am actually trying to find an alternative to read_excel and XLConnect, hence openxlsx. Also trying with the explicit name makes no difference. – Gio Mar 30 '17 at 19:55
0

I ran into the same problem, but found a workaround. First load in the workbook using read.xlsx(). Then rename the problematic sheet to avoid the ampersand. To fix the code in your example:

wb = read.xlsx("Book1.xlsx")
renameWorksheet(wb, "S&P500 TR", "NEW NAME")
output = read.xlsx(wb, "NEW NAME")

Hope this helps!

Mendy F.
  • 1
0

First load the workbook, then use the which and grepl function to return the sheet number containing the sheet name (which can include the '&' character when done in this way). This seems to work quite well in an application I am currently working on.

An (incomplete) example is given below that should be easily modified to your context. In my case 'i' is a file name (looping over many files). The "toy" code is here:

wb <- loadWorkbook(file = i)
which( grepl("CAPEX & Depreciation", names(wb)) )
Ethan Field
  • 4,646
  • 3
  • 22
  • 43
dcb_1982
  • 1