php后台execl数据日期筛选导出功能程序源码
php后台execl日期筛选导出功能程序源码
public function export_excel(){
set_time_limit(3600);
vendor('Excel.PHPExcel');
//$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
//PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '16MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = new PHPExcel();
$file_name = 'product'.date('Ymd');
$g_name = I('g_name', '', 'htmlspecialchars,trim');
$g_id = I('g_id', 0, 'intval');
$g_common = I('g_common', '', 'htmlspecialchars,trim');
$g_sn = I('g_sn', '', 'htmlspecialchars,trim');
$g_cate = I('g_cate', 0, 'intval');
$g_brand = I('g_brand', '', 'htmlspecialchars,trim');
$g_brand_id = I('g_brand_id', 0, 'intval');
$g_is_drug = I('is_drug', 0, 'intval');
$g_spec = I('g_spec', '', 'htmlspecialchars,trim');
$g_spec_id = I('g_spec_id', 0, 'intval');
$g_manufacturer = I('g_manufacturer', '', 'htmlspecialchars,trim');
$g_manufacturer_id = I('g_manufacturer_id', 0, 'intval');
$g_license = I('g_license', 0, 'htmlspecialchars,trim');
$g_license_id = I('g_license_id', 0, 'intval');
$g_is_on_sale = I('g_is_on_sale', 0, 'intval');
$g_department = I('g_department', 0, 'intval');
$g_stock = I('g_stock', 0, 'intval');
$g_is_code = I('g_is_code', 0, 'intval');
$g_code = I('g_code', 0, 'htmlspecialchars,trim');
$g_purchase_cate = I('g_purchase_cate', 0, 'intval');
$add_time1 = I('add_time1', '', 'htmlspecialchars,trim');
$add_time2 = I('add_time2', '', 'htmlspecialchars,trim');
$field = array('g.new_goods_sn, g.goods_name, g.keywords, g.effect, g.common_name, g.license_number, gs.spec_name, g.is_drug, g.drug_description, g.form, g.goods_name as new_goods_name, g.material, g.leading_effect, g.crowd, gs.spec_name as new_spec_name, g.instructions, m.manufacturer_name, b.brand_name, gt.type_name, g.is_import, c.cate_name, g.haipin_id, g.is_on_sale, g.stock, g.code');
$table = 'pro_goods g';
$where = array('g.status'=>1, 'g.cate_id'=>array('neq', 9));
if($g_id){
$kwhere['g.goods_id']= array('eq', $g_id);
}else{
if($g_name){
$kwhere['g.goods_name']= array('like',array('%'.$g_name.'%'));
}
}
if($g_common){
$kwhere['g.common_name']= array('like',array('%'.$g_common.'%'));
}
if($g_sn){
$kwhere['g.new_goods_sn']= array('like',array('%'.$g_sn.'%'));
}
if($g_cate){
$kwhere['g.cate_id']= array('eq', $g_cate);
}
if($g_brand_id){
$kwhere['g.brand_id']= array('eq', $g_brand_id);
}else{
if($g_brand){
$kwhere['g.brand_id'] = array('in', $this->getList('Brand', 'brand_name', $g_brand, 'b_id'));
}
}
if($g_is_drug){
$kwhere['g.is_drug']= array('eq', $g_is_drug);
}
if($g_spec_id){
$kwhere['g.spec_id']= array('eq', $g_spec_id);
}else{
if($g_spec){
$kwhere['g.spec_id'] = array('in', $this->getList('Goods_spec', 'spec_name', $g_spec, 's_id'));
}
}
if($g_manufacturer_id){
$kwhere['g.manufacturer_id']= array('eq', $g_manufacturer_id);
}else{
if($g_manufacturer){
$kwhere['g.manufacturer_id'] = array('in', $this->getList('Manufacturer', 'manufacturer_name', $g_manufacturer, 'm_id'));
}
}
if($g_license_id){
$kwhere['g.goods_id']= array('eq', $g_license_id);
}else{
if($g_license){
$kwhere['g.license_number']= array('like',array('%'.$g_license.'%'));
}
}
if($g_is_on_sale){
$kwhere['g.is_on_sale']= array('eq', $g_is_on_sale-1);
}
if($g_department){
$kwhere['g.department_id']= array('eq', $g_department);
}
if($g_stock){
$g_stock = $g_stock-1;
if(empty($g_stock)){
$kwhere['g.stock']= array('eq', $g_stock);
}else{
$kwhere['g.stock']= array('gt', 0);
}
}
if($g_is_code){
$g_is_code = $g_is_code-1;
if(empty($g_is_code)){
$kwhere['g.code']= array('eq', '');
}else{
$kwhere['g.code']= array('neq', '');
}
}
if($g_code){
$kwhere['g.code'] = array('like', array('%'.$g_code.'%'));
}
if($g_purchase_cate){
$kwhere['g.purchase_cate']= array('eq', $g_purchase_cate);
}
//日期索引
if($add_time1){
if($add_time2){
$add_time1 = strtotime($add_time1.' 00:00:00');
$add_time2 = strtotime($add_time2.' 23:59:59');
if($add_time2 < $add_time1){
$this->error('申请时间到期时间不能小于开始时间!');
}else{
$kwhere['g.add_time'] = array('between', array($add_time1, $add_time2));
}
}else{
$add_time1 = strtotime($add_time1.' 00:00:00');
$kwhere['g.add_time'] = array('egt', $add_time1);
}
}else{
if($add_time2){
$add_time2 = strtotime($add_time2.' 23:59:59');
$kwhere['g.add_time'] = array('elt', $add_time2);
}
}
if(count($kwhere)){
$kwhere['_logic'] = 'AND';
$where['_complex'] = $kwhere;
}
$count = M('Goods')->table($table)
->join('LEFT JOIN pro_brand b ON g.brand_id=b.b_id')
->join('LEFT JOIN pro_goods_spec gs ON g.spec_id=gs.s_id')
->join('LEFT JOIN pro_cate c ON g.cate_id=c.cate_id')
->join('LEFT JOIN pro_manufacturer m ON g.manufacturer_id=m.m_id')
->join('LEFT JOIN pro_goods_type gt ON g.type_id=gt.t_id')
->where($where)->count();
$page = 1;
$page_number = 1024;
$count += $page_number;
$arr = array();
$is_drug = C(IS_DRUG);
$begin = ($page -1) * $page_number;
while($begin < $count){
$res = M('Goods')->table($table)
->join('LEFT JOIN pro_brand b ON g.brand_id=b.b_id')
->join('LEFT JOIN pro_goods_spec gs ON g.spec_id=gs.s_id')
->join('LEFT JOIN pro_cate c ON g.cate_id=c.cate_id')
->join('LEFT JOIN pro_manufacturer m ON g.manufacturer_id=m.m_id')
->join('LEFT JOIN pro_goods_type gt ON g.type_id=gt.t_id')
->where($where)->field($field)->limit($begin, $page_number)->select();
foreach($res as $key=>$val){
$res[$key]['is_drug'] = $is_drug[$val['is_drug']];
$res[$key]['is_import'] = $val['is_import']? '是': '';
$res[$key]['is_on_sale'] = empty($val['is_on_sale'])? '否': '是';
$new_key = $begin + $key;
$arr[] = $res[$key];
}
unset($res);
$page ++;
$begin = ($page -1) * $page_number;
}
$letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
$title = array("新编码", "商品名称", "商品关键字", "商品功效", "通用名称", "批准文号", "商品规格", "是否处方药", "处方药说明", "剂型", "产品名称", "主要原料", "主要作用", "适宜人群", "产品规格", "用法用量", "生产企业", "品牌名称", "药品类别", "是否进口", "商品分类", "海品id", "是否采购", "库存", "条形码");
$objPHPExcel->setActiveSheetIndex(0);
$j =0;
foreach($title as $t_val){
$index = $letter[$j];
$objPHPExcel->getActiveSheet()->setCellValue($index."1", $t_val);
$j++;
}
$arr_count = count($arr);
$i = 2;
foreach($arr as $key=>$val){
if($key < $arr_count){
$j =0;
foreach($val as $val2){
$index = $letter[$j];
$objPHPExcel->getActiveSheet()->setCellValue($index.$i, $val2);
$j++;
}
$i++;
}
}
$objPHPExcel->getActiveSheet()->setTitle('商品信息表');
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename="'.$file_name.'.xls"');
header('Cache-Control:max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
页:
[1]