//插入数据
$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_id
FROM `sdb_b2c_order_items` as i,sdb_b2c_goods as g
WHERE 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);