php教程|php手册
利用,phpexcel,数据库,数据,导入,excel,筛选
php教程-php手册
微信淘宝客源码百度云盘,vscode点击方法名跳转,ubuntu部署ntp,修改tomcat启动标识,爬虫爬了,php逗号连接符,惠州站外seo哪里好,网站下载asp代码,恩斯道模板怎么设置lzw
对导出的excel作了简单的样式调整附带筛选和文件的加密 无 ?php/**author zhy*date 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’
软件下载站源码 带采集,vscode终端运行没有显示,itools+ubuntu,tomcat内网不能访问,爬虫软件验证,php数组整理,荆门广告seo推广开户,源码网站 ptcms,大商创 模板 标题 首页lzw
58同城源码包,ubuntu 重定向串口,idea同一个tomcat,小怪养爬虫,济南php程序员人才招聘,事业编seolzw
对导出的excel作了简单的样式调整 附带 筛选和文件的加密
.CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;}.CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;}.CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;}.CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;}.CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;}.CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;}.CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;}.CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;}.CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;}.CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;}
<?php/**author zhy*date 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, TRUE);define(EOL,(PHP_SAPI == cli) ? PHP_EOL :
‘);require_once (‘../Classes/PHPExcel.php’);require_once(“config.php”);require_once(“mysql.class.php”);//根据时间生成采购报表$time = date(“a”);$minute = date(“i”);$apm = “”;if($time==’pm’){ $apm= $time; $stime = mktime(12,00,00,date(‘m’),date(‘d’)-1,date(‘Y’)); $etime = mktime(11,59,59,date(‘m’),date(‘d’),date(‘Y’));}else{ $apm= $time; $stime = mktime(12,00,00,date(‘m’),date(‘d’)-1,date(‘Y’)); $etime = mktime(11,59,59,date(‘m’),date(‘d’),date(‘Y’));}//实例化excel类$objPHPExcel = new PHPExcel();////////获取文档信息////////$objProps = $objPHPExcel->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 保存//$objWriter = new PHPExcel_Writer_Excel($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’);?>
