PHP Excel Import to MySQL using PHPExcel

Ok so if you haven’t heard there is a great project called phpexcel. Now this has many
functionality but I’ll just cover this well because I had to do it and didn’t know hehe. This is fairly easy(once you’ve figured it out)..

First we reference 2 files: PHPExcel.php and IOFactory.php. Important! Don’t touch these files unless you’re a complete expert.

1 2 3

//include the following 2 files require '../class/PHPExcel.php'; require_once '../class/PHPExcel/IOFactory.php';

Now we start we create an object from the file in this example $path is my filename that is in the server like this:
$path = "filefolder/myfile.xls";
So once we do that you can capture every detail of the file here I have Title, Highest Row, Highest Column, Highest ColumnIndex

1 2 3 4 5 6 7

$objPHPExcel = PHPExcel_IOFactory::load($path); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {      $worksheetTitle = $worksheet->getTitle();      $highestRow = $worksheet->getHighestRow(); // e.g. 10      $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'      $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); }

With that info you can get the total number of columns

1	$nrColumns = ord($highestColumn) - 64;

Now you can start displaying or saving to your db table. What I do here is show the user the worksheet title, how many columns and how many rows were imported.

1 2 3

echo "File ".$worksheetTitle." has "; echo $nrColumns . ' columns'; echo ' y ' . $highestRow . ' rows.';

Now just so the user feels warm and fuzzy I go ahead and show them the data being imported by echoing line by line.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

echo 'Data: <table width="100%" cellpadding="3" cellspacing="0"><tr>'; for ($row = 1; $row <= $highestRow; ++ $row) {    echo '<tr>';    for ($col = 0; $col < $highestColumnIndex; ++ $col) {        $cell = $worksheet->getCellByColumnAndRow($col, $row);        $val = $cell->getValue();        if($row === 1)             echo '<td style="background:#000; color:#fff;">' . $val . '</td>';        else            echo '<td>' . $val . '</td>';    } echo '</tr>'; } echo '</table>';

Ok and finally(I know you’ve been waiting for this. Here is my insert into statement.
I start at row 2 because obviously row 1 is your column headers. Note: if you don’t have headers then just use 1.

1	for ($row = 2; $row <= $highestRow; ++ $row) {

Now I create an array to hold the value of each column.

1 2 3 4 5

$val=array(); for ($col = 0; $col < $highestColumnIndex; ++ $col) {    $cell = $worksheet->getCellByColumnAndRow($col, $row);    $val[] = $cell->getValue();  }

And last but not least we insert.

1 2 3 4 5 6

$sql="insert into tablename(column1, column2, column3, column4, column5, column6) values('".$val[1] . "','" . $val[2] . "','" . $val[3]. "','" . $val[4]. "','" . $val[5]. "','" . $val[6]. "')"; //Run your mysql_query } } }

And just like that you went from excel -> mysql and a nice display for the user 

Important: you must make sure that your excel files has the same amount of columns as your insert statement, so I would recommend giving them a template to download
first.