PhpSpreadsheet 引入类库 导出 excel

/**
     * 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 \PhpOffice\PhpSpreadsheet\Spreadsheet();

        $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 = \PhpOffice\PhpSpreadsheet\IOFactory::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/