phpexcel数据导入数据库

这里分2中情况:

1.后缀为xls和xlsx

2.后缀为csv

注意:后缀为csv的一次可以导入上万条数据,后缀为xls和xlsx的能导几千条,csv是一个黑科技


后缀为csv的情况:

1.获取上传的文件名$_FILES['file']['name']

2.上传的零时文件$_FILES['file']['tmp_name']

3.获取文件的后缀名:主要用到的:explode

4.设置上传路径$savePath 和 给文件创建的新的文件名(一般以时间来命名)

5.复制文件到项目中:用到:copy($tmp_file,新的路径.新的文件名)

6.读取保存的文件:file_get_contents(新的路径.新的文件名);

7.进行数据的替换

8.插入数据库

9.删除上传的文件

具体代码如下


后缀为xls和xlsx(excel)

1.需要导入phpexcel类

    import("Org.Util.PHPExcel");

    $objPHPExcel = new \PHPExcel();

    import("Org.Util.PHPExcel.IOFactory");

    import("Org.Util.PHPExcel.Cell");

2.关于phpexcel的学习这里就不介绍了(可以自己查文档)

3.具体代码如下


代码如下:

    public function order_import()
    {
        header('content-type:text/html;charset=utf-8');
//        if(!empty($this->now_id)){
            set_time_limit(0);
            if (!empty ($_FILES ['file'] ['name'])) {
                //上传的文件名
                $old_name = $_FILES ['file'] ['name'];
                //上传的零时文件
                $tmp_file = $_FILES ['file'] ['tmp_name'];
                //以.将文件名分割成数组
                $file_types = explode(".", $old_name);
                //获取文件名的后缀
                $file_type = $file_types [count($file_types) - 1];   //string(3) "xls"

                /*判别是不是.xls文件,判别是不是excel文件*/
                if (strtolower($file_type) != "xls" && strtolower($file_type) != "xlsx") {

                    if (strtolower($file_type) != "csv") {
                        $this->ajaxReturn(array('code' => '100003', 'result' => '上传类型错误'));
                    }
                    /*设置上传路径*/
                    $savePath = C('IMAGE_SAVE_PATH') . C('XLS_DIR');
                    /*以时间来命名上传的文件*/
                    $str = date('Ymdhis');

                    $file_name = $str . "." . $file_type;  //string(18) "20170526020302.xls"

                    if (!copy($tmp_file, $savePath . $file_name)) {
                        $this->ajaxReturn(array('code' => '100002', 'result' => '上传失败'));
                    }
//                读取保存的文件
                    $show_data = file_get_contents($savePath . $file_name);
                    $show_data = str_replace('"', '', $show_data);
                    $wrap = array("\n\r", "\r", "\n");
                    $replace = '<br/>';
                    $newstr = str_replace($wrap, $replace, $show_data);
                    $list = explode('<br/>', trim($newstr));
                    foreach ($list as $list_k => $list_v) {
                        $test_kong = explode(',', $list_v);
                        if ($test_kong[0] == '') {
                            unset($list[$list_k]);
                        }
                    }
                    $list = array_values($list);


//                对文件进行中文乱码处理,以及插入数据库
                    M()->startTrans();
                    try {
                        foreach ($list as $show_data_k => $show_data_v) {
                            if ($show_data_v) {
                                $handle[] = explode(',', iconv('gbk', 'utf-8', $show_data_v));

                                $mark = $str . '-' . $this->now_id;//上传标记

                                if ($show_data_k >= 1) {
                                    if ($handle[$show_data_k][17] == '') {
                                        $handle[$show_data_k][17] = date('1970-1-1 0:0:0', time());
                                    }
                                    if ($handle[$show_data_k][18] == '') {
                                        $handle[$show_data_k][18] = date('1970-1-1 0:0:0', time());
                                    }
                                    if ($handle[$show_data_k][51] == '') {
                                        $handle[$show_data_k][51] = date('1970-1-1 0:0:0', time());
                                    }
                                    $shopName = $handle[$show_data_k][26];
                                    $ordno = trim(trim($handle[$show_data_k][0], '='), '"');
                                    if ($handle[$show_data_k][53] == '') {
                                        $handle[$show_data_k][53] = 'null';
                                    }

                                    $param = [
                                        'order_no' => $ordno, 'customer_name' => $handle[$show_data_k][1], 'alipay_account' => $handle[$show_data_k][2], 'need_payprice' => $handle[$show_data_k][3],
                                        'customer_postage' => $handle[$show_data_k][4], 'need_integral' => $handle[$show_data_k][5], 'total_price' => $handle[$show_data_k][6], 'get_integral' => $handle[$show_data_k][7],
                                        'real_payprice' => $handle[$show_data_k][8], 'real_integral' => $handle[$show_data_k][9], 'order_status' => $handle[$show_data_k][10], 'coustomer_msg' => $handle[$show_data_k][11],
                                        'shr_name' => $handle[$show_data_k][12], 'shr_address' => $handle[$show_data_k][13], 'deliver_method' => $handle[$show_data_k][14], 'shr_tele' => $handle[$show_data_k][15],
                                        'shr_phone' => $handle[$show_data_k][16], 'created_at' => $handle[$show_data_k][17], 'pay_at' => $handle[$show_data_k][18], 'goods_title' => $handle[$show_data_k][19],
                                        'goods_type' => $handle[$show_data_k][20], 'express_no' => $handle[$show_data_k][21], 'comtype' => $handle[$show_data_k][22], 'order_note' => $handle[$show_data_k][23],
                                        'goods_num' => $handle[$show_data_k][24], 'shop_id' => $handle[$show_data_k][25], 'shop_name' => $handle[$show_data_k][26], 'close_reason' => $handle[$show_data_k][27],
                                        'seller_serfee' => $handle[$show_data_k][28], 'buyer_serfee' => $handle[$show_data_k][29], 'invoice_header' => $handle[$show_data_k][30], 'is_phoneorder' => $handle[$show_data_k][31],
                                        'stage_info' => $handle[$show_data_k][32], 'privilege_orderid' => $handle[$show_data_k][33], 'contract_img' => $handle[$show_data_k][34], 'receipts_img' => $handle[$show_data_k][35],
                                        'is_otherpay' => $handle[$show_data_k][36], 'deposit_rank' => $handle[$show_data_k][37], 'edited_sku' => $handle[$show_data_k][38], 'edited_address' => $handle[$show_data_k][39],
                                        'error_msg' => $handle[$show_data_k][40], 'Tmall_coupon' => $handle[$show_data_k][41], 'jfb_coupon' => $handle[$show_data_k][42], 'is_o2o' => $handle[$show_data_k][43],
                                        'retail_type' => $handle[$show_data_k][44], 'retail_shop' => $handle[$show_data_k][45], 'retail_shop_id' => $handle[$show_data_k][46], 'retail_sent' => $handle[$show_data_k][47],
                                        'retail_sent_id' => $handle[$show_data_k][48], 'refund_amount' => $handle[$show_data_k][49], 'appointment_store' => $handle[$show_data_k][50], 'confirm_getgoods' => $handle[$show_data_k][51],
                                        'pay_seller' => $handle[$show_data_k][52], 'individual_red' => $handle[$show_data_k][53], 'upload_mark' => $mark

                                    ];
                                    $result = D('order')->add($param);
//                                if (empty($result)) {
//                                    @M('order')->where(array('upload_mark' => $mark))->delete();
//                                }


                                }

                            }
                        }

                        M()->commit();
                    } catch (\Exception $e) {
                        M()->rollback();
                        $this->ajaxReturn(array('result' => '100003', 'result' => '上传失败!'));
                    }

                    @unlink($savePath . $file_name);
                    $order_nums = count($list);
                    if ($result) {
                        M('upload_record')->add($rec = [
                            'upload_mark' => $mark,
                            'action_user' => $this->now_id,
                            'upload_at' => time(),
                            'order_num' => $order_nums,
                            'shop_name' => $shopName,
                            'file_name' => $old_name,
                        ]);
                        $rec['upload_at'] = date('Y-m-d H:i:s', $rec['upload_at']);
                        $rec['active'] = $this->now_id == $rec['action_user'] ? 1 : 0;
                        $rec['username'] = M('admin')->where(array('id' => $this->now_id))->getField('username');
                        $this->ajaxReturn(array('code' => '100000', 'result' => '上传成功!', 'add_record' => $rec));

                    } else {
                        $this->ajaxReturn(array('code' => '100003', 'result' => '上传失败!'));
                    }
                    $this->ajaxReturn(array('code' => '100000', 'result' => '上传成功!', 'add_record' => $rec));
                }


                /*是xls和xlsx*/

                /*设置上传路径*/
                $savePath = C('IMAGE_SAVE_PATH') . C('XLS_DIR');
                //dump($savePath);die;

                /*以时间来命名上传的文件*/
                $str = date('Ymdhis');
                $file_name = $str . "." . $file_type;  //string(18) "20170526020302.xls"

                if (!copy($tmp_file, $savePath . $file_name)) {
                    $this->ajaxReturn(array('code' => '100002', 'result' => '上传失败'));
                }

//                var_dump('ssssss');die;
                /*
                   *对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
                   *注意:这里调用执行了第三步类里面的read函数,把Excel转化为数组并返回给$res,再进行数据库写入
                */
                // $res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );

                import("Org.Util.PHPExcel");
                $objPHPExcel = new \PHPExcel();
                import("Org.Util.PHPExcel.IOFactory");
                import("Org.Util.PHPExcel.Cell");

                //读取文件
                if (strtolower($file_type) == "xlsx") {
                    $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                } else {
                    $objReader = \PHPExcel_IOFactory::createReader('Excel5');
                }

                $objReader->setReadDataOnly(true);
                $objPHPExcel = $objReader->load($savePath . $file_name);

                //得到当前活动的表
                $objWorksheet = $objPHPExcel->getActiveSheet();
                $highestRow = $objWorksheet->getHighestRow();
                $highestColumn = $objWorksheet->getHighestColumn();
                $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
                $excelData = array();

                for ($row = 2; $row <= $highestRow; $row++) {
                    for ($col = 0; $col < $highestColumnIndex; $col++) {
                        if ($col == 17 || $col == 18 || $col == 51) {//指定列为时间格式还原所在列
                            $excelData[$row][] = gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP((string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
                        } else {
                            $excelData[$row][] = (string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
                        }
                    }
                }
                //剔除空的
                foreach ($excelData as $excelData_k => $excelData_v) {
                    if ($excelData_v[0] == '') {
                        unset($excelData[$excelData_k]);
                    }
                }
//                dump($excelData);die;

                $excelData = array_values($excelData);
                $mark = $str . '-69';//上传标记

                M()->startTrans();
                try {
                    foreach ($excelData as $k => $v) {
                        $shopName = $v[26];
                        $ordno = trim(trim($v[0], '='), '"');
                        if ($v[53] == '') {
                            $v[53] = 'null';
                        }
                        $param = [
                            'order_no' => $ordno, 'customer_name' => $v[1], 'alipay_account' => $v[2], 'need_payprice' => $v[3],
                            'customer_postage' => $v[4], 'need_integral' => $v[5], 'total_price' => $v[6], 'get_integral' => $v[7],
                            'real_payprice' => $v[8], 'real_integral' => $v[9], 'order_status' => $v[10], 'coustomer_msg' => $v[11],
                            'shr_name' => $v[12], 'shr_address' => $v[13], 'deliver_method' => $v[14], 'shr_tele' => $v[15],
                            'shr_phone' => $v[16], 'created_at' => $v[17], 'pay_at' => $v[18], 'goods_title' => $v[19],
                            'goods_type' => $v[20], 'express_no' => $v[21], 'comtype' => $v[22], 'order_note' => $v[23],
                            'goods_num' => $v[24], 'shop_id' => $v[25], 'shop_name' => $v[26], 'close_reason' => $v[27],
                            'seller_serfee' => $v[28], 'buyer_serfee' => $v[29], 'invoice_header' => $v[30], 'is_phoneorder' => $v[31],
                            'stage_info' => $v[32], 'privilege_orderid' => $v[33], 'contract_img' => $v[34], 'receipts_img' => $v[35],
                            'is_otherpay' => $v[36], 'deposit_rank' => $v[37], 'edited_sku' => $v[38], 'edited_address' => $v[39],
                            'error_msg' => $v[40], 'Tmall_coupon' => $v[41], 'jfb_coupon' => $v[42], 'is_o2o' => $v[43],
                            'retail_type' => $v[44], 'retail_shop' => $v[45], 'retail_shop_id' => $v[46], 'retail_sent' => $v[47],
                            'retail_sent_id' => $v[48], 'refund_amount' => $v[49], 'appointment_store' => $v[50], 'confirm_getgoods' => $v[51],
                            'pay_seller' => $v[52], 'individual_red' => $v[53], 'upload_mark' => $mark

                        ];

                        $result = D('order')->add($param);

                        if (empty($result)) {
                            @M('order')->where(array('upload_mark' => $mark))->delete();
                        }

                    }
                    M()->commit();
                } catch (\Exception $e) {
                    M()->rollback();
                    $this->ajaxReturn(array('result' => '100003', 'result' => '上传失败!'));

                }

                @unlink($savePath . $file_name);
                $order_num = count($excelData);
                if ($result) {
                    M('upload_record')->add($rec = [
                        'upload_mark' => $mark,
                        'action_user' => $this->now_id,
                        'upload_at' => time(),
                        'order_num' => $order_num,
                        'shop_name' => $shopName,
                        'file_name' => $old_name,
                    ]);
                    $rec['upload_at'] = date('Y-m-d H:i:s', $rec['upload_at']);
                    $rec['active'] = $this->now_id == $rec['action_user'] ? 1 : 0;
                    $rec['username'] = M('admin')->where(array('id' => $this->now_id))->getField('username');
                    $this->ajaxReturn(array('code' => '100000', 'result' => '上传成功!', 'add_record' => $rec));
                } else {
                    $this->ajaxReturn(array('code' => '100003', 'result' => '上传失败!'));
                }
            } else {
                //列表展示页面
                $page = I('page', 1);
                $pagesize = 7;
                $totalPage = M('upload_record')->count('id');

                $record = M('upload_record ur')->join('LEFT JOIN __ADMIN__ a ON a.id=ur.action_user')
                    ->order('upload_at desc')
                    ->field('ur.*,a.username')
                    ->limit(($page - 1) * $pagesize, $pagesize)->select();

                foreach ($record as $key => $val) {
                    $record[$key]['upload_at'] = date('Y-m-d H:i:s', $val['upload_at']);//上传时间格式化
                    $record[$key]['active'] = $this->now_id == $val['action_user'] ? 1 : 0;  //判断是否可操作
                }
                $this->ajaxReturn(array('code' => '100000', 'record' => $record ? $record : [], 'pagesize' => $pagesize, 'total' => (int)$totalPage));
            }
//        }else{
//            $this->ajaxReturn(array('code' => '100004', 'result' => '请重新登录'));
//        }
    }



文章来源: phpexcel数据导入数据库

人吐槽 人点赞

猜你喜欢

发表评论

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:phpexcel数据导入数据库