PHPExcel中文帮助手册|PHPExcel使用方法(分享)

下面是总结的几个使用方法

include 'phpexcel.php';
include 'phpexcel/writer/excel2007.php';
//或者include 'phpexcel/writer/excel5.php'; 用于输出.xls的
创建一个excel
$objphpexcel = new phpexcel();
保存excel—2007格式
$objwriter = new phpexcel_writer_excel2007($objphpexcel);
//或者$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');
——————————————————————————————————————–
设置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);
}
//从数据库输出数据处理方式
从数据库读取数据如：
$db = new mysql($dbconfig);
$sql = "select * from 表名";
$row = $db->getall($sql); // $row 为二维数组
$count = count($row);
for ($i = 2; $i <= $count+1; $i++) {
 $objphpexcel->getactivesheet()->setcellvalue('a' . $i, convertutf8($row[$i-2][1]));
 $objphpexcel->getactivesheet()->setcellvalue('b' . $i, convertutf8($row[$i-2][2]));
 $objphpexcel->getactivesheet()->setcellvalue('c' . $i, convertutf8($row[$i-2][3]));
 $objphpexcel->getactivesheet()->setcellvalue('d' . $i, convertutf8($row[$i-2][4]));
 $objphpexcel->getactivesheet()->setcellvalue('e' . $i, convertutf8(date("y-m-d", $row[$i-2][5])));
 $objphpexcel->getactivesheet()->setcellvalue('f' . $i, convertutf8($row[$i-2][6]));
 $objphpexcel->getactivesheet()->setcellvalue('g' . $i, convertutf8($row[$i-2][7]));
 $objphpexcel->getactivesheet()->setcellvalue('h' . $i, convertutf8($row[$i-2][8]));
}
 
在默认sheet后，创建一个worksheet
echo date('h:i:s') . " create new worksheet object\n";
$objphpexcel->createsheet();
$objwriter = phpexcel_iofactory::createwriter($objexcel, 'excel5');
$objwriter-save('php://output');

以上这篇phpexcel中文帮助手册|phpexcel使用方法(分享)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。