PHPExcel กับ Reader การอ่านไฟล์ Excel (.xls,.xlsx) และการนำเข้าฐานข้อมูล MySQL

<?php

/** PHPExcel */
require_once 'Classes/PHPExcel.php';

/** PHPExcel_IOFactory - Reader */
include 'Classes/PHPExcel/IOFactory.php';


$inputFileName = "myData.xls";  
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);  
$objReader = PHPExcel_IOFactory::createReader($inputFileType);  
$objReader->setReadDataOnly(true);  
$objPHPExcel = $objReader->load($inputFileName);  

/*
// for No header
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();

$r = -1;
$namedDataArray = array();
for ($row = 1; $row <= $highestRow; ++$row) {
    $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
    if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
        ++$r;
        $namedDataArray[$r] = $dataRow[$row];
    }
}
*/

$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();

$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];

$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
    $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
    if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
        ++$r;
        foreach($headingsArray as $columnKey => $columnHeading) {
            $namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
        }
    }
}

//echo '<pre>';
//var_dump($namedDataArray);
//echo '</pre><hr />';

?>
<table width="500" border="1">
  <tr>
    <td>CustomerID</td>
    <td>Name</td>
    <td>Email</td>
    <td>CountryCode</td>
    <td>Budget</td>
    <td>Used</td>
  </tr>
<?php
foreach ($namedDataArray as $result) {
?>
	  <tr>
		<td><?php echo $result["CustomerID"];?></td>
		<td><?php echo $result["Name"];?></td>
		<td><?php echo $result["Email"];?></td>
		<td><?php echo $result["CountryCode"];?></td>
		<td><?php echo $result["Budget"];?></td>
		<td><?php echo $result["Used"];?></td>
	  </tr>
<?php
}
?>
</table>

array(4) {
  [0]=>
  array(6) {
    ["CustomerID"]=>
    string(4) "C001"
    ["Name"]=>
    string(13) "Win Weerachai"
    ["Email"]=>
    string(28) "[email protected]"
    ["CountryCode"]=>
    string(2) "TH"
    ["Budget"]=>
    float(1000000)
    ["Used"]=>
    float(600000)
  }
  [1]=>
  array(6) {
    ["CustomerID"]=>
    string(4) "C002"
    ["Name"]=>
    string(11) "John  Smith"
    ["Email"]=>
    string(25) "[email protected]"
    ["CountryCode"]=>
    string(2) "EN"
    ["Budget"]=>
    float(2000000)
    ["Used"]=>
    float(800000)
  }
  [2]=>
  array(6) {
    ["CustomerID"]=>
    string(4) "C003"
    ["Name"]=>
    string(9) "Jame Born"
    ["Email"]=>
    string(24) "[email protected]"
    ["CountryCode"]=>
    string(2) "US"
    ["Budget"]=>
    float(3000000)
    ["Used"]=>
    float(600000)
  }
  [3]=>
  array(6) {
    ["CustomerID"]=>
    string(4) "C004"
    ["Name"]=>
    string(12) "Chalee Angel"
    ["Email"]=>
    string(27) "[email protected]"
    ["CountryCode"]=>
    string(2) "US"
    ["Budget"]=>
    float(4000000)
    ["Used"]=>
    float(100000)
  }
}

CREATE TABLE `customer` (
  `CustomerID` varchar(4) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Email` varchar(50) NOT NULL,
  `CountryCode` varchar(2) NOT NULL,
  `Budget` double NOT NULL,
  `Used` double NOT NULL,
  PRIMARY KEY  (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

<?php

/** PHPExcel */
require_once 'Classes/PHPExcel.php';

/** PHPExcel_IOFactory - Reader */
include 'Classes/PHPExcel/IOFactory.php';


$inputFileName = "myData.xls";  
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);  
$objReader = PHPExcel_IOFactory::createReader($inputFileType);  
$objReader->setReadDataOnly(true);  
$objPHPExcel = $objReader->load($inputFileName);  

$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();

$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];

$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
    $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
    if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
        ++$r;
        foreach($headingsArray as $columnKey => $columnHeading) {
            $namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
        }
    }
}

//echo '<pre>';
//var_dump($namedDataArray);
//echo '</pre><hr />';

//*** Connect to MySQL Database ***//
$objConnect = mysql_connect("localhost","root","root") or die(mysql_error());
$objDB = mysql_select_db("mydatabase");
$i = 0;
foreach ($namedDataArray as $result) {
		$i++;
		$strSQL = "";
		$strSQL .= "INSERT INTO customer ";
		$strSQL .= "(CustomerID,Name,Email,CountryCode,Budget,Used) ";
		$strSQL .= "VALUES ";
		$strSQL .= "('".$result["CustomerID"]."','".$result["Name"]."' ";
		$strSQL .= ",'".$result["Email"]."','".$result["CountryCode"]."' ";
		$strSQL .= ",'".$result["Budget"]."','".$result["Used"]."') ";
		mysql_query($strSQL) or die(mysql_error());
		echo "Row $i Inserted...<br>";
}
mysql_close($objConnect);
?>