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

前端技术 2023/09/06 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.phpstudy.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程序设计有所帮助。

本文地址：https://www.stayed.cn/item/16352

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案