将Excel范围导出为图像（VB.NET） [英] Exporting Excel Range as image (VB.NET)

问题描述

我有一个工作excel vba宏，它做了我想从这里，我试图。

I have a working excel vba macro that does what I want from here and I am trying to convert it to VB.NET.

来自VBA的代码：

Sub bah()
''' Set Range you want to export to file
    Dim rgExp As Range: Set rgExp = Range("B2:C6")
    ''' Copy range as picture onto Clipboard
    rgExp.CopyPicture Appearance:=xlScreen, format:=xlBitmap
    ''' Create an empty chart with exact size of range copied
    With ActiveSheet.ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, _
    Width:=rgExp.Width, Height:=rgExp.Height)
    .Name = "ChartVolumeMetricsDevEXPORT"
    .Activate
    End With
    ''' Paste into chart area, export to file, delete chart.
    ActiveChart.Paste
   ActiveSheet.ChartObjects("ChartVolumeMetricsDevEXPORT").Chart.Export "C:\Users\ajohnson\Desktop\workdamnit.jpg"
ActiveSheet.ChartObjects("ChartVolumeMetricsDevEXPORT").Delete
End Sub

这是做一个excel范围，然后

What this does is take an excel range and then put it into a chart that is a copy of the range and save it as a JPG.

这是我最近尝试做的一个VB.NET：

Here is my most recent attempt at making it VB.NET:

Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlRange As Excel.Range
xlWorkBook = xlApp.Workbooks.Open("C:\test.xlsx")
xlWorkSheet = xlWorkBook.Sheets("Sheet1")


 xlRange = xlWorkSheet.Range("B2:C6")
        With xlWorkSheet.ChartObjects.add(xlRange.Left, xlRange.Top, xlRange.Width, xlRange.Height)
            .name = "Chart1"
            .activate()
        End With
xlWorkSheet.ChartObjects("Chart1").Paste()
        xlWorkSheet.ChartObjects("Chart1").chart.export(Filename:="C:\Users\ajohnson\Desktop\saveit.jpg")
        xlWorkSheet.ChartObjects("Chart1").delete()

我遇到麻烦转换 ActiveChart.Paste 方法。我不能让它在VB.NET中工作。它抛出一个错误或者它只是留下一个空盒子，当我在VB.NET（如果我添加 .chart 在它运行的粘贴，但不粘贴任何值），但在VBA中它填充感兴趣的值。我已经尝试创建一个图表对象，但是似乎没有工作。

I am running into trouble converting the ActiveChart.Paste method. I can't get it to work in VB.NET. It either throws an error or It just leaves an empty box when I do it in VB.NET (if I add .chart before the paste it runs, but doesn't paste any values), but in VBA it fills in the values of interest. I have tried creating a chart object, but that did not seem to work either.

我觉得我很接近把它整理出来，但我不能得到它。我想我可以把它作为一个VBA宏，并从VB.NET调用它，但这似乎在某种程度上荒谬。任何帮助将非常感谢。我也对不同的方法开放，它只是这是我遇到的事情在VBA中运作良好，所以我认为这是一个很好的起点。

I feel like I am close to having it sorted out, but I can't quite get it. I suppose I could leave it as a VBA macro and call it from VB.NET, but that seems absurd on some level. Any help would be greatly appreciated. I am also open to different approaches, its just this is the thing I came across that worked well in VBA, so I figured it was a good starting point.

感谢总是！

推荐答案

我只是打了一个更难一点的MSDN到那里。原来，你必须把chartobject放在图表中，我工作的代码看起来像：

I just had to hit the MSDN up a little harder to get there. Turns out you have to put the chartobject inside a chart, the code I got working looks like:

  xlRange = xlWorkSheet.Range("B2:C6")
    xlRange.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlPicture)
    Dim oChtobj As Excel.ChartObject = xlWorkSheet.ChartObjects.add(xlRange.Left, xlRange.Top, xlRange.Width, xlRange.Height)
    Dim oCht As Excel.Chart
    oCht = oChtobj.Chart
    oCht.Paste()
    oCht.Export(Filename:="C:\saveit.jpg")
    oChtobj.Delete()

删除问题，因为它已经解决了我这么快（这忽略了我在之前我把它放在这里的时间一点点），但是当我搜索像我的一个问题，它来到这个页面，所以也许这将帮助某人在将来。如果你正在寻找一个范围从excel到某种原因（可能附加到一个Outlook电子邮件的正文，因为这是我正在做的），这应该为你工作。

I was going to delete the question, since it got solved by me so quickly (this ignores the decent bit of time I spent before I posted it here), but when I search for a problem like mine it comes to this page, so maybe this will help someone in the future. If you are looking to copy a range from excel to a jpg for some reason (perhaps attaching it to the body of an outlook email, because that is what I am doing), this should work for you.

这篇关于将Excel范围导出为图像（VB.NET）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！