05 06月 2015

直接上代码

//导出
public function excel($list){
header("Content-type: text/html; charset=utf-8");
Vendor('PHPExcel.PHPExcel'); //加载扩展包  thinkphp

 Yii::import('application.extensions.PHPExcel.PHPExcel');  //Yiii

//新建
$resultPHPExcel = new PHPExcel();
//设置参数
$resultPHPExcel->getActiveSheet()->setCellValue('A1', '订单编号');
$resultPHPExcel->getActiveSheet()->setCellValue('B1', '总金额');
$resultPHPExcel->getActiveSheet()->setCellValue('C1', '商品');
$resultPHPExcel->getActiveSheet()->setCellValue('D1', '会员用户名');
$resultPHPExcel->getActiveSheet()->setCellValue('E1', '收货人');
$resultPHPExcel->getActiveSheet()->setCellValue('F1', '联系电话');
$resultPHPExcel->getActiveSheet()->setCellValue('G1', '收货地址');
$resultPHPExcel->getActiveSheet()->setCellValue('H1', '配送方式');
$resultPHPExcel->getActiveSheet()->setCellValue('I1', '支付方式');
$resultPHPExcel->getActiveSheet()->setCellValue('J1', '下单时间');
$resultPHPExcel->getActiveSheet()->setCellValue('K1', '付款时间');
$resultPHPExcel->getActiveSheet()->setCellValue('L1', '买家留言');

$resultPHPExcel->getActiveSheet()->setCellValue('M1', '订单状态');

//值
$i = 2;
//$list[]=array("orderid"=>$q['orderid'],'total'=>$q['price'],'product'=>'','nickname'=>'','username'=>$q['truename'],'tel'=>$q['tel'],'address'=>$address['fullarea'],'sendtype'=>'','paytype'=>$q['paytypename'],'paytime'=>date("Y-m-d",$q['time']),'msg'=>'','remark'=>'','status'=>$status);
foreach ($list as $item) {

$resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['orderid']);
$resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['total']);
$resultPHPExcel->getActiveSheet()->setCellValue('C' . $i, $item['product']);
$resultPHPExcel->getActiveSheet()->setCellValue('D' . $i, $item['nickname']);
$resultPHPExcel->getActiveSheet()->setCellValue('E' . $i, $item['username']);
$resultPHPExcel->getActiveSheet()->setCellValue('F' . $i, $item['tel']);
$resultPHPExcel->getActiveSheet()->setCellValue('G' . $i, $item['address']);
$resultPHPExcel->getActiveSheet()->setCellValue('H' . $i, $item['sendtype']);
$resultPHPExcel->getActiveSheet()->setCellValue('I' . $i, $item['paytype']);
$resultPHPExcel->getActiveSheet()->setCellValue('J' . $i, $item['paytime']);
$resultPHPExcel->getActiveSheet()->setCellValue('K' . $i, $item['givetime']);
$resultPHPExcel->getActiveSheet()->setCellValue('L' . $i, $item['msg']);

$resultPHPExcel->getActiveSheet()->setCellValue('M' . $i, $item['status']);

$i++;
}
//设置导出文件名

$outputFileName = '订单详情.xls';

$xlsWriter = new PHPExcel_Writer_Excel5($resultPHPExcel);

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

header('Content-Disposition:inline;filename="' . $outputFileName . '"');

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");

$xlsWriter->save("php://output");


}

可以直接设置单元格属性可以直接跟在value后面

  1. //填入主标题
  2.         $PHPExcel->getActiveSheet()->setCellValue('A1', '上海**人力资源服务有限公司');
  3.         //填入副标题
  4.         $PHPExcel->getActiveSheet()->setCellValue('A2', '简历列表(导出日期：'.date('Y-m-d',time()).')');
  5.         
  6.         //填入表头
  7.         $PHPExcel->getActiveSheet()->setCellValue('A3', 'ID');
  8.         $PHPExcel->getActiveSheet()->setCellValue('B3', '姓名');
  9.         $PHPExcel->getActiveSheet()->setCellValue('C3', '性别');
  10.         $PHPExcel->getActiveSheet()->setCellValue('D3', '年龄');
  11.         $PHPExcel->getActiveSheet()->setCellValue('E3', '联系方式');
  12.         $PHPExcel->getActiveSheet()->setCellValue('F3', '学历');
  13.         $PHPExcel->getActiveSheet()->setCellValue('G3', '是否有AQE证书');
  14.         $PHPExcel->getActiveSheet()->setCellValue('H3', '住址');
  15.         $PHPExcel->getActiveSheet()->setCellValue('I3', '面试日期');
  16.         $PHPExcel->getActiveSheet()->setCellValue('J3', '面试时间');
  17.         $PHPExcel->getActiveSheet()->setCellValue('K3', '所属HR');
  18.         
  19.         $PHPExcel->getActiveSheet()->setCellValue('L3', '备注');
  20.         $PHPExcel->getActiveSheet()->setCellValue('M3', '证件号码');
  21.         $PHPExcel->getActiveSheet()->setCellValue('N3', 'QQ');
  22.         $PHPExcel->getActiveSheet()->setCellValue('O3', '电子邮箱');
  23.         $PHPExcel->getActiveSheet()->setCellValue('P3', '出生日期');
  24.         $PHPExcel->getActiveSheet()->setCellValue('Q3', '婚姻状况');
  25.         $PHPExcel->getActiveSheet()->setCellValue('R3', '户籍');
  26.         $PHPExcel->getActiveSheet()->setCellValue('S3', '毕业时间');
  27.         $PHPExcel->getActiveSheet()->setCellValue('T3', '毕业院校');
  28.         
  29.         $PHPExcel->getActiveSheet()->setCellValue('U3', '专业');
  30.         $PHPExcel->getActiveSheet()->setCellValue('V3', '工作经历');
  31.         $PHPExcel->getActiveSheet()->setCellValue('W3', '期望工作地点');
  32.         $PHPExcel->getActiveSheet()->setCellValue('X3', '期望薪酬');
  33.         $PHPExcel->getActiveSheet()->setCellValue('Y3', '期望职业');
  34.         $PHPExcel->getActiveSheet()->setCellValue('Z3', '面试单位');
  35.         $PHPExcel->getActiveSheet()->setCellValue('AA3', '面试岗位');
  36.         $PHPExcel->getActiveSheet()->setCellValue('AB3', '目前状态');
  37.         $PHPExcel->getActiveSheet()->setCellValue('AC3', '处理状态');
  38.         $PHPExcel->getActiveSheet()->setCellValue('AD3', '自我评价');
  39.         $PHPExcel->getActiveSheet()->setCellValue('AE3', '其他');
  40.         
  41.         //填入列表
  42.         $k = 1;
  43.         foreach ($list as $key => $value){
  44.             $k++;
  45.             
  46.             $PHPExcel->getActiveSheet()->setCellValue('A'.($key+4), $value['id']);
  47.             $PHPExcel->getActiveSheet()->setCellValue('B'.($key+4), $value['name']);
  48.             
  49.             //性别
  50.             $PHPExcel->getActiveSheet()->getCell('C'.($key+4))->getDataValidation()
  51.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  52.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  53.             -> setAllowBlank(false)
  54.             -> setShowInputMessage(true)
  55.             -> setShowErrorMessage(true)
  56.             -> setShowDropDown(true)
  57.             -> setErrorTitle('请选择性别')
  58.             -> setError('您输入的值不在下拉框列表内.')
  59.             -> setPromptTitle('性别')
  60.             -> setFormula1('"'.join(',', $sex).'"');
  61.             $PHPExcel->getActiveSheet()->setCellValue('C'.($key+4), $sex[$value['sex']]);
  62.             
  63.             //年龄
  64.             $PHPExcel->getActiveSheet()->setCellValue('D'.($key+4), $value['age']);
  65.             
  66.             $PHPExcel->getActiveSheet()->setCellValue('E'.($key+4), $value['tel']);
  67.             $PHPExcel->getActiveSheet()->setCellValue('F'.($key+4), $value['xueli']);
  68.             
  69.             //是否有AQE证书
  70.             $PHPExcel->getActiveSheet()->getCell('G'.($key+4))->getDataValidation()
  71.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  72.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  73.             -> setAllowBlank(false)
  74.             -> setShowInputMessage(true)
  75.             -> setShowErrorMessage(true)
  76.             -> setShowDropDown(true)
  77.             -> setErrorTitle('请选择是否有AQE证书')
  78.             -> setError('您输入的值不在下拉框列表内.')
  79.             -> setPromptTitle('是否有AQE证书')
  80.             -> setFormula1('"'.join(',', $AQE).'"');
  81.             $PHPExcel->getActiveSheet()->setCellValue('G'.($key+4), $AQE[$value['hasAQE']]);
  82.             
  83.             $PHPExcel->getActiveSheet()->setCellValue('H'.($key+4), $value['juzhudi']);
  84.             $PHPExcel->getActiveSheet()->setCellValue('I'.($key+4), setDate($value['auditionTime'],'Y年m月d日'));//面试日期
  85.             $PHPExcel->getActiveSheet()->setCellValue('J'.($key+4), setDate($value['auditionTime'],'H点i分'));//面试时间
  86.             $PHPExcel->getActiveSheet()->setCellValue('K'.($key+4), $hr[$value['userid']]); //所属HR
  87.             $PHPExcel->getActiveSheet()->setCellValue('L'.($key+4), $value['remark']);//备注
  88.             
  89.             $PHPExcel->getActiveSheet()->setCellValue('M'.($key+4), $value['cid']);//证件号码
  90.             $PHPExcel->getActiveSheet()->setCellValue('N'.($key+4), $value['qq']);
  91.             $PHPExcel->getActiveSheet()->setCellValue('O'.($key+4), $value['email']);
  92.             $PHPExcel->getActiveSheet()->setCellValue('P'.($key+4), setDate($value['birthday']));
  93.             
  94.                 
  95.             
  96.                 
  97.             //婚姻
  98.             $PHPExcel->getActiveSheet()->getCell('Q'.($key+4))->getDataValidation()
  99.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  100.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  101.             -> setAllowBlank(false)
  102.             -> setShowInputMessage(true)
  103.             -> setShowErrorMessage(true)
  104.             -> setShowDropDown(true)
  105.             -> setErrorTitle('请选择婚姻')
  106.             -> setError('您输入的值不在下拉框列表内.')
  107.             -> setPromptTitle('性别')
  108.             -> setFormula1('"'.join(',', $hunyin).'"');
  109.             $PHPExcel->getActiveSheet()->setCellValue('Q'.($key+4), $hunyin[$value['hunyin']]);
  110.                 
  112.             $PHPExcel->getActiveSheet()->setCellValue('R'.($key+4), $value['huji']);
  113.             $PHPExcel->getActiveSheet()->setCellValue('S'.($key+4), setDate($value['graduationTime']));
  114.             $PHPExcel->getActiveSheet()->setCellValue('T'.($key+4), $value['graduationSchool']);
  115.             $PHPExcel->getActiveSheet()->setCellValue('U'.($key+4), $value['specialty']);
  116.             $PHPExcel->getActiveSheet()->setCellValue('V'.($key+4), $value['works']);
  117.             $PHPExcel->getActiveSheet()->setCellValue('W'.($key+4), $value['expectAddress']);
  118.             $PHPExcel->getActiveSheet()->setCellValue('X'.($key+4), $value['expectSalary']);
  119.             $PHPExcel->getActiveSheet()->setCellValue('Y'.($key+4), $value['expectProfession']);
  120.                 
  121.             //面试单位
  122.             $PHPExcel->getActiveSheet()->getCell('Z'.($key+4))->getDataValidation()
  123.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  124.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  125.             -> setAllowBlank(false)
  126.             -> setShowInputMessage(true)
  127.             -> setShowErrorMessage(true)
  128.             -> setShowDropDown(true)
  129.             -> setErrorTitle('输入的值有误')
  130.             -> setError('您输入的值不在下拉框列表内.')
  131.             -> setPromptTitle('面试单位')
  132.             -> setFormula1('data!$C$1:$C$'.count($company));
  133.             $PHPExcel->getActiveSheet()->setCellValue('Z'.($key+4), $companyList[$value['company']]);//面试单位
  134.             $PHPExcel->getActiveSheet()->setCellValue('AA'.($key+4), $value['post']); //面试岗位
  135.                 
  136.             //简历状态
  137.             $PHPExcel->getActiveSheet()->getCell('AB'.($key+4))->getDataValidation()
  138.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  139.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  140.             -> setAllowBlank(false)
  141.             -> setShowInputMessage(true)
  142.             -> setShowErrorMessage(true)
  143.             -> setShowDropDown(true)
  144.             -> setErrorTitle('输入的值有误')
  145.             -> setError('您输入的值不在下拉框列表内.')
  146.             -> setPromptTitle('简历状态')
  147.             -> setFormula1('data!$A$1:$A$'.count($resumeState));
  148.             $PHPExcel->getActiveSheet()->setCellValue('AB'.($key+4), $resumeState[$value['resumeState']]);
  149.             
  150.             //处理状态
  151.             $PHPExcel->getActiveSheet()->getCell('AC'.($key+4))->getDataValidation()
  152.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  153.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  154.             -> setAllowBlank(false)
  155.             -> setShowInputMessage(true)
  156.             -> setShowErrorMessage(true)
  157.             -> setShowDropDown(true)
  158.             -> setErrorTitle('输入的值有误')
  159.             -> setError('您输入的值不在下拉框列表内.')
  160.             -> setPromptTitle('处理状态')
  161.             -> setFormula1('data!$B$1:$B$'.count($processingStatus));
  162.             $PHPExcel->getActiveSheet()->setCellValue('AC'.($key+4), $processingStatus[$value['processingStatus']]);
  163.                 
  164.             $PHPExcel->getActiveSheet()->setCellValue('AD'.($key+4), $value['selfIntroduction']);
  165.             $PHPExcel->getActiveSheet()->setCellValue('AE'.($key+4), $value['other']);
  166.         
  167.             //设置每一行行高
  168.             $PHPExcel->getActiveSheet()->getRowDimension($key+4)->setRowHeight(30);
  169.         }
  170.         
  171.         //合并单元格
  172.         $PHPExcel->getActiveSheet()->mergeCells('A1:AE1');
  173.         $PHPExcel->getActiveSheet()->mergeCells('A2:AE2');
  174.         
  175.         //设置单元格宽度
  176.         $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
  177.         $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
  178.         $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
  179.         $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
  180.         $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
  181.         $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
  182.         $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(16);
  183.         $PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
  184.         $PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
  185.         $PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
  186.         $PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
  187.         $PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
  188.         $PHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
  189.         $PHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
  190.         $PHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);
  191.         $PHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
  192.         $PHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
  193.         $PHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);
  194.         $PHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
  195.         $PHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(18);
  196.         $PHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);
  197.         $PHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(30);
  198.         $PHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15);
  199.         $PHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);
  200.         $PHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
  201.         $PHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(20);
  202.         $PHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(20);
  203.         $PHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(20);
  204.         $PHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(15);
  205.         $PHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(30);
  206.         $PHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(30);
  207.         
  208.         //设置表头行高
  209.         $PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
  210.         $PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22);
  211.         $PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);
  212.         
  213.         //设置字体样式
  214.         $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
  215.         $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
  216.         $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
  217.         $PHPExcel->getActiveSheet()->getStyle('A3:AE3')->getFont()->setBold(true);
  218.             
  219.         $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
  220.         $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);
  221.         
  222.         $PHPExcel->getActiveSheet()->getStyle('A4:AE'.($k+2))->getFont()->setSize(10);
  223.         //设置居中
  224.         $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  225.             
  226.         //所有垂直居中
  227.         $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  228.             
  229.         //设置单元格边框
  230.         $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  231.         
  232.         //设置自动换行
  233.         $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getAlignment()->setWrapText(true);
  235.         
  236.         
  237.         //保存为2003格式
  238.         $objWriter = new PHPExcel_Writer_Excel5($PHPExcel);
  239.         header("Pragma: public");
  240.         header("Expires: 0");
  241.         header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
  242.         header("Content-Type:application/force-download");
  243.         header("Content-Type:application/vnd.ms-execl");
  244.         header("Content-Type:application/octet-stream");
  245.         header("Content-Type:application/download");
  246.         
  247.         //多浏览器下兼容中文标题
  248.         $encoded_filename = urlencode($fileName);
  249.         $ua = $_SERVER["HTTP_USER_AGENT"];
  250.         if (preg_match("/MSIE/", $ua)) {
  251.             header('Content-Disposition: attachment; filename="' . $encoded_filename . '.xls"');
  252.         } else if (preg_match("/Firefox/", $ua)) {
  253.             header('Content-Disposition: attachment; filename*="utf8\'\'' . $fileName . '.xls"');
  254.         } else {
  255.             header('Content-Disposition: attachment; filename="' . $fileName . '.xls"');
  256.         }
  257.         
  258.         header("Content-Transfer-Encoding:binary");
  259.         $objWriter->save('php://output');


特殊说明，本文版权归 ning个人博客 所有带原创标签请勿转载，转载请注明出处.

本文标题： PHP导出excel表格方法

本文网址： http://www.ningguoteng.com/show-76.html

点击下载扩展包： http://pan.baidu.com/s/1c0rV4di

发表评论