欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-09 16:17:41
下面是总结的几个使用方法 include 'phpexcel.php'; include 'phpexcel/writer/excel2007.php';...

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

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使用方法(分享)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。