Zellen aus PDF-Dokument auslesen und in Excel-Datei übertragen

# Variablen bitte anpassen ---------------------------
# Pfad zur PDF Datei
$sourcefile = "C:\data\quelle.pdf"  
# Pfad zum Excel-Template
$templatefile = "c:\data\template.xlsx"  
# ------------------------------------------------------
$ErrorActionPreference = 'Stop'  

# Funktion zum Laden des iTextSharp Assemblies
function Load-iTextLibrary {
    if($psscriptroot -ne ''){  
        $localpath = join-path $psscriptroot 'itextsharp.dll'  
    }else{
        $localpath = join-path $env:TEMP 'itextsharp.dll'  
    }
    $zip = $null;$tmp = ''  
    try{
        if(!(Test-Path $localpath)){
            Add-Type -A System.IO.Compression.FileSystem
            $tmp = "$env:TEMP\$([IO.Path]::GetRandomFileName())"  
            write-host "Downloading and extracting required 'iTextSharp.dll' ... " -F Green -NoNewline  

            (New-Object System.Net.WebClient).DownloadFile('https://www.nuget.org/api/v2/package/iTextSharp/5.5.13.1', $tmp)  
            $zip = [System.IO.Compression.ZipFile]::OpenRead($tmp)
            $zip.Entries | ?{$_.Fullname -eq 'lib/itextsharp.dll'} | %{  
                [System.IO.Compression.ZipFileExtensions]::ExtractToFile($_,$localpath)
            }
            write-host "OK" -F Green   
        }
        if (Get-Item $localpath -Stream zone.identifier -ea SilentlyContinue){
            Unblock-File -Path $localpath
        }
        Add-Type -Path $localpath
    }catch{
        throw "Error: $($_.Exception.Message)"  
    }finally{
        if ($zip){$zip.Dispose()}
        if ($tmp -ne ''){del $tmp -Force -EA SilentlyContinue}  
    }
}
# iText Library laden
Load-iTextLibrary

# auch geschütze PDFs öffnen aktivieren
[iTextSharp.text.pdf.PdfReader]::unethicalreading = $true

$reader = $null; $xl = $null
try {
    # Excel Object erstellen
    $xl = New-Object -Com Excel.Application -P @{Visible = $false}
    # workbook öffnen
    $wb = $xl.Workbooks.Open($templatefile)
    # wir arbeiten auf dem ersten Arbeitsblatt
    $ws = $wb.Sheets.Item(1)
    write-host "Exportiere Daten aus '$($sourcefile)' ... " -F Green -NoNewline  
    # PDF Reader Object erstellen
    $reader = New-Object iTextSharp.text.pdf.PdfReader $sourcefile
    # Gesamtergebnisse aus dem PDF in die entsprechenden Felder der Exceldatei übertragen
    $ws.Range("E7").Value = $reader.AcroFields.GetField("GESAMT")  
    $ws.Range("I4").Value = $reader.AcroFields.GetField("GESAMT_2")  
    $ws.Range("B10").Value = $reader.AcroFields.GetField("GESAMT_3")  
    $ws.Range("K7").Value = $reader.AcroFields.GetField("GESAMT_4")  
    # Arbeitsmappe speichern und schließen
    $wb.Save();$wb.Close($true)
    # reader schließen und ressourcen freigeben
    $reader.Close(); $reader.Dispose()
}catch{
    write-host $_.Exception.Message -F Red
}finally{
    # cleanup
    if($reader){$reader.Dispose()}
    # Excel schließen
    $xl.Quit()
    # release com resources
    [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
}

# Variablen bitte anpassen ---------------------------
# Pfad zur PDF Datei
$sourcefile = "C:\data\quelle.pdf"  
# Pfad zum Excel-Template
$templatefile = "c:\data\template.xlsx"  
# ------------------------------------------------------
$ErrorActionPreference = 'Stop'  

# install ImportExcel module
If(!(Get-Module -ListAvailable -Name ImportExcel)){
    write-host "Installing 'ImportExcel' module ..." -F Green  
    Install-Module -Name ImportExcel -Scope CurrentUser -Force
}

# Funktion zum Laden des iTextSharp Assemblies
function Load-iTextLibrary {
    if($psscriptroot -ne ''){  
        $localpath = join-path $psscriptroot 'itextsharp.dll'  
    }else{
        $localpath = join-path $env:TEMP 'itextsharp.dll'  
    }
    $zip = $null;$tmp = ''  
    try{
        if(!(Test-Path $localpath)){
            Add-Type -A System.IO.Compression.FileSystem
            $tmp = "$env:TEMP\$([IO.Path]::GetRandomFileName())"  
            write-host "Downloading and extracting required 'iTextSharp.dll' ... " -F Green -NoNewline  

            (New-Object System.Net.WebClient).DownloadFile('https://www.nuget.org/api/v2/package/iTextSharp/5.5.13.1', $tmp)  
            $zip = [System.IO.Compression.ZipFile]::OpenRead($tmp)
            $zip.Entries | ?{$_.Fullname -eq 'lib/itextsharp.dll'} | %{  
                [System.IO.Compression.ZipFileExtensions]::ExtractToFile($_,$localpath)
            }
            write-host "OK" -F Green   
        }
        if (Get-Item $localpath -Stream zone.identifier -ea SilentlyContinue){
            Unblock-File -Path $localpath
        }
        Add-Type -Path $localpath
    }catch{
        throw "Error: $($_.Exception.Message)"  
    }finally{
        if ($zip){$zip.Dispose()}
        if ($tmp -ne ''){del $tmp -Force -EA SilentlyContinue}  
    }
}
# iText Library laden
Load-iTextLibrary

# auch geschütze PDFs öffnen aktivieren
[iTextSharp.text.pdf.PdfReader]::unethicalreading = $true

$reader = $null; $xl = $null
try {
    # Excelmappe öffnen
    $xl = Open-ExcelPackage -Path $templatefile
    # Benutze das erste Arbeitsblatt für die Ausgabe
    $ws = $xl.Workbook.Worksheets[1]
    write-host "Exportiere Daten aus '$($sourcefile)' ... " -F Green -NoNewline  
    # PDF Reader Object erstellen
    $reader = New-Object iTextSharp.text.pdf.PdfReader $sourcefile

    # Gesamtergebnisse aus dem PDF in die Exceldatei übertragen
    $ws.Cells['E7'].Value  = [int]$reader.AcroFields.GetField("GESAMT")  
    $ws.Cells['I4'].Value  = [int]$reader.AcroFields.GetField("GESAMT_2")  
    $ws.Cells['B10'].Value = [int]$reader.AcroFields.GetField("GESAMT_3")  
    $ws.Cells['K7'].Value  = [int]$reader.AcroFields.GetField("GESAMT_4")  

    # reader schließen und ressourcen freigeben
    $reader.Close(); $reader.Dispose()
}catch{
    write-host $_.Exception.Message -F Red
}finally{
    # cleanup
    if($reader){
        $reader.Dispose()
    }
    # close excel session
    if($xl){
        Close-ExcelPackage -ExcelPackage $xl
    }
}

$reader.AcroFields.GetField("GESAMT")