CodeIgniter 4 Import Excel File Tutorial

PHPSpreadsheet is a third party library that allows developers to create feature to import and export excel/csv file from database in any PHP framework such as codeigniter, laravel, yii, etc.

Let’s start to create import excel or csv file data in mysql database using phpspreadsheet library in codeIgniter 4 applications:

Step 1: Install the PHPSpreadsheet Library

Run the following composer command to install PHPSpreadsheet library in your application:

composer require phpoffice/phpspreadsheet

Step 2: Create a Model for Data Import

Go to the app/Models/ directory and create a model file name ExcelModel.php that handles the database operations to import data from the Excel file:

<?php

namespace App\Models;

use CodeIgniter\Model;

class ExcelModel extends Model
{
    protected $table = 'your_table_name'; // Replace with your table name
    protected $allowedFields = ['column1', 'column2', 'column3']; // Replace with your table columns
}

Step 3: Create a Controller for Excel Import

Go to the app/Controllers/ directory and create a controller file named ExcelImportController.php that handles the file upload and processing:

<?php

namespace App\Controllers;

use App\Models\ExcelModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

class ExcelImportController extends BaseController
{
    public function index()
    {
        return view('excel_import');
    }

    public function import()
    {
        $file = $this->request->getFile('file');
        
        if ($file->isValid() && !$file->hasMoved()) {
            $spreadsheet = new Spreadsheet();
            $reader = new Xlsx();
            $spreadsheet = $reader->load($file->getTempName());

            $excelModel = new ExcelModel();

            $sheetData = $spreadsheet->getActiveSheet()->toArray();

            foreach ($sheetData as $key => $value) {
                if ($key == 0) {
                    continue; // Skip header row
                }

                $data = [
                    'column1' => $value[0],
                    'column2' => $value[1],
                    'column3' => $value[2],
                ];

                $excelModel->insert($data);
            }

            return redirect()->to('/excel-import')->with('message', 'Excel file imported successfully');
        }

        return redirect()->to('/excel-import')->with('error', 'Please choose a valid Excel file');
    }
}

Step 4: Create a View for Excel Import

Create a new view file name excel_import.php in app/Views/ directory, and create html form in it where users can select the Excel file to upload:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import Excel File Data in MySQL using CodeIgniter 4 - itcodstuff.com</title>
</head>
<body>
    <h1>Import Excel File</h1>
    <?php if (session()->getFlashdata('message')): ?>
        <p><?= session()->getFlashdata('message') ?></p>
    <?php endif; ?>
    <?php if (session()->getFlashdata('error')): ?>
        <p><?= session()->getFlashdata('error') ?></p>
    <?php endif; ?>
    <form action="<?= base_url('excel-import') ?>" method="post" enctype="multipart/form-data">
        <input type="file" name="file" required>
        <button type="submit">Import</button>
    </form>
</body>
</html>

Step 5: Define Routes

Edit app/Config/Routes.php file, and define import file routes in it:

$routes->get('excel-import', 'ExcelImportController::index');
$routes->post('excel-import', 'ExcelImportController::import');

Step 6: Test the Import Functionality

Type url http://yourdomain/excel-import on browser to test this application.