Last updated on December 15th, 2022 at 03:56 pm
Many webmasters face issues while reading Microsoft excel spreadsheet data and insert them into the mysql database. Here is simple script that can read the excel file data and display it on the page. User also has the ability to validate the details and then using a submit button add the data from excel to MySQL database using php.
The below tutorial contains database connection details along with Excel file name. Also please make sure to download/install PHPSpreadsheet, We are using this package to get data from Spreadsheet.
Table of Contents
- Installing PHP Composer first time
- Install PHPSpreadsheet
- Common Error
- Load Excel File
- Read Rows To An Array
- Print Rows From Sheet
- Connect to MySQL
- Add Excel Data to MySQL
- Code with MySQL Data Insert
For easy installation of PhpSpreadsheet you may have to use Composer. Don’t worry if you are new to composer and have never installed it. I will give you step by step instruction on that below.
Installing PHP Composer first time
If you are not using composer and dealing with it for the first time its better to install it to a specific directory and then configure PHPSpreadsheet, we are downloading the install file in /tmp location.
cd /tmp
php -r "copy('https://getcomposer.org/installer', 'composer-setup.php');"
php -r "if (hash_file('sha384', 'composer-setup.php') === '55ce33d7678c5a611085589f1f3ddf8b3c52d662cd01d4ba75c0ee0459970c2200a51f492d557530c71c15d8dba01eae') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;"
Now that you have verified the composer setup file next step is to install it. For example I have created a directory under /var/www/mysite with the name bin_composer_installed_location. Now go back to /tmp where you have the composer installation file and run this command.
cd /tmp
php composer-setup.php --install-dir=/var/www/mysite/bin_composer_installed_location
Sample Output
All settings correct for using Composer
Downloading...
Composer (version 2.4.4) successfully installed to: /var/www/mysite/bin_composer_installed_location/composer.phar
Use it: php /var/www/mysite/bin_composer_installed_location/composer.phar
Install PHPSpreadsheet
The above command will install a file named composer.phar
/var/www/mysite/bin_composer_installed_location % ls -rlt
-rwxr-xr-x 1 root staffer 2810737 Dec 15 13:51 composer.phar
Now run the command to install the PHPSpreadsheet package
/var/www/mysite/bin_composer_installed_location % ./composer.phar require phpoffice/phpspreadsheet
Info from https://repo.packagist.org: #StandWithUkraine
Using version ^1.25 for phpoffice/phpspreadsheet
./composer.json has been created
Running composer update phpoffice/phpspreadsheet
Loading composer repositories with package information
Updating dependencies
Lock file operations: 10 installs, 0 updates, 0 removals
- Locking ezyang/htmlpurifier (v4.16.0)
- Locking maennchen/zipstream-php (v2.4.0)
- Locking markbaker/complex (3.0.2)
- Locking markbaker/matrix (3.0.1)
- Locking myclabs/php-enum (1.8.4)
- Locking phpoffice/phpspreadsheet (1.25.2)
- Locking psr/http-client (1.0.1)
- Locking psr/http-factory (1.0.1)
- Locking psr/http-message (1.0.1)
- Locking psr/simple-cache (3.0.0)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 10 installs, 0 updates, 0 removals
- Downloading myclabs/php-enum (1.8.4)
- Downloading psr/simple-cache (3.0.0)
- Downloading psr/http-message (1.0.1)
- Downloading psr/http-factory (1.0.1)
- Downloading psr/http-client (1.0.1)
- Downloading markbaker/matrix (3.0.1)
- Downloading markbaker/complex (3.0.2)
- Downloading maennchen/zipstream-php (v2.4.0)
- Downloading ezyang/htmlpurifier (v4.16.0)
- Downloading phpoffice/phpspreadsheet (1.25.2)
- Installing myclabs/php-enum (1.8.4): Extracting archive
- Installing psr/simple-cache (3.0.0): Extracting archive
- Installing psr/http-message (1.0.1): Extracting archive
- Installing psr/http-factory (1.0.1): Extracting archive
- Installing psr/http-client (1.0.1): Extracting archive
- Installing markbaker/matrix (3.0.1): Extracting archive
- Installing markbaker/complex (3.0.2): Extracting archive
- Installing maennchen/zipstream-php (v2.4.0): Extracting archive
- Installing ezyang/htmlpurifier (v4.16.0): Extracting archive
- Installing phpoffice/phpspreadsheet (1.25.2): Extracting archive
5 package suggestions were added by new dependencies, use `composer suggest` to see details.
Generating autoload files
2 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
No security vulnerability advisories found
Common Error
If you are getting error similar to
In PackageDiscoveryTrait.php line 313:
Package phpoffice/phpspreadsheet has requirements incompatible with your PHP version, PHP extensions and Composer version:
- phpoffice/phpspreadsheet 1.25.2 requires ext-zip * but it is not present.
To fix this error install php-zip
sudo apt install php-zip
There is another way to overcome this error using –ignore-platform-reqs but I would recommend not to do this as it will break some of the functionalities of PHPSpreadsheet.
./composer.phar require phpoffice/phpspreadsheet --ignore-platform-reqs
Note: –ignore-platform-reqs: ignore all platform requirements (php, hhvm, lib-* and ext-*) and force the installation even if the local machine does not fulfill these
Load Excel File
Its pretty straight forward to read excel, lets assume my Excel name is hello world.xlsx
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$inputFileName = 'hello world.xlsx';
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);
The above example assumes that it has only one sheet.
If you have multiple sheet then retrieve the worksheet by using getSheetByName(‘<SHEET_NAME>’)
// Retrieve the worksheet called 'Worksheet A'
$spreadsheet->getSheetByName('Worksheet A');
Read Rows To An Array
To read rows to an array we just have to call toArray() as show
$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();
print_r($rows);
Sample Output (Saved file as read_spreadsheet.php)
% php read_spreadsheet.php
Array
(
[0] => Array
(
[0] => Scott
[1] => Shane
[2] => John
)
[1] => Array
(
[0] => USA
[1] => Australia
[2] => India
)
[2] => Array
(
[0] => IL
[1] => Melbourne
[2] => Bangalore
)
)
Print Rows From Sheet
If you have header in the row for example the first row says type of data then you can either print it or unset it
Without Header
unset($rows[0]);
$i=1;
foreach ($rows as $r) {
// process element here;
// access column by index
echo "Row ".$i.")".$r[0].",".$r[1].",".$r[2]." \n";
$i++;
}
This will print output as (We are removing the first row using unset statement). If you want the header to be intact just remove the line which unset it.
Row 1)Scott,Shane,John
Row 2)USA,Australia,India
Row 3)IL,Melbourne,Bangalore
Complete Code
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$inputFileName = 'hello world.xlsx';
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();
print_r($rows);
unset($rows[0]);
$i=1;
foreach ($rows as $r) {
echo "Row ".$i.")".$r[0].",".$r[1].",".$r[2]." \n";
$i++;
}
?>
Also refer to excel spreadsheet related scripts posted before,
Export MySQL to CSV (Excel) using php
Connect to MySQL
Now that we have the data handy read from Spreadsheet, we need to connect to your MySQL database and add the data. We are using procedural style, name it as connect.php , make sure to modify connection string according to your use case. I have created a database with name mydatabase for demo purpose
<?php
$host = "localhost";
$username = "root";
$password = "yourpassword";
$database = "mydatabase";
//Make your connection to database
$con = mysqli_connect($host,$username,$password);
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
else
{
echo "Connected";
}
?>
Now that we have the database ready insert a table, for demo I created 3 rows to match the spreadsheet I have and table name is spreadsheet_data
mysql> CREATE TABLE spreadsheet_data (
-> ID int NOT NULL AUTO_INCREMENT,
-> header1 varchar(255),
-> header2 varchar(255),
-> header3 varchar(255),
-> PRIMARY KEY (ID)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| spreadsheet_data |
+----------------------+
1 row in set (0.00 sec)
Add Excel Data to MySQL
For this all you do is include the connect.php in your main script and replace the for loop with the one below
foreach ($rows as $r) {
echo "Row ".$i.")".$r[0].",".$r[1].",".$r[2]." \n";
$sql = "INSERT INTO spreadsheet (header1,header2,header3) VALUES ('$r[0]', '$r[1]', '$r[2]')";
if (!mysqli_query($con, $sql)) {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
$i++;
}
Code with MySQL Data Insert
Here is the complete code with the MySQL data insert. Note this is very basic script and logic of data duplication check before Insert and other details need to be added according to your requirement. I don’t have those validation steps in this script.
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
include('connect.php');
$spreadsheet = new Spreadsheet();
$inputFileName = 'hello world.xlsx';
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();
print_r($rows);
unset($rows[0]);
$i=1;
foreach ($rows as $r) {
echo "Row ".$i.")".$r[0].",".$r[1].",".$r[2]." \n";
$sql = "INSERT INTO spreadsheet (header1,header2,header3) VALUES ('$r[0]', '$r[1]', '$r[2]')";
if (!mysqli_query($con, $sql)) {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
$i++;
}
?>
admin