欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-10-25 09:16:28
本文实例讲述了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 . '//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程序设计有所帮助。