Heim >php教程 >PHP源码 >PHP使用PHPexcel导入导出数据的方法_php技巧

PHP使用PHPexcel导入导出数据的方法_php技巧

PHP中文网
PHP中文网Original
2016-05-25 17:00:18994Durchsuche

这篇文章主要介绍了PHP使用PHPexcel导入导出数据的方法,以实例形式较为详细的分析了PHP使用PHPexcel实现数据的导入与导出操作相关技巧,需要的朋友可以参考下

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

导入数据:

<?php
error_reporting(E_ALL); //开启错误
set_time_limit(0); //脚本不超时
date_default_timezone_set(&#39;Europe/London&#39;); //设置时间
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . &#39;http://www.jb51.net/../Classes/&#39;);//设置环境变量
/** PHPExcel_IOFactory */
include &#39;PHPExcel/IOFactory.php&#39;;
//$inputFileType = &#39;Excel5&#39;; //这个是读 xls的
 $inputFileType = &#39;Excel2007&#39;;//这个是计xlsx的
//$inputFileName = &#39;./sampleData/example2.xls&#39;;
$inputFileName = &#39;./sampleData/book.xlsx&#39;;
  echo &#39;Loading file &#39;,pathinfo($inputFileName,PATHINFO_BASENAME),&#39; using IOFactory with a defined reader type of &#39;,$inputFileType,&#39;<br />&#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 "<br>";
  $highestColumn = $objWorksheet->getHighestColumn();
  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
  echo &#39;highestColumnIndex=&#39;.$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(
     &#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;<br />&#39;;
  }
?>

导出数据:

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

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;); 
}

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:使用PHP发送邮件Nächster Artikel:YII 分库分表扩展(支持主从)