How to Import Data from Excel Using PHP with phpoffice/phpspreadsheet

Technology and Entertainment > How to Import Data from Excel Using PHP with phpoffice/phpspreadsheet
06/08/2024 10:00

701

phpoffice/phpspreadsheet is a popular PHP library used for working with Excel files, including creating new files, reading data from existing files, and editing various data within Excel files.

Steps for Importing Data

1. Install the Library

Composer: The easiest way is to use Composer to install this library. Simply run the following command in the terminal at your project directory:

composer require phpoffice/phpspreadsheet

2. Create a PHP File

Create an index_import.php file as a form for uploading the Excel file you want to load for reading data.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Import Excel File Data with PHP</title>

</head>
<body>

    <form action='importData.php' method='post' enctype='multipart/form-data'>

        <table>
            <tr>
                <td>
                    <input type='file' class='form-control' name='file' id='fileInput' />
                </td>
                <td>
                    <input type='submit' class='btn btn-primary mb-3' name='importSubmit' value='Click to import data'>
                </td>
            </tr>
        </table>
    </form>

</body>
</html>

3. Write the importData.php File

Create an index_import.php file as a form for uploading the Excel file you want to load for reading data.

<?php 

require_once 'vendor/autoload.php'; 
use PhpOffice\PhpSpreadsheet\Reader\Xlsx; 
 
if(isset($_POST['importSubmit'])){ 
     
    // Allowed type
    $excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); 
     
    // บล็อกโค้ดนี้ตรวจสอบว่ามีการ Submit ฟอร์มที่มีปุ่มชื่อ importSubmit หรือไม่
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $excelMimes)){ 
         
        // is file is exist
        if(is_uploaded_file($_FILES['file']['tmp_name'])){ 
            $reader = new Xlsx(); 
            $spreadsheet = $reader->load($_FILES['file']['tmp_name']); 
            $worksheet = $spreadsheet->getActiveSheet();  
            $worksheet_arr = $worksheet->toArray(); 
 
            // Remove header row 
            unset($worksheet_arr[0]); 
 
            foreach($worksheet_arr as $row){ 
                $first_name = $row[0]; 
                $last_name = $row[1]; 
                $email = $row[2]; 
                $phone = $row[3]; 
                $status = $row[4]; 

                echo $first_name.'<br>';
                echo $last_name.'<br>';
                echo $email.'<br>';
                echo $phone.'<br>';
                echo $status.'<br>';
         
            } 
             
        }

    }
} 
 
// Redirect to the listing page 
header("Location: index_import.php".$qstring); 
 
?>

Code Explanation

require 'vendor/autoload.php';: This line is used to load the autoload.php file, which helps us use various classes in the phpoffice/phpspreadsheet library.
Using Namespace use PhpOffice\PhpSpreadsheet\Reader\Xlsx; specifies the use of the Xlsx class from the PhpSpreadsheet library directly, making the code shorter and easier to read.

With this, you can import data from an Excel file for further use, whether it is importing into a database or performing various validations, etc.

Please rate your satisfaction with this article

Post a Comment
Your email address will not be displayed to others. Required fields are marked *

How to Import Data from Excel Using PHP with phpoffice/phpspreadsheet

1. Install the Library

2. Create a PHP File

3. Write the importData.php File

Code Explanation

Recommended Content