如何将Excel工作表范围导出到R中的图片 [英] How to export an Excel sheet range to a picture, from within R
问题描述
我们正在尝试在 R
脚本中自动创建一些图片文件。
我们将Excel文件放在我们想要的位置,但现在需要制作这些excel表格的JPG或PNG图片副本,以方便网页发布。
我们一直在使用库(xlsx)
包,以便我们在 R
和Excel,看起来我们应该能够通过类似?的类型发送特定的java命令。jmethods
但是不清楚我们将如何传递我们需要的行数。
在 R
下面是一个示例Excel文件,其范围可以打印
$ b $
文件< - system.file(tests,test_import.xlsx,package =xlsx)
文件
这里是将Excel范围导出到图片文件的Excel宏
Sub Tester()
工作表(deletedFields)。Range(A8:J36)。CopyPicture xlScreen,xlBitmap
Application.DisplayAlerts = False
设置oCht = Charts.Add
带有oCht
.Past e
.Export文件名:=C:\temp\SavedRange.jpg,Filtername:=JPG
.Delete
结束
End Sub
任何帮助自动化这将非常感谢!
考虑让R在您的宏中完全按照VBA的方式进行:将COM接口设置为Excel对象库。您可以使用 RDCOMClient
软件包,在R语法中保留与宏几乎相同的代码。
库(RDCOMClient)
xlApp< - COMCreate(Excel.Application)
xlWbk< - xlApp $ Workbooks()$打开(C:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
xlWbk $ Worksheets(deletedFields)$ Range(A8:J36)$ CopyPicture(xlScreen,xlBitmap)
xlApp [['DisplayAlerts']] - FALSE
oCht< - xlApp [['Charts']] $ Add()
oCht $ Paste()
oCht $ Export(C:\\ Temp\\\SavedRange.jpg,JPG)
oCht $ Delete()
#关闭工作簿和APP
xlWbk $关闭(FALSE)
xlApp $ Quit()
#释放资源
oCht< - xlWbk< - xlApp< - NULL
rm(oCht,xlWbk,xlApp)
gc ()
输出 (随机数据/图表) em>
We are trying to automate the creation of some picture files within an R
Script.
We have the Excel files looking the way that we want them to, but now need to make a JPG or PNG picture-copy of those excel tables for easier web posting.
We've been using the library(xlsx)
package for most of our interactions between R
and Excel, and it looks like we should be able to send specific java commands through something like ?.jmethods
but it's unclear how we would pass as many lines as we need to.
In an R
session, here's a minimal reproducible example...
Here's an example Excel file with a range to print
library(xlsx)
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
file
And here's the Excel macro that exports the Excel range to a picture file
Sub Tester()
Worksheets("deletedFields").Range("A8:J36").CopyPicture xlScreen, xlBitmap
Application.DisplayAlerts = False
Set oCht = Charts.Add
With oCht
.Paste
.Export Filename:="C:\temp\SavedRange.jpg", Filtername:="JPG"
.Delete
End With
End Sub
Any help automating this would be much appreciated!
Consider having R do exactly as VBA does in your macro: making a COM interface to the Excel object library. You can do so with the RDCOMClient
package, retaining nearly same code as macro in the R syntax.
library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("C:\\Path\\To\\test_import.xlsx")
xlScreen = 1
xlBitmap = 2
xlWbk$Worksheets("deletedFields")$Range("A8:J36")$CopyPicture(xlScreen, xlBitmap)
xlApp[['DisplayAlerts']] <- FALSE
oCht <- xlApp[['Charts']]$Add()
oCht$Paste()
oCht$Export("C:\\Temp\\SavedRange.jpg", "JPG")
oCht$Delete()
# CLOSE WORKBOOK AND APP
xlWbk$Close(FALSE)
xlApp$Quit()
# RELEASE RESOURCES
oCht <- xlWbk <- xlApp <- NULL
rm(oCht, xlWbk, xlApp)
gc()
Output (random data/chart)
这篇关于如何将Excel工作表范围导出到R中的图片的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!