PHP Excel import MySQL

code is as follows

copy code

Require_once './includes/reader.php '; Excelfile ($filename, $encoding); $data = new Spreadsheet_excel_reader (); Set output encoding. $data->setoutputencoding (' GBK '); "Data.xls" refers to the Excel file that you want to import into MySQL $data->read (' Date.xls '); @ $db = mysql_connect (' localhost ', ' root ', ' 1234 ') or Die ("Could not connect to database."); /Connection Database mysql_query ("Set names ' GBK '")//Output Chinese mysql_select_db (' wenhuaedu '); Select Database Error_reporting (e_all ^ e_notice); for ($i = 1; $i <= $data->sheets[0][' numrows '; $i + +) { The following comment () for loops print Excel table data for ($j = 1; $j <= $data->sheets[0][' numcols '; $j + +) { echo $data->sheets[0][' cells ' [$i] [$j]. ","; } echo "n"; Ttp://sourceforge.net/projects/phpexcelreader

The following is an importer, indicating that the MySQL database structure is the same as the XLS document structure.

The code is as follows

Copy Code

$data = new Spreadsheet_excel_reader (); $data->setoutputencoding (' utf-8 ');//Set encoding $data->read (' Test.xls ');//excel,csv file location Error_reporting (e_all ^ e_notice); for ($i = 2; $i <= $data->sheets[0][' numrows '; $i + +) {//Insert database start $sql = "INSERT INTO {$table}kehu (userid,sortid,danwei,xingming,zuncheng,tel,mobile,fax,email,website,qq,address, Postcode,sex,beizhu,addtime) VALUES (' $_cookie[wecms_user_id] ', ' $sortid ', "$data->sheets[0][' cells ' [$i][1]." ' "$data->sheets[0][' cells ' [$i][2]." ' "$data->sheets[0][' cells ' [$i][3]." ' "$data->sheets[0][' cells ' [$i][4]." ' "$data->sheets[0][' cells ' [$i][5]." ' "$data->sheets[0][' cells ' [$i][6]." ' "$data->sheets[0][' cells ' [$i][7]." ' "$data->sheets[0][' cells ' [$i][8]." ' "$data->sheets[0][' cells ' [$i][9]." ' "$data->sheets[0][' cells ' [$i][10]." ' "$data->sheets[0][' cells ' [$i][11]." ' "$data->sheets[0][' cells ' [$i][12]." ' "$data->sheets[0][' cells ' [$i][13]." ' ' $nowtime '); $res = $db->query ($sql); Insert Database End

}

Import the CSV into the database.

The code is as follows

Copy Code

function Getmicrotime () { List ($usec, $sec) = Explode ("", Microtime ()); Return ((float) $usec + (float) $sec); } $time _start = Getmicrotime (); Include ("connectdb.php"); function Insert_data ($id, $summary, $description, $additional _information, $category) { $my _query1 = "INSERT into mantis_bug_text_table (id,description,additional_information) VALUES (' $id ', ' $description ', ' $additional _information ') "; $first = mysql_query ($my _query1); $my _query2 = "INSERT into mantis_bug_table (id,project_id,summary,bug_text_id) VALUES (' $id ', ' $category ', ' $s Ummary ', ' $id '); $second = mysql_query ($my _query2); Return } $fp = fopen ("Test.csv", "R"); while ($data = Fgetcsv ($fp, ' 1000 ', ', ')) { Insert_data ($data [0], $data [1], $data [2], $data [3], $data [4]); echo "<font color = #ff0000 size = 20> data import success! </font><br><br> "; } Fclose ($FP); $time _end = Getmicrotime (); $time = $time _end-$time _start; echo "Program execution Time:". $time. " Seconds ";