PHP mysql通过excel导入数据

Question

I want to import data using php mysql from excel sheet containing 1.6 million records. What is the best way to do this?

this is the sample code I have used to iterate excel file and insert data in database:

public function iterateData($file_name) {
        $fileDirectory = '';
        $file_name = $fileDirectory . $file_name;
        if (file_exists($file_name)) {
            $this->truncateTable();
            include 'PHPExcel2/Classes/PHPExcel/IOFactory.php';
            $objReader = PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($file_name);
            $count = 1;
            foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                foreach ($worksheet->getRowIterator() as $row) {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(true); // Loop all cells, even if it is not set
                    $cellValues = array();
                    foreach ($cellIterator as $cell) {
                        if (!is_null($cell)) {
                            $cellValues[] = $cell->getCalculatedValue();
                        }
                    }
                    if (isset($cellValues[0]) && $cellValues[0] != 'Product' && $cellValues[0] != '') {
                        $this->inserInDatabase($cellValues);
                    } elseif (empty($cellValues[0]) && empty($cellValues[1]) && empty($cellValues[2])) {
                        continue;
                    }
                }
                if ($objPHPExcel->getSheetCount() == $count) {
                    return TRUE;
                }
                $count++;
            }
        } else {
            return FALSE;
        }
    } private function inserInDatabase($data) {
        $dbDetails = array(
            'db_name' => '*',
            'db_pass' => '*',
            'db_host' => 'localhost',
            'db_user' => '*'
        );
        $dbh = dbConnect::connect($dbDetails);
        $date = date('Y-m-d H:i:s');
        $sql = "INSERT INTO product_description (product_id, prpoduct_description, price, created_date) values ('" . mysql_escape_string($data[0]) . "', '" . mysql_escape_string($data[1]) . "', '" . mysql_escape_string($data[2]) . "', '$date')";
        if (!$dbh->dbh->query($sql)) {
            die('Database Connection Failed.');
        }
    }

Answer 1

export you excel data to csv format, and then import the csv format to mysql