开发应用：如何通过HTML将SQL数据库数据填充至已有内容的PDF表单？

Hey there! Let's walk through how you can fill existing PDF fields with data from your SQL database, using HTML as a middle layer. Here are two practical, battle-tested approaches depending on your tech stack:

This works great if you need to replicate the layout of your existing PDF (with dynamic data) or if your original PDF doesn’t have editable form fields.

• Step 1: Pull data from your SQL database
  Use your backend language to query the database and fetch the fields you need. For example, in Python:

  import psycopg2  # For PostgreSQL; use MySQLdb/mysql-connector for MySQL
  
  def fetch_customer_data(customer_id):
      conn = psycopg2.connect("dbname=your_db user=your_user password=your_pw host=your_host")
      cursor = conn.cursor()
      cursor.execute("SELECT full_name, email, order_number FROM customers WHERE id = %s", (customer_id,))
      data = cursor.fetchone()
      conn.close()
      return {
          "full_name": data[0],
          "email": data[1],
          "order_number": data[2]
      }
  

• Step 2: Inject data into an HTML template
  Use a template engine (like Jinja2 for Python, Thymeleaf for Java, or EJS for Node.js) to populate an HTML template with your SQL data. Here’s a Jinja2 example (pdf_template.html):

  <!DOCTYPE html>
  <html>
  <head>
      <style>
          /* Match your original PDF's styling here */
          body { font-family: Arial, sans-serif; margin: 2rem; }
          .field-label { font-weight: bold; }
      </style>
  </head>
  <body>
      <h1>Customer Order Details</h1>
      <p><span class="field-label">Name:</span> {{ full_name }}</p>
      <p><span class="field-label">Email:</span> {{ email }}</p>
      <p><span class="field-label">Order #:</span> {{ order_number }}</p>
      <!-- Add all other fixed/dynamic content to match your PDF -->
  </body>
  </html>
  

• Step 3: Convert the rendered HTML to PDF
  Use tools like wkhtmltopdf (cross-language), WeasyPrint (Python), or html-pdf-node (Node.js) to turn your filled HTML into a PDF. If you need to merge this with your original PDF, use libraries like PyPDF2 (Python) or iText (Java) to combine the files.

If your existing PDF already has editable form fields (created via Adobe Acrobat or tools like PDFescape), this is the most straightforward approach.

• Step 1: Identify PDF field names
  First, find the exact names of the form fields in your PDF (e.g., customer_name, order_id). You can use Adobe Acrobat’s "Prepare Form" tool or open-source tools like pdftk to list fields.

• Step 2: Fetch SQL data (same as Step 1 in Scheme 1)
  Pull the required data from your database using your backend language.

• Step 3: Populate the PDF fields
  Use a PDF manipulation library to fill the fields with your SQL data. Examples:

  • Python with PyMuPDF (lightweight and reliable):

    import fitz  # PyMuPDF
    
    def fill_pdf_form(input_pdf, output_pdf, data):
        doc = fitz.open(input_pdf)
        form = doc.load_page(0).widgets  # Get form widgets on the first page
        for widget in form:
            if widget.field_name in data:
                widget.field_value = data[widget.field_name]
                widget.update()  # Save the change
        doc.save(output_pdf)
        doc.close()
    
    # Usage
    customer_data = fetch_customer_data(123)
    fill_pdf_form("original_form.pdf", "filled_form.pdf", {
        "customer_name": customer_data["full_name"],
        "order_id": customer_data["order_number"],
        "customer_email": customer_data["email"]
    })
    

  • Java with iText 7:

    import com.itextpdf.kernel.pdf.PdfDocument;
    import com.itextpdf.kernel.pdf.PdfReader;
    import com.itextpdf.kernel.pdf.PdfWriter;
    import com.itextpdf.forms.PdfAcroForm;
    
    public class PdfFormFiller {
        public static void main(String[] args) throws Exception {
            // Fetch data from SQL (simulated here)
            String fullName = "Jane Smith";
            String email = "jane@example.com";
            String orderNumber = "ORD-7890";
    
            PdfReader reader = new PdfReader("original_form.pdf");
            PdfWriter writer = new PdfWriter("filled_form.pdf");
            PdfDocument pdfDoc = new PdfDocument(reader, writer);
            PdfAcroForm form = PdfAcroForm.getAcroForm(pdfDoc, true);
    
            // Fill fields
            form.getField("customer_name").setValue(fullName);
            form.getField("customer_email").setValue(email);
            form.getField("order_id").setValue(orderNumber);
    
            // Optional: Make form read-only
            form.flattenFields();
            pdfDoc.close();
        }
    }
    

• Step 4: Serve the filled PDF to HTML
  Your backend can return the filled PDF as a file stream to your frontend HTML, which can trigger a download or display it in an <iframe> for preview.

• If your original PDF has no form fields, use tools like Adobe Acrobat or PDFescape to add them first before using Scheme 2.
• Ensure your data keys match the PDF field names exactly to avoid missing fills.
• Test for encoding issues (stick to UTF-8) and font compatibility, especially if your PDF uses non-standard fonts.

内容的提问来源于stack exchange，提问作者Rúben Cardoso