How to use PHPExcel to batch export data into excel tables

The following editor will bring you an article on how to use PHPExcel to batch export data into excel tables (a must-read). The editor thinks it’s pretty good, so I’ll share it with you now and give it as a reference. Let’s follow the editor and take a look.

First you need to download the PHPExecel class file. For help documents, please refer to the PHPExcel Chinese Help Manual|How to use PHPExcel.

The example below is a simple batch export data to excel that I wrote myself

The front page is relatively simple, that is A hyperlink jumps to the processing page. The hyperlink can also be followed by some parameters (depending on the requirements)!

<a href="./Process1.php" rel="external nofollow" >导出excel表格</a>

Backend Process.php page

/**
* 批量导出数据
* @param $arr 从数据库查询出来，即要导出的数据
*  $name excel表歌名
*/
function expExcel($arr,$name){
 
 require_once 'PHPExcel.php';
 //实例化
 $objPHPExcel = new PHPExcel();
 /*右键属性所显示的信息*/
  $objPHPExcel->getProperties()->setCreator("zxf")  //作者
       ->setLastModifiedBy("zxf")  //最后一次保存者
       ->setTitle('数据EXCEL导出')  //标题
       ->setSubject('数据EXCEL导出') //主题
       ->setDescription('导出数据')  //描述
       ->setKeywords("excel")   //标记
       ->setCategory("result file");  //类别


 //设置当前的表格 
 $objPHPExcel->setActiveSheetIndex(0);
 // 设置表格第一行显示内容
 $objPHPExcel->getActiveSheet()
  ->setCellValue('A1', '业主姓名')
  ->setCellValue('B1', '密码')
  ->setCellValue('C1', '手机号码')
  ->setCellValue('D1', '地址')
  //设置第一行为红色字体
  ->getStyle('A1:D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

 $key = 1;
 /*以下就是对处理Excel里的数据，横着取数据*/
 foreach($arr as $v){

 //设置循环从第二行开始
 $key++;
  $objPHPExcel->getActiveSheet()

     //Excel的第A列，name是你查出数组的键值字段，下面以此类推
     ->setCellValue('A'.$key, $v['name']) 
     ->setCellValue('B'.$key, $v['pwd'])
     ->setCellValue('C'.$key, $v['phone'])
     ->setCellValue('D'.$key, $v['address']);

 }
 //设置当前的表格 
 $objPHPExcel->setActiveSheetIndex(0);
　　 ob_end_clean();  //清除缓冲区,避免乱码
  header('Content-Type: application/vnd.ms-excel'); //文件类型
  header('Content-Disposition: attachment;filename="'.$name.'.xls"'); //文件名
  header('Cache-Control: max-age=0');
  header('Content-Type: text/html; charset=utf-8'); //编码
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel 2003
  $objWriter->save('php://output'); 
  exit;

}

/***********调用**********************/
header("Content-type:text/html;charset=utf-8");

//链接数据库
$link = @mysql_connect('localhost','root','') or die('连接数据库失败');
mysql_select_db('test',$link);
mysql_query('set names utf8');

//先获取数据
$sql = "select * from house";
$res = mysql_query($sql);
$arr = array();
//把$res=>$arr,把结果集内容转移到一个数组中
while ($row = mysql_fetch_assoc($res)){
 $arr[] = $row;
}

//excel表格名
$name = "用户表";

//调用
expExcel($arr,$name)

This completes the use of PHPExcel to export data. For information on using PHPExcel to import data into the database, please refer to Using PHPExcel to batch upload data to the database.

The above is the detailed content of How to use PHPExcel to batch export data into excel tables. For more information, please follow other related articles on the PHP Chinese website!