Cara menggunakan phpspreadsheet multiple sheets

Pertama-tama Anda harus memiliki PHP versi 7.1 atau yang lebih baru untuk dikembangkan menggunakan PhpSpreadsheet. Cara yang disarankan untuk menginstal PhpSpreadsheet ke dalam proyek Anda adalah dengan menggunakan komposer. Buka terminal di direktori root proyek Anda dan jalankan perintah:

Berikut perintahnya

 composer require phpoffice/phpspreadsheet 

Komposer menawarkan penginstal yang nyaman yang dapat Anda jalankan langsung dari baris perintah. Silakan unduh file ini atau tinjau di GitHub. Ada dua cara untuk menginstal Komposer. Instal secara lokal sebagai bagian dari proyek Anda, atau secara global sebagai sistem yang dapat dieksekusi secara luas.


Bekerja dengan Sel Spreadsheet

PHPSpreadsheet memungkinkan Anda untuk mengakses sel dan mengatur nilainya dengan koordinat. Anda juga dapat membuat sel baru dan menyiapkan formula di dalamnya. Anda juga dapat mengonfigurasi sel untuk menempati berbagai tipe data, seperti tanggal, waktu, dan angka dengan angka nol di depan. PHPSpreadsheet juga memungkinkan Anda untuk mengatur rentang sel dari array, serta mengulang sel menggunakan iterator atau indeks. Anda juga dapat menggunakan pengikat nilai untuk memungkinkan entri data yang mudah digunakan.

Buat XLSX baru - PHP

getActiveSheet();
// Set cell value
$sheet->setCellValue('A1', 'File Format Developer Guide !');
// Save in Xlsx format
$writer = new Xlsx($spreadsheet);
$writer->save('FileFormat.xlsx');

Buat & Terapkan Filter Otomatis

Menggunakan PHPSpreadsheet, Anda dapat menerapkan rentang filter otomatis untuk memfilter dan hanya menampilkan baris yang cocok dengan kriteria yang telah Anda tetapkan di filter otomatis. Anda dapat menerapkan filter ke lebih dari satu kolom sebagai aditif.

PHPSpreadsheet memungkinkan Anda untuk mengatur area filter otomatis pada lembar kerja. Anda dapat membuat, membaca, dan menulis ekspresi filter otomatis. Selain itu, berbagai jenis filter tersedia, seperti filter sederhana, pencocokan kosong, filter DateGroup, filter khusus, filter dinamis, dan sepuluh filter teratas.

Terapkan Filter Otomatis di XLSX - PHP

setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());
// Set active filters
$autoFilter = $spreadsheet->getActiveSheet()->getAutoFilter();
// Filter the Country column on a filter value of Germany
$autoFilter->getColumn('C')
  ->setFilterType(Column::AUTOFILTER_FILTERTYPE_FILTER)
  ->createRule()
  ->setRule(
    Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
    'Germany'
  );
// Save file
$writer = new Xlsx($spreadsheet);
$writer->save('Filter.xlsx');

Mesin Perhitungan Rumus

Untuk melakukan perhitungan tingkat dasar hingga lanjutan dalam spreadsheet bisnis Anda, Anda dapat dengan mudah membangkitkan mesin kalkulasi rumus PHPSpreadsheet, dan sisanya akan mengurus sisanya.

Pengurai rumus PHPSpreadsheet dapat secara otomatis menyesuaikan rumus saat baris/kolom disisipkan/dihapus. Rumus Microsoft Excel diubah menjadi kode PHP sebelum dieksekusi. Untuk meningkatkan kinerja, cache perhitungan digunakan untuk menyimpan hasil rumus. Demikian pula, setiap lembar kerja individu dikelola oleh cache terpisah.

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. Open application/config/config.php file and set your vendor directory path. $config['composer_autoload'] = 'vendor/autoload.php';
4. Use phpspreadsheet the library inside your controller.

The following SQL query creates a

<?php 

$route['user/import']      = 'Upload/import';

?>

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

<?php 

$route['user/import']      = 'Upload/import';

?>

<?php 

$route['user/import']      = 'Upload/import';

?>

Create a model file named User_model.php inside the

<?php 

$route['user/import']      = 'Upload/import';

?>

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

class User_model extends CI_Model {

	public function __construct() {
		parent::__construct();
		$this->table = 'user_info';
	}

	public function add($data) {
		$this->db->insert($this->table, $data);
		return $this->db->insert_id();
	}

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

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

	public function get($where = 0) {
		if($where) 
			$this->db->where($where);
		$query = $this->db->get($this->table);
		return $query->row();
	}

	public function get_all($where = 0, $order_by_column = 0, $order_by = 0) {
		if($where) 
			$this->db->where($where);
		if($order_by_column and $order_by) 
			$this->db->order_by($order_by_column, $order_by);
		$query = $this->db->get($this->table);
		return $query->result();
	}

	public function get_num_rows($where = 0) {
		if($where) 
			$this->db->where($where);
		$query = $this->db->get($this->table);		
		return $query->num_rows();
	}

	public function add_batch($data) {
		return $this->db->insert_batch($this->table, $data);
	}

}

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

<?php 
defined('BASEPATH') OR exit('No direct script access allowed');
date_default_timezone_set('Asia/Kolkata');
require 'vendor/autoload.php';

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

class Upload extends CI_Controller {
	
	public function __construct() {
		parent::__construct();
		// Load Model
		$this->load->model('User_model', 'user');
		$this->ip_address    = $_SERVER['REMOTE_ADDR'];
		$this->datetime 	    = date("Y-m-d H:i:s");
	}
	
	public function index() {
	    $this->load->view("index");
	}
	
	public function display() {
    	$data 	= [];
    	$data ["result"] = $this->user->get_all();
    	$this->load->view("index");
    }

	public function import() {
		$path 		= 'documents/users/';
		$json 		= [];
		$this->upload_config($path);
		if (!$this->upload->do_upload('file')) {
			$json = [
				'error_message' => showErrorMessage($this->upload->display_errors()),
			];
		} else {
			$file_data 	= $this->upload->data();
			$file_name 	= $path.$file_data['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->user->get(["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->user->add_batch($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 upload_config($path) {
		if (!is_dir($path)) 
			mkdir($path, 0777, TRUE);		
		$config['upload_path'] 		= './'.$path;		
		$config['allowed_types'] 	= 'csv|CSV|xlsx|XLSX|xls|XLS';
		$config['max_filename']	 	= '255';
		$config['encrypt_name'] 	= TRUE;
		$config['max_size'] 		= 4096; 
		$this->load->library('upload', $config);
	}
}

Create a view named index.php inside the

<?php 

$route['user/import']      = 'Upload/import';

?>

<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 a view named display.php inside the

<?php 

$route['user/import']      = 'Upload/import';

?>