php生成excel完整实例代码,PHP输出Excel实例代码

这里使用PHPExcel的开源类

一个完整的实例：

require_once("../includes/function.php"); //提供了SQL注入检测函数inject_check

require_once("../class/DB.php"); //DB操作类，自己扩展一下

$db = new DB();

if($_GET['show_year']){

$_GET['show_year'] = inject_check($_GET['show_year']);

} else {

echo "";

return;

}

//查出开设预约的班级的学生名单

$sql = "select * from bishe_student,student where bishe_student.sno=student.sno ";

if($_GET['show_year'] && $_GET['show_year']!='-1'){

$lagreYear = $_GET['show_year']+1;

$sql .= " and bishe_student.time>='{$_GET['show_year']}

-01-01 00:00:00' and bishe_student.time

}

$sql .= "order by bishe_student.time asc; ";

$rs = $db->query($sql);

/**

*该PHP代码块开始为能够输出EXCEL文件作准备

*/

第一步，开头操作，包括引入类、设定文件基本属性

require_once("../Excel/Classes/PHPExcel.php");

require_once("../Excel/Classes/PHPExcel/Writer/Excel5.php");

//单元格的字母坐标

$colx = array('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s'

,'t','u','v','w','x','y','z','aa','ab','ac','ad','ae','af','ag','ah','ai','ak','al',

'am','an','ao','ap','aq','ar','as','at','au','av','aw','ax','ay','az');

// 创建一个处理对象实例

$objExcel = new PHPExcel();

// 创建文件格式写入对象实例, uncomment

$objWriter = new PHPExcel_Writer_Excel5($objExcel);

//设置文档基本属性

$objProps = $objExcel->getProperties();

$objProps->setCreator( gbkToUtf8("计算机学院专业实践中心") );

$objProps->setLastModifiedBy( gbkToUtf8("计算机学院专业实践中心") );

$objProps->setTitle( gbkToUtf8("毕业设计预约学生表") );

$objProps->setSubject( gbkToUtf8("毕业设计预约学生表") );

$objProps->setDescription( gbkToUtf8("毕业设计预约学生表") );

$objProps->setKeywords( gbkToUtf8("毕业设计预约学生表") );

$objProps->setCategory( gbkToUtf8("毕业设计预约学生表") );

//*******

//设置当前的sheet索引，用于后续的内容操作。

//一般只有在使用多个sheet的时候才需要显示调用。

//缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0

$objExcel->setActiveSheetIndex(0);

$objActSheet = $objExcel->getActiveSheet();

//设置当前活动sheet的名称

$objActSheet->setTitle('sheet1');

//设置宽度

function setWidth($index,$width=5){

global $objActSheet;

$objActSheet->getColumnDimension(gbkToUtf8($index))->setWidth($width);

}

//宽度设置

for($i=0;$i<51;$i++){

//echo $colx[$i]."";

$ind = $colx[$i];

setWidth(gbkToUtf8("$ind"),11);

}

$objActSheet->getRowDimension(1)->setRowHeight(30);

$objActSheet->getRowDimension(2)->setRowHeight(20);

$objActSheet->getRowDimension(3)->setRowHeight(16);

//函数：设置border

function setBorder($index){

global $objActSheet;

$objActSheet->getStyle($index)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objActSheet->getStyle($index)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objActSheet->getStyle($index)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objActSheet->getStyle($index)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objActSheet->getStyle($index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objActSheet->getStyle($index)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objActSheet->getStyle($index)->getAlignment()->setWrapText(true);

}

//设置值

function setValue($index,$value){

global $objActSheet;

$objActSheet->setCellValue($index,gbkToUtf8($value) );

}

//设置值

function setValueGBK($index,$value){

global $objActSheet;

$objActSheet->getCell($index)->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);

}

//合并单元格

function merge($fc,$lc){

global $objActSheet;

$objActSheet->mergeCells($fc.":".$lc);

}

//********************开始写入数据

$lastIndex = 10;

$objActSheet->setCellValue('A1',gbkToUtf8( '毕业设计上机预约学生表' ) );

//合并单元格

$objActSheet->mergeCells("A1:".$colx[$lastIndex]."1");

//设置样式

$objStyleA1 = $objActSheet->getStyle('A1');

$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objFontA1 = $objStyleA1->getFont();

$objFontA1->setName('Courier New');

$objFontA1->setSize(18);

$objFontA1->setBold(true);

$rowIndexB = 3;

function showBottomRows($xuhao='序号',$xuehao='学号',

$xingming='姓名',$bishelaoshi='毕设老师',$bishetimu='毕设题目',

$phone='电话',$email='Email',$beizhu='备注'){

global $colx;

global $rowIndexB;

$colIndexB = 0;

//都分三步，设值、合并、边框

//序号

setValue($colx[$colIndexB].$rowIndexB , $xuhao);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB].$rowIndexB );

$colIndexB = $colIndexB+1;

//学号

setValue($colx[$colIndexB].$rowIndexB , $xuehao);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB].$rowIndexB );

$colIndexB = $colIndexB+1;

//姓名

setValue($colx[$colIndexB].$rowIndexB , $xingming);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB].$rowIndexB );

$colIndexB = $colIndexB+1;

//毕设老师

setValue($colx[$colIndexB].$rowIndexB , $bishelaoshi);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB].$rowIndexB );

$colIndexB = $colIndexB+1;

//毕设题目

setValue($colx[$colIndexB].$rowIndexB , $bishetimu);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB+2].$rowIndexB );

$colIndexB = $colIndexB+3;

//电话

setValue($colx[$colIndexB].$rowIndexB , $phone);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB].$rowIndexB );

$colIndexB = $colIndexB+1;

//Email

setValue($colx[$colIndexB].$rowIndexB , $email);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB+1].$rowIndexB );

$colIndexB = $colIndexB+2;

//备注

setValue($colx[$colIndexB].$rowIndexB , $beizhu);

merge($colx[$colIndexB].$rowIndexB , $colx[$colIndexB].$rowIndexB );

$colIndexB = $colIndexB+1;

for($xx=0;$xx

setBorder($colx[$xx].$rowIndexB);

}

$rowIndexB++;

}

showBottomRows();

//***表格头部结束

第一步结束，第二步就可以开始打印数据，在下面的主要函数里面

$order=1;

while($row = $db->getRows($rs)){

showBottomRows(

$order++ ,

" ".$row['sno'],

" ".$row['sname'] ,

" ".$row['bishe_teacher'],

" ".$row['bishe_title'],

" ".$row['bishe_phone'],

" ".$row['bishe_email'],

" "

);

}

//刷新border

for($i=0;$i<=$lastIndex;$i++){

$objStyleA3 = $objActSheet->getStyle('A3');

$objStyleA3->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objStyleA3->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objStyleA3->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objStyleA3->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );

$objStyleA3->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objStyleA3->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//$objActSheet->duplicateStyle($objStyleA3, 'A3:'.$colx[$lastIndex].(mysql_num_rows( $result_stuList )+3) );

$objActSheet->duplicateStyle($objStyleA3, 'A3:'.$colx[$lastIndex].($db->getRowsNum($rs)+3) );

}

$cancel_time=date("YmdHis");

$outputFileName = "{$cancel_time}bisheYuyueStudentList.xls";

//到文件

$objWriter->save('../temp/'.$outputFileName);

header("Content-Type: application/OCTET-STREAM");

header("Content-Disposition: inline; filename={$_GET['show_year']}年毕业设计上机预约学生表.xls");

readfile('../temp/'.$outputFileName);

echo "

location.href='{$_SERVER['REQUEST_URI']}';

";

?>