<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>
          

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>
          

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>
          

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>
                <ruby id="bdb3f"></ruby>
                

                
    
                
        
                
                            ## 利用PHPExcel 實現excel數據的導入導出
首先需要載入phpexcel ，以下為參考代碼。
```
//載入PHPExcel
cp::re('phpexcel');
//引入PHPExcel
 $objPHPExcel = new PHPExcel();
```
### 導入
相比導出，導入就簡單多了
利用form表單獲取上傳的excel文件
```
$file = $_FILES['file'];
if ($file['error'] == 4) $this->error('請選擇上傳excel文件');
$file_types = explode ( ".", $file['name'] );
$excel_type = array('xls','csv','xlsx');
if (!in_array(strtolower(end($file_types)),$excel_type)){
    $this->error("不是Excel文件，請重新上傳");
}
```
然后，讀取excel文件里面的數據，這里是邊讀取邊處理，或者全部讀取出來再處理
```
//設置獲取excel對象
$objReader = \PHPExcel_IOFactory::createReader('Excel5');//配置成2003版本，因為office版本可以向下兼容
$objPHPExcel = $objReader->load($file['tmp_name'],$encode='utf-8');//$file 為解讀的excel文件
//dump($objPHPExcel);die;
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得總行數
$success_item = $fail_item = 0;
//發貨
$order_model = new OrderModel();
//開始讀取數據
for($j=3;$j<=$highestRow;$j++)
{
    $order_num = $objPHPExcel->getActiveSheet()->getCell('A'.$j)->getValue();
    $poster = $objPHPExcel->getActiveSheet()->getCell("AB".$j)->getValue();//物流公司
    $logistics = $objPHPExcel->getActiveSheet()->getCell("AC".$j)->getValue();//物流單號
    $comm = $objPHPExcel->getActiveSheet()->getCell("V".$j)->getValue();//賣家備注
    //判斷條件
    if(!is_null($order_num) && $order_num){
        $res = $order_model->where('order_num',$order_num)->field('itemid,status')->find();
        if ($res && $res['status'] == 2) {
            if ((!is_null($poster) && $poster) || (!is_null($logistics) && $logistics)) {
                //更改狀態
                OrderPlanModel::addLog($res['itemid'],'確認發貨',$comm);
                $order_model->Update([
                    'status'=>3,
                    'poster'=>$poster,
                    'logistics'=>$logistics,
                ],['itemid'=>$res['itemid']]);
                $success_item ++;
            } else {
                $fail_item ++;
            }
        } else {
            $fail_item ++;
        }
    } else {
        $fail_item++;
    }
}
$this->success('成功條數:'.$success_item.'，失敗條數：'.$fail_item);
```
### 導出
設置Excel導出文件的樣式以及為每個單元格賦值，這一步是導出最重要最復雜的一步，好在我都做了注釋
```
// 設置excel文檔的屬性
$objPHPExcel->getProperties()->setCreator("cyf")
    ->setLastModifiedBy("cyf Test")
    ->setTitle("order")
    ->setSubject("Test1")
    ->setDescription("Test2")
    ->setKeywords("Test3")
    ->setCategory("Test result file");
//設置excel工作表名及文件名
$title = '待發貨訂單';
$excel_filename = '待發貨訂單_'.date('Ymd_His');
// 操作第一個工作表
$objPHPExcel->setActiveSheetIndex(0);
//第一行設置內容
$objPHPExcel->getActiveSheet()->setCellValue('A1',$excel_filename);
//合并
$objPHPExcel->getActiveSheet()->mergeCells('A1:AC1');
//設置單元格內容加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
//設置單元格內容水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//設置excel的表頭
$sheet_title = array('訂單編號','下單時間','訂單金額','訂單狀態','支付方式','付款時間','支付單號','配送方式',
    '收貨人','聯系電話','省','市','區','地址','運費','商品名稱','商品貨號','商品規格','商品數量','商品單價',
    '發貨單號','賣家備注','實付金額','微信/支付寶金額','余額支付','優惠金額','買家備注','物流公司','物流單號');
// 設置第一行和第一行的行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
$letter = 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','AA','AB','AC');
//設置單元格
$objPHPExcel->getActiveSheet()->getStyle('A2:AC2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
//首先是賦值表頭
for ($k=0;$k<29;$k++) {
    $objPHPExcel->getActiveSheet()->setCellValue($letter[$k].'2',$sheet_title[$k]);
    $objPHPExcel->getActiveSheet()->getStyle($letter[$k].'2')->getFont()->setSize(10)->setBold(true);
    //設置單元格內容水平居中
    $objPHPExcel->getActiveSheet()->getStyle($letter[$k].'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //設置每一列的寬度
    $objPHPExcel->getActiveSheet()->getColumnDimension($letter[$k])->setWidth(18);
    $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(30);

}
//開始賦值
for ($i=0;$i<$count;$i++) {
    //先確定行
    $row = $i+3;//再確定列，最頂部占一行，表頭占用一行，所以加3
    $temp = $res[$i];
    for ($j = 0;$j<29;$j++) {
        //開始為每個單元格賦值
        //初始化地址數據
        $address_arr = [];
        $address_arr = explode(',',$temp['address_address']);
        //初始化商品數據
        $product_name = $product_number = $product_attr = $product_num = $product_price = '';
        $cl = '，'.chr(10);
        foreach ($temp['o_g_model'] as $v) {
            $product_name .= $v['title'].$cl;
            $product_number .= $v['attrid'].$cl;
            $attr = [];
            $attr = json_decode($v['attr_name'],true);
            if ($attr) {
                foreach ($attr as $vv) {
                    $product_attr .= $vv.' ';
                }
                $product_attr .= $cl;
            }
            $product_num .= $v['num'].$cl;
            $product_price .= $v['user_price'].$cl;
        }
        switch ($j) {
            case 0 :
                //訂單編號
                $cellvalue = $temp['order_num'];
                break;
            case 1 :
                //下單時間
                $cellvalue = date('Y-m-d H:i:s',$temp['addtime']);
                break;
            case 2 :
                //訂單金額
                $cellvalue = $temp['price_sum'];
                break;
            case 3 :
                //訂單狀態
                $cellvalue = lang('ORDER_STATUS')[$temp['status']];
                break;
            case 4 :
                //支付方式
                $cellvalue = lang('ORDER_PAY')[$temp['pay_type']];
                break;
            case 5 :
                //付款時間
                $cellvalue = '';
                break;
            case 6 :
                //支付單號
                $cellvalue = '';
                break;
            case 7 :
                //配送方式
                $cellvalue = lang('POST_TYPE')[$temp['post_type']];
                break;
            case 8 :
                //收貨人姓名
                $cellvalue = $temp['address_name'];
                break;
            case 9 :
                //聯系電話
                $cellvalue = $temp['address_phone'];
                break;
            case 10 :
                //省
                $cellvalue = $address_arr[0];
                break;
            case 11 :
                //市
                $cellvalue = $address_arr[1];
                break;
            case 12 :
                //區
                $cellvalue = $address_arr[2];
                break;
            case 13 :
                //地址
                $cellvalue = $address_arr[3];
                break;
            case 14 :
                //運費
                $cellvalue = $temp['p_price'];
                break;
            case 15 :
                //商品名稱
                $cellvalue = $product_name;
                break;
            case 16 :
                //商品貨號
                $cellvalue = $product_number;
                break;
            case 17 :
                //商品規格
                $cellvalue = $product_attr;
                break;
            case 18 :
                //商品數量
                $cellvalue = $product_num;
                break;
            case 19 :
                //商品單價
                $cellvalue = $product_price;
                break;
            case 20 :
                //發貨單號
                $cellvalue = '已廢棄';
                break;
            case 21 :
                //賣家備注
                $cellvalue = '';
                break;
            case 22 :
                //實付金額
                $cellvalue = $temp['pay_money'];
                break;
            case 23 :
                //微信支付寶金額
                $cellvalue = $temp['pay_money'];
                break;
            case 24 :
                //余額支付
                $cellvalue = $temp['static_money'];
                break;
            case 25 :
                //優惠金額
                $cellvalue = $temp['coupon'];
                break;
            case 26 :
                //買家備注
                $cellvalue = '';
                break;
            case 27 :
                //物流公司
                $cellvalue = '';
                break;
            case 28 :
                //物流單號
                $cellvalue = '';
                break;
        }
        //賦值
        $objPHPExcel->getActiveSheet()->setCellValue($letter[$j].$row, $cellvalue);
        //設置字體大小
        $objPHPExcel->getActiveSheet()->getStyle($letter[$j].$row)->getFont()->setSize(10);
        //設置單元格內容水平居中
        $objPHPExcel->getActiveSheet()->getStyle($letter[$j].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //設置自動換行
        if ((in_array($j,[15,16,17,18,19])) && "" != $cellvalue) {
            $objPHPExcel->getActiveSheet()->getStyle($letter[$j].$row)->getAlignment()->setWrapText(true); // 自動換行
            $objPHPExcel->getActiveSheet()->getStyle($letter[$j].$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); // 垂直方向上中間居中
        }
    }
    // 設置行高
    $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(21);
}
unset($res);
```
設置完之后，將生成的excel文件，通過瀏覽器下載到本地
```
//賦值結束，開始輸出
$objPHPExcel->getActiveSheet()->setTitle($title);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$excel_filename.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
```
也可以在服務器上生成excel文件，然后發送到用戶郵箱
```
$filename = __ROOT__.'/upload/files/'.$excel_filename;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($filename);
```

                    
                
        
                
            
        
                
    
                
    
                
        
                
    
                

                







                  <ruby id="bdb3f"></ruby>
                  <p id="bdb3f"><cite id="bdb3f"></cite></p>
                    

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>
                        

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>
                        

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>
                        

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>
                              <ruby id="bdb3f"></ruby>
                              

哎呀哎呀视频在线观看