PHP使用PHPexcel导入导出数据的方法-PHP源码

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

导入数据：

&＃39;;
  $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 &＃39;highestRow=&＃39;.$highestRow;
  echo "
";
  $highestColumn = $objWorksheet->getHighestColumn();
  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
  echo &＃39;highestColumnIndex=&＃39;.$highestColumnIndex;
  echo "
";
  $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(
     &＃39;word1&＃39;=>"$strs[0]",
     &＃39;word2&＃39;=>"$strs[1]",
     &＃39;word3&＃39;=>"$strs[2]",
     &＃39;word4&＃39;=>"$strs[3]",
    );
    //在这儿，你可以连接，你的数据库，写入数据库了
    print_r($info);
    echo &＃39;
&＃39;;
  }
?>

导出数据：

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

private function _export_data($data = array())
{
 error_reporting(E_ALL); //开启错误
 set_time_limit(0); //脚本不超时
 date_default_timezone_set(&＃39;Europe/London&＃39;); //设置时间
 /** Include path **/
 set_include_path(FCPATH.APPPATH.&＃39;/libraries/Classes/&＃39;);//设置环境变量
 // Create new PHPExcel object
 Include &＃39;PHPExcel.php&＃39;;
 $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(&＃39;A&＃39;,&＃39;B&＃39;,&＃39;C&＃39;,&＃39;D&＃39;,&＃39;E&＃39;,&＃39;F&＃39;,&＃39;G&＃39;,&＃39;H&＃39;,&＃39;I&＃39;,&＃39;J&＃39;,&＃39;K&＃39;,&＃39;L&＃39;,&＃39;M&＃39;,&＃39;N&＃39;,&＃39;O&＃39;,&＃39;P&＃39;,&＃39;Q&＃39;,&＃39;R&＃39;,&＃39;S&＃39;,&＃39;T&＃39;,&＃39;U&＃39;,&＃39;V&＃39;,&＃39;W&＃39;,&＃39;X&＃39;,&＃39;Y&＃39;,&＃39;Z&＃39;);    
 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(&＃39;Y-m-d&＃39;,time());  
 // Rename worksheet
 $objPHPExcel->getActiveSheet()->setTitle($date);
 $objPHPExcel->setActiveSheetIndex(0);
 // Redirect output to a client&＃39;s web browser (Excel2007)
 header(&＃39;Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&＃39;);
 header(&＃39;Content-Disposition: attachment;filename="&＃39;.$date.&＃39;.xlsx"&＃39;);
 header(&＃39;Cache-Control: max-age=0&＃39;);
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &＃39;Excel2007&＃39;);
 $objWriter->save(&＃39;php://output&＃39;);
 exit;
}

直接上代码：

public function export_data($data = array())
{
 # code...
 include_once(APP_PATH.&＃39;Tools/PHPExcel/Classes/PHPExcel/Writer/IWriter.php&＃39;) ;
 include_once(APP_PATH.&＃39;Tools/PHPExcel/Classes/PHPExcel/Writer/Excel5.php&＃39;) ;
 include_once(APP_PATH.&＃39;Tools/PHPExcel/Classes/PHPExcel.php&＃39;) ;
 include_once(APP_PATH.&＃39;Tools/PHPExcel/Classes/PHPExcel/IOFactory.php&＃39;) ;
 $obj_phpexcel = new PHPExcel();
 $obj_phpexcel->getActiveSheet()->setCellValue(&＃39;a1&＃39;,&＃39;Key&＃39;);
 $obj_phpexcel->getActiveSheet()->setCellValue(&＃39;b1&＃39;,&＃39;Value&＃39;); 
 if($data){
  $i =2;
  foreach ($data as $key => $value) {
  # code...
  $obj_phpexcel->getActiveSheet()->setCellValue(&＃39;a&＃39;.$i,$value);
  $i++;
  }
 } 
 $obj_Writer = PHPExcel_IOFactory::createWriter($obj_phpexcel,&＃39;Excel5&＃39;);
 $filename = "outexcel.xls";
 header("Content-Type: application/force-download"); 
 header("Content-Type: application/octet-stream"); 
 header("Content-Type: application/download"); 
 header(&＃39;Content-Disposition:inline;filename="&＃39;.$filename.&＃39;"&＃39;); 
 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(&＃39;php://output&＃39;); 
}