thinkPHP+phpexcel实现excel报表输出功能示例

<?php
/**
 * created by lonm.shi.
 * date: 2012-02-09
 * time: 下午4:54
 * to change this template use file | settings | file templates.
 */
class exportstatisticsaction extends action {
  public function index(){
    $model= d("ordersview");
    $ordersdata= $model->select(); //查询数据得到$ordersdata二维数组
    vendor("phpexcel176.phpexcel");
    // 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(10);
    $objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(25);
    $objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(12);
    $objphpexcel->getactivesheet()->getcolumndimension('e')->setwidth(50);
    $objphpexcel->getactivesheet()->getcolumndimension('f')->setwidth(10);
    $objphpexcel->getactivesheet()->getcolumndimension('g')->setwidth(12);
    $objphpexcel->getactivesheet()->getcolumndimension('h')->setwidth(12);
    $objphpexcel->getactivesheet()->getcolumndimension('i')->setwidth(12);
    $objphpexcel->getactivesheet()->getcolumndimension('j')->setwidth(30);
    //设置行高度
    $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:j2')->getfont()->setbold(true);
    $objphpexcel->getactivesheet()->getstyle('a2:j2')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center);
    $objphpexcel->getactivesheet()->getstyle('a2:j2')->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('d')->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);
    //合并cell
    $objphpexcel->getactivesheet()->mergecells('a1:j1');
    // set table header content
    $objphpexcel->setactivesheetindex(0)
      ->setcellvalue('a1', '订单数据汇总 时间:'.date('y-m-d h:i:s'))
      ->setcellvalue('a2', '订单id')
      ->setcellvalue('b2', '下单人')
      ->setcellvalue('c2', '客户名称')
      ->setcellvalue('d2', '下单时间')
      ->setcellvalue('e2', '需求机型')
      ->setcellvalue('f2', '需求数量')
      ->setcellvalue('g2', '需求交期')
      ->setcellvalue('h2', '确认bom料号')
      ->setcellvalue('i2', 'pmc确认交期')
      ->setcellvalue('j2', 'pmc交货备注');
    // miscellaneous glyphs, utf-8
    for($i=0;$i<count($ordersdata)-1;$i++){
      $objphpexcel->getactivesheet(0)->setcellvalue('a'.($i+3), $ordersdata[$i]['id']);
      $objphpexcel->getactivesheet(0)->setcellvalue('b'.($i+3), $ordersdata[$i]['realname']);
      $objphpexcel->getactivesheet(0)->setcellvalue('c'.($i+3), $ordersdata[$i]['customer_name']);
      $objphpexcel->getactivesheet(0)->setcellvalue('d'.($i+3), todate($ordersdata[$i]['create_time'])); //这里调用了common.php的时间戳转换函数
      $objphpexcel->getactivesheet(0)->setcellvalue('e'.($i+3), $ordersdata[$i]['require_product']);
      $objphpexcel->getactivesheet(0)->setcellvalue('f'.($i+3), $ordersdata[$i]['require_count']);
      $objphpexcel->getactivesheet(0)->setcellvalue('g'.($i+3), $ordersdata[$i]['require_time']);
      $objphpexcel->getactivesheet(0)->setcellvalue('h'.($i+3), $ordersdata[$i]['product_bom_encoding']);
      $objphpexcel->getactivesheet(0)->setcellvalue('i'.($i+3), $ordersdata[$i]['delivery_time']);
      $objphpexcel->getactivesheet(0)->setcellvalue('j'.($i+3), $ordersdata[$i]['delivery_memo']);
      $objphpexcel->getactivesheet()->getstyle('a'.($i+3).':j'.($i+3))->getalignment()->setvertical(phpexcel_style_alignment::vertical_center);
      $objphpexcel->getactivesheet()->getstyle('a'.($i+3).':j'.($i+3))->getborders()->getallborders()->setborderstyle(phpexcel_style_border::border_thin);
      $objphpexcel->getactivesheet()->getrowdimension($i+3)->setrowheight(16);
    }
    // sheet命名
    $objphpexcel->getactivesheet()->settitle('订单汇总表');
    // set active sheet index to the first sheet, so excel opens this as the first sheet
    $objphpexcel->setactivesheetindex(0);
    // excel头参数
    header('content-type: application/vnd.ms-excel');
    header('content-disposition: attachment;filename="订单汇总表('.date('ymd-his').').xls"'); //日期为文件名后缀
    header('cache-control: max-age=0');
    $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); //excel5为xls格式，excel2007为xlsx格式
    $objwriter->save('php://output');
  }
}