I am wondering is it possible to read an excel file that is currently open, and capture things you manually test into R?
我想知道是否可以读取当前打开的excel文件,并将手动测试的内容捕获到R中?
I have an excel file opened (in Windows). In my excel, I have connected to a SSAS cube. And I do some manipulations using PivotTable Fields (like changing columns, rows, and filters) to understand the data. I would like to import some of the results I see in excel into R to create a report. (I mean without manually copy/paste the results into R or saving excel sheets to read them later). Is this a possible thing to do in R?
我打开了一个excel文件(在Windows中)。在我的Excel中,我已连接到SSAS多维数据集。我使用数据透视表字段(如更改列,行和过滤器)进行一些操作来理解数据。我想将我在excel中看到的一些结果导入到R中以创建报告。 (我的意思是没有手动将结果复制/粘贴到R或保存excel表以便稍后阅读)。这是R中可能做的事吗?
UPDATE
I was able to find an answer. Thanks to awesome package created by Andri Signorell.
我找到了答案。感谢Andri Signorell创造的精美包装。
library(DescTools)
fxls<-GetCurrXL()
tttt<-XLGetRange(header=TRUE)
3 个解决方案
#1
1
Copy the values you are interested in (in a single spread sheet at a time) to clipboard.
将您感兴趣的值(一次只能在一张电子表格中)复制到剪贴板。
Then
dat = read.table('clipboard', header = TRUE, sep = "\t")
#2
1
You can save the final excel spreadsheet as a csv file (comma separated). Then use read.csv("filename") in R and go from there. Alternatively, you can use read.table("filename",sep=",") which is the more general version of read.csv(). For tab separated files, use sep="\t" and so forth.
您可以将最终的Excel电子表格保存为csv文件(以逗号分隔)。然后在R中使用read.csv(“filename”)并从那里开始。或者,您可以使用read.table(“filename”,sep =“,”)这是read.csv()的更通用版本。对于制表符分隔文件,请使用sep =“\ t”等。
I will assume this blog post will be useful: http://www.r-bloggers.com/a-million-ways-to-connect-r-and-excel/
我将假设这篇博文有用:http://www.r-bloggers.com/a-million-ways-to-connect-r-and-excel/
In the R console, you can type
在R控制台中,您可以键入
?read.table
for more information on the arguments and uses of this function. You can just repeat the same call in R after Excel sheet changes have been saved.
有关此函数的参数和用法的更多信息。保存Excel工作表更改后,您可以在R中重复相同的调用。
#3
1
I was able to find an answer. Thanks to awesome package created by Andri Signorell.
我找到了答案。感谢Andri Signorell创造的精美包装。
library(DescTools)
fxls<-GetCurrXL()
tttt<-XLGetRange(header=TRUE)
#1
1
Copy the values you are interested in (in a single spread sheet at a time) to clipboard.
将您感兴趣的值(一次只能在一张电子表格中)复制到剪贴板。
Then
dat = read.table('clipboard', header = TRUE, sep = "\t")
#2
1
You can save the final excel spreadsheet as a csv file (comma separated). Then use read.csv("filename") in R and go from there. Alternatively, you can use read.table("filename",sep=",") which is the more general version of read.csv(). For tab separated files, use sep="\t" and so forth.
您可以将最终的Excel电子表格保存为csv文件(以逗号分隔)。然后在R中使用read.csv(“filename”)并从那里开始。或者,您可以使用read.table(“filename”,sep =“,”)这是read.csv()的更通用版本。对于制表符分隔文件,请使用sep =“\ t”等。
I will assume this blog post will be useful: http://www.r-bloggers.com/a-million-ways-to-connect-r-and-excel/
我将假设这篇博文有用:http://www.r-bloggers.com/a-million-ways-to-connect-r-and-excel/
In the R console, you can type
在R控制台中,您可以键入
?read.table
for more information on the arguments and uses of this function. You can just repeat the same call in R after Excel sheet changes have been saved.
有关此函数的参数和用法的更多信息。保存Excel工作表更改后,您可以在R中重复相同的调用。
#3
1
I was able to find an answer. Thanks to awesome package created by Andri Signorell.
我找到了答案。感谢Andri Signorell创造的精美包装。
library(DescTools)
fxls<-GetCurrXL()
tttt<-XLGetRange(header=TRUE)