Download First
Download Phpexcel file, address: phpexcel.codeplex.com/
Find the following similar code in the reader.php file (the first line is both) and change to the correct oleread.php path: require_once ' oleread.php ';
Then create a new PHP file to introduce reader.php,
The code is as follows:
| The code is as follows |
Copy Code |
<?php
Require_once ' excel/reader.php ';
$data = new Spreadsheet_excel_reader ();
$data->setoutputencoding (' GBK '); Set the encoding here, it's usually GBK mode.
$data->read (' Book1.xls ');//file path
Error_reporting (e_all ^ e_notice);
Here I only output the contents of the Excel file, to the storage, as long as the output of the place, write a MySQL statement can ~
for ($i = 1; $i <= $data->sheets[0][' numrows '; $i + +) {
for ($j = 1; $j <= $data->sheets[0][' numcols '; $j + +) {
echo "". $data->sheets[0][' cells ' [$i] [$j]. "", ";
}
echo "n";
}
?> |
code example
| The code is as follows |
Copy Code |
| Require_once ' phpexcel/classes/phpexcel.php ';
Require_once ' phpexcel/classes/phpexcel/iofactory.php ';
Require_once ' phpexcel/classes/phpexcel/reader/excel5.php ';
$objReader = Phpexcel_iofactory::createreader (' Excel5 ');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load ($filename); $filename can be an uploaded file, or a specified file.
$sheet = $objPHPExcel->getsheet (0);
$highestRow = $sheet->gethighestrow (); Get total number of rows
$highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained
$k = 0; Iterate through the Excel file, read one, insert a
for ($j =2; $j <= $highestRow; $j + +)
{ $a = $objPHPExcel->getactivesheet ()->getcell ("a". $j)->getvalue ();//Get the value of column A
$b = $objPHPExcel->getactivesheet ()->getcell ("B". $j)->getvalue ();//Get the value of column B
$sql = "INSERT into table VALUES (". $a. ",". $b. ")";
mysql_query ($sql); } |
Code instance CVS import to database
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 "; |
For more details see: http://www.111cn.net/phper/php-database/excel-mysql.htm
