用PHPExcel插件导出Excel表格
PHPExcel插件下载:http://download.csdn.net/detail/haibo0668/9844591
config.php代码:
<?php
session_start();
header("Content-type: text/html;charset=utf-8");
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "book";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
/* Close the connection 关闭连接*/
// mysqli_close($conn);
date_default_timezone_set('prc');//时区问题,php环境默认时差与北京时间相差8小时,我们要想获取正确的时间就必须设置在PHP文件开始处 加上date_default_timezone_set('prc');
//代替_get('str')=$_GET['xxx']
function _get($str){
$val = !empty($_GET[$str]) ? $_GET[$str] : null;
return $val;
}
function _REQUEST($str){
$val = !empty($_REQUEST[$str]) ? $_REQUEST[$str] : null;
return $val;
}
function _POST($str){
$val = !empty($_POST[$str]) ? $_POST[$str] : null;
return $val;
}
ini_set("error_reporting","E_ALL & ~E_NOTICE");//屏蔽错误信息
?>
<?php
include("config.php");
header("Content-type: text/html;charset=utf-8");
//检查权限 04=导出
require_once("Classes/PHPExcel.php");
include("Classes/PHPExcel/IOFactory.php");
//$id=$_GET["id"];
// $id="SA00000008";
// $sql_saleinfo="select * from OrderInfoTable where SalesID='$id' ";
// $rs_saleinfo=mysql_query($sql_saleinfo);
$sql_saleinfo = "SELECT * FROM data_guestbook";
$rs_saleinfo = mysqli_query($conn, $sql_saleinfo);
//创建一个excel对象
$objPHPExcel = new PHPExcel();
// Set properties 设置文件属性
$objPHPExcel->getProperties()->setCreator("ctos")
->setLastModifiedBy("ctos")
->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");
//set width 设置表格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(60);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(60);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(30);
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('L')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('M')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('O')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// set table header content 设置表头名称
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '项目')
->setCellValue('B1', '推广渠道')
->setCellValue('C1', '电话')
->setCellValue('D1', '标题')
->setCellValue('E1', '备注')
->setCellValue('F1', '学员名称')
->setCellValue('G1', 'QQ')
->setCellValue('H1', '微信号')
->setCellValue('I1', 'Email')
->setCellValue('J1', '网址')
->setCellValue('K1', '负责人')
->setCellValue('L1', '分类1')
->setCellValue('M1', '分类2')
->setCellValue('N1', '分类3')
->setCellValue('O1', '留言时间');
$rownum=1;
//while ($rows_saleinfo=mysql_fetch_assoc($rs_saleinfo))
while($rs = mysqli_fetch_assoc($rs_saleinfo))
{
$rownum++;
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rownum, $rs['project']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $rownum, $rs['ad']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $rownum, $rs['phone']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $rownum, $rs['title']);
$objPHPExcel->getActiveSheet()->setCellValue('E' . $rownum, $rs['content']);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $rownum, $rs['username']);
$objPHPExcel->getActiveSheet()->setCellValue('G' . $rownum, $rs['qq']);
$objPHPExcel->getActiveSheet()->setCellValue('H' . $rownum, $rs['wx']);
$objPHPExcel->getActiveSheet()->setCellValue('I' . $rownum, $rs['email']);
$objPHPExcel->getActiveSheet()->setCellValue('J' . $rownum, $rs['url']);
$objPHPExcel->getActiveSheet()->setCellValue('K' . $rownum, $rs['admin_name']);
$objPHPExcel->getActiveSheet()->setCellValue('L' . $rownum, $rs['type1']);
$objPHPExcel->getActiveSheet()->setCellValue('M' . $rownum, $rs['type2']);
$objPHPExcel->getActiveSheet()->setCellValue('N' . $rownum, $rs['type3']);
$objPHPExcel->getActiveSheet()->setCellValue('O' . $rownum, $rs['time']);
}
mysqli_close($conn);
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// $filename="销售订单".date('Y-m-d');
// Redirect output to a client’s web browser (Excel5)
// ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
// header('Content-Disposition: attachment;filename='.$filename);
header('Content-Disposition: attachment;filename="01simple.xls"');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>