<?php
include(DT_ROOT.'/file/PHPexcel.php');
include(DT_ROOT.'/file/PHPexcel/Writer/Excel5.php');
class MYEXCEL{
private $db;//数据库类对象
private $objExc;//实例化excel类
private $fieldsNum;//字段数量
public $rows;//存放查询获取的数组
private $arr;//定义传过来的二维数组
private $nameArr;//定义数组,为传值中二维数组的第一项,为中文的字段名字
private $fieldArr;//定义数组,为传值中二维数组的第二项,为字段的名字
private $lengthArr;//定义数组,为传值中二维数组的第三项,为字段长度
private $cols;//确定列数
//private //
//构造函数$arr是定义的二维数组样式是$fields = array(0=>array("姓名","name","12"),1=>array("年龄","age","10"));
function MYEXCEL($db,$arr) {
$this->db=$db;
$this->arr=$arr;
$this->nameArr=$nameArr;
$this->fieldArr=$fieldArr;
$this->lengthArr=$lengthArr;
$this->cols=$cols;
$this->objExc = new PHPExcel();
if($arr){//将二维数组拆分
foreach($arr as $k=>$v){
$this->nameArr[$k]=$v[0];//取二维数组中的中文字义
$this->fieldArr[$k]=$v[1];//取二维数组中的字段的意思
$this->lengthArr[$k]=$v[2];//取二维数组中的字段的长度
}
}
}
//根据条件(一个是数据表名,二个是条件可为空)得到SQL语句,然后执行在调用文件进行,一些特殊的字段需要转换
function getArr($tbname,$condition='') {
if(is_array($this->fieldArr)){
$fieldsStr = implode($this->fieldArr,',');
}
$sql = "SELECT ".$fieldsStr." FROM ".$tbname;
if($condition) {
$sql.=" WHERE ".$condition;
}
/*$query = $this->db->query($sql);
while($rs = $this->db->fetch_array($query)) {
//一些转换字段格式再此进行,比如时间、性别、部门。。。。
$rows[] = $rs;
}
$this->rows = $rows;*/
return $sql;
}
//设置基本信息
function setBase($title,$subject,$description,$lastmodified,$creator='会商宝') {
$this->objExc->getProperties()->setCreator($creator);
$this->objExc->getProperties()->setLastModifiedBy($lastmodified);
$this->objExc->getProperties()->setTitle($title);
$this->objExc->getProperties()->setSubject($subject);
$this->objExc->getProperties()->setDescription($description);
$this->objExc->setActiveSheetIndex(0);
}
//确定列数,头部信息(第一个参数是字体大小,第二个是颜色,第三个默认是加粗)
function getColsNum($length="12",$color="FFCC66",$bold="true"){
$this->cols=count($this->arr);
$colsNum=$this->num2letter($this->cols);//调用数字转换字母函数,确定最后一列的字母是什么
//echo $colsNum;exit;
$this->objExc->getActiveSheet()->getStyle('A1:'.$colsNum."1")->getFont()->setSize($length);
$this->objExc->getActiveSheet()->getStyle('A1:'.$colsNum."1")->getFont()->setBold($bold);
$this->objExc->getActiveSheet()->getStyle('A1:'.$colsNum."1")->getFont()->getColor()->setARGB($color);
}
//设置列宽(长度是由拆分数组的lengthArr,一 一对应得出)
function setColsWidth(){
for($i=0;$i<$this->cols;$i++){
$num=$this->num2letter($i+1);
$this->objExc->getActiveSheet()->getColumnDimension($num)->setWidth($this->lengthArr[$i]);
}
}
//填充首行信息
function setFirstContents(){
for($i=0;$i<$this->cols;$i++){
$num=$this->num2letter($i+1);
$this->objExc->getActiveSheet()->SetCellValue($num.'1',$this->nameArr[$i]);
}
}
//填充内容(从A2开始,$rows数组中获取 其中获取到的字段的值,循环出数据库中循环的值)
function setAllContents(){
foreach($this->rows as $k=>$v) {
for($i=2;$i<($this->cols+2);$i++){
$num=$this->num2letter($i-1);
$contents=$this->fieldArr[$i-2];
$this->objExc->getActiveSheet()->SetCellValue($num.($k+2),$v[$contents]);
}
}
}
//设置是否冻结首行(默认是true则进行冻结首行否则是false不冻结首行)
function freezeLine($freeze="true"){
if($freeze){
$this->objExc->getActiveSheet()->freezePane('A2');
}
}
//设置excel信息(第一个参数是显示时间,一般为当前时间,第二个是导出显示的名字, 第三个是是否带上时间,如果为flase则不带时间,默认为ture)
function setExcel($excelName,$haveTime="true"){
$this->objWriter = new PHPExcel_Writer_Excel5($this->objExc);
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
if($haveTime){
header('Content-Disposition:inline;filename="'.date("Y-m-d",time()).$excelName.'.xls"');
}else{
header('Content-Disposition:inline;filename="'.$excelName.'.xls"');
}
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
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");
$this->objWriter->save('php://output');
}
//数字转化成字母
function num2letter($a){
if($a>26){
$b=ceil(($a-26)/26);
$a=$a-26*$b;
$a=$a+64;
$b=chr($b+64);
return $b.chr($a);
}else{
$a=$a+64;
return chr($a);
}
}
//不需要传参数的函数
function publicFunction(){
$this->setColsWidth();
$this->setAllContents();
$this->setFirstContents();
$this->freezeLine();
}
}
?>