Import Excel file using PHPSpreadsheet Library in CodeIgniter 4 using AJAX

Overview

PhpSpreadsheet is a PHP library for reading and writing spreadsheet files. Importing Excel and CSV into MySQL help to save the user time and avoid repetitive work.

Software requirements

PHP version 7.2 or newer to develop using PhpSpreadsheet. Other requirements, such as PHP extensions, are enforced by the composer.

Installation

1. Download and install CodeIgniter.
2. Use Composer to install PhpSpreadsheet into your project:
3. Use phpspreadsheet the library inside in your controller

composer require phpoffice/phpspreadsheet

Create MySQL Database and Table

The following SQL query creates a user_info the table in the MySQL database.

CREATE TABLE `client_info` (
  `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `status` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create Routes

Open App/Config/Routes.php file and add the following lines.

<?php 

$routes->get('/', 'User::index');
$routes->get('/display', 'User::display');
$routes->post('user/import', 'User::import');

?>

Create Model

Create a model file named UserModel.php inside the App/Models folder.

<?php namespace App\Models;

use CodeIgniter\Model;
use CodeIgniter\Database\ConnectionInterface;

class UserModel extends Model {

    protected $db;
    public function __construct(ConnectionInterface &$db) {
        $this->db =& $db;
        $this->table_name = 'user_info';
    }

    public function addUser($data) {
        $this->db
                        ->table($this->table_name)
                        ->insert($data);
        return $this->db->insertID();
    }

    public function getUserList() {
        return $this->db
                        ->table($this->table_name)
                        ->get()
                        ->getResult();
    }

    public function getUser($where) {
        return $this->db
                        ->table($this->table_name)
                        ->where($where)
                        ->get()
                        ->getRow();
    }

    public function updateUser($where, $data) {
        return $this->db
                        ->table($this->table_name)
                        ->where($where)
                        ->set($data)
                        ->update();
    }

    public function deleteUser($where) {
        return $this->db
                        ->table($this->table_name)
                        ->where($where)
                        ->delete();
    }

    public function bulkInsert($data) {
        return $this->db
                        ->table($this->table_name)
                        ->insertBatch($data);
    }

}

Create Controller and load class

Create a controller named User.php and use the PHPSpreadsheet library inside the controller. See the following code for the controller.

<?php 
namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\UserModel;

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

class User extends BaseController {

	public function __construct() {
		$db 						= db_connect();
		$this->userModel     		= new UserModel($db);

		$this->ip_address 			= $_SERVER['REMOTE_ADDR'];
		$this->datetime 			= date("Y-m-d H:i:s");
	}

	public function index() {
		echo view("index");
	}

	public function display() {
    	$data 	= [];
    	$data ["result"] = $this->userModel->getUserList();
    	echo view("display", $data);
    }

	public function import() {
		$path 			= 'documents/users/';
		$json 			= [];
		$file_name 		= $this->request->getFile('file');
		$file_name 		= $this->uploadFile($path, $file_name);
		$arr_file 		= explode('.', $file_name);
		$extension 		= end($arr_file);
		if('csv' == $extension) {
			$reader 	= new \PhpOffice\PhpSpreadsheet\Reader\Csv();
		} else {
			$reader 	= new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
		}
		$spreadsheet 	= $reader->load($file_name);
		$sheet_data 	= $spreadsheet->getActiveSheet()->toArray();

		$list 			= [];
		foreach($sheet_data as $key => $val) {
			if($key != 0) {
				$result 	= $this->userModel->getUser(["country_code" => $val[2], "mobile" => $val[3]]);
				if($result) {
				} else {
					$list [] = [
						'name'					=> $val[0],
						'country_code'			=> $val[1],
						'mobile'				=> $val[2],
						'email'					=> $val[3],
						'city'					=> $val[4],
						'ip_address'			=> $this->ip_address,
						'created_at' 			=> $this->datetime,
						'status'				=> "1",
					];
				}
			}
		}

		if(file_exists($file_name))
			unlink($file_name);
		if(count($list) > 0) {
			$result 	= $this->userModel->bulkInsert($list);
			if($result) {
				$json = [
					'success_message' 	=> showSuccessMessage("All Entries are imported successfully."),
				];
			} else {
				$json = [
					'error_message' 	=> showErrorMessage("Something went wrong. Please try again.")
				];
			}
		} else {
			$json = [
				'error_message' => showErrorMessage("No new record is found."),
			];
		}

		echo json_encode($json);
	}

	public function uploadFile($path, $image) {
    	if (!is_dir($path)) 
			mkdir($path, 0777, TRUE);
		if ($image->isValid() && ! $image->hasMoved()) {
			$newName = $image->getRandomName();
			$image->move('./'.$path, $newName);
			return $path.$image->getName();
		}
		return "";
	}

}

Create View

Create a view named index.php inside the App/Views directory. See the following code for the view file.

<section class="content">
    <div class="card">
        <div class="card-header">
            <h3 class="card-title">Upload</h3>
            <div class="card-tools">
                <button type="button" class="btn btn-tool" data-card-widget="collapse" title="Collapse"> <i class="fas fa-minus"></i>
                </button>
                <button type="button" class="btn btn-tool" data-card-widget="remove" title="Remove"> <i class="fas fa-times"></i>
                </button>
            </div>
        </div>
        <div class="card-body">
            <form id="form-upload-user" method="post" autocomplete="off">
                <div class="sub-result"></div>
                <div class="form-group">
                    <label class="control-label">Choose File <small class="text-danger">*</small></label>
                    <input type="file" class="form-control form-control-sm" id="file" name="file" accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" required>
                    <small class="text-danger">Upload excel or csv file only.</small>
                    </div>
                <div class="form-group">
                    <div class="text-center">
                        <div class="user-loader" style="display: none; ">
                            <i class="fa fa-spinner fa-spin"></i> <small>Please wait ...</small>
                        </div>
                    </div>
                </div>
                <div class="form-group">
                    <button type="submit" class="btn btn-primary btn-sm waves-effect waves-light" id="btnUpload">Upload</button>
                </div>
            </form>
        </div>
    </div>
</section>
<!-- /.content -->

<script>
    $(document).ready(function() {
        $("body").on("submit", "#form-upload-user", function(e) {
            e.preventDefault();
            var data = new FormData(this);
            $.ajax({
                type: 'POST',
                url: "<?php echo base_url('user/import') ?>",
                data: data,
                dataType: 'json',
                contentType: false,
                cache: false,
                processData:false,
                beforeSend: function() {
                    $("#btnUpload").prop('disabled', true);
                    $(".user-loader").show();
                }, 
                success: function(result) {
                    $("#btnUpload").prop('disabled', false);
                    if($.isEmptyObject(result.error_message)) {
                        $(".result").html(result.success_message);
                    } else {
                        $(".sub-result").html(result.error_message);
                    }
                    $(".user-loader").hide();
                }
            });
        });
    });
</script>

Create View

Create a view named display.php inside the App/Views directory. See the following code for the view file.

<section class="content">
    <div class="card">
        <div class="card-header">
            <h3 class="card-title">Dipslay</h3>
            <div class="card-tools">
            </div>
        </div>
        <div class="card-body">
            <table class="table table-sm table-bordered table-striped table-hover" width="100%">
                <thead>
                    <tr>
                        <th>No.</th>
                        <th>Name</th>
                        <th>Mobile</th>
                        <th>Email</th>
                        <th>City</th>
                    </tr>
                </thead>
                <tbody>
                <?php 
                $i = 0;
                foreach($result as $row) { 
                ?>    
                    <tr>
                        <td><?php ++$i; ?></td>
                        <td><?php echo $row->name; ?></td>
                        <td><?php echo $row->country_code.$row->mobile; ?></td>
                        <td><?php echo $row->email; ?></td>
                        <td><?php echo $row->city; ?></td>
                    </tr>
                <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</section>

Read more at https://infovistar.com/codeigniter4/import-excel