Codeigniter 4 upload excel file to database

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

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

1. Download and install CodeIgniter.
2. Use Composer to install PhpSpreadsheet into your project:

   composer require phpoffice/phpspreadsheet

3. Use phpspreadsheet library inside in your controller

The following SQL query creates a user_info 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;

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

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

?>

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

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 a controller named User.php and use the PHPSpreadsheet library inside the controller. See the following code for the controller.

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 a view named index.php inside the App/Views directory. See the following code for the view file.

    

        

            
Upload
            

                 
                
                 
                
            
        
        

            

                

                

                    Choose File *
                    
                    Upload excel or csv file only.
                    
                

                    

                        

                             Please wait ...
                        
                    
                
                

                    Upload
                
            
        
    

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

    

        

            
Dipslay
            

            
        
        

            

                

                    

                        
No.
                        
Name
                        
Mobile
                        
Email
                        
City
                    
                
                

                    
                    

                        

                        
name; ?>
                        
country_code.$row->mobile; ?>
                        
email; ?>
                        
city; ?>