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

本文实例讲述了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.jb51.net/../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'); 
    }

希望本文所述对大家php程序设计有所帮助。