python - 如何从 Excel 2007 数据透视表检索 SQL 代码

Question

我有一个数据透视表，我想通过 python 以编程方式重新创建它。有没有办法检索数据透视表的当前 sql 代码以便我在 python 中使用它？

Answer 1

这是我最近写的。它可能对你有用。它尝试将事件工作簿的数据透视缓存和/或查询表数据复制到剪贴板。它被 On Error Resume Next 包围，因此如果它没有找到特定的数据，它会继续运行:

Sub Copy_Connection_Info_To_Clipboard()

Dim ptCache As Excel.PivotCache
Dim qtQueryTable As Excel.QueryTable
Dim strPtCacheInfo As String
Dim strQueryTableInfo As String
Dim ws As Excel.Worksheet
Dim strConnectionInfo As String
Dim doConnectionInfo As DataObject

On Error Resume Next
For Each ptCache In ActiveWorkbook.PivotCaches
    With ptCache
        strPtCacheInfo = _
        strPtCacheInfo _
        & "PivotCache #" & "Index: " & .Index & vbCrLf & vbCrLf _
                         & "SourceDataFile: " & .SourceDataFile & vbCrLf & vbCrLf _
                         & "CommandText: " & .CommandText & vbCrLf & vbCrLf _
                         & "SourceConnectionFile: " & .SourceConnectionFile & vbCrLf & vbCrLf _
                         & "Connection: " & .Connection & vbCrLf & vbCrLf
    End With
Next ptCache
If strPtCacheInfo <> "" Then
    strPtCacheInfo = "PivotCache Info" & vbCrLf & vbCrLf & strPtCacheInfo
End If

For Each ws In ActiveWorkbook.Worksheets
    If ws.QueryTables.Count > 0 Then
        strQueryTableInfo = "Worksheet: " & ws.Name & vbCrLf
        For Each qtQueryTable In ActiveSheet.QueryTables
            With qtQueryTable
                strQueryTableInfo = _
                strQueryTableInfo _
                & "QueryTable Name: " & .Name & vbCrLf & vbCrLf _
                & .SourceDataFile & vbCrLf & vbCrLf _
                & .CommandText & vbCrLf & vbCrLf _
                & .SourceConnectionFile & vbCrLf & vbCrLf _
                & .Connection & vbCrLf & vbCrLf
            End With
        Next qtQueryTable
    End If
Next ws
If strQueryTableInfo <> "" Then
    strQueryTableInfo = "Query Table Info" & vbCrLf & strQueryTableInfo
End If

strConnectionInfo = strPtCacheInfo & strQueryTableInfo
If strConnectionInfo <> "" Then
    Set doConnectionInfo = New DataObject
    doConnectionInfo.SetText strConnectionInfo
    doConnectionInfo.PutInClipboard
End If

End Sub