navcat excel数据导入mysql的方法

navcat excel数据导入mysql的方法

先navcat导出 xls格式 然后把数据复制到往这个xls里 (按照这个xls格式) 然后导入mysql就行了

如果导入的过程无法识别excel里的内容 就想办法第一行能识别 自己重新输入下 就行 一般都是格式问题 第一行通过就行了

下面是通过phpexcel读取excel里的内容 然后存入数据库的

    public function daochuexcel()
    {
        set_time_limit(0);
		 ini_set("memory_limit","512M");
        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
        date_default_timezone_set('Asia/Shanghai');

        if (PHP_SAPI == 'cli')
            die('This example should only be run from a Web Browser');

        /** Include PHPExcel */
        require_once dirname(__FILE__) . '/../../../Public/phpexcel/Classes/PHPExcel.php';


// Create new PHPExcel object
        $objPHPExcel = new \PHPExcel();

// Set document properties
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");


// Add some data
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('f')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '姓名')
            ->setCellValue('B1', '手机号')
            ->setCellValue('C1', '奖品名字');

        $VModel = new HuanShanVoteModel();
        $sql = "select u.name,u.phone,p.prize_id from guagua_prize as p left join guaguaka_userinfo as u on p.openid=u.openid";
        $list = $VModel->query($sql);
        foreach ($list as $k => $v) {
            $prizeinfo = $this->prize_id($v['prize_id']);
            $list[$k]['prize_shortname'] = $prizeinfo['prize_shortname'];
        }


        $i = 1;
        foreach ($list as $k => $v) {
            $i++;
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A' . $i, $v['name'])
                ->setCellValue('B' . $i, $v['phone'])
                ->setCellValue('C' . $i, $v['prize_shortname']);
        }


        $objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="01simple.xls"');
        header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

原创文章如转载,请注明本文链接: http://newmiracle.cn/?p=1332

发表评论

电子邮件地址不会被公开。 必填项已用*标注