PHP使用PHPexcel导入导出数据的方法_PHP教程

本文实例讲述了PHP使用PHPexcel 导入导出数据的方法。分享给大家供大家参考，具体如下：

导入数据：

				 
				?

									<?php

									error_reporting(E_ALL); //开启错误

									set_time_limit(0); //脚本不超时

									date_default_timezone_set('Europe/London'); //设置时间

									/** Include path **/

									set_include_path(get_include_path() . PATH_SEPARATOR . 'http://www.zzvips.com/../Classes/');//设置环境变量

									/** PHPExcel_IOFactory */

									include 'PHPExcel/IOFactory.php';

									//$inputFileType = 'Excel5'; //这个是读 xls的

									 $inputFileType = 'Excel2007';//这个是计xlsx的

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

									$inputFileName = './sampleData/book.xlsx';

									  echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';

									  $objReader = PHPExcel_IOFactory::createReader($inputFileType);

									  $objPHPExcel = $objReader->load($inputFileName);

									  /*

									  $sheet = $objPHPExcel->getSheet(0);

									  $highestRow = $sheet->getHighestRow(); //取得总行数

									  $highestColumn = $sheet->getHighestColumn(); //取得总列

									  */

									  $objWorksheet = $objPHPExcel->getActiveSheet();//取得总行数

									  $highestRow = $objWorksheet->getHighestRow();//取得总列数

									  echo 'highestRow='.$highestRow;

									  echo "<br>";

									  $highestColumn = $objWorksheet->getHighestColumn();

									  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数

									  echo 'highestColumnIndex='.$highestColumnIndex;

									  echo "<br />";

									  $headtitle=array();

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

									  {

									   $strs=array();

									   //注意highestColumnIndex的列数索引从0开始

									   for ($col = 0;$col < $highestColumnIndex;$col++)

									   {

									    $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();

									   }

									    $info = array(

									     'word1'=>"$strs[0]",

									     'word2'=>"$strs[1]",

									     'word3'=>"$strs[2]",

									     'word4'=>"$strs[3]",

									    );

									    //在这儿，你可以连接，你的数据库，写入数据库了

									    print_r($info);

									    echo '<br />';

									  }

									?>

导出数据：

（如果有特殊的字符串 = 麻烦　　str_replace(array('='),'',$val['roleName']);）

				 
				?

									private function _export_data($data = array())

									{

									 error_reporting(E_ALL); //开启错误

									 set_time_limit(0); //脚本不超时

									 date_default_timezone_set('Europe/London'); //设置时间

									 /** Include path **/

									 set_include_path(FCPATH.APPPATH.'/libraries/Classes/');//设置环境变量

									 // Create new PHPExcel object

									 Include 'PHPExcel.php';

									 $objPHPExcel = new PHPExcel();

									 // Set document properties

									 $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")

									    ->setLastModifiedBy("Maarten Balliauw")

									    ->setTitle("Office 2007 XLSX Test Document")

									    ->setSubject("Office 2007 XLSX Test Document")

									    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

									    ->setKeywords("office 2007 openxml php")

									    ->setCategory("Test result file");

									 // Add some data

									 $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');    

									 if($data){

									  $i = 1;

									  foreach ($data as $key => $value) {

									  $newobj = $objPHPExcel->setActiveSheetIndex(0);

									  $j = 0; 

									  foreach ($value as $k => $val) {

									   $index = $letter[$j]."$i";

									   $objPHPExcel->setActiveSheetIndex(0)->setCellValue($index, $val);

									   $j++;

									  }

									   $i++;

									  }

									 }   

									 $date = date('Y-m-d',time());  

									 // Rename worksheet

									 $objPHPExcel->getActiveSheet()->setTitle($date);

									 $objPHPExcel->setActiveSheetIndex(0);

									 // Redirect output to a client's web browser (Excel2007)

									 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

									 header('Content-Disposition: attachment;filename="'.$date.'.xlsx"');

									 header('Cache-Control: max-age=0');

									 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

									 $objWriter->save('php://output');

									 exit;

									}

直接上代码：

				 
				?

									public function export_data($data = array())

									{

									 # code...

									 include_once(APP_PATH.'Tools/PHPExcel/Classes/PHPExcel/Writer/IWriter.php') ;

									 include_once(APP_PATH.'Tools/PHPExcel/Classes/PHPExcel/Writer/Excel5.php') ;

									 include_once(APP_PATH.'Tools/PHPExcel/Classes/PHPExcel.php') ;

									 include_once(APP_PATH.'Tools/PHPExcel/Classes/PHPExcel/IOFactory.php') ;

									 $obj_phpexcel = new PHPExcel();

									 $obj_phpexcel->getActiveSheet()->setCellValue('a1','Key');

									 $obj_phpexcel->getActiveSheet()->setCellValue('b1','Value'); 

									 if($data){

									  $i =2;

									  foreach ($data as $key => $value) {

									  # code...

									  $obj_phpexcel->getActiveSheet()->setCellValue('a'.$i,$value);

									  $i++;

									  }

									 } 

									 $obj_Writer = PHPExcel_IOFactory::createWriter($obj_phpexcel,'Excel5');

									 $filename = "outexcel.xls";

									 header("Content-Type: application/force-download"); 

									 header("Content-Type: application/octet-stream"); 

									 header("Content-Type: application/download"); 

									 header('Content-Disposition:inline;filename="'.$filename.'"'); 

									 header("Content-Transfer-Encoding: binary"); 

									 header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 

									 header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 

									 header("Pragma: no-cache"); 

									 $obj_Writer->save('php://output'); 

									}