vba 将 Excel 范围导出为图像 (VB.NET)

Exporting Excel Range as image (VB.NET)

提问by asjohnson

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

我有一个可以工作的 excel vba 宏，它可以从这里执行我想要的操作，我正在尝试将其转换为 VB.NET。

The code from VBA:

来自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

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.

它的作用是获取一个 excel 范围，然后将其放入作为该范围副本的图表中，并将其另存为 JPG。

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

这是我最近尝试将其变为 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()

I am running into trouble converting the ActiveChart.Pastemethod. 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 .chartbefore 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.

我在转换ActiveChart.Paste方法时遇到了麻烦。我无法让它在 VB.NET 中工作。它要么引发错误，要么在 VB.NET 中执行此操作时只留下一个空框（如果我.chart在粘贴之前添加它运行，但不粘贴任何值），但在 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.

我觉得我已经接近解决它了，但我不能完全理解它。我想我可以将它保留为 VBA 宏并从 VB.NET 调用它，但这在某种程度上似乎很荒谬。任何帮助将不胜感激。我也对不同的方法持开放态度，这正是我遇到的在 VBA 中运行良好的方法，所以我认为这是一个很好的起点。

Thanks as always!

一如既往的感谢！

回答by asjohnson

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:

我只需要更努力地访问 MSDN 就可以到达那里。原来你必须把图表对象放在图表中，我得到的代码看起来像：

  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()

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 复制到 jpg（也许将其附加到 Outlook 电子邮件的正文中，因为这就是我正在做的），这应该适合您。

回答by Porkbutts

And the C# equivalent requires the call to Activate() or COMException will be thrown

并且 C# 等价物需要调用 Activate() 或 COMException 将被抛出

Excel.Range xlRange = xlWorkSheet.Range("B2:C6");

range.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlPicture);
Excel.ChartObject chartObj = myWorksheet.ChartObjects().Add(range.Left, range.Top, range.Width, range.Height);

chartObj.Activate();  // Don't Forget!

Excel.Chart chart = chartObj.Chart;
chart.Paste();
chart.Export(@"C:\image.png");

chartObj.Delete();