Read named ranges

[eibanez]

Excel allows the option to give a special name to a range of cells. The name manager (See Formulas > Name manager) shows the relationship between these names and their corresponding ranges.

Once #8 is complete, it should be fairly straightforward to convert the name reference to a range.

[g3o2]

I concur, this is a very useful feature:

• keeps your spreadsheets what they are, i.e. spreadsheets ;
• reduces micro-management in case of changes within a range.

XLConnect has a relatively clean implementation of this feature but it depends on Java.

RODBC also works but it relies on the ODBC driver, which can become a problem if you use mismatching versions on each end (32bit Excel vs 64bit R).

[eibanez]

The implementation for xlxs files is done.

Trying to figure out how to read DEFINEDNAME records through libxls. Is there anybody with C experience that can help?

[eibanez]

Related issue in libxls: https://sourceforge.net/p/libxls/feature-requests/8/

[fleimgruber]

@eibanez What is the status on this? How much work does this need for a pull request?

[eibanez]

I haven't looked at this in a while, but I think the infrastructure is mostly ready. Issue #8 needs to be solved first so that this method can be added as an optional selector for cells.

[fleimgruber]

AFAICS, this is already implemented for xlsx files. I do not know how the xls vs. xlsx handling is done feature-wise, but would it be feasible to support this for xlsx files only until #8, i.e. the libxls part is done?

[eibanez]

I implemented the function named_ranges in my branch (https://github.com/eibanez/readxl), which extracts the ranges associated with a name. The only thing that is left is to add the appropriate hook in the reading functions. Feel free to take a stab at it, but it would be a lot easier after #8 is closed.

[fleimgruber]

Thanks for the heads-up. In this case I might have a look out of curiosity, and otherwise wait for #8.

[jennybc]

We're not going to implement this in readxl at this point. The focus is clearly on reading static data.

The most immediate place to seek or build this functionality is in packages that target tricky spreadsheets and sheet logic, e.g. tidyxl and the rsheets packages.

However, it's true that once #8 is done (which will be soon!), reading named ranges is not such a huge leap. I'm closing for now, but we might revisit later.