如何在CodeIgniter 3中使用PHPSpreadsheet读取Excel（.xlsx和.xls）文件数据？

Hey there! I’ve been in your shoes before—setting up PHPSpreadsheet with CodeIgniter can feel a bit tricky at first, but let’s break this down into simple, actionable steps that’ll get you reading Excel files (both .xls and .xlsx) and inserting data into MySQL in no time.

First, let’s get your PHPSpreadsheet files in a place that fits CodeIgniter’s structure:

• Rename the unzipped PHPSpreadsheet folder to something clean, like phpspreadsheet.
• Move this folder into your CodeIgniter project’s application/third_party directory—this is where third-party libraries live in CodeIgniter, so it keeps things organized.
• In any controller where you want to use PHPSpreadsheet, start by loading its autoloader:

require_once APPPATH . 'third_party/phpspreadsheet/vendor/autoload.php';

Let’s create a controller that handles file uploads, reads the Excel data, and inserts it into your database. Here’s a complete example:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Excel_import extends CI_Controller {

    public function __construct() {
        parent::__construct();
        // Load CodeIgniter's database library
        $this->load->database();
        // Load PHPSpreadsheet
        require_once APPPATH . 'third_party/phpspreadsheet/vendor/autoload.php';
        // Load session library for flash messages (optional but helpful)
        $this->load->library('session');
    }

    // Show the upload form
    public function index() {
        $this->load->view('excel_upload_form');
    }

    // Handle file upload and data import
    public function import_data() {
        // Check if a file was uploaded
        if (!isset($_FILES['excel_file']) || $_FILES['excel_file']['error'] == UPLOAD_ERR_NO_FILE) {
            $this->session->set_flashdata('error', 'Please select an Excel file to upload!');
            redirect('excel_import');
            return;
        }

        $temp_file = $_FILES['excel_file']['tmp_name'];
        $file_ext = pathinfo($_FILES['excel_file']['name'], PATHINFO_EXTENSION);

        // Initialize the correct reader based on file type
        switch ($file_ext) {
            case 'xls':
                $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
                break;
            case 'xlsx':
                $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
                break;
            default:
                $this->session->set_flashdata('error', 'Only .xls and .xlsx files are supported!');
                redirect('excel_import');
                return;
        }

        try {
            // Load the spreadsheet
            $spreadsheet = $reader->load($temp_file);
            $worksheet = $spreadsheet->getActiveSheet();
            $max_row = $worksheet->getHighestRow(); // Total rows in the sheet
            // $max_col = $worksheet->getHighestColumn(); // Total columns (if you need it)

            // Skip the header row (adjust this if your Excel has no header)
            for ($row = 2; $row <= $max_row; $row++) {
                // Read data from each column (customize these to match your Excel structure)
                $import_data = array(
                    'name' => $worksheet->getCell('A' . $row)->getValue(),
                    'email' => $worksheet->getCell('B' . $row)->getValue(),
                    'phone' => $worksheet->getCell('C' . $row)->getValue()
                );

                // Optional: Add data validation here (e.g., check if email is valid)
                if (empty($import_data['name']) || !filter_var($import_data['email'], FILTER_VALIDATE_EMAIL)) {
                    // Skip invalid rows or log errors
                    continue;
                }

                // Insert into your MySQL table (replace 'your_table' with your actual table name)
                $this->db->insert('your_table', $import_data);
            }

            $this->session->set_flashdata('success', "Successfully imported " . ($max_row - 1) . " rows of data!");
            redirect('excel_import');

        } catch (Exception $e) {
            $this->session->set_flashdata('error', 'Failed to read file: ' . $e->getMessage());
            redirect('excel_import');
        }
    }
}

Make a simple view (application/views/excel_upload_form.php) to let users upload their Excel files:

<!DOCTYPE html>
<html>
<head>
    <title>Excel Data Import</title>
    <style>
        .flash { padding: 10px; margin: 15px 0; border-radius: 4px; }
        .success { background: #d4edda; color: #155724; }
        .error { background: #f8d7da; color: #721c24; }
        .container { max-width: 600px; margin: 50px auto; padding: 0 20px; }
    </style>
</head>
<body>
    <div class="container">
        <h1>Import Excel to MySQL</h1>

        <?php if ($this->session->flashdata('success')): ?>
            <div class="flash success"><?= $this->session->flashdata('success') ?></div>
        <?php endif; ?>

        <?php if ($this->session->flashdata('error')): ?>
            <div class="flash error"><?= $this->session->flashdata('error') ?></div>
        <?php endif; ?>

        <?= form_open_multipart('excel_import/import_data') ?>
            <div>
                <label for="excel_file">Select Excel File:</label>
                <input type="file" name="excel_file" id="excel_file" accept=".xls,.xlsx" required>
            </div>
            <br>
            <button type="submit">Upload & Import</button>
        <?= form_close() ?>
    </div>
</body>
</html>

• Match Your Table Structure: Double-check that the fields in $import_data match the column names in your MySQL table. If your Excel has different columns, adjust the cell references (e.g., D$row for the 4th column) and array keys.
• Handle Large Files: If you’re working with huge Excel files, increase PHP’s upload_max_filesize and post_max_size in your php.ini file. You might also want to batch inserts to avoid timeouts.
• Data Sanitization: Always validate and sanitize the data before inserting it into the database—this prevents invalid or malicious data from breaking your database.
• Namespace Issues: Make sure you’re using the full namespace for PHPSpreadsheet classes (like \PhpOffice\PhpSpreadsheet\Reader\Xlsx)—missing this will cause "class not found" errors.

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