Excel To MySql Database | Code From An Amateur

Presenting here the work of a day trader and above all a school dropout.

The script presented below first reads and then imports data from Excel to Mysql database, using PHPEXCEL project.

Version details: PHPEXCEL:1.7.8; Apache:2.4.4; PHP:5.4.13; Excel:2007

I dedicate my code to 3 professional programmers –

1. d’alar’cop

2. LWol

3. Bashing-om

and to my best friend, my mentor and a stark critic –  Mr. Ashwin Vyas, without their encouragement and support I would not have even dreamt of attempting to start venturing into such a complex world of programming.

My main objective was to write a script to store real time stock quotes from Excel sheet to Mysql database; to achieve that, first I decided to learn testing a test data from Excel to Mysql database.

I used PHPExcel Project developed by Mr. MarkBaker in my script to read  Excel sheet and export the same to Mysql database using Mysqli  Prepared INSERT INTO statement.

In order to ensure freshers like me get full idea of how to use PHPEXCEL, I have presented my full working script, starting from establishing connection to Mysql database till inserting data to Mysql table.

<?php  
//In case you want the script to be executed at a predefined time interval, use this   
/*header('Refresh: 30'); */  

Establishing connection to Mysql Database:

  
$mysqli = new mysqli("localhost", "(username)","(password)>","(database)");   
if (mysqli_connect_errno()) { 	 
echo mysqli_connect_error(); 	 
exit(); }   

PHPEXCEL Reader:

  
require 'c:/server/www/classes/phpexcel.php';
require_once 'c:/server/www/classes/phpexcel/iofactory.php';

$inputFileName 	= 'c:/server/users.xls';
$inputFileType	= 'Excel5'; 

$objReader		= PHPExcel_IOFactory::createReader("$inputFileType");
$objReader->setReadDataOnly(true);

$objPHPExcel	= $objReader->load("$inputFileName");
$objWorksheet	= $objPHPExcel->getActiveSheet();

$highestRow		= $objWorksheet->getHighestRow();
$highestColumn	= $objWorksheet->getHighestColumn();
$highestColumnIndex	= PHPExcel_Cell::columnIndexFromString($highestColumn);

Display Excel data on webpage:

  
echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
	echo '<tr>' . "\n";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {
	echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
}
echo '</tr>' . "\n";	
}
echo '</table>' . "\n";

Preparing to import data from Excel to Mysql Database:

for($row = 2; $row <= $highestRow; ++$row) {
for($col = 0; $col < $highestColumnIndex; ++$col) { 	
$rows[$col] = $objWorksheet->getCellByColumnAndRow($col, $row);

}

// Prepared Statement
$stmt = $mysqli->prepare("INSERT INTO users (Username,Email,Gender,Country) VALUES (?,?,?,?)");

//Prepared Statement Bound
$stmt->bind_param('ssss', $Username, $Email, $Gender, $Country);

	$Username 	="$rows[0]";
	$Email 		="$rows[1]";
	$Gender 	="$rows[2]";
	$Country 	="$rows[3]";

//Prepared Statement Executed
$stmt->execute();
printf("%s Row Inserted.\n", $stmt->affected_rows);

//Prepared Statement Closed
$stmt->close();

// If you don't want to use prepared statement, you can use this one
/* $mysqli->query("INSERT INTO users (Username,Email,Gender,Country) VALUES ('$rows[0]', '$rows[1]', '$rows[2]', '$rows[3]')"); */

}

$mysqli->close();
?>

Images of Data in Excel, webpage and Mysql Table:

Data in Excel:

Webpage and Mysql Table:

Healthy And Wealthy Coding

Healthy And Wealthy Life