php excel导入和导出

导入

/**
 * Excel 导入订单
 */
public function import2()
{
    $file = request()->file('file');
    $post = input('');
    if (!$file) {
        ajaxReturn(['status' => -1, 'msg' => 'file文件为空']);
    }
    $result = $this->validate(    //验证excel文件
        ['file' => $file],
        ['file' => 'fileSize:1500000|fileExt:xls'],
        ['file.fileSize' => '上传excel文件过大', 'file.fileExt' => '仅能上传xls文件']
    );
    if (true !== $result) {
        ajaxReturn(['msg' => $result, 'status' => -1]);
    }

	$CommonOrderLogic = new OrderLogic();
    $excel_data = $CommonOrderLogic->excel_import($file);

    //excel模板头数组
    $excel_model = array(
        '下单时间', '产品信息', '颜色', '数量', '重量', '订单号', '收件人国家', '物流方式', '跟踪单号', '订单金额/USD', '物流运费/CNY', '备注', '成本件/CNY', '利润/CNY'
    );

    $excel_title = $excel_data[1]; //excel头部标题部分

    foreach ($excel_title  as $k => $v) {
        if (!$v || $v == '' || $v == null) {
            unset($excel_title[$k]);
        }
    }

    if ($excel_title !== $excel_model) {
        return ['msg' => 'excel数据格式错误,请下载并参照excel模板', 'status' => -1];
    }
    unset($excel_data[1]);

    foreach ($excel_data as $k => $v) {

        $r1 = OrderModel::where(['order_id' => $v[1]])->find();
        if ($r1) {
            continue;
        }

        $order_id = $v[5];
        $had_order_id = Db::name('erp_order')->where(['order_id'=>$order_id])->find();
        
        $new = [
            'order_time' =>  $v[0],
            'product_name' =>  $v[1],
            'product_specifications' =>  $v[2],
            'product_quantity' => $v[3],
            'weight' =>  $v[4],
            'order_id' =>  $order_id,
            'country_of_consignee' =>  $v[6],
            'logistics_mode' =>  $v[7],
            'tracking_number' =>  $v[8],
            'order_amount' =>  $v[9],
            'freight' =>  $v[10],
            'order_notes' =>  $v[11],
            'cost_price' =>  $v[12],
            'profit_price' => $v[13],
            'add_time' => time(),
        ];
        if(!$had_order_id){
            OrderModel::insert($new);
        }else{
            OrderModel::where(['order_id'=>$order_id])->update($new);
        }
        
    }
    ajaxReturn(['status' => 1, 'msg' => '导入成功']);
}

导出

/**
 * excel 导出
 */
public function daochu_excel()
{
    $order_sn = input('order_sn');
    if ($order_sn) {
        $where['order_sn'] = $order_sn;
    }

    $excel_name = '导出订单记录';

    $res = OrderModel::where($where)->order('pay_time desc,add_time desc')->select();

    foreach ($res as $k => $v) {
        $res[$k]['area'] = BallGroundArea::where(['id' => $v['area_id']])->value('name');
        $res[$k]['timeduan'] = get_ground_area_time_duan($v['time_id']);
        $res[$k]['add_time'] = date('Y-m-d H:i:s',$v['add_time']);
        $res[$k]['pay_time'] = date('Y-m-d H:i:s',$v['pay_time']);
      
        if ($v['pay_state'] == 0) {
            $res[$k]['pay_state'] = '未支付';
        }elseif($v['pay_state'] == 1){
            $res[$k]['pay_state'] = '已支付';
        }
    }
    require_once ROOT_PATH . 'public/plugins/PHPExcel/PHPExcel.php';	//导入PHPExcel文件
    
    $objPHPExcel = new \PHPExcel();// 首先创建一个新的对象  PHPExcel object
    
    // 设置文件的一些属性，在xls文件——>属性——>详细信息里可以看到这些值，xml表格里是没有这些值的
	$objPHPExcel->getProperties()  //获得文件属性对象，给下文提供设置资源
      ->setCreator($excel_name)                 //设置文件的创建者
      ->setLastModifiedBy("wu")          //设置最后修改者
      ->setTitle($excel_name)    //设置标题
      ->setSubject($excel_name)  //设置主题
      ->setDescription($excel_name) //设置备注
      ->setKeywords($excel_name)        //设置标记
      ->setCategory($excel_name);                //设置类别
      
    // 给表格添加数据
    $objPHPExcel->getActiveSheet()->setCellValue('A1', '订单id');
    $objPHPExcel->getActiveSheet()->setCellValue('B1', '订单编号');
    $objPHPExcel->getActiveSheet()->setCellValue('C1', '订单类型');
    $objPHPExcel->getActiveSheet()->setCellValue('D1', '用户');
    $objPHPExcel->getActiveSheet()->setCellValue('E1', '手机号');
    $objPHPExcel->getActiveSheet()->setCellValue('F1', '总金额');
    $objPHPExcel->getActiveSheet()->setCellValue('G1', '球馆');
    $objPHPExcel->getActiveSheet()->setCellValue('H1', '球馆地址');
    $objPHPExcel->getActiveSheet()->setCellValue('I1', '订场时间段');
    $objPHPExcel->getActiveSheet()->setCellValue('J1', '下单时间');
    $objPHPExcel->getActiveSheet()->setCellValue('K1', '支付时间');
    $objPHPExcel->getActiveSheet()->setCellValue('L1', '支付状态');
    $objPHPExcel->getActiveSheet()->setCellValue('M1', '支付名称');
    $objPHPExcel->getActiveSheet()->setCellValue('N1', '核销状态');
    
    foreach ($res as $k => $v) {
        $num = $k + 2;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A' . $num, $v['order_id']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B' . $num, $v['order_sn']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C' . $num, $v['type']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D' . $num, $v['nickname']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E' . $num, $v['mobile']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F' . $num, '￥'.$v['total_amount']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G' . $num, $v['title'].' '.$v['area'].' '.$v['ground_type']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H' . $num, $v['address']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I' . $num, $v['date'].' '.$v['timeduan']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J' . $num, $v['add_time']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K' . $num, $v['pay_time']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L' . $num, $v['pay_state']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M' . $num, $v['pay_name']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N' . $num, $v['code_status']);
    
    }
    $objPHPExcel->getActiveSheet()->setTitle($excel_name);
    $objPHPExcel->setActiveSheetIndex(0);
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="订单列表.xls"');
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
}

注：导出方式

//直接生成一个文件
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('my.xlsx');

//提示下载文件  生成excel格式的xls文件
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

//提示下载文件  生成excel格式的xlsx文件
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory:: createWriter($objPHPExcel, 'Excel2007');
$objWriter->save( 'php://output');
exit;

//提示下载文件  下载一个pdf文件
header('Content-Type: application/pdf');
header('Content-Disposition: attachment;filename="01simple.pdf"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('php://output');
exit;

// 生成一个pdf文件
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('a.pdf');