Home > Article > Backend Development > PHPExcel exports mysql database data_PHP tutorial
Export file using PHPExcel
First of all, take a look at the above article. The writing method is the same, but there are additional database operations in the middle, and there is excel browser output at the end.
Database code (the database configuration file is completed by itself)
<!--?php /*db.php*/ require dirname(__FILE__)./dbconfig.php; class db{ public $conn = null; public function __construct($config){ $this--->conn=mysql_connect($config['host'],$config['username'],$config['password']) or die(mysql_error()); mysql_select_db($config['database'],$this->conn) or die(mysql_error()); mysql_query(set names .$config['charset']) or die(mysql_error()); } public function getResult($sql){ $resource = mysql_query($sql,$this->conn) or die(mysql_error()); $res = array(); while(($row=mysql_fetch_assoc($resource))!=false){ $res[] = $row; } return $res; } public function getUserinfo(){ $sql = 。。。; $res = self::getResult($sql); return $res; } }
Database export code:
<!--?php $dir = dirname(__FILE__); require $dir./db.php; require $dir./PHPExcel.php; $db = new db($phpexcel); $objPHPExcel = new PHPExcel(); for($i=0; $i<3; $i++){ if($i-->0){ $objPHPExcel->createSheet(); } $objPHPExcel->setActiveSheetIndex($i); $objSheet = $objPHPExcel->getActiveSheet(); $data = $db->getUserinfo(); $objSheet->setCellValue(A1,编号)->setCellValue(B1,登陆名) ->setCellValue(C1,昵称)->setCellValue(D1,电子邮箱) ->setCellValue(E1,学校)->setCellValue(F1,最后登陆时间); $j = 2; foreach ($data as $key => $value) { # code... $objSheet->setCellValue(A.$j,$value['id'])->setCellValue(B.$j,$value['user_login']) ->setCellValue(C.$j,$value['user_nicename'])->setCellValue(D.$j,$value['user_email']) ->setCellValue(E.$j,$value['sch_name'])->setCellValue(F.$j,$value['last_login_time']); $j++; } } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,Excel5); // $objWriter->save($dir.'/export.xls'); //生成excel文件 browser_export(Excel5,browser_excel03.xls); //浏览器输出 $objWriter->save(php://output); function browser_export($type, $filename){ if($type == Excel5){ header('Content-Type: application/vnd.ms-excel'); //excel2003 }else{ header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007 } header('Content-Disposition: attachment;filename='.$filename.''); header('Cache-Control: max-age=0'); }