对导出的excel作了简单的样式调整附带筛选和文件的加密 无 ?php/**author zhy*date 2012 06 12*for excel*/date_default_timezone_set(prc); error_reporting(e_all);error_reporting(0);ini_set('display_errors', true);ini_set('display_startup_errors'
对导出的excel作了简单的样式调整 附带 筛选和文件的加密 getproperties();///////print_r($objprops);///////echo
;///////$objprops->setdescription(test_123456);///////print_r($objprops);$objphpexcel->setactivesheetindex(0) ->setcellvalue('a5','商品编码') ->setcellvalue('b5','货号') ->setcellvalue('c5','商品名称') ->setcellvalue('d5','采购量');//设置选定sheet表名$objphpexcel->getactivesheet()->settitle('祖名');//设置字体样式$objphpexcel->getactivesheet()->getstyle('a1')->getfont()->setname('arial')->setsize(25);//////->setunderline(true);/////->getcolor()->setargb('ffff0000');///->setbold(true);//合并单元格 给单元格赋值(数值，字符串，公式)$objphpexcel->getactivesheet()->mergecells('a1:d3')->setcellvalue('a1', 'zhongyi清单');///////$objphpexcel->getactivesheet()->mergecells('a4:d4')->setcellvalue('a4', =sum(e4:f4));$date_now = date(y-m-d);$objphpexcel->getactivesheet()->mergecells('a4:d4')->setcellvalue('a4', 采购日期：.$date_now. .$apm.　);//设置单列宽度$objphpexcel->getactivesheet()->getcolumndimension('a')->setwidth(15);$objphpexcel->getactivesheet()->getcolumndimension('b')->setwidth(20);//$objphpexcel->getactivesheet()->getcolumndimension('g')->setrowheight(50);/$objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(44);$objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(15);//大边框样式 边框加粗$lineborder = array( 'borders' => array( 'outline' => array( 'style' => phpexcel_style_border::border_thick, 'color' => array('argb' => '000000'), ), ),);//表头样式$head = array( 'font' => array( 'bold' => true ), 'alignment' => array( 'horizontal' => phpexcel_style_alignment::horizontal_center, 'vertical' => phpexcel_style_alignment::vertical_center ), );//标题样式$title = array( 'font' => array( 'bold' => true ),);//居中对齐$center = array( 'alignment' => array( 'horizontal' => phpexcel_style_alignment::horizontal_center, 'vertical' => phpexcel_style_alignment::vertical_center ),);//靠右对齐$right = array( 'alignment' => array( 'horizontal' => phpexcel_style_alignment::horizontal_right, 'vertical' => phpexcel_style_alignment::vertical_center ),);//细边框样式$linestyle = array( 'borders' => array( 'outline' => array( 'style' => phpexcel_style_border::border_thin, 'color' => array('argb' => 'ff000000'), ), ),);$objphpexcel->getactivesheet()->getstyle('a1:d3')->applyfromarray($head);///->getalignment()->gethorizontal('');///->getborders()->gettop()->setborderstyle('');//->setwraptext(true);自动换行$objphpexcel->getactivesheet()->getstyle('a4:d4')->applyfromarray($right); $objphpexcel->getactivesheet()->getstyle('a5:d5')->applyfromarray($title); //填充色/////$objphpexcel->getactivesheet()->getstyle('a1')->getfill()->getstartcolor()->setargb('ffff0000');/ //插入数据$dsql->execute('omebrand_list',select i.goods_id , sum( `nums` ) as num, i.name,i.addon,i.price,g.bn as b,i.bn as h,g.goods_id,i.goods_id,i.order_idfrom `sdb_b2c_order_items` as i,sdb_b2c_goods as gwhere i.order_id in (select order_id from sdb_b2c_orders where status ='active' and createtime between $stime and $etime) and i.goods_id=g.goods_id and g.cat_id=173 group by h);$m = 0; unset($re); while($row=$dsql->getobject('omebrand_list')) { $re[$m] = get_object_vars($row); $m++; }$row_count = 5;$objphpexcel->setactivesheetindex(0) ->setcellvalue('a6', 12325416541) ->setcellvalue('b6', 4962132165262) ->setcellvalue('c6', 121515212515241521) ->setcellvalue('d6', 96215465415);foreach($re as $r => $datarow) { $baserow = 6; $row = $baserow + $r; $bn=$datarow[h]; $goods_id = $datarow[goods_id]; $spec_value = ; $aa = unserialize($datarow[addon]); if ($aa['product_attr']){ foreach ($aa['product_attr'] as $arr_special_info) { $spec_value = $arr_special_info['value']; } } preg_match_all('/\-?\d+\.?\d*/i',$spec_value,$row1); $num = $row1[0][0]; $all = $num*$datarow[num]; if($spec_value==''){ $all=$datarow['num']; //$prce=$datarow[price]; } $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$row, $datarow['b']) ->setcellvalue('b'.$row, $bn) ->setcellvalue('c'.$row, $datarow['name']) ->setcellvalue('d'.$row, $all); $objphpexcel->getactivesheet()->getstyle('a'.$row_count)->applyfromarray($linestyle); $objphpexcel->getactivesheet()->getstyle('b'.$row_count)->applyfromarray($linestyle); $objphpexcel->getactivesheet()->getstyle('c'.$row_count)->applyfromarray($linestyle); $objphpexcel->getactivesheet()->getstyle('d'.$row_count)->applyfromarray($linestyle); $baserow++; $row_count++;}$objphpexcel->getactivesheet()->getstyle('a'.$row_count)->applyfromarray($linestyle); $objphpexcel->getactivesheet()->getstyle('b'.$row_count)->applyfromarray($linestyle);$objphpexcel->getactivesheet()->getstyle('c'.$row_count)->applyfromarray($linestyle);$objphpexcel->getactivesheet()->getstyle('d'.$row_count)->applyfromarray($linestyle); $objphpexcel->getactivesheet()->getstyle('a5:d'.$row_count)->applyfromarray($center); $objphpexcel->getactivesheet()->getstyle('a1:d'.$row_count)->applyfromarray($lineborder);//设置打印页边距$objphpexcel->getactivesheet()->getpagemargins()->settop(0);$objphpexcel->getactivesheet()->getpagemargins()->setright(0);$objphpexcel->getactivesheet()->getpagemargins()->setleft(0);$objphpexcel->getactivesheet()->getpagemargins()->setbottom(0);//设置纸张类型$objphpexcel->getactivesheet()->getpagesetup()->setpapersize(phpexcel_worksheet_pagesetup::papersize_a4);//设置自动筛选$objphpexcel->getactivesheet()->setautofilter('a5:d'.$row_count);//设置自动换行$objphpexcel->getactivesheet()->getstyle('b6:b'.$row_count)->getalignment()->setwraptext(true);//设置格式化数字$objphpexcel->getactivesheet()->getstyle('a6:a'.$row_count)->getnumberformat()->setformatcode('0000000000');//设置安全级别$md=md5(time());$md=substr($md,0,8);$objphpexcel->getactivesheet()->getprotection()->setpassword($md);$objphpexcel->getactivesheet()->getprotection()->setsheet(true);//$objphpexcel->getactivesheet()->getprotection()->setsort(true);$objphpexcel->getactivesheet()->getprotection()->setinsertrows(true);$objphpexcel->getactivesheet()->getprotection()->setformatcells(true);//添加图片 /*$obj=$objphpexcel->getactivesheet();$objdrawing = new phpexcel_worksheet_drawing(); $objdrawing->setname('wsyimg'); $objdrawing->setdescription('image inserted by zhy'); $objdrawing->setpath('./wsy.jpg'); $objdrawing->setheight(50); $objdrawing->setcoordinates('h23'); $objdrawing->setoffsetx(60); $objdrawing->setrotation(-10); /$objdrawing->getshadow()->setvisible(true); $objdrawing->getshadow()->setdirection(-20); / $objdrawing->setworksheet($obj);*///页眉页脚//$objphpexcel->getactivesheet()->getheaderfooter()->setoddheader('zhy'); //$objphpexcel->getactivesheet()->getheaderfooter()->setoddfooter('end'); $objphpexcel->setactivesheetindex(0);$tname=date('y-m-dh',time());$tnam=iconv('utf-8','gbk','祖名订单');$tname=$tnam.$tname;// excel 2007保存//$objwriter = new phpexcel_writer_excel2007($objphpexcel); //$objwriter->save(str_replace('.php', '.xlsx', __file__)); // excel 5保存 //$objwriter = new phpexcel_writer_excel5($objphpexcel); //$objwriter->save(str_replace('.php', '.xls', __file__)); $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5');$objwriter->save(str_replace('.php', '.xls', __file__));//$url = /data/home/htdocs/ec/public/files/.date(y)./.date(ym)./;createdir($url);function createdir($dir) { if (!is_dir ($dir )) { mkdir($dir, 0777, true); chmod($dir, 0777); chown( $dir, 'daemon' ); chgrp( $dir, 'daemon' ); }}$name='forexmple_excel';rename(str_replace('.php', '.xls', __file__), $name.'.xls');?>