如何用PHP动态生成Excel并保存本地后通过PHPMailer发送邮件附件

Let's tackle both of your use cases with practical, working code examples. We'll use PhpSpreadsheet (the modern successor to PHPExcel) for Excel generation and PHPMailer for email handling—both are reliable, widely-used tools in PHP development.

1. Generate Excel File, Save Locally, and Send as Attachment #

Step 1: Install Required Libraries #

First, use Composer to pull in the dependencies if you haven't already:

composer require phpoffice/phpspreadsheet phpmailer/phpmailer

Step 2: Generate & Save Excel File #

Here's a script that creates a sample Excel file and saves it to a local directory. Make sure the target folder has write permissions for your web server user (like www-data on Apache):

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Initialize a new spreadsheet
$spreadsheet = new Spreadsheet();
$activeSheet = $spreadsheet->getActiveSheet();

// Populate with sample data
$activeSheet->setCellValue('A1', 'ID');
$activeSheet->setCellValue('B1', 'Product Name');
$activeSheet->setCellValue('C1', 'Price');
$activeSheet->setCellValue('A2', 101);
$activeSheet->setCellValue('B2', 'Wireless Headphones');
$activeSheet->setCellValue('C2', 99.99);

// Define save path and filename
$saveDir = __DIR__ . '/generated_excels/';
if (!file_exists($saveDir)) {
    mkdir($saveDir, 0755, true); // Create directory if it doesn't exist
}
$fileName = 'product_list_' . date('YmdHis') . '.xlsx';
$fullPath = $saveDir . $fileName;

// Save the file
$writer = new Xlsx($spreadsheet);
$writer->save($fullPath);

// Clean up memory
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
?>

Step 3: Send Email with Attachment #

Add this code right after the Excel generation to send the file as an attachment:

<?php
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// Set up PHPMailer
$mail = new PHPMailer(true);

try {
    // SMTP configuration (adjust to your email provider)
    $mail->isSMTP();
    $mail->Host = 'smtp.example.com'; // e.g., smtp.gmail.com for Gmail
    $mail->SMTPAuth = true;
    $mail->Username = 'your-email@example.com';
    $mail->Password = 'your-email-password'; // Use App Password if 2FA is enabled for Gmail
    $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS; // Use 'tls' for port 587
    $mail->Port = 465;

    // Recipients
    $mail->setFrom('your-email@example.com', 'Your Name');
    $mail->addAddress('recipient@example.com', 'Recipient Name');

    // Attach the generated Excel file
    $mail->addAttachment($fullPath);

    // Email content
    $mail->isHTML(true);
    $mail->Subject = 'Your Requested Product List';
    $mail->Body = 'Hi there,<br><br>Please find the attached product list Excel file.<br><br>Best regards,<br>Your Name';

    $mail->send();
    echo 'Email sent successfully!';

    // Optional: Delete the file after sending to save storage
    unlink($fullPath);
} catch (Exception $e) {
    echo "Oops, email couldn't be sent. Error: {$mail->ErrorInfo}";
}
?>

2. Dynamic Excel Generation from HTML Form Submission, Save Locally, and Send as Attachment #

Step 1: Create the HTML Form #

First, build a simple form to collect user input:

<form method="post" action="process_form.php">
    <label>Full Name:</label>
    <input type="text" name="full_name" required><br>

    <label>Employee ID:</label>
    <input type="text" name="emp_id" required><br>

    <label>Department:</label>
    <input type="text" name="department" required><br>

    <button type="submit">Submit & Send Record</button>
</form>

Step 2: Process Form Submission & Generate Excel #

Create process_form.php to handle the submission, generate the Excel file, save it, and send the email:

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// Only process if form is submitted via POST
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Sanitize user input to avoid security issues
    $fullName = htmlspecialchars($_POST['full_name']);
    $empId = htmlspecialchars($_POST['emp_id']);
    $department = htmlspecialchars($_POST['department']);

    // Generate Excel file with form data
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setCellValue('A1', 'Full Name');
    $sheet->setCellValue('B1', 'Employee ID');
    $sheet->setCellValue('C1', 'Department');
    $sheet->setCellValue('A2', $fullName);
    $sheet->setCellValue('B2', $empId);
    $sheet->setCellValue('C2', $department);

    // Save to local directory
    $saveDir = __DIR__ . '/form_records/';
    if (!file_exists($saveDir)) {
        mkdir($saveDir, 0755, true);
    }
    $fileName = 'employee_record_' . date('YmdHis') . '.xlsx';
    $fullPath = $saveDir . $fileName;

    $writer = new Xlsx($spreadsheet);
    $writer->save($fullPath);

    // Clean up memory
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);

    // Send email with attachment
    $mail = new PHPMailer(true);

    try {
        // SMTP settings (adjust to your provider)
        $mail->isSMTP();
        $mail->Host = 'smtp.example.com';
        $mail->SMTPAuth = true;
        $mail->Username = 'admin@example.com';
        $mail->Password = 'admin-password';
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;
        $mail->Port = 465;

        // Recipients
        $mail->setFrom('admin@example.com', 'HR Department');
        $mail->addAddress('hr@example.com', 'HR Team');
        // Optional: Send a copy to the employee
        $mail->addAddress($userEmail, $fullName);

        // Attach the Excel file
        $mail->addAttachment($fullPath);

        // Email content
        $mail->isHTML(true);
        $mail->Subject = 'New Employee Record Submission';
        $mail->Body = "Hi HR,<br><br>A new employee record has been submitted:<br><br>Name: $fullName<br>ID: $empId<br>Department: $department<br><br>Attached is the Excel file with the details.<br><br>Thanks";

        $mail->send();
        echo 'Form submitted successfully! We\'ve sent the record to HR.';

        // Optional: Delete the file after sending
        unlink($fullPath);
    } catch (Exception $e) {
        echo "Error: Could not send email. {$mail->ErrorInfo}";
    }
} else {
    echo 'Invalid request method. Please submit the form.';
}
?>

Key Notes #

• Permissions: Double-check that your save directories have write access for the web server user.
• Security: Always sanitize user input to prevent XSS attacks or malicious data injection.
• Email Providers: For services like Gmail, use an App Password if two-factor authentication is enabled. For production, consider using a dedicated SMTP service like SendGrid or Mailgun for better deliverability.

内容的提问来源于stack exchange，提问作者Pooja s p