How to Read Excel File in CodeIgniter 4

General
1

CodeIgniter
CodeIgniter444×478 28.4 KB

Handling Excel files is a common requirement in many web applications, especially when dealing with data import functionalities. In this blog post, we will guide you step-by-step on how to read an Excel file in CodeIgniter 4 using a popular library called PhpSpreadsheet. Whether you’re building a school management system, inventory tracker, or HR tool, understanding this process is crucial.

Why Read Excel Files in Web Applications?

Excel files (.xls or .xlsx) are widely used for storing and sharing tabular data. Reading Excel files in web apps enables functionalities like:

  • Bulk data import from external systems
  • Automated processing of spreadsheet data
  • Enabling users to upload reports or logs
  • Reducing manual data entry

CodeIgniter 4, being a modern PHP framework, can easily integrate with PhpSpreadsheet to handle Excel files effectively.

Prerequisites

Before we begin, ensure the following:

  1. You have a working CodeIgniter 4 project setup.
  2. Composer is installed on your system.
  3. Basic understanding of CodeIgniter 4 structure and MVC pattern.

Step 1: Install PhpSpreadsheet via Composer

PhpSpreadsheet is a PHP library for reading and writing spreadsheet files. To install it, open your terminal in the root directory of your project and run:

composer require phpoffice/phpspreadsheet

This command will download and install PhpSpreadsheet and its dependencies in your CodeIgniter 4 project.

Step 2: Create File Upload Form

You need a form that allows users to upload Excel files. Create a new view file named upload_excel.php inside the app/Views directory with the following code:

<!DOCTYPE html>
<html>
<head>
    <title>Upload Excel in CodeIgniter 4</title>
</head>
<body>
    <h2>Upload Excel File</h2>
    <?php if(session()->getFlashdata('message')): ?>
        <p><?= session()->getFlashdata('message'); ?></p>
    <?php endif; ?>
    <form method="post" action="<?= base_url('excel/import') ?>" enctype="multipart/form-data">
        <input type="file" name="file" accept=".xls,.xlsx" required>
        <button type="submit">Upload</button>
    </form>
</body>
</html>

Step 3: Create Controller for Excel Handling

Now, let’s create a controller that will handle the Excel file upload and reading logic.

Create the Controller

Run the following command in your terminal:

php spark make:controller Excel

This will generate a controller file at app/Controllers/Excel.php.

Add Import Functionality

Open Excel.php and update it as follows:

<?php

namespace App\Controllers;
use App\Controllers\BaseController;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Excel extends BaseController
{
    public function index()
    {
        return view('upload_excel');
    }

    public function import()
    {
        $file = $this->request->getFile('file');

        if ($file->isValid() && !$file->hasMoved()) {
            $filePath = WRITEPATH . 'uploads/' . $file->getRandomName();
            $file->move(WRITEPATH . 'uploads', $file->getRandomName());

            // Load the spreadsheet file
            $spreadsheet = IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            // Output the read data
            echo "<pre>";
            print_r($data);
            echo "</pre>";
        } else {
            return redirect()->back()->with('message', 'File upload failed!');
        }
    }
}

What This Does:

  • Uploads the Excel file to the writable/uploads directory.
  • Loads the file using PhpSpreadsheet.
  • Converts the sheet content to an array and displays it on the page.

Step 4: Set Route for Upload and Import

Open the app/Config/Routes.php file and define the routes:

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

You can now access the upload form via http://yourdomain.com/excel.

Step 5: Test the Functionality

  1. For your CodeIgniter project start your local development server:
php spark serve
  1. Visit http://localhost:8080/excel.
  2. Upload an Excel file (.xls or .xlsx).
  3. You should see the contents of the file printed on the screen.

Optional: Display Excel Data in HTML Table

Instead of dumping the raw array, you might want to display the data in a formatted table. Modify the import() function to:

public function import()
{
    $file = $this->request->getFile('file');

    if ($file->isValid() && !$file->hasMoved()) {
        $filePath = WRITEPATH . 'uploads/' . $file->getRandomName();
        $file->move(WRITEPATH . 'uploads', $file->getRandomName());

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $data = $sheet->toArray();

        echo "<table border='1'>";
        foreach ($data as $row) {
            echo "<tr>";
            foreach ($row as $cell) {
                echo "<td>" . htmlspecialchars($cell) . "</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    } else {
        return redirect()->back()->with('message', 'File upload failed!');
    }
}

This will render the spreadsheet data in a clean HTML table format.

Best Practices and Tips

  • Validate Excel content: Always validate the data before inserting it into the database.
  • Handle large files: For large files, consider reading the file in chunks or using batch processing.
  • Security: Restrict uploads to specific file types and sizes to prevent abuse.
  • Error handling: Add try-catch blocks around file reading operations for better error messages.

Conclusion

Reading Excel files in CodeIgniter 4 is straightforward with the help of PhpSpreadsheet. You can quickly build powerful import features by combining CodeIgniter’s clean MVC structure with PhpSpreadsheet’s robust Excel handling capabilities.

With the ability to parse and process spreadsheet data, your applications become more user-friendly and flexible, especially in enterprise environments where Excel is a standard.

Start experimenting with more advanced features like reading multiple sheets, formatting data, and importing into databases to take your implementation to the next level.