PHP使用PHPexcel導入導出數據的方法

2020-03-22 20:32:07

字體：大中小

來源：轉載

供稿：網友

本文實例講述了PHP使用PHPexcel導入導出數據的方法。分享給大家供大家參考，具體如下：導入數據：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.phpstudy.net/../Classes/');//設置html' target='_blank'>環境變量/** 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程序設計有所幫助。PHP教程

鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。

上一篇：深入淺出php socket編程

下一篇：詳解PHP實現異步調用的4種方法