利用phpexcel对数据库数据的导入excel（excel筛选）、导出excel
话不多说，请看代码：
');
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=newPHPExcel();
////////获取文档信息
////////$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',"selecti.goods_id,sum(`nums`)ASnum,i.name,i.addon,i.price,g.bnasb,i.bnash,
g.goods_id,i.goods_id,i.order_id
FROM`sdb_b2c_order_items`asi,sdb_b2c_goodsasg
WHEREi.order_idin(selectorder_idfromsdb_b2c_orderswherestatus='active'andcreatetimebetween$stimeand$etime)andi.goods_id=g.goods_idandg.cat_id=173GROUPBYh");
$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($reas$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=newPHPExcel_Worksheet_Drawing();
$objDrawing->setName('wsyImg');
$objDrawing->setDescription('Imageinsertedbyzhy');
$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;
//Excel2007保存
//$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);
//$objWriter->save(str_replace('.php','.xlsx',__FILE__));
//Excel5保存
//$objWriter=newPHPExcel_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);
functioncreateDir($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');
?>
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持毛票票!