How to get an excel range into a data.frame

Mar 25, 2015 at 10:50 PM
Edited Mar 26, 2015 at 11:43 AM
I am probably giving more info than you need.

The code below creates name, value pairs where the value is an Excel Range.
      Dictionary<string, Name> dictionary = new Dictionary<string, Name>();
        foreach (Name n in wbNames) {
            if (n.Name.IndexOf("!") <= 0) {
                dictionary.Add(n.Name, wbNames.Item(n.Name));
            }
        }
Suppose I want to create a data.frame that is one of the values in the "dictionary".

For example I can write:
var df = dictionary["xyz"];
How can I get the data from df into an R data.frame?

Thanks for your time,
Best,
KW
Developer
Apr 20, 2015 at 3:05 PM
Not knowing what your class Name consists of, the question is unclear.

You will find some examples illustrating how to create/manipulate data frames from .NET in Sample2 at this github site. It illustrate the latest syntactic facilities; building data frame by passing vectors (C# arrays) is also possible, this is just a bit more of a string processing.
Apr 22, 2015 at 5:59 PM
Edited Apr 22, 2015 at 6:00 PM
My apologies for not giving enough information.

The code finds all the ranges given the names of the ranges in a Excel workboo. I assume that the wbNames.Item(n.Name) actually retrieves the data from the workbook and puts the name & value in the dictionary.

What I would like to do is to be able to pass the data from a given range to R code for processing. For example summing up all the values in a range.

Thanks for your help.

Here is more code:

static void Main(string[] args){
        TestRWithExcel trwe = new TestRWithExcel();

        REngine.SetEnvironmentVariables();
        REngine engine = REngine.GetInstance();
        // REngine requires explicit initialization.
        // You can set some parameters.
        engine.Initialize();

        string bookName = args[0];
        string path = Directory.GetCurrentDirectory() + "\\" + args[0];

        Application excelApp = new Application();
        Workbook myWorkbook = excelApp.Workbooks.Open(path);
        Names wbNames = myWorkbook.Names;

        Dictionary<string, Name> dictionary = new Dictionary<string, Name>();
        foreach (Name n in wbNames) {
            if (n.Name.IndexOf("!") <= 0) {
                dictionary.Add(n.Name, wbNames.Item(n.Name));
            }
        }

        var df = dictionary["portf"];