phpExcel中文帮助手册之常用功能指南

phpexcel基本操作：
定义excel实体
即定义一个phpexcel对象，并设置excel对象内显示内容

// excel开始
// 准备excel的包括文件
// error reporting 
error_reporting(0);
// phpexcel 
require_once dirname(__file__) . 'phpexcel.php';
// 生成新的excel对象
$objphpexcel = new phpexcel();
// 设置excel文档的属性
$objphpexcel->getproperties()->setcreator("sam.c")
             ->setlastmodifiedby("sam.c test")
             ->settitle("microsoft office excel document")
             ->setsubject("test")
             ->setdescription("test")
             ->setkeywords("test")
             ->setcategory("test result file");
// 开始操作excel表
// 操作第一个工作表
$objphpexcel->setactivesheetindex(0);
// 设置工作薄名称
$objphpexcel->getactivesheet()->settitle(iconv('gbk', 'utf-8', 'phpexcel测试'));
// 设置默认字体和大小
$objphpexcel->getdefaultstyle()->getfont()->setname(iconv('gbk', 'utf-8', '宋体'));
$objphpexcel->getdefaultstyle()->getfont()->setsize(10);

三、输出文件

// 如果需要输出excel格式
if($m_exporttype=="excel"){   
    $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5');
    // 从浏览器直接输出$filename
    header("pragma: public");
    header("expires: 0");
    header("cache-control:must-revalidate, post-check=0, pre-check=0");
    header("content-type:application/force-download");
    header("content-type: application/vnd.ms-excel;");
    header("content-type:application/octet-stream");
    header("content-type:application/download");
    header("content-disposition:attachment;filename=".$filename);
    header("content-transfer-encoding:binary");
    $objwriter->save("php://output"); 
}
// 如果需要输出pdf格式
if($m_exporttype=="pdf"){
    $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'pdf');
    $objwriter->setsheetindex(0);
    header("pragma: public");
    header("expires: 0");
    header("cache-control:must-revalidate, post-check=0, pre-check=0");
    header("content-type:application/force-download");
    header("content-type: application/pdf");
    header("content-type:application/octet-stream");
    header("content-type:application/download");
    header("content-disposition:attachment;filename=".$m_stroutputpdffilename);
    header("content-transfer-encoding:binary");
    $objwriter->save("php://output"); 
}

设置一列的宽度：

$objphpexcel->getactivesheet()->getcolumndimension('a')->setwidth(15);

设置一行的高度：

$objphpexcel->getactivesheet()->getrowdimension('6')->setrowheight(30);

合并单元格：

$objphpexcel->getactivesheet()->mergecells('a1:p1');

设置a1单元格加粗，居中：

$stylearray1 = array(
  'font' => array(
    'bold' => true,
    'size'=>12,
    'color'=>array(
      'argb' => '00000000',
    ),
  ),
  'alignment' => array(
    'horizontal' => phpexcel_style_alignment::horizontal_center,
  ),
);
// 将a1单元格设置为加粗，居中
$objphpexcel->getactivesheet()->getstyle('a1')->applyfromarray($stylearray1);

$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setbold(true);

给特定单元格中写入内容：

$objphpexcel->getactivesheet()->setcellvalue('a1', 'hello baby');

设置单元格样式（水平/垂直居中）：
 

$objphpexcel->getactivesheet()->getstyle('a1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);
  $objphpexcel->getactivesheet()->getstyle('a1')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center);

设置单元格样式（黑色字体）：

$objphpexcel->getactivesheet()->getstyle('h5')->getfont()->getcolor()->setargb(phpexcel_style_color::color_black); // 黑色

设置单元格格式（背景）：

$objphpexcel->getactivesheet()->getstyle('h5')->getfill()->getstartcolor()->setargb('00ff99cc'); // 将背景设置为浅粉色

设置单元格格式（数字格式）：

$objphpexcel->getactivesheet()->getstyle('f'.$ilinenumber)->getnumberformat()->setformatcode('0.000');

给单元格中放入图片：

// 将数据中心图片放在j1单元格内
$objdrawing = new phpexcel_worksheet_drawing();
$objdrawing->setname('logo');
$objdrawing->setdescription('logo');
$objdrawing->setpath('test.jpg');
$objdrawing->setwidth(400);
$objdrawing->setheight(123);
$objdrawing->setcoordinates('j1');
$objdrawing->setworksheet($objphpexcel->getactivesheet());

在单元格中设置超链接：

$objphpexcel->getactivesheet()->setcellvalue('h8', iconv('gbk', 'utf-8', '燕南天'));
$objphpexcel->getactivesheet()->getcell('h8')->gethyperlink()->seturl('//www.jb51.net/');

设置单元格边框

$stylethinblackborderoutline = array(
    'borders' => array (
       'outline' => array (
          'style' => phpexcel_style_border::border_thin,  //设置border样式
          //'style' => phpexcel_style_border::border_thick, 另一种样式
          'color' => array ('argb' => 'ff000000'),     //设置border颜色
      ),
   ),
);
$objphpexcel->getactivesheet()->getstyle( 'a4:e10')->applyfromarray($stylethinblackborderoutline);

//添加一个新的worksheet 
          $objexcel->createsheet(); 
          $objactsheet = $objexcel->getsheet($s); 
          $objactsheet->settitle('表'.$gsheet);