set_time_limit(0); // 脚本不超时
//以下三步加载phpExcel的类
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';
require_once 'PHPExcel/Reader/Excel5.php';
//use excel2007 for 2007 format
$objReader = PHPExcel_IOFactory::createReader('Excel5');
//指定的文件
$filename = '11.xls';
//载入xls文件,$filename可以是上传的文件,或者是指定的文件
$objPHPExcel = $objReader->load($filename);
//生成工作表的对象--从0开始
$sheet = $objPHPExcel->getSheet(0);
// 取得总行数
$highestRow = $sheet->getHighestRow();
// 取得总列数
$highestColumn = $sheet->getHighestColumn();
//连接数据库
$conn = new mysqli('localhost', 'root', '','demo');
if(!$conn){
die("Could not connect to database");
}
//设置字符编码
mysqli_set_charset($conn, "utf8");
//计数初始化
$k = 0;
//循环读取excel文件,读取一条,插入一条
//j表示从哪一行开始读取
for($j=2;$j<=$highestRow;$j++){
$a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取A列的值
$b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//获取B列的值
$c = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//获取C列的值
$d = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();//获取D列的值
$e = $objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();//获取E列的值
$f = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();//获取F列的值
$g = $objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();//获取G列的值
$h = $objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();//获取H列的值
$i = $objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue();//获取I列的值
$sql = 'INSERT INTO `t_order` (id, order_sn, user_id,shipping,shipping_name,goods_amount,consignee,mobile,address,add_time) VALUES(null,'.trim($a,"`").','.$b.',"'.$c.'","'.$d.'",'.$e.',"'.$f.'",'.trim($g,"`").',"'.$h.'",'.excelTime($i).');';
//
echo $sql."<br/>";
if ($conn->query($sql) !== TRUE) {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
echo "completed";
//把excle中的时间转为正常时间
function excelTime($t){
return strtotime(gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($t)));
}
if(!TRUE){
//连接数据库
$conn = new mysqli('localhost', 'root', '','demo');
if(!$conn){
die("Could not connect to database");
}
//设置字符编码
mysqli_set_charset($conn, "utf8");
$page = isset($_GET['page']) ? $_GET['page'] : 1;
if((int)$page < 1){
$page = 1;
}
$pagesize = 10;
$limit = ($page-1)*$pagesize.",".$pagesize;
$query = "select * from t_order order by id desc limit ".$limit;
$result = $conn->query($query);
if ($result) {
if($result->num_rows>0){ //判断结果集中行的数目是否大于0
while($row = $result->fetch_array() ){ //循环输出结果集中的记录
echo ($row[0])."<br>";
echo ($row[1])."<br>";
echo ($row[2])."<br>";
echo ($row[3])."<br>";
echo ($row[4])."<br>";
echo ($row[5])."<br>";
echo ($row[6])."<br>";
echo ($row[7])."<br>";
echo "<hr>";
}
}
}else {
echo "查询失败";
}
$result->free();
}