How To Upload Excel File In Php

While working on spider web applications, sometimes we come across a situation where we demand to import a CSV or Excel file into the database. It'due south a preferred way of importing big data in the database rather than entering it one by one. Adding large records programmatically saves a ton of time.

To achieve such tasks, you lot demand to write a program that can read data from spreadsheets. These records can then be inserted into the database.

PhpSpreadsheet is the library that helps you lot read the CSV or Excel file. The library provides support for reading and writing different types of file formats. Below is the screenshot of supported file formats.

Before I came to know this library, I was using the fgetcsv method for reading the CSV file. And for the Excel files, I needed to convert Excel to CSV starting time and then read it using the fgetcsv() part.

PhpSpreadsheet simplifies this task for developers. Using PhpSpreadsheet, it is easier to handle the data from CSV and Excel files. Plus, you don't need to catechumen Excel to CSV, the library directly reads the Excel file.

That being said, allow'southward take a wait at how to read CSV and Excel files using PhpSpreadsheet.

Installation of PhpSpreadsheet Library

For the installation of this library, I recommend using Composer. Open the terminal in your project root directory and run the command:

composer crave phpoffice/phpspreadsheet

In the side by side role of the tutorial, I am going to read CSV/Excel files and insert their data into the database. For this, I created dummy files that contain fake data as follows:

To store these records, I am creating a table 'users' in the database.

CREATE TABLE `users` (  `id` int(11) NOT Cypher AUTO_INCREMENT,  `proper noun` varchar(255) DEFAULT Zippo,  `electronic mail` varchar(255) DEFAULT Cipher,  `visitor` varchar(255) DEFAULT Nix,  PRIMARY Cardinal (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

For the sake of the tutorial, I am using the above dummy data. The user should change the table construction as per their dataset.

Next, create a config.php and add together a lawmaking for database connexion in information technology.

config.php

<?php $db_host = 'DB_HOST'; $db_username = 'DB_USERNAME'; $db_password = 'DB_PASSWORD'; $db_name = 'DB_NAME';   $db = new mysqli($db_host, $db_username, $db_password, $db_name);   if($db->connect_error){     die("Unable to connect database: " . $db->connect_error); }

Brand sure to supplant the placeholders with the actual values.

Read CSV/Excel File in PHP

Let'due south do the actual coding. First, create a class that will accept a file input and submit button. The user uploads their CSV/Excel file and hits the submit push button. On form submission, the entries of the uploaded file should be inserted into the 'users' tabular array.

<form method="mail" enctype="multipart/course-information">     <input type="file" name="file" />     <p><push type="submit" name="submit">Submit</button></p> </grade>

Adjacent, the code which will execute on the grade submission will be as follows.

<?php require_once 'vendor/autoload.php'; require_once 'config.php';   use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Reader\Csv; utilise PhpOffice\PhpSpreadsheet\Reader\Xlsx;   if (isset($_POST['submit'])) {       $file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/10-csv', 'text/x-csv', 'text/csv', 'application/csv', 'awarding/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');           if(isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {               $arr_file = explode('.', $_FILES['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($_FILES['file']['tmp_name']);           $sheetData = $spreadsheet->getActiveSheet()->toArray();           if (!empty($sheetData)) {             for ($i=1; $i<count($sheetData); $i++) { //skipping showtime row                 $proper name = $sheetData[$i][0];                 $email = $sheetData[$i][one];                 $company = $sheetData[$i][ii];                  $db->query("INSERT INTO USERS(proper noun, email, company) VALUES('$name', '$email', '$company')");             }         }         echo "Records inserted successfully.";     } else {         echo "Upload only CSV or Excel file.";     } } ?>

In the above code, I am finding the type of file whether it is CSV or Excel. And then I am reading the file using the functions provided for these file formats. You can read more than nearly this on official documentation here.

I hope you understand how to read CSV and Excel files using PhpSpreadsheet. If you want to write to the spreadsheet then bank check out the commodity Exporting MySQL Database Data to Excel/CSV Using PHP.