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程序设计有所帮助。
上一篇: 史上最省事的懒人健身法 上班族赶紧学起来
下一篇: 瑜伽修身养性 盘点最适合男性练的瑜伽招式
推荐阅读