/** * Notes:excel 导出 * Created by xxg@seabig.cn * DateTime: 2020/6/15 11:37 * /?t={{'mes'|encrypt}}&m={{'materiaEstimate'|encrypt}}&a={{'outExcel'|encrypt}} */ public function outExcel() { $this->_globals(); $input = $_GET; ob_end_clean(); //解决乱码核心 $spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet(); $materials = $this->mBasic->getList("mes_material", array("erp_cat = 18 and product_category = 2 and is_del = 0 and status = 1 and (name != '' or sn != '' ) and name not like '%?%'"), FALSE, 'name', 'asc'); $staffs = $this->mBasic->getList('hr_staffs', array("erp_dep like '%23%'"), false, 'id', 'asc'); if ($input['month']) { $tap_date = ''.date("Y").''.$input['month'].''; } else{ $tap_date = $tmp_date = date("Ym"); } //切割出年份 $tmp_year = substr($tap_date,0,4); //切割出月份 $tmp_mon = substr($tap_date,4,2); //获取后三个月的数据 $rearOne = date("m",mktime(0,0,0,$tmp_mon + 1,1,$tmp_year)); $rearTow = date("m",mktime(0,0,0,$tmp_mon + 2,1,$tmp_year)); $rearThee = date("m",mktime(0,0,0,$tmp_mon + 3,1,$tmp_year)); $report_month = $input['month']; $months = array($rearOne, $rearTow, $rearThee); $fixedColsNum = 12; $totalColsNum = $fixedColsNum + 2 * count($staffs); foreach ($months as $m => $month) { $data = $this->sheet($report_month, $month, $materials, $staffs); if ($m == 0) { $worksheet = $spreadsheet->getActiveSheet(); } else { $worksheet = $spreadsheet->createSheet(); } $worksheet->setTitle(intval($month) . '月'); $worksheet->fromArray($data); $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(43); $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12); $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(12); // $worksheet->getColumnDimension('B')->setAutoSize(true); for ($i = 1; $i <= $fixedColsNum; $i++) { $colName = $this->intToChr2($i); //合并产品 $worksheet->mergeCells($colName."1:".$colName."2"); } for ($j = $fixedColsNum + 1; $j < $totalColsNum; $j = $j + 2) { //echo $j . "================="; $colNameStart = $this->intToChr2($j); //echo $colNameStart . "================="; $colNameEnd = $this->intToChr2($j+1); //echo $j + 1 . "================="; //echo $colNameEnd . "================="; //echo "<br>"; //合并业务员姓名 $worksheet->mergeCells($colNameStart."1:".$colNameEnd."1"); } } $name = date("Y")."年".$input['month']."份底上报预估数量"; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$name.'.xls"'); header('Cache-Control: max-age=0'); $writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xls'); $writer->save('php://output'); $spreadsheet->disconnectWorksheets(); unset($spreadsheet); exit; } protected function sheet($report_month, $month, $materials, $staffs) { $products = array(); $data = array( array('存货编码', '存货名称', '规格型号', '单价', '安全库存量调整', '可用量', '预估数量汇总', '预估销量汇总', '计划数量', '计划备货量', '计划完成情况', '备货后库存'), array(null, null, null, null, null, null, null, null, null, null, null, null), ); foreach ($materials as $key => $v) { $products[$v['id']] = $v; //获取某个月的预估总数 $numberTotal = $this->mDb->getOne("SELECT sum(number) FROM " . get_table('mes_estimate') . " WHERE material_id = {$v['id']} AND `month` = '{$month}' AND report_month ='{$report_month}' AND is_del = 0"); //预估销售汇总 $mobeyTotal = $numberTotal * $v['unit_price']; $tmp = array( $v['sn'], $v['name'], $v['standard_pecification'] == $v['standard_pecification'] ? $v['standard_pecification'] : '-', //规格型号 $v['unit_price'], $v['safety_stock'], '', //可用量 $numberTotal == '' ? '0' : $numberTotal, $mobeyTotal == '' ? '0' : $mobeyTotal, '', '', '', '' ); $data[$key + 2] = $tmp; } $tmpStaffs = array(); foreach ($staffs as $k => $val) { $tmpStaffs[$val['id']] = $val; // 获取预估的人数 $dongbao = $this->mBasic->getList("mes_estimate", array("staff_id = {$val['id']}", "report_month='{$report_month}'", "month='{$month}'"), false); if ($dongbao) { foreach ($dongbao as $l) { $productCode = $products[$l['material_id']]['sn']; $tmpStaffs[$l['staff_id']]['products'][$productCode] = $l['number']; } } } foreach ($staffs as $key => $vl) { $data[0][] = $vl['name']; $data[0][] = null; $data[1][] = '数量'; $data[1][] = '金额'; foreach ($materials as $j => $v) { //循环产品 $key = 2 + $j; //获取产品编号 if ($tmpStaffs && isset($tmpStaffs[$vl['id']]['products']) && array_key_exists($v['sn'], $tmpStaffs[$vl['id']]['products'])) { //$staffs[$vl['id']]['products'] 根据上面设置键的staff_id 进行比较 $data[$key][] = $tmpStaffs[$vl['id']]['products'][$v['sn']]; $data[$key][] = $tmpStaffs[$vl['id']]['products'][$v['sn']] * $v['price']; } else { $data[$key][] = ""; $data[$key][] = ""; } } } return $data; } public function intToChr2($int) { if (!is_int($int) || $int <= 0) { return ''; } $array = 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'); $str = ''; //如果能整除 if ($int % 26 == 0) { $str .= $this->intToChr2(($int / 26) - 1); $str .= $array[25]; return $str; } elseif ($int > 26) { $str .= $this->intToChr2((int)floor($int / 26)); $str .= $array[$int % 26 - 1]; return $str; } else { return $array[$int - 1]; } }
execl等类库的手册:https://phpspreadsheet.readthedocs.io/en/latest/