blocks|key|625372|text|使用PHPExcel库读取Excel文件并将数据传输到数据库|type|unstyled|depth|inlineStyleRanges|entityRanges|data|625373|//++Include+PHPExcel_IOFactory
include+'PHPExcel/IOFactory.php';

$inputFileName+=+'./sampleData/example1.xls';

//++Read+your+Excel+workbook
try+{
++++$inputFileType+=+PHPExcel_IOFactory::identify($inputFileName);
++++$objReader+=+PHPExcel_IOFactory::createReader($inputFileType);
++++$objPHPExcel+=+$objReader->load($inputFileName);
}+catch(Exception+$e)+{
++++die('Error+loading+file+"'.pathinfo($inputFileName,PATHINFO_BASENAME).'":+'.$e->getMessage());
}

//++Get+worksheet+dimensions
$sheet+=+$objPHPExcel->getSheet(0);+
$highestRow+=+$sheet->getHighestRow();+
$highestColumn+=+$sheet->getHighestColumn();

//++Loop+through+each+row+of+the+worksheet+in+turn
for+($row+=+1;+$row+<=+$highestRow;+$row%2B%2B){+
++++//++Read+a+row+of+data+into+an+array
++++$rowData+=+$sheet->rangeToArray('A'+.+$row+.+':'+.+$highestColumn+.+$row,
++++++++++++++++++++++++++++++++++++NULL,
++++++++++++++++++++++++++++++++++++TRUE,
++++++++++++++++++++++++++++++++++++FALSE);
++++//++Insert+row+data+array+into+your+database+of+choice+here
}|code-block|syntax|javascript|625374|任何其他内容都会变得非常依赖于您的数据库，以及您希望在其中构建数据的方式|625375|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Using the PHPExcel library to read an Excel file and transfer the data into a database

<pre><code>// Include PHPExcel_IOFactory
include 'PHPExcel/IOFactory.php';

$inputFileName = './sampleData/example1.xls';

// Read your Excel workbook
try {
 $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
 $objReader = PHPExcel_IOFactory::createReader($inputFileType);
 $objPHPExcel = $objReader-&gt;load($inputFileName);
} catch(Exception $e) {
 die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e-&gt;getMessage());
}

// Get worksheet dimensions
$sheet = $objPHPExcel-&gt;getSheet(0); 
$highestRow = $sheet-&gt;getHighestRow(); 
$highestColumn = $sheet-&gt;getHighestColumn();

// Loop through each row of the worksheet in turn
for ($row = 1; $row &lt;= $highestRow; $row++){ 
 // Read a row of data into an array
 $rowData = $sheet-&gt;rangeToArray('A' . $row . ':' . $highestColumn . $row,
 NULL,
 TRUE,
 FALSE);
 // Insert row data array into your database of choice here
}
</code></pre>

Anything more becomes very dependent on your database, and how you want the data structured in it

blocks|key|625438|text|为了通过codeigniter从microsoft+excel+2007中读取数据，只需创建一个helper函数excel_helper.php并在中添加以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|625439|++++++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%2B%2B)
++++{

+++++++for($j=0;$j<count($arrayLabel);$j%2B%2B)
+++++++{
+++++++++++//==+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+" The+worksheet+".$worksheetTitle."+has+";
++++echo+$nrColumns+.+'+columns+(A-'+.+$highestColumn+.+')+';
++++echo+'+and+'+.+$highestRow+.+'+row.';
++++echo+' Data:+<table+border="1"><tr>';
++++//-----+loop+from+all+rows+-----
++++for+($row+=+1;+$row+<=+$highestRow;+%2B%2B+$row)+
++++{
++++++++echo+'<tr>';
++++++++echo+"<td>".$row."</td>";
++++++++//---+read+each+excel+column+for+each+row+----
++++++++for+($col+=+0;+$col+<+$highestColumnIndex;+%2B%2B+$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");|code-block|syntax|javascript|625440|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In order to read data from microsoft excel 2007 by codeigniter just create a helper function excel_helper.php and add the following in:

<pre><code> 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' =&gt; '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-&gt;setReadDataOnly(true);

 $objPHPExcel = $objectReader-&gt;load("./forms/test.xlsx");
 $objWorksheet = $objPHPExcel-&gt;setActiveSheetIndexbyName('Sheet1');

 $starting = 1;
 $end = 3;
 for($i = $starting;$i&lt;=$end; $i++)
 {

 for($j=0;$j&lt;count($arrayLabel);$j++)
 {
 //== display each cell value
 echo $objWorksheet-&gt;getCell($arrayLabel[$j].$i)-&gt;getValue();
 }
 }
 //or dump data
 $sheetData = $objPHPExcel-&gt;getActiveSheet()-&gt;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-&gt;benchmark-&gt;mark('code_start');
 //=== change php ini limits. =====
 $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
 $cacheSettings = array( ' memoryCacheSize ' =&gt; '50MB');
 PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
 //==== create excel object of reader
 $objReader = PHPExcel_IOFactory::createReader('Excel2007');
 //$objReader-&gt;setReadDataOnly(true);
 //==== load forms tashkil where the file exists
 $objPHPExcel = $objReader-&gt;load("./forms/5000records.xlsx");
 //==== set active sheet to read data
 $worksheet = $objPHPExcel-&gt;setActiveSheetIndexbyName('Sheet1');


 $highestRow = $worksheet-&gt;getHighestRow(); // e.g. 10
 $highestColumn = $worksheet-&gt;getHighestColumn(); // e.g 'F'
 $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
 $nrColumns = ord($highestColumn) - 64;
 $worksheetTitle = $worksheet-&gt;getTitle();

 echo "&lt;br&gt;The worksheet ".$worksheetTitle." has ";
 echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
 echo ' and ' . $highestRow . ' row.';
 echo '&lt;br&gt;Data: &lt;table border="1"&gt;&lt;tr&gt;';
 //----- loop from all rows -----
 for ($row = 1; $row &lt;= $highestRow; ++ $row) 
 {
 echo '&lt;tr&gt;';
 echo "&lt;td&gt;".$row."&lt;/td&gt;";
 //--- read each excel column for each row ----
 for ($col = 0; $col &lt; $highestColumnIndex; ++ $col) 
 {
 if($row == 1)
 {
 // show column name with the title
 //----- get value ----
 $cell = $worksheet-&gt;getCellByColumnAndRow($col, $row);
 $val = $cell-&gt;getValue();
 //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
 echo '&lt;td&gt;' . $val ."(".$row." X ".$col.")".'&lt;/td&gt;';
 }
 else
 {
 if($col == 9)
 {
 //----- get value ----
 $cell = $worksheet-&gt;getCellByColumnAndRow($col, $row);
 $val = $cell-&gt;getValue();
 //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
 echo '&lt;td&gt;zone ' . $val .'&lt;/td&gt;';
 }
 else if($col == 13)
 {
 $date = PHPExcel_Shared_Date::ExcelToPHPObject($worksheet-&gt;getCellByColumnAndRow($col, $row)-&gt;getValue())-&gt;format('Y-m-d');
 echo '&lt;td&gt;' .dateprovider($date,'dr') .'&lt;/td&gt;';
 }
 else
 {
 //----- get value ----
 $cell = $worksheet-&gt;getCellByColumnAndRow($col, $row);
 $val = $cell-&gt;getValue();
 //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
 echo '&lt;td&gt;' . $val .'&lt;/td&gt;';
 }
 }
 }
 echo '&lt;/tr&gt;';
 }
 echo '&lt;/table&gt;';
 $this-&gt;benchmark-&gt;mark('code_end');

 echo "Total time:".$this-&gt;benchmark-&gt;elapsed_time('code_start', 'code_end'); 
 $this-&gt;load-&gt;view("error");
</code></pre>

blocks|key|625462|text|++++++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%2B%2B;
++++++++}

++++++++//+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%2B%2B;
++++++++++++}

++++++++++++$row%2B%2B;
++++++++}

++++++++$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');
+++++}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|625463|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> 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' =&gt; '800MB');
 //set php excel settings
 PHPExcel_Settings::setCacheStorageMethod(
 $cacheMethod,$cacheSettings
 );


 $objPHPExcel-&gt;getProperties()-&gt;setTitle("export")-&gt;setDescription("none");

 $objPHPExcel-&gt;setActiveSheetIndex(0);

 // Field names in the first row
 $fields = $query-&gt;list_fields();
 $col = 0;
 foreach ($fields as $field)
 {
 $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($col, 1, $field);
 $col++;
 }

 // Fetching the table data
 $row = 2;
 foreach($query-&gt;result() as $data)
 {
 $col = 0;
 foreach ($fields as $field)
 {
 $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($col, $row, $data-&gt;$field);
 $col++;
 }

 $row++;
 }

 $objPHPExcel-&gt;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-&gt;save('php://output');
 }
</code></pre>

blocks|key|410370|text|++++if($this->mng_auth->get_language()=='en')
++++++++++{
++++++++++++++++++++$excel->getActiveSheet()->setRightToLeft(false);+
++++++++++}
++++++++++++++++else
++++++++++++++++{++
++++++++++++++++++++$excel->getActiveSheet()->setRightToLeft(true);+
++++++++++++++++}

+++++++$styleArray+=+array(
++++++++++++++++'borders'+=>+array(
++++++++++++++++++++'allborders'+=>+array(
++++++++++++++++++++++++'style'+=>+PHPExcel_Style_Border::BORDER_THIN,
++++++++++++++++++++++++++++'color'+=>+array('argb'+=>+'00000000'),
++++++++++++++++++++),
++++++++++++++++),
++++++++++++);

++++++++++//SET+property
++++++++++$objPHPExcel->getActiveSheet()->getStyle('A1:M10001')->applyFromArray($styleArray);


++++$objPHPExcel->getActiveSheet()->getStyle('A1:M10001')->getAlignment()->setWrapText(true);+


$objPHPExcel->getActiveSheet()->getStyle('A1:'.chr(65%2Bcount($fields)-1).$query->num_rows())->applyFromArray($styleArray);++

$objPHPExcel->getActiveSheet()->getStyle('A1:'.chr(65%2Bcount($fields)-1).$query->num_rows())->getAlignment()->setWrapText(true);+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|410371|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> if($this-&gt;mng_auth-&gt;get_language()=='en')
 {
 $excel-&gt;getActiveSheet()-&gt;setRightToLeft(false); 
 }
 else
 { 
 $excel-&gt;getActiveSheet()-&gt;setRightToLeft(true); 
 }

 $styleArray = array(
 'borders' =&gt; array(
 'allborders' =&gt; array(
 'style' =&gt; PHPExcel_Style_Border::BORDER_THIN,
 'color' =&gt; array('argb' =&gt; '00000000'),
 ),
 ),
 );

 //SET property
 $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1:M10001')-&gt;applyFromArray($styleArray);


 $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1:M10001')-&gt;getAlignment()-&gt;setWrapText(true); 


$objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1:'.chr(65+count($fields)-1).$query-&gt;num_rows())-&gt;applyFromArray($styleArray); 

$objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1:'.chr(65+count($fields)-1).$query-&gt;num_rows())-&gt;getAlignment()-&gt;setWrapText(true); 
</code></pre>

blocks|key|625562|text|下面是文件中对这个问题的最新答案:+07reader.php|type|unstyled|depth|inlineStyleRanges|entityRanges|data|625563|<?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('%25.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('%25.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;|code-block|syntax|javascript|625564|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a very recent answer to this question from the file: 07reader.php

<pre><code>&lt;?php


error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '&lt;br /&gt;');

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-&gt;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;
</code></pre>

blocks|key|410492|text|使用PHPExcel库，下面的代码就可以了。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|410493|require_once+dirname(__FILE__)+.+'/../Classes/PHPExcel/IOFactory.php';++++
$objReader+=+PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);+//optional

$objPHPExcel+=+$objReader->load(__DIR__.'/YourExcelFile.xlsx');
$objWorksheet+=+$objPHPExcel->getActiveSheet();

$i=1;
foreach+($objWorksheet->getRowIterator()+as+$row)+{

++++$column_A_Value+=+$objPHPExcel->getActiveSheet()->getCell("A$i")->getValue();//column+A
++++//you+can+add+your+own+columns+B,+C,+D+etc.

++++//inset+$column_A_Value+value+in+DB+query+here

++++$i%2B%2B;
}|code-block|syntax|javascript|410494|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/PHPOffice/PHPExcel^0|2|8|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Using the <a href="https://github.com/PHPOffice/PHPExcel" rel="nofollow noreferrer">PHPExcel</a> library, the following code will do.

<pre><code>require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php'; 
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader-&gt;setReadDataOnly(true); //optional

$objPHPExcel = $objReader-&gt;load(__DIR__.'/YourExcelFile.xlsx');
$objWorksheet = $objPHPExcel-&gt;getActiveSheet();

$i=1;
foreach ($objWorksheet-&gt;getRowIterator() as $row) {

 $column_A_Value = $objPHPExcel-&gt;getActiveSheet()-&gt;getCell("A$i")-&gt;getValue();//column A
 //you can add your own columns B, C, D etc.

 //inset $column_A_Value value in DB query here

 $i++;
}
</code></pre>

blocks|key|625765|text|Inci框架你可以像这样下载：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|625766|function+clubDownload($clubname)
{

++++$this->load->library("excel");

++++$object+=+new+PHPExcel();
++++$object->setActiveSheetIndex(0);
++++$this->load->model('Members_student_model');
++++$query+=+$this->db->query("SELECT+*+FROM+student+WHERE+$clubname!=''++order+by+id+desc");
++++$resultdatanew=$query->result_array();
++++$page+=+($this->uri->segment(3))+?+$this->uri->segment(3)+:+1;

++++$object->getActiveSheet()->getStyle("A1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');

++++$object->getActiveSheet()->getStyle("B1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');


++++$object->getActiveSheet()->getStyle("C1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');

++++$object->getActiveSheet()->getStyle("D1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');


++++$object->getActiveSheet()->getStyle("E1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');

++++$object->getActiveSheet()->getStyle("F1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');
++++$object->getActiveSheet()->getStyle("G1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');

++++$object->getActiveSheet()->getStyle("H1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');
++++$object->getActiveSheet()->getStyle("I1")->getFont()->setBold(true)
++++++++++++++++++++++++++++->setName('Verdana')
++++++++++++++++++++++++++++->setSize(10)
++++++++++++++++++++++++++++->getColor()->setRGB('330000');
++++$headerStyle+=+array(
++++++++++++++++'fill'+=>+array(
++++++++++++++++++++++++'type'+=>+PHPExcel_Style_Fill::FILL_SOLID,
++++++++++++++++++++++++'color'+=>+array('rgb'=>'CCE5FF'),
++++++++++++++++),
++++++++++++++++'font'+=>+array(
++++++++++++++++++++++++'bold'+=>+true,
++++++++++++++++)
++++++++);

++++$object->getActiveSheet()->getStyle('A1:'.'I1')->applyFromArray($headerStyle);
++++$table_columns+=+array("id",+"studentid",+"passport",+"lastname",+"firstname","university","commencing",$clubname,"added_date");
++++$column+=+0;
++++foreach($table_columns+as+$field)
++++{
++++$object->getActiveSheet()->setCellValueByColumnAndRow($column,+1,+$field);
++++$column%2B%2B;
++++}
++++$excel_row+=+2;




++++foreach($resultdatanew+as+$row)
++++{


++++++++++++++++$id=$row['id'];
++++++++++++++++$studentid=$row['studentid'];
++++++++++++++++$passport=$row['passport'];
++++++++++++++++$lastname=$row['last_name'];
++++++++++++++++$firstname=$row['first_name'];
++++++++++++++++$passport=$row['university'];
++++++++++++++++$commencing=$row['commencing'];
++++++++++++++++$email_id=$row['email_id'];
++++++++++++++++$added_date=$row['added_date'];


++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(0,+$excel_row,$id);

++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(1,+$excel_row,+$studentid);
++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(2,+$excel_row,+$passport);
++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(3,+$excel_row,+$lastname);
++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(4,+$excel_row,+$firstname);
++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(5,+$excel_row,+$passport);
++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(6,+$excel_row,++$commencing);
++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(7,+$excel_row,+$email_id);
++++++++++++++++$object->getActiveSheet()->setCellValueByColumnAndRow(8,+$excel_row,+$added_date);


++++++++++++++++$excel_row%2B%2B;
}

$object_writer+=+PHPExcel_IOFactory::createWriter($object,+'Excel5');
header('Content-Type:+application/vnd.ms-excel');
header('Content-Disposition:+attachment;filename="club'+.$clubname.'-'.date('Y-m-d')+.+'.xls');
$object_writer->save('php://output');|code-block|syntax|javascript|625767|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Inci framework you can do download like so:

<pre><code>function clubDownload($clubname)
{

 $this-&gt;load-&gt;library("excel");

 $object = new PHPExcel();
 $object-&gt;setActiveSheetIndex(0);
 $this-&gt;load-&gt;model('Members_student_model');
 $query = $this-&gt;db-&gt;query("SELECT * FROM student WHERE $clubname!='' order by id desc");
 $resultdatanew=$query-&gt;result_array();
 $page = ($this-&gt;uri-&gt;segment(3)) ? $this-&gt;uri-&gt;segment(3) : 1;

 $object-&gt;getActiveSheet()-&gt;getStyle("A1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');

 $object-&gt;getActiveSheet()-&gt;getStyle("B1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');


 $object-&gt;getActiveSheet()-&gt;getStyle("C1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');

 $object-&gt;getActiveSheet()-&gt;getStyle("D1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');


 $object-&gt;getActiveSheet()-&gt;getStyle("E1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');

 $object-&gt;getActiveSheet()-&gt;getStyle("F1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');
 $object-&gt;getActiveSheet()-&gt;getStyle("G1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');

 $object-&gt;getActiveSheet()-&gt;getStyle("H1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');
 $object-&gt;getActiveSheet()-&gt;getStyle("I1")-&gt;getFont()-&gt;setBold(true)
 -&gt;setName('Verdana')
 -&gt;setSize(10)
 -&gt;getColor()-&gt;setRGB('330000');
 $headerStyle = array(
 'fill' =&gt; array(
 'type' =&gt; PHPExcel_Style_Fill::FILL_SOLID,
 'color' =&gt; array('rgb'=&gt;'CCE5FF'),
 ),
 'font' =&gt; array(
 'bold' =&gt; true,
 )
 );

 $object-&gt;getActiveSheet()-&gt;getStyle('A1:'.'I1')-&gt;applyFromArray($headerStyle);
 $table_columns = array("id", "studentid", "passport", "lastname", "firstname","university","commencing",$clubname,"added_date");
 $column = 0;
 foreach($table_columns as $field)
 {
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($column, 1, $field);
 $column++;
 }
 $excel_row = 2;




 foreach($resultdatanew as $row)
 {


 $id=$row['id'];
 $studentid=$row['studentid'];
 $passport=$row['passport'];
 $lastname=$row['last_name'];
 $firstname=$row['first_name'];
 $passport=$row['university'];
 $commencing=$row['commencing'];
 $email_id=$row['email_id'];
 $added_date=$row['added_date'];


 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(0, $excel_row,$id);

 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(1, $excel_row, $studentid);
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(2, $excel_row, $passport);
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(3, $excel_row, $lastname);
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(4, $excel_row, $firstname);
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(5, $excel_row, $passport);
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(6, $excel_row, $commencing);
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(7, $excel_row, $email_id);
 $object-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow(8, $excel_row, $added_date);


 $excel_row++;
}

$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="club' .$clubname.'-'.date('Y-m-d') . '.xls');
$object_writer-&gt;save('php://output');
</code></pre>

I have a <code>php</code> application where I want to read data from excel, Insert into database and then generate pdf reports for specific users.
I searched a lot but nothing specific given about both things.

How to use phpexcel to read data and insert into database?

数据库

我有一个php应用程序，我想从excel中读取数据，插入到数据库中，然后为特定用户生成pdf报告。我搜索了很多，但没有给出关于这两个东西的具体信息。

问如何使用phpexcel读取数据并插入到数据库？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用phpexcel读取数据并插入到数据库？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用phpexcel读取数据并插入到数据库？
EN