首页 / 网页编程 / PHP / 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 . "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 />";}?>

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"）; }