解析PHPExcel使用的常用说明以及把PHPExcel整合进CI框架的介绍

excel的写入与生成操作：

include 'phpexcel.php';
include 'phpexcel/writer/excel2007.php';
//或者include 'phpexcel/writer/excel5.php'; 用于输出.xls的
include 'phpexcel/iofactory.php';//phpexcel工厂类
//创建一个excel
$objphpexcel = new phpexcel();
//保存excel—2007格式
$objwriter = new phpexcel_writer_excel2007($objphpexcel);
//也可以使用
//$objwriter = phpexcel_iofactory::createwriter($objphpexcel, "excel2007");
//或者$objwriter = new phpexcel_writer_excel5($objphpexcel); 非2007格式
$objwriter->save("xxx.xlsx");
//直接输出到浏览器
$objwriter = new phpexcel_writer_excel5($objphpexcel);
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-execl");
header("content-type:application/octet-stream");
header("content-type:application/download");;
header('content-disposition:attachment;filename="resume.xls"');
header("content-transfer-encoding:binary");
$objwriter->save('php://output');
//直接生成文件
$objwriterr->save(‘文件名');
//设置excel的属性：
//创建人
$objphpexcel->getproperties()->setcreator("maarten balliauw");
//最后修改人
$objphpexcel->getproperties()->setlastmodifiedby("maarten balliauw");
//标题
$objphpexcel->getproperties()->settitle("office 2007 xlsx test document");
//题目
$objphpexcel->getproperties()->setsubject("office 2007 xlsx test document");
//描述
$objphpexcel->getproperties()->setdescription("test document for office 2007 xlsx, generated using php classes.");
//关键字
$objphpexcel->getproperties()->setkeywords("office 2007 openxml php");
//种类
$objphpexcel->getproperties()->setcategory("test result file");
//设置当前的sheet
$objphpexcel->setactivesheetindex(0);
//设置sheet的name
$objphpexcel->getactivesheet()->settitle('simple');
//设置单元格的值
$objphpexcel->getactivesheet()->setcellvalue('a1', 'string');
$objphpexcel->getactivesheet()->setcellvalue('a2', 12);
$objphpexcel->getactivesheet()->setcellvalue('a3', true);
$objphpexcel->getactivesheet()->setcellvalue('c5', '=sum(c2:c4)');
$objphpexcel->getactivesheet()->setcellvalue('b8', '=min(b2:c5)');
//合并单元格
$objphpexcel->getactivesheet()->mergecells('a18:e22');
//分离单元格
$objphpexcel->getactivesheet()->unmergecells('a28:b28');
//保护cell
$objphpexcel->getactivesheet()->getprotection()->setsheet(true); // needs to be set to true in order to enable any worksheet protection!
$objphpexcel->getactivesheet()->protectcells('a3:e13', 'phpexcel');
//设置格式
// set cell number formats
echo date('h:i:s') . " set cell number formats\n";
$objphpexcel->getactivesheet()->getstyle('e4')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_currency_eur_simple);
$objphpexcel->getactivesheet()->duplicatestyle( $objphpexcel->getactivesheet()->getstyle('e4'), 'e5:e13' );
//设置宽width
// set column widths
$objphpexcel->getactivesheet()->getcolumndimension('b')->setautosize(true);
$objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(12);
//设置font
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setname('candara');
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setsize(20);
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setbold(true);
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setunderline(phpexcel_style_font::underline_single);
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->getcolor()->setargb(phpexcel_style_color::color_white);
$objphpexcel->getactivesheet()->getstyle('e1')->getfont()->getcolor()->setargb(phpexcel_style_color::color_white);
$objphpexcel->getactivesheet()->getstyle('d13')->getfont()->setbold(true);
$objphpexcel->getactivesheet()->getstyle('e13')->getfont()->setbold(true);
//设置align
$objphpexcel->getactivesheet()->getstyle('d11')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);
$objphpexcel->getactivesheet()->getstyle('d12')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);
$objphpexcel->getactivesheet()->getstyle('d13')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);
$objphpexcel->getactivesheet()->getstyle('a18')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_justify);
//垂直居中
$objphpexcel->getactivesheet()->getstyle('a18')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center);
设置column的border
$objphpexcel->getactivesheet()->getstyle('a4')->getborders()->gettop()->setborderstyle(phpexcel_style_border::border_thin);
$objphpexcel->getactivesheet()->getstyle('b4')->getborders()->gettop()->setborderstyle(phpexcel_style_border::border_thin);
$objphpexcel->getactivesheet()->getstyle('c4')->getborders()->gettop()->setborderstyle(phpexcel_style_border::border_thin);
$objphpexcel->getactivesheet()->getstyle('d4')->getborders()->gettop()->setborderstyle(phpexcel_style_border::border_thin);
$objphpexcel->getactivesheet()->getstyle('e4')->getborders()->gettop()->setborderstyle(phpexcel_style_border::border_thin);
设置border的color
$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->getleft()->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->gettop()->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->getbottom()->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('e13')->getborders()->gettop()->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('e13')->getborders()->getbottom()->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('e13')->getborders()->getright()->getcolor()->setargb('ff993300');
设置填充颜色
$objphpexcel->getactivesheet()->getstyle('a1')->getfill()->setfilltype(phpexcel_style_fill::fill_solid);
$objphpexcel->getactivesheet()->getstyle('a1')->getfill()->getstartcolor()->setargb('ff808080');
$objphpexcel->getactivesheet()->getstyle('b1')->getfill()->setfilltype(phpexcel_style_fill::fill_solid);
$objphpexcel->getactivesheet()->getstyle('b1')->getfill()->getstartcolor()->setargb('ff808080');
//加图片
$objdrawing = new phpexcel_worksheet_drawing();
$objdrawing->setname('logo');
$objdrawing->setdescription('logo');
$objdrawing->setpath('./images/officelogo.jpg');
$objdrawing->setheight(36);
$objdrawing->setworksheet($objphpexcel->getactivesheet());
$objdrawing = new phpexcel_worksheet_drawing();
$objdrawing->setname('paid');
$objdrawing->setdescription('paid');
$objdrawing->setpath('./images/paid.png');
$objdrawing->setcoordinates('b15');
$objdrawing->setoffsetx(110);
$objdrawing->setrotation(25);
$objdrawing->getshadow()->setvisible(true);
$objdrawing->getshadow()->setdirection(45);
$objdrawing->setworksheet($objphpexcel->getactivesheet());
//处理中文输出问题
//需要将字符串转化为utf-8编码，才能正常输出，否则中文字符将输出为空白，如下处理：
 $str  = iconv('gb2312', 'utf-8', $str);
或者你可以写一个函数专门处理中文字符串：
function convertutf8($str)
{
   if(empty($str)) return '';
   return  iconv('gb2312', 'utf-8', $str);
}

读取excel
1.导入一个excel最简单的方法是使用phpexel的io factory，调用phpexcel_iofactory类的静态法load，它可以自动识别文档格式，包括excel2007、excel2003xml、oocalcsylk、gnumeric、csv。返回一个phpexcel的实例。
 

 //加载工厂类
include'phpexcel/iofactory.php';
//要读取的xls文件路径
$inputfilename = './sampledata/example1.xls';
/** 用phpexcel_iofactory的load方法得到excel操作对象  **/
$objphpexcel = phpexcel_iofactory::load($inputfilename);
//得到当前活动表格，调用toarray方法，得到表格的二维数组
$sheetdata =$objphpexcel->getactivesheet()->toarray(null,true,true,true);
var_dump($sheetdata);

1.创建一个excelreader去加载一个excel文档
如果你知道这个excel文档的格式，可以建立一个相应的reader去加载要读取的excel文档。但是如果你加载了错误的文档类型，可会产生不可预知的错误。

$inputfilename = './sampledata/example1.xls';
/** create a new excel5 reader  **/
$objreader = new phpexcel_reader_excel5();
//    $objreader = new phpexcel_reader_excel2007();
//    $objreader = new phpexcel_reader_excel2003xml();
//    $objreader = new phpexcel_reader_oocalc();
//    $objreader = new phpexcel_reader_sylk();
//    $objreader = new phpexcel_reader_gnumeric();
//    $objreader = new phpexcel_reader_csv();
/** load $inputfilename to a phpexcel object  **/
$objphpexcel = $objreader->load($inputfilename);
//得到当前活动sheet
$cursheet =$objphpexcel->getactivesheet();
//以二维数组形式返回该表格的数据
$sheetdata = $cursheet->toarray(null,true,true,true);
var_dump($sheetdata);

也可以用phpexcel_iofactory的createreader方法去得到一个reader对象，无需知道要读取文件的格式。

$inputfiletype = 'excel5';
//    $inputfiletype = 'excel2007';
//    $inputfiletype = 'excel2003xml';
//    $inputfiletype = 'oocalc';
//    $inputfiletype = 'sylk';
//    $inputfiletype = 'gnumeric';
//    $inputfiletype = 'csv';
$inputfilename = './sampledata/example1.xls';
/**  create a new reader of the type defined in $inputfiletype  **/
$objreader = phpexcel_iofactory::createreader($inputfiletype);
/**  load $inputfilename to a phpexcel object  **/
$objphpexcel = $objreader->load($inputfilename);
//得到当前活动sheet
$cursheet = $objphpexcel->getactivesheet();
//以二维数组形式返回该表格的数据
$sheetdata = $cursheet->toarray(null,true,true,true);
var_dump($sheetdata);

如果在读取文件之前，文件格式未知，你可以通过iofactory 的 identify()方法得到文件类型，然后通过createreader()方法去穿件阅读器。

$inputfilename = './sampledata/example1.xls';
/**  确定输入文件的格式  **/
$inputfiletype = phpexcel_iofactory::identify($inputfilename);
/** 穿件相对应的阅读器  **/
$objreader = phpexcel_iofactory::createreader($inputfiletype);
/**  加载要读取的文件  **/
$objphpexcel = $objreader->load($inputfilename);

2.设置excel的读取选项
在使用load()方法加载文件之前，可以设置读取选项来控制load的行为.
2.1.readingonly data from a spreadsheet file
setreaddataonly()方法，配置阅读器不关注表格数据的数据类型，都以string格式返回

$inputfiletype = 'excel5';
$inputfilename = './sampledata/example1.xls';
/**  create a new reader of the type defined in $inputfiletype  **/
$objreader = phpexcel_iofactory::createreader($inputfiletype);
/**  配置单元格数据都以字符串返回  **/
$objreader->setreaddataonly(true);
/**  load $inputfilename to a phpexcel object  **/
$objphpexcel = $objreader->load($inputfilename);
$sheetdata =$objphpexcel->getactivesheet()->toarray(null,true,true,true);
var_dump($sheetdata);

返回数据：

array(8) {
  [1]=>
  array(6) {
   ["a"]=>
   string(15) "integer numbers"
   ["b"]=>
    string(3)"123"
   ["c"]=>
    string(3)"234"
   ["d"]=>
    string(4)"-345"
   ["e"]=>
    string(3)"456"
    ["f"]=>
    null
  }
  [2]=>
  array(6) {
    ["a"]=>
    string(22) "floating pointnumbers"
    ["b"]=>
    string(4) "1.23"
    ["c"]=>
    string(5) "23.45"
    ["d"]=>
    string(10) "0.00e+0.00"
    ["e"]=>
    string(6) "-45.68"
    ["f"]=>
    string(7) "£56.78"
  }
  [3]=>
  array(6) {
    ["a"]=>
    string(7) "strings"
    ["b"]=>
    string(5) "hello"
    ["c"]=>
    string(5) "world"
    ["d"]=>
    null
    ["e"]=>
    string(8) "phpexcel"
    ["f"]=>
    null
  }
  [4]=>
  array(6) {
    ["a"]=>
    string(8) "booleans"
    ["b"]=>
    bool(true)
    ["c"]=>
    bool(false)
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [5]=>
  array(6) {
    ["a"]=>
    string(5) "dates"
    ["b"]=>
    string(16) "19 december 1960"
    ["c"]=>
    string(15) "10 october 2010"
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [6]=>
  array(6) {
    ["a"]=>
    string(5) "times"
    ["b"]=>
    string(4) "9:30"
   ["c"]=>
    string(5) "23:59"
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [7]=>
  array(6) {
    ["a"]=>
    string(8) "formulae"
    ["b"]=>
    string(3) "468"
    ["c"]=>
    string(7) "-20.998"
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [8]=>
  array(6) {
    ["a"]=>
    string(6) "errors"
    ["b"]=>
    string(4) "#n/a"
    ["c"]=>
    string(7) "#div/0!"
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
}
如果不设置则返回：
array(8) {
  [1]=>
  array(6) {
    ["a"]=>
    string(15) "integer numbers"
    ["b"]=>
    float(123)
    ["c"]=>
    float(234)
    ["d"]=>
    float(-345)
    ["e"]=>
    float(456)
    ["f"]=>
    null
  }
  [2]=>
  array(6) {
    ["a"]=>
    string(22) "floating point numbers"
    ["b"]=>
    float(1.23)
    ["c"]=>
    float(23.45)
    ["d"]=>
    float(3.45e-6)
    ["e"]=>
    float(-45.678)
    ["f"]=>
    float(56.78)
  }
  [3]=>
  array(6) {
    ["a"]=>
    string(7) "strings"
    ["b"]=>
    string(5) "hello"
    ["c"]=>
    string(5) "world"
    ["d"]=>
    null
    ["e"]=>
    string(8) "phpexcel"
    ["f"]=>
    null
  }
  [4]=>
  array(6) {
    ["a"]=>
    string(8) "booleans"
    ["b"]=>
    bool(true)
    ["c"]=>
    bool(false)
   ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [5]=>
  array(6) {
    ["a"]=>
    string(5) "dates"
    ["b"]=>
    float(22269)
    ["c"]=>
    float(40461)
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [6]=>
  array(6) {
    ["a"]=>
    string(5) "times"
    ["b"]=>
    float(0.39583333333333)
    ["c"]=>
    float(0.99930555555556)
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [7]=>
  array(6) {
    ["a"]=>
    string(8) "formulae"
    ["b"]=>
    float(468)
    ["c"]=>
    float(-20.99799655)
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
  [8]=>
  array(6) {
    ["a"]=>
    string(6) "errors"
    ["b"]=>
    string(4) "#n/a"
    ["c"]=>
    string(7) "#div/0!"
    ["d"]=>
    null
    ["e"]=>
    null
    ["f"]=>
    null
  }
}

reading only data from a spreadsheetfile applies to readers:
       excel2007      yes         excel5            yes         excel2003xml yes
       oocalc          yes         sylk              no          gnumeric       yes
       csv         no

2.2.readingonly named worksheets from a file
setloadsheetsonly(),设置要读取的worksheet，接受worksheet的名称作为参数。

/** phpexcel_iofactory */
include'phpexcel/iofactory.php';
$inputfiletype = 'excel5';
//  $inputfiletype = 'excel2007';
//  $inputfiletype = 'excel2003xml';
//  $inputfiletype = 'oocalc';
//  $inputfiletype = 'gnumeric';
$inputfilename ='./sampledata/example1.xls';
$sheetname = 'data sheet #2';

echo 'loading file',pathinfo($inputfilename,pathinfo_basename),' using iofactory with a definedreader type of ',$inputfiletype,'<br />';
$objreader = phpexcel_iofactory::createreader($inputfiletype);
echo 'loading sheet"',$sheetname,'" only<br />';
$objreader->setloadsheetsonly($sheetname);
$objphpexcel =$objreader->load($inputfilename);
echo '<hr />';
echo$objphpexcel->getsheetcount(),' worksheet',(($objphpexcel->getsheetcount()== 1) ? '' : 's'),' loaded<br /><br />';
$loadedsheetnames =$objphpexcel->getsheetnames();
foreach($loadedsheetnames as$sheetindex => $loadedsheetname) {
    echo $sheetindex,' -> ',$loadedsheetname,'<br />';
}

如果想读取多个worksheet，可以传递一个数组

$inputfiletype = 'excel5';
$inputfilename = './sampledata/example1.xls';
$sheetnames = array('data sheet #1','data sheet #3');
/**  create a new reader of the type defined in $inputfiletype  **/
$objreader = phpexcel_iofactory::createreader($inputfiletype);
/**  advise the reader of which worksheets we want to load  **/
$objreader->setloadsheetsonly($sheetnames);
/**  load $inputfilename to a phpexcel object  **/
$objphpexcel = $objreader->load($inputfilename);

如果想读取所有worksheet，可以调用setloadallsheets()。