php excel ajax 导出文件

Love The Way You Lie 2022-03-16 03:08 514阅读 0赞

$list = [];//要导出的数据
    
            $path = dirname(__FILE__); //找到当前脚本所在路径
            vendor("PHPExcel.PHPExcel.PHPExcel");
            vendor("PHPExcel.PHPExcel.Writer.IWriter");
            vendor("PHPExcel.PHPExcel.Writer.Abstract");
            vendor("PHPExcel.PHPExcel.Writer.Excel5");
            vendor("PHPExcel.PHPExcel.Writer.Excel2007");
            vendor("PHPExcel.PHPExcel.IOFactory");
            $objPHPExcel = new \PHPExcel();
            $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
            $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
    
            // 设置表头信息
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '订单编号')
                ->setCellValue('B1', '原始单号')
                ->setCellValue('C1', '产品编号')
                ->setCellValue('D1', '产品名称')
                ->setCellValue('E1', '产品规格')
                ->setCellValue('F1', '产品批号')
                ->setCellValue('G1', '产品数量')
                ->setCellValue('H1', '数量单位')
                ->setCellValue('I1', '单价')
                ->setCellValue('J1', '生产日期')
                ->setCellValue('K1', '有限期至')
                ->setCellValue('L1', '收货单位')
                ->setCellValue('M1', '联系人')
                ->setCellValue('N1', '联系方式')
                ->setCellValue('O1', '收货地址');
    
            /*--------------开始从数据库提取信息插入Excel表中------------------*/
    
            $i=2;
            foreach ($list as $key=>$val){
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $val['bl_order_no']);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $val['orig_code']);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $val['goods_code']);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $val['goods_name']);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $val['goods_spec']);
                $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $val['goods_batch']);
                $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $val['goods_num']);
                $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $val['goods_unit']);
                $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $val['price']);
                $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $val['make_date']);
                $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $val['validity_date']);
                $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $val['to_corp']);
                $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $val['to_user_name']);
                $objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $val['to_mobile']);
                $objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $val['to_addr']);
                $i++;
            }
    
            /*--------------下面是设置其他信息------------------*/
    
            $objPHPExcel->getActiveSheet()->setTitle('入库单产品详情');      //设置sheet的名称
            $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来
    
            $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
            $filename = "excel";
           header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
           header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $filePath = 'excel/'.$filename.'.xlsx';
            ob_start();
            //$objWriter->save($filePath);
            $PHPWriter->save('php://output'); //表示在$path路径下面生成demo.xlsx文件
            $xlsdata = ob_get_contents();
           ob_end_clean();
            return ['filename' => $filename.'.xlsx', 'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsdata)];

$("#export").click(function () {
        var search = $("[name=search_from]").serializeArray();
        $.ajax({
            type:'post',
            url:'{:url("export")}',
            data: search,
            success:function (res) {
                    var $a = $("<a>");
                    $a.attr("href", res.file);
                    $a.attr("download", res.filename);
                    $("body").append($a);
                    $a[0].click();
                    $a.remove();
            }
        })
    });

thinkphp5 excel导出