PHP使用PHPexcel导入导出数据的方法
本文实例讲述了PHP使用PHPexcel导入导出数据的方法。分享给大家供大家参考，具体如下：
导入数据：
<?php
error_reporting(E_ALL);//开启错误
set_time_limit(0);//脚本不超时
date_default_timezone_set('Europe/London');//设置时间
/**Includepath**/
set_include_path(get_include_path().PATH_SEPARATOR.'https://www.nhooo.com/../Classes/');//设置环境变量
/**PHPExcel_IOFactory*/
include'PHPExcel/IOFactory.php';
//$inputFileType='Excel5';//这个是读xls的
$inputFileType='Excel2007';//这个是计xlsx的
//$inputFileName='./sampleData/example2.xls';
$inputFileName='./sampleData/book.xlsx';
echo'Loadingfile',pathinfo($inputFileName,PATHINFO_BASENAME),'usingIOFactorywithadefinedreadertypeof',$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']);）
privatefunction_export_data($data=array())
{
error_reporting(E_ALL);//开启错误
set_time_limit(0);//脚本不超时
date_default_timezone_set('Europe/London');//设置时间
/**Includepath**/
set_include_path(FCPATH.APPPATH.'/libraries/Classes/');//设置环境变量
//CreatenewPHPExcelobject
Include'PHPExcel.php';
$objPHPExcel=newPHPExcel();
//Setdocumentproperties
$objPHPExcel->getProperties()->setCreator("MaartenBalliauw")
->setLastModifiedBy("MaartenBalliauw")
->setTitle("Office2007XLSXTestDocument")
->setSubject("Office2007XLSXTestDocument")
->setDescription("TestdocumentforOffice2007XLSX,generatedusingPHPclasses.")
->setKeywords("office2007openxmlphp")
->setCategory("Testresultfile");
//Addsomedata
$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($dataas$key=>$value){
$newobj=$objPHPExcel->setActiveSheetIndex(0);
$j=0;
foreach($valueas$k=>$val){
$index=$letter[$j]."$i";
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($index,$val);
$j++;
}
$i++;
}
}
$date=date('Y-m-d',time());
//Renameworksheet
$objPHPExcel->getActiveSheet()->setTitle($date);
$objPHPExcel->setActiveSheetIndex(0);
//Redirectoutputtoaclient'swebbrowser(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;
}
直接上代码：
publicfunctionexport_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=newPHPExcel();
$obj_phpexcel->getActiveSheet()->setCellValue('a1','Key');
$obj_phpexcel->getActiveSheet()->setCellValue('b1','Value');
if($data){
$i=2;
foreach($dataas$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,dMYH:i:s")."GMT");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Pragma:no-cache");
$obj_Writer->save('php://output');
}
希望本文所述对大家php程序设计有所帮助。