THE DUMMY TABLE

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `test`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

ALTER TABLE `test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

A very simple test table, with only 3 fields.

DUMMY EXCEL FILE

Next, we have some dummy data in an Excel file that will be used for uploading.

THE SCRIPT

  
    

      

        Choose an Excel file:
      
      
  

THE EXPLANATION

Yep, there’s nothing special about this script. Just a true blue HTML file upload form – Please feel free to make this look pretty, or implement your own upload plugin.

GETTING THE LIBRARY

Download and install an application called Composer – Something like Git, quite a useful one for pulling libraries automatically. Then navigate to your project folder in the command prompt (or terminal) and run composer require phpoffice/phpspreadsheet.

D:\http\test>composer require phpoffice/phpspreadsheet
Using version ^1.6 for phpoffice/phpspreadsheet
./composer.json has been created
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 4 installs, 0 updates, 0 removals
  - Installing markbaker/matrix (1.1.4): Loading from cache
  - Installing markbaker/complex (1.4.7): Loading from cache
  - Installing psr/simple-cache (1.0.1): Loading from cache
  - Installing phpoffice/phpspreadsheet (1.6.0): Loading from cache
phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
Writing lock file
Generating autoload files

That’s it. If you do not want to install Composer, you can try downloading PHP SpreadSheet manually from GitHub.

THE CONFIG FILE

If you do not already have a config file in your project, please do create one now… Remember to change the database settings to your own.

THE IMPORT SCRIPT

 PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
}

// ERROR
catch (Exception $ex) {
  die("Failed to connect to database");
}

// (3) INIT PHP SPREADSHEET
require 'vendor/autoload.php';
if (pathinfo($_FILES['upexcel']['name'], PATHINFO_EXTENSION) == 'csv') {
  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} else {
  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
}
$spreadsheet = $reader->load($_FILES['upexcel']['tmp_name']);

// (4) READ DATA & IMPORT
// ! NOTE ! EXCEL MUST BE IN EXACT FORMAT!
// @TODO - Add your own data validation checks if you want.
// @TODO - Output a nicer HTML import result if you want.
$worksheet = $spreadsheet->getActiveSheet();
$sql = "INSERT INTO `test` (`name`, `email`) VALUES (?, ?)";
foreach ($worksheet->getRowIterator() as $row) {
  // Fetch data
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false);
  $data = [];
  foreach ($cellIterator as $cell) {
    $data[] = $cell->getValue();
  }

  // Insert database
  print_r($data);
  try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($data);
    // $this->pdo->lastInsertId(); // If you need the last insert ID
    echo "OK
";
  } catch (Exception $ex) {
    echo "ERROR
";
  }
  $stmt = null;
}

// (5) CLOSE DATABASE CONNECTION
if ($stmt !== null) { $stmt = null; }
if ($pdo !== null) { $pdo = null; }
?>

THE EXPLANATION

Holy cow. This looks like some script that can cause serious brain damage. But if you look through the code section-by-section, it is actually pretty straightforward.

1. First, we do some checks on the uploaded file – Make sure that it is a legit Excel file.
2. Next, we will connect to the MySQL database. I have used PDO here, and I will recommend moving away from the MySQLi extension…
3. Initiate PHP SpreadSheet, fetch the uploaded Excel file.
4. Read the SpreadSheet line-by-line, and import them into the database.
5. Finally, we close the database connection when everything is done.

Done. But this is pretty much just a working skeletal code, and plenty of work still needs to be done on it.

REFERENCES

• The official PHP SpreadSheet documentation.

EXPORT EXCEL FROM DATABASE

Need to do the opposite of exporting into Excel files? Here’s how:

3 Easy Steps to Create Excel Files With PHP & MYSQL

QUICK START

Skipped the entire tutorial? Here are a few quick steps to set up the example:

• Download and unzip into the project folder.
• Create a test database and import sql/test.sql.
• A copy of PHP SpreadSheet is not included in the zip file. Please get the latest version from their repository.
• Access 1-upload.html to start the demo.

SOURCE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
 

The post 3 Steps to Import Excel Into MySQL in PHP appeared first on Code Boxx.