本文实例讲述了PHP使用PHPexcel导入导出数据的方法。分享给大家供大家参考，具体如下：
导入数据：
<?phperror_reporting(E_ALL); //开启错误set_time_limit(0); //脚本不超时date_default_timezone_set("Europe/London"); //设置时间/** Include path **/set_include_path(get_include_path() . PATH_SEPARATOR . "//www.rzxsoft.cn/../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程序设计有所帮助。
