将 Excel 图表导出到 SVG 创建一个空文件 [英] Export Excel chart to SVG creates an empty file

问题描述

我正在尝试使用 VBA 以 SVG 格式导出 Excel 图表.

I am trying to export an Excel chart in SVG format using VBA.

    Set objChrt = ActiveChart.Parent
    objChrt.Activate
    Set curChart = objChrt.Chart
    
    curChart.Export fileName:=fileName, FilterName:="SVG"

如果我替换SVG"通过PNG"，导出完全按预期工作并生成有效的 PNG 文件.然而，SVG"结果是一个空文件.(手动，Excel 365 中可以选择另存为 SVG，因此存在导出过滤器).

If I replace "SVG" by "PNG", the export works exactly as intended and produces a valid PNG file. However, "SVG" results in an empty file. (Manually, there is an option to save as SVG inside Excel 365, so the export filter exists).

根据文档，Filtername 是出现在注册表中的图形过滤器的语言独立名称."，但我在注册表中找不到类似的东西，无论哪种方式，都很难想象 SVG 过滤器名称被命名为SVG"以外的任何名称.

According to the documentation, Filtername is "The language-independent name of the graphic filter as it appears in the registry.", but I couldn't find anything like that in the registry, and either way, it's hard to imagine the SVG filtername being named anything other than "SVG".

有没有办法使用 VBA 以 SVG 格式导出图表?

Is there a way to export a Chart in SVG format using VBA?

注意:还有一个关于 Chart.export 生成空文件的问题，修复方法是在导出之前使用 ChartObject.Activate.这个问题是不同的，因为代码与PNG"一起正常工作.但因SVG"而失败(所以这不是与激活或可见性相关的问题).推荐的修复也不起作用.

Note: There is another question about Chart.export producing an empty file, and the fix was to use ChartObject.Activate before the export. This question is different because the code works correctly with "PNG" but fails with "SVG" (so it's not an issue related to activation or visibility). Also the recommended fix does not work.

推荐答案

当您将图表复制到剪贴板时，Excel 会添加许多不同的剪贴板格式.自 2011 版以来，这现在包括image/svg+xml".

When you copy a chart to the clipboard, Excel adds lots of different clipboard formats. Since version 2011, this now includes "image/svg+xml".

所以我们要做的就是在剪贴板上找到该格式并将其保存到文件中.结果证明这很烦人.

So all we have to do is find that format on the clipboard and save it to a file. Which turns out to be fairly annoying.

Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
Private Declare PtrSafe Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
Private Declare PtrSafe Function GetClipboardFormatName Lib "user32" _
    Alias "GetClipboardFormatNameW" _
    (ByVal wFormat As Long, _
    ByVal lpString As LongPtr, _
    ByVal nMaxCount As Integer) As Integer
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long

Private Declare PtrSafe Function GlobalUnlock Lib "Kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "Kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalSize Lib "Kernel32" (ByVal hMem As LongPtr) As LongPtr

Private Declare PtrSafe Function CreateFile Lib "Kernel32" _
    Alias "CreateFileA" (ByVal lpFileName As String, _
    ByVal dwDesiredAccess As Long, _
    ByVal dwShareMode As Long, _
    ByVal lpSecurityAttributes As LongPtr, _
    ByVal dwCreationDisposition As Long, _
    ByVal dwFlagsAndAttributes As Long, _
    ByVal hTemplateFile As LongPtr) As LongPtr

Private Declare PtrSafe Function WriteFile Lib "Kernel32" _
    (ByVal hFile As LongPtr, _
    ByVal lpBuffer As LongPtr, _
    ByVal nNumberOfBytesToWrite As Long, _
    ByRef lpNumberOfBytesWritten As Long, _
    ByVal lpOverlapped As LongPtr) As Long

Private Declare PtrSafe Function CloseHandle Lib "Kernel32" (ByVal hObject As LongPtr) As Long


Sub SaveClipboard(formatName As String, filename As String)
    Dim fmtName As String
    Dim fmt As Long
    Dim length As Long
    Dim wrote As Long
    Dim data As LongPtr
    Dim fileHandle As LongPtr
    Dim content As LongPtr
    Dim ret As Long
    
    If OpenClipboard(ActiveWindow.hwnd) = 0 Then
        Exit Sub
    End If
    
    fmt = 0
    Do
        fmt = EnumClipboardFormats(fmt)
        If fmt = 0 Then Exit Do
        
        fmtName = String$(255, vbNullChar)
        length = GetClipboardFormatName(fmt, StrPtr(fmtName), 255)
        If length <> 0 And Left(fmtName, length) = formatName Then
            data = GetClipboardData(fmt)
            
            length = GlobalSize(data)
            content = GlobalLock(data)

            ' use win32 api file handling to avoid copying buffers
            fileHandle = CreateFile(filename, &H120089 Or &H120116, 0, 0, 2, 0, 0)
            ret = WriteFile(fileHandle, content, length, wrote, 0)
            CloseHandle fileHandle
            
            GlobalUnlock data
            Exit Do
        End If
    Loop

    CloseClipboard
    
    If fmt = 0 Then
        MsgBox "Did not find clipboard format " & formatName
        Exit Sub
    End If

End Sub

然后只需复制图表并保存svg；

Then just copy the chart and save the svg;

shape.Copy
SaveClipboard "image/svg+xml", "C:	empoutput.svg"

这篇关于将 Excel 图表导出到 SVG 创建一个空文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！