python - 如何使用 Python 或 R 将 Excel 中带有替代文本的图形导出为 PDF？

Question

我使用 VBA 在 Excel 中生成了大约 500 个图表，我需要将它们导出为 pdf。这些图表有替代文本，使盲人可以访问它们。当我使用 VBA (ExportAsFixedFormat) 生成 pdf 时，pdf 中将丢失替代文本。 python 或 R 中是否有代码将图形从 excel 转换为 pdf 并保留替代文本？

如果我手动将图表保存为 pdf，替代文本将与图表一起保存在 pdf 文件中。但是，由于我的图表太多，所以能够自动执行此操作会很好。

 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFileName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

VBA 中的上述代码有助于创建 pdf，但不保留替代文本。

Answer 1

以下代码为 ThisWorkbook 中的每个 Sheet(不包括任何 Worksheet)生成一个 pdf 文件:

Sub Charts_Export()
Const kPath As String = "D:\@D_Trash\SO Questions\Output\#Name.pdf"    'Update as required
Dim oSht As Object, sPath As String
    With ThisWorkbook
        For Each oSht In .Sheets
            With oSht
                If oSht.Type <> xlWorksheet Then
                    sPath = Replace(kPath, "#Name", .Name)    'Update as required
                    .ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        Filename:=sPath, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False
    End If: End With: Next: End With

    End Sub

打开 pdf 文件后，同时按 Shift + Ctrl + Y 激活 Read Out Loud pdf 中的选项。然后同时按 Shift + Ctrl + V 以阅读 AlternativeText。

之前的代码使用了 OP 发布的同一部分，将图表导出为 pdf 文件，每个文件中都包含 Alternative text。

这似乎表明问题可能是由于用于将 AlternativeText 添加到 Chart 的方法引起的。当 AlternativeText 作为 Sheet 移动后，我找不到将它添加到 Chart 的方法，所以 AlternativeText 必须在将 Chart 移动到 Sheet 之前添加，当 Chart 仍然是一个对象(Shape) 在工作表中。

使用此方法将 AlternativeText 添加到每个 Chart，然后再将其移动到 Sheet`。

Private Sub Charts_Add_AlternativeText()
Const kAltTxt As String = "This is a test of the Alt Text in graph [#Name]"    'Update as required
Dim ws As Worksheet
Dim co As ChartObject

    Set ws = ThisWorkbook.Worksheets("DATA")    'Update as required
    For Each co In ws.ChartObjects
        co.ShapeRange.AlternativeText = Replace(kAltTxt, "#Name", co.Name)    'Update as required
    Next

    End Sub

或使用此方法将AlternativeText 添加到每个Chart 工作表。

Private Sub Charts_Add_AlternativeText()
Const kWsName As String = "!Temp"
Const kAltTxt As String = "This is a test of the Alt Text in graph [#Name]"     'Update as required
Dim wb As Workbook, ws As Worksheet
Dim oSht As Object, sp As Shape
Dim sChName As String, bIdx As Byte

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Application.Calculation = xlCalculationManual
    End With

    Set wb = ThisWorkbook
    With wb

        Rem Add Temp Worksheet
        On Error Resume Next
        .Worksheets(kWsName).Delete
        On Error GoTo 0
        Set ws = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = kWsName

        Rem Work with Chart Sheets
        For Each oSht In .Sheets
            With oSht
                If oSht.Type <> xlWorksheet Then

                    Rem Move Chart to Temp Worksheet
                    bIdx = .Index
                    sChName = .Name
                    .Location Where:=xlLocationAsObject, Name:=kWsName

                    Set sp = ws.Shapes(1)
                    With sp

                        Rem Add AlternativeText to Shape (Chart)
                        .AlternativeText = Replace(kAltTxt, "#Name", sChName)    'Update as required

                        Rem Move Chart to Chart Sheet
                        .Chart.Location Where:=xlLocationAsNewSheet, Name:=sChName
                        wb.Sheets(sChName).Move Before:=wb.Sheets(bIdx)

    End With: End If: End With: Next: End With

    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Application.Calculation = xlCalculationAutomatic
    End With

End Sub