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

php  /  管理员 发布于 7年前   293

本文实例讲述了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 '
';  }?>

导出数据：

（如果有特殊的字符串 = 麻烦　　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程序设计有所帮助。

您可能感兴趣的文章:

• 使用PHPExcel实现数据批量导出为excel表格的方法(必看)
• 利用phpExcel实现Excel数据的导入导出(全步骤详细解析)
• ThinkPHP使用PHPExcel实现Excel数据导入导出完整实例
• php实现利用phpexcel导出数据
• Yii中使用PHPExcel导出Excel的方法
• PHPExcel导出2003和2007的excel文档功能示例
• Codeigniter+PHPExcel实现导出数据到Excel文件
• thinkphp3.2中实现phpexcel导出带生成图片示例
• 基于PHPExcel的常用方法总结
• PHPExcel实现表格导出功能示例【带有多个工作sheet】