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 no2.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,'
';
$objreader = phpexcel_iofactory::createreader($inputfiletype);
echo 'loading sheet',$sheetname,' only
';
$objreader->setloadsheetsonly($sheetname);
$objphpexcel =$objreader->load($inputfilename);
echo '';
echo$objphpexcel->getsheetcount(),' worksheet',(($objphpexcel->getsheetcount()== 1) ? '' : 's'),' loaded
';
$loadedsheetnames =$objphpexcel->getsheetnames();
foreach($loadedsheetnames as$sheetindex => $loadedsheetname) {
echo $sheetindex,' -> ',$loadedsheetname,'
';
}
如果想读取多个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()。
http://www.bkjia.com/phpjc/327797.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/327797.htmltecharticleexcel的写入与生成操作： 复制代码 代码如下: include 'phpexcel.php'; include 'phpexcel/writer/excel2007.php'; //或者include 'phpexcel/writer/excel5.php'; 用于输出...