从打开的Excel工作表中获取数据到R?

时间:2021-09-12 05:06:17

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)