这篇文章主要介绍了php使用phpexcel导入导出数据的方法,以实例形式较为详细的分析了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 . 'http://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');
}