thinkphp之Excel的导出

学海无涯GL 2015-05-28

展开全文

Excel的导出步骤：

1.下载phpexcel包，置于以下thinkphp框架路径。

内部还有一个文件夹和一个php文件

2.创建excel对象并设置excel对象的属性(ExportALLAction.class.php );

3.设置excel的行列样式(字体、高宽、颜色、边框、合并等)

4.绘制报表表头

5.将查询数据写入excel

6.设置excel的sheet的名称<

7.设置excel报表打开后初始的sheet

8.设置输出的excel的头参数及文件名

9.调用创建excel的方法生成excel文件

前端代码（可以用form表单提交，本例子用js跳转，只要能调到控制层函数就行）：

[html] view plain copy

<pre name="code" class="html"><div class="main">
<p class="currentPosition"><a href="#">搜索</a>>业务员>A类</p>
<p class="aButton"><span><input type="button" value="导出" name="download" onclick="exportdata()"/></span></p>
<div>
<div >
<table>
<thead>
<tr>
<th style="border:1px solid #DDD" > 序号</th>
<th style="border:1px solid #DDD" > 姓名</th>
<th style="border:1px solid #DDD" > 电话</th>
<th style="border:1px solid #DDD" > 备注</th>
</tr>
</thead>
<tbody>
<volist name="data" id="vo" key="k">
<tr style="line-height:1.5em">
<td style="border:1px solid #DDD" ><{$k}></td>
<td style="border:1px solid #DDD"><{$vo.clientname}></td>
<td style="border:1px solid #DDD" ><{$vo.phone}></td>
<td style="border:1px solid #DDD" ><{$vo.remark}></td>
</tr>
<tr><tr>
</volist>
</tbody>
</table>
</div>
</div>
</div>
<script language="javascript" type="text/javascript">
function exportdata(){
window.location.href="__APP__/Admin/ExportALL/index/name/saler/type/A";
}
</script>

控制层代码(前面部分是查数据，可以忽略，注意：ob_end_clean();//清除缓冲区,避免乱码)：

[php] view plain copy

<?php
class ExportALLAction extends Action {
public function index(){
$name=$_GET['name'];
$type=$_GET['type'];
switch($name){
case 'agent':{
switch($type){
case 'A':$data=M('agentclient')->where(array('type'=>1))->select();$filename="代理商A类";break;
case 'B':$data=M('agentclient')->where(array('type'=>2))->select();$filename="代理商B类";break;
case 'C':$data=M('agentclient')->where(array('type'=>3))->select();$filename="代理商C类";break;
case 'D':$data=M('agentclient')->where(array('type'=>4))->select();$filename="代理商D类";break;
case 'collect':$data=M('agentclient')->where(array('type'=>5))->select();$filename="代理商收藏";break;
case 'black':$data=M('agentclient')->where(array('type'=>6))->select();$filename="代理商黑名单";break;
default:break;
}
}break;
case 'manager':{
switch($type){
case 'A':$data=M('managerclient')->where(array('type'=>1))->select();$filename="销售经理A类";break;
case 'B':$data=M('managerclient')->where(array('type'=>2))->select();$filename="销售经理B类";break;
case 'C':$data=M('managerclient')->where(array('type'=>3))->select();$filename="销售经理C类";break;
case 'D':$data=M('managerclient')->where(array('type'=>4))->select();$filename="销售经理D类";break;
case 'collect':$data=M('managerclient')->where(array('type'=>5))->select();$filename="销售经理收藏";break;
case 'black':$data=M('managerclient')->where(array('type'=>6))->select();$filename="销售经理黑名单";break;
default:break;
}
}break;
case 'saler':{
switch($type){
case 'A':$data=M('salerclient')->where(array('type'=>1))->select();$filename="业务员A类";break;
case 'B':$data=M('salerclient')->where(array('type'=>2))->select();$filename="业务员B类";break;
case 'C':$data=M('salerclient')->where(array('type'=>3))->select();$filename="业务员C类";break;
case 'D':$data=M('salerclient')->where(array('type'=>4))->select();$filename="业务员D类";break;
case 'collect':$data=M('salerclient')->where(array('type'=>5))->select();$filename="业务员收藏";break;
case 'black':$data=M('salerclient')->where(array('type'=>6))->select();$filename="业务员黑名单";break;
default:break;
}
}break;
default:break;
}
//P($data);
//exit;
$OrdersData=$data;
if(!$OrdersData){
echo"<h1 align='center'>没有数据</h1>";
return;
}
// P($OrdersData);
// exit;
Vendor('PHPExcel.PHPExcel');
Vendor('PHPExcel.PHPExcel.IOFactory');
Vendor('PHPExcel.PHPExcel.Reader.Excel5');
// Create new PHPExcel object
$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(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
//设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
//set font size bold
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$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()->mergeCells('A1:N1');
// set table header content
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $filename.'记录时间:'.date('Y-m-d H:i:s'))
->setCellValue('A2', '编号')
->setCellValue('B2', '姓名')
->setCellValue('C2', '电话')
->setCellValue('D2', '备注') ;
// Miscellaneous glyphs, UTF-8
for($i=0;$i<=count($OrdersData)-1;$i++){
$objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $i+1);
$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $OrdersData[$i]['clientname']);
$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $OrdersData[$i]['phone']);
$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $OrdersData[$i]['remark']);
$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
}
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle($filename.'记录');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();//清除缓冲区,避免乱码
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
$filenames=$filename.'('.date('Ymd-His').').xls';
header("Content-Disposition: attachment;filename={$filenames}");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
}