如何用PowerShell选择Excel图表并导出图表/数据透视表为图片？

Got it, let's expand your existing PowerShell script to handle exporting every chart (embedded and standalone sheets) and pivot table from your Excel workbook as images. Here's a complete, commented solution:

# Initialize Excel COM object
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$Excel.WindowState = "xlMaximized"

# Define your source file path and output directory
$sourcePath = "C:\abc.xlsx"  # Update this to your actual file path (adjust format if needed, e.g., .xlsb)
$outputDir = "C:\Excel_Exported_Assets"

# Create output directory if it doesn't exist
if (-not (Test-Path $outputDir)) {
    New-Item -ItemType Directory -Path $outputDir | Out-Null
}

# Function to export embedded charts on a worksheet
function Export-EmbeddedCharts {
    param(
        [Parameter(Mandatory=$true)]
        $Worksheet,
        [Parameter(Mandatory=$true)]
        $OutputDirectory
    )
    foreach ($chartObj in $Worksheet.ChartObjects()) {
        $chartFileName = "$($Worksheet.Name)_$($chartObj.Name).png"
        $exportPath = Join-Path $OutputDirectory $chartFileName
        $chartObj.Chart.Export($exportPath, "PNG")
        Write-Host "Successfully exported embedded chart: $exportPath"
    }
}

# Function to export standalone chart sheets (entire sheets that are charts)
function Export-ChartSheets {
    param(
        [Parameter(Mandatory=$true)]
        $Workbook,
        [Parameter(Mandatory=$true)]
        $OutputDirectory
    )
    foreach ($sheet in $Workbook.Sheets) {
        # Check if the sheet is a chart type (xlChart = 3)
        if ($sheet.Type -eq 3) {
            $chartFileName = "ChartSheet_$($sheet.Name).png"
            $exportPath = Join-Path $OutputDirectory $chartFileName
            $sheet.Export($exportPath, "PNG")
            Write-Host "Successfully exported chart sheet: $exportPath"
        }
    }
}

# Function to export pivot tables as images (using a temporary chart workaround)
function Export-PivotTables {
    param(
        [Parameter(Mandatory=$true)]
        $Worksheet,
        [Parameter(Mandatory=$true)]
        $OutputDirectory
    )
    foreach ($pivotTable in $Worksheet.PivotTables()) {
        $pivotFileName = "$($Worksheet.Name)_$($pivotTable.Name).png"
        $exportPath = Join-Path $OutputDirectory $pivotFileName
        
        # Copy the pivot table as a bitmap image
        $pivotTable.TableRange2.CopyPicture(1, 2)  # 1 = xlScreen, 2 = xlBitmap
        
        # Create a temporary chart to host the copied pivot table image
        $tempChartShape = $Worksheet.Shapes.AddChart2(201, 4)  # 201 = xlColumnClustered, 4 = xlChartType
        $tempChart = $tempChartShape.Chart
        $tempChart.Paste()
        
        # Export the temporary chart (which now contains the pivot table image)
        $tempChart.Export($exportPath, "PNG")
        
        # Clean up the temporary chart to avoid clutter
        $tempChartShape.Delete()
        
        Write-Host "Successfully exported pivot table: $exportPath"
    }
}

# Open the source workbook
try {
    $source_wb = $Excel.Workbooks.Open($sourcePath)
    
    # Process all worksheets for embedded charts and pivot tables
    foreach ($ws in $source_wb.Worksheets) {
        Export-EmbeddedCharts -Worksheet $ws -OutputDirectory $outputDir
        Export-PivotTables -Worksheet $ws -OutputDirectory $outputDir
    }
    
    # Process any standalone chart sheets
    Export-ChartSheets -Workbook $source_wb -OutputDirectory $outputDir
} catch {
    Write-Error "An error occurred: $_"
} finally {
    # Optional: Clean up Excel COM object (uncomment to close Excel automatically)
    # $Excel.Quit()
    # [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
    # [System.GC]::Collect()
    # [System.GC]::WaitForPendingFinalizers()
}

Key Details to Note: #

• File Paths: Update $sourcePath to your actual Excel file path (I corrected .xml to .xlsx since that's standard for Excel workbooks—adjust if you're using a different format like .xlsb).
• Export Format: The code uses PNG, but you can change the export format to "JPG", "GIF", or "BMP" by modifying the second parameter in the Export() method.
• Pivot Table Workaround: Pivot tables aren't natively exportable as images, so we use a temporary chart to hold the copied pivot table image before exporting. This is a reliable way to get a high-quality image of the pivot table.
• Visibility: I kept $Excel.Visible = $true as you had it, but if you want to run this in the background, set it to $false.
• Error Handling: Added a try/catch block to catch and report any issues (like missing files or permission errors).
• Cleanup: The optional cleanup code at the end will close Excel and release the COM object to avoid leaving Excel processes running in the background. Uncomment those lines if you don't need to keep Excel open after export.

内容的提问来源于stack exchange，提问作者Mehul Gogri