PHP查询数据库并导出到Excel示例

  • baagee 发布于 2016-06-23 21:13:35
  • 分类:PHP
  • 1414 人围观
  • 2 人喜欢

以前测试做的PHP查询数据库数据输出到excel中,但是数据表结构丢了,只剩下PHP代码了,就这样简单记录一下吧,以便日后参考,PHP代码:

先来份简单的输出到Excel示例代码:

<?php
$dir=dirname(__FILE__);//找到当前文件目录
require $dir.'/Classes/PHPExcel.php';//引入文件
$objPHPExcel=new PHPExcel();
$objSheet=$objPHPExcel->getActiveSheet();//获得当前活动的sheet操作对象
$objSheet->setTitle('daochu');//给活动sheet起名字
$data=array(
	array('姓名','分数'),
	array('李四','12'),
	array('王五','90'),
);
//不推荐,,耗内存
$objSheet->fromArray($data);
//推荐
/*$objSheet->setCellValue('A1','姓名')->setCellValue('B1','分数');//填充数据
$objSheet->setCellValue('A2','张三')->setCellValue('B2','20');//填充数据*/
$obbjWrite=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//按照指定格式生成
$obbjWrite->save($dir.'/daochu2.xlsx');

查询数据库导出类似:

<?php
/*
	根据tutor_id分别输出到不同excel的sheet中
*/
$dir=dirname(__FILE__);//找到当前文件目录
require $dir.'/Classes/PHPExcel.php';//引入文件

$conn=mysql_connect('127.0.0.1','root','9359') or die(mysql_error());
mysql_select_db('tutor');

function getresult($sql){
	$resource=mysql_query($sql);
	$res=array();
	while (($row=mysql_fetch_assoc($resource))!=false) {
		$res[]=$row;
	}
	return $res;
}
/*通过tutor_id获取数据库的资料*/
function getByTId($tutor_id){
	$sql="SELECT * FROM `order` WHERE `tutor_id`=".$tutor_id." ORDER BY time DESC";
	return getresult($sql);
}
/*获取tutor_id*/
function getTutorId(){
	$sql="SELECT DISTINCT tutor_id FROM `order`";
	$resource=mysql_query($sql);
	$res=array();
	while (($row=mysql_fetch_assoc($resource))!=false) {
		$res[]=$row['tutor_id'];
	}
	return $res;
}
/*输出到浏览器并且下载*/
function browser_export($type,$filename){
	if($type=='Excel5'){
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
	}else if($type=='Excel2007'){
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
	}
	header('Cache-Control: max-age=0');//禁止缓存
}
$objPHPexcel=new PHPExcel();
$tuid=getTutorId();
foreach ($tuid as $key => $value) {
	if($key>0){
		$objPHPexcel->createSheet();//从第二个创建多个sheet
	}
	$objPHPexcel->setActiveSheetIndex($key);//创建多个sheet
	$objSheet=$objPHPexcel->getActiveSheet();//获得当前活动的sheet操作对象
	$objSheet->getDefaultStyle()->getAlignment()->
	setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->
	setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平垂直居中
	$objSheet->getStyle("A1:Z1")->getFont()->setName('微软雅黑')
	->setSize('11')->setBold(True);//设置第一行字体大小,字体样式。
	$objSheet->getStyle('A1:I1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
	->getStartColor()->setRGB('00b33b');//设置背景颜色
	/*配置边框颜色*/
	$styleBorder=array(
		'borders'=>array(
			'outline'=>array(
				'style'=>PHPExcel_Style_Border::BORDER_THICK,
				'color'=>array('rgb'=>'f00')
				)
			),
		);
	$objSheet->getStyle("A1:I1")->applyFromArray($styleBorder);//设置边框
	// $objSheet->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
	$objSheet->setTitle('tutor_'.$value);//给活动sheet起名字
	$objSheet->setCellValue('A1','订单号')->setCellValue('B1','姓名')
		->setCellValue('C1','电话号')->setCellValue('D1','地址')
		->setCellValue('E1','年级')->setCellValue('F1','性别')
		->setCellValue('G1','科目')->setCellValue('H1','tutor_id')
		->setCellValue('I1','时间');//写入表头
	$j=2;
	$data=getByTId($value);
	//写入数据
	foreach ($data as $k => $v) {
		$objSheet->setCellValueExplicit('A'.$j,$v['Id'],PHPExcel_Cell_DataType::TYPE_STRING)
		->setCellValue('B'.$j,$v['name'])
		->setCellValueExplicit('C'.$j,$v['phone'],PHPExcel_Cell_DataType::TYPE_STRING)//以字符串方式储存数字
		->setCellValue('D'.$j,$v['realaddress'])
		->setCellValue('E'.$j,$v['grade'])->setCellValue('F'.$j,$v['sex'])
		->setCellValue('G'.$j,$v['subject'])->setCellValue('H'.$j,$v['tutor_id'])
		->setCellValue('I'.$j,$v['time']);
		$j++;
	}
}
$obbjWrite=PHPExcel_IOFactory::createWriter($objPHPexcel,'Excel5');
browser_export('Excel5','mysqlshuchu');
$obbjWrite->save("php://output");
// $obbjWrite->save($dir.'/mysqlshuchu.xls');//直接保存
?>

输出结果 如图:


标签: PHP PHPExcel

评论

点击图片切换
还没有评论,快来抢沙发吧!