[예제] CodeIgniter 4, AJAX를 사용하여 PHPSpreadsheet 라이브러리를 사용하여 Excel 파일 가져오기

PhpSpreadsheet는 스프레드시트 파일을 읽고 쓰기 위한 PHP 라이브러리입니다. Excel 및 CSV를 MySQL로 가져오면 사용자 시간을 절약하고 반복 작업을 방지하는 데 도움이 됩니다.
 
소프트웨어 요구사항
PhpSpreadsheet를 사용하여 개발하려면 PHP 버전 7.2 이상입니다. PHP 확장과 같은 기타 요구 사항은 작성기에 의해 적용됩니다.
 
설치
CodeIgniter를 다운로드하고 설치합니다.
Composer를 사용하여 PhpSpreadsheet를 프로젝트에 설치합니다.

composer require phpoffice/phpspreadsheet

MySQL 데이터베이스 및 테이블 생성
다음 SQL 쿼리는 MySQL 데이터베이스에 user_info 테이블을 생성합니다.

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;

경로 생성
App/Config/Routes.php 파일을 열고 다음 줄을 추가합니다.

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

모델 생성
App/Models 폴더 안에 UserModel.php라는 모델 파일을 생성합니다.

<?php namespace AppModels;
use CodeIgniterModel;
use CodeIgniterDatabase\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);
    }
}
 
컨트롤러 생성 및 클래스 로드
User.php라는 컨트롤러를 만들고 컨트롤러 내부에서 PHPSpreadsheet 라이브러리를 사용합니다. 컨트롤러에 대해서는 다음 코드를 참조하세요.

<?php 
namespace AppControllers;

use AppControllersBaseController;
use AppModelsUserModel;

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

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 PhpOfficePhpSpreadsheetReaderCsv();
        } else {
            $reader     = new PhpOfficePhpSpreadsheetReaderXlsx();
        }
        $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 "";
    }
}
 
뷰 생성 – index.php
App/Views 디렉터리 내에 index.php라는 뷰를 만듭니다. 뷰 파일에 대해서는 다음 코드를 참조하세요.

<section class="content">
    <div class="card">
        <div class="card-header">
            <h3 class="card-title">Upload</h3>
        </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" id="btnUpload">Upload</button>
                </div>
            </form>
        </div>
    </div>
</section>

<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>
 
뷰 생성 – display.php
App/Views 디렉토리 내에 display.php라는 뷰를 생성합니다. 뷰 파일에 대해서는 다음 코드를 참조하세요.

<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>