Comment utiliser phpexcel pour lire les données et les insérer dans la base de données?

Afin de lire les données à partir de microsoft excel 2007 par codeigniter il suffit de créer une fonction d'assistance excel_helper.php et ajoutez la ligne suivante dans:

      require_once APPPATH.'libraries/phpexcel/PHPExcel.php';
require_once APPPATH.'libraries/phpexcel/PHPExcel/IOFactory.php';
in controller add the following code to read spread sheet by active sheet
//initialize php excel first  
ob_end_clean();
//define cachemethod
$cacheMethod   = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '20MB');
//set php excel settings
PHPExcel_Settings::setCacheStorageMethod(
$cacheMethod,$cacheSettings
);
$arrayLabel = array("A","B","C","D","E");
//=== set object reader
$objectReader = PHPExcel_IOFactory::createReader('Excel2007');
$objectReader->setReadDataOnly(true);
$objPHPExcel = $objectReader->load("./forms/test.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');
$starting = 1;
$end      = 3;
for($i = $starting;$i<=$end; $i++)
{
for($j=0;$j<count($arrayLabel);$j++)
{
//== display each cell value
echo $objWorksheet->getCell($arrayLabel[$j].$i)->getValue();
}
}
//or dump data
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);
//see also the following link
http://blog.mayflower.de/561-Import-and-export-data-using-PHPExcel.html
----------- import in another style around 5000 records ------
$this->benchmark->mark('code_start');
//=== change php ini limits. =====
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize ' => '50MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
//==== create excel object of reader
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
//$objReader->setReadDataOnly(true);
//==== load forms tashkil where the file exists
$objPHPExcel = $objReader->load("./forms/5000records.xlsx");
//==== set active sheet to read data
$worksheet  = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');
$highestRow         = $worksheet->getHighestRow(); //e.g. 10
$highestColumn      = $worksheet->getHighestColumn(); //e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns          = ord($highestColumn) - 64;
$worksheetTitle     = $worksheet->getTitle();
echo "<br>The worksheet ".$worksheetTitle." has ";
echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
echo ' and ' . $highestRow . ' row.';
echo '<br>Data: <table border="1"><tr>';
//----- loop from all rows -----
for ($row = 1; $row <= $highestRow; ++ $row) 
{
echo '<tr>';
echo "<td>".$row."</td>";
//--- read each excel column for each row ----
for ($col = 0; $col < $highestColumnIndex; ++ $col) 
{
if($row == 1)
{
//show column name with the title
//----- get value ----
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
//$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo '<td>' . $val ."(".$row." X ".$col.")".'</td>';
}
else
{
if($col == 9)
{
//----- get value ----
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
//$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo '<td>zone ' . $val .'</td>';
}
else if($col == 13)
{
$date = PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow($col, $row)->getValue())->format('Y-m-d');
echo '<td>' .dateprovider($date,'dr') .'</td>';
}
else
{
//----- get value ----
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
//$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo '<td>' . $val .'</td>';
}
}
}
echo '</tr>';
}
echo '</table>';
$this->benchmark->mark('code_end');
echo "Total time:".$this->benchmark->elapsed_time('code_start', 'code_end');     
$this->load->view("error");

      if($query)
{
//try to export to excel the whole data ---
//initialize php excel first  
ob_end_clean();
//--- create php excel object ---
$objPHPExcel = new PHPExcel();
//define cachemethod
ini_set('memory_limit', '3500M');
$cacheMethod   = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '800MB');
//set php excel settings
PHPExcel_Settings::setCacheStorageMethod(
$cacheMethod,$cacheSettings
);
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
$objPHPExcel->setActiveSheetIndex(0);
//Field names in the first row
$fields = $query->list_fields();
$col = 0;
foreach ($fields as $field)
{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
//Fetching the table data
$row = 2;
foreach($query->result() as $data)
{
$col = 0;
foreach ($fields as $field)
{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
$col++;
}
$row++;
}
$objPHPExcel->setActiveSheetIndex(0);
//redirect to cleint browser
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=Provinces.xlsx');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
}

Ici est très récente réponse à cette question par le fichier: 07reader.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** Include PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';
if (!file_exists("05featuredemo.xlsx")) {
exit("Please run 05featuredemo.php first." . EOL);
}
echo date('H:i:s') , " Load from Excel2007 file" , EOL;
$callStartTime = microtime(true);
$objPHPExcel = PHPExcel_IOFactory::load("05featuredemo.xlsx");
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo 'Call time to read Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
//Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
//Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
//Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
//Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;