本文实例讲述了PHP使用PHPexcel导入导出数据的方法。分享给大家供大家参考，具体如下：
导入数据：
PHP;">
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.cc/../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,'
'; $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 '
'; } ?>
'; $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']);）
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;
}
直接上代码：
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程序设计有所帮助。
