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

ThinkPHP中调用PHPExcel的实现代码

程序员文章站 2024-03-08 16:12:40
核心代码: //引入phpexcel vendor('phpexcel.phpexcel'); // create new phpexcel object...

核心代码:

//引入phpexcel
vendor('phpexcel.phpexcel');

// create new phpexcel object
$objphpexcel = new phpexcel();

//设置文档属性
$objphpexcel->getproperties()
    ->setcreator("web100.cc")
    ->setlastmodifiedby("web100.cc")
    ->settitle("office 2007 xlsx test document")
    ->setsubject("office 2007 xlsx test document")
    ->setdescription("test document generated using php classes.")
    ->setkeywords("office 2007 openxml php")
    ->setcategory("test result file");
    
//设置当前活动的sheet
$objphpexcel->setactivesheetindex(0);

//选定当前sheet
$objactsheet = $objphpexcel->11getactivesheet();
 
//设置sheet名字
$objactsheet->settitle('phpexcel demo');
 
//设置默认行高
$objactsheet->getdefaultrowdimension()->setrowheight(15);
 
//由phpexcel根据传入内容自动判断单元格内容类型
$objactsheet->setcellvalue('a1', "firstname");
$objactsheet->setcellvalue('b1', "lastname");
$objactsheet->setcellvalue('c1', "phone");
$objactsheet->setcellvalue('d1', "fax");
$objactsheet->setcellvalue('e1', "address");
$objactsheet->setcellvalue('f1', "zip");
$objactsheet->setcellvalue('g1', "date");
 
 
$objactsheet->setcellvaluebycolumnandrow(0, 8, 'firstname');
$objactsheet->setcellvaluebycolumnandrow(1, 8, 'lastname');
 
 
// 单元格填充值
$objactsheet->setcellvalue('a2', "小风");
$objactsheet->setcellvalue('b2', "wang");

//设置列宽
$objactsheet->getcolumndimension('c')->setwidth('20');
 
 
// 设置行高
$objactsheet->getrowdimension('9')->setrowheight(20); //第9行

// 设置字体
$objactsheet->getstyle('a1')->getfont()->setname('宋体');
// 字号
$objactsheet->getstyle('a1')->getfont()->setsize(12);
// 加粗
$objactsheet->getstyle('a1')->getfont()->setbold(true);

//说明:$objactsheet->getstyle('a1:g10'),可以通过这种方式来选择一片单元格!

// 设置单元格格式
$objactsheet->getcell('c2')->setvalueexplicit('861391327543258', phpexcel_cell_datatype::type_numeric);
 
// 日期
$objactsheet->setcellvalue('g2', '2008-12-31');
$objactsheet->getstyle('g2')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_date_yyyymmddslash);
 
// 时间戳
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$objactsheet->setcellvalue('g3', phpexcel_shared_date::phptoexcel($time));
$objactsheet->getstyle('g3')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_date_yyyymmddslash);
 
// url
$objactsheet->setcellvalue('g11', 'www.spalybow.com');
$objactsheet->getcell('g11')->gethyperlink()->seturl('http://www.spalybow.com');
 
// 另一个sheet
$objactsheet->setcellvalue('g12', 'sheetb');
$objactsheet->getcell('g12')->gethyperlink()->seturl("sheet://'sheetb'!a1");
 
// 水平居上
$objactsheet->getstyle('a9:b9')->getalignment()->setvertical(phpexcel_style_alignment::vertical_top);
 
// 单元格换行
$objactsheet->getstyle('g2:g3')->getalignment()->setwraptext(true);
 
// 合并
$objactsheet->mergecells('a18:e22');
 
 
// 隐藏d列
$objactsheet->getcolumndimension('d')->setvisible(false);
 
//
$objactsheet->getcolumndimension('e')->setoutlinelevel(1);
$objactsheet->getcolumndimension('e')->setvisible(false);
$objactsheet->getcolumndimension('f')->setoutlinelevel(1);
$objactsheet->getcolumndimension('f')->setvisible(false);
$objactsheet->getcolumndimension('f')->setcollapsed(true);
 
// 固定第一行
$objactsheet->freezepane('a2');
 
// 保护工作表
$objactsheet->getprotection()->setpassword('phpexcel');
$objactsheet->getprotection()->setsheet(true);
$objactsheet->getprotection()->setsort(true);
$objactsheet->getprotection()->setinsertrows(true);
$objactsheet->getprotection()->setformatcells(true);
 
//设置边框
$sharedstyle1 = new phpexcel_style();
$sharedstyle1->applyfromarray(
  array('borders' => array(
                'left'    => array('style' => phpexcel_style_border::border_medium)
              )
     ));
$objactsheet->setsharedstyle($sharedstyle1, "b1:b10");
 
// 创建一个新的工作表
$objworksheet1 = $objphpexcel->createsheet();
$objworksheet1->settitle('sheetb');
 
 
$objphpexcel->setactivesheetindex(1);
 
 
// 创建一个图片
$gdimage = @imagecreatetruecolor(200, 20) or die('cannot initialize new gd image stream');
$textcolor = imagecolorallocate($gdimage, 255, 255, 255);
imagestring($gdimage, 1, 5, 5, 'created with phpexcel (c1gstudio.com)', $textcolor);
 
// 把创建的图片添加到工作表
$objdrawing = new phpexcel_worksheet_memorydrawing();
$objdrawing->setname('sample image');
$objdrawing->setdescription('sample image');
$objdrawing->setimageresource($gdimage);
$objdrawing->setrenderingfunction(phpexcel_worksheet_memorydrawing::rendering_jpeg);
$objdrawing->setmimetype(phpexcel_worksheet_memorydrawing::mimetype_default);
$objdrawing->setheight(36);
$objdrawing->setworksheet($objactsheet);
 
$objphpexcel->setactivesheetindex(0);
 
// 保存
$objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5');
$objwriter->save('testexcel'.time().'.xls');