PHP使用PHPexcel实现数据的导入与导出操作

本篇文章主要介绍php使用phpexcel实现数据的导入与导出操作，感兴趣的朋友参考下，希望对大家有所帮助。

导入数据：

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

导出数据：

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免费学习笔记（深入）”；

网趣网上购物系统旗舰版

网趣网上购物系统支持PC电脑版+手机版+APP，数据一站式更新，支持微信支付与支付宝支付接口，是专业的网上商城系统，网趣商城系统支持淘宝数据包导入，实现与淘宝同步更新!支持上传图片水印设置、图片批量上传功能，同时支持订单二次编辑以及多级分类隐藏等实用功能，新版增加商品大图浏览与列表显示功能，使分类浏览更方便，支持最新的支付宝即时到帐接口。

下载

相关推荐：

php上传进度条详细解读（推荐）

php操作日期及字符串精确计算年龄

PHP登录功能如何编写