excel 2003 和excel 2007的区别, excel5 和 excel 2007两个库API,在phpExcel官网上提供源代码,而且带有Demo实例
<?php
//include("conn.php");echo "start0";
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
$inputFileName = 'admin.xls';
echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using PHPExcel_Reader_Excel5<br />';
$objReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($inputFileName);
echo '<hr />';
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 2);//设置超时时间
$mysqli->real_connect('localhost', 'root', 'sp728948', 'Kinder');
mysqli_query($mysqli,'set names utf8');
//$sql = "INSERT INTO `YEY_Course` (`NAME`, `TYPE`, `DATE`, `CLASS_NUM`, `TIME`, `TEACHER_NUM`, `INTRODUCTION`, `SCHOOL_LICENSE`) VALUES ('".$cname."','".$ctype."', '".$cdate."', '".$cclassnum."', '".$ctime."', '".$cteachernum."', '".$cintroduction."', '".$cshoollicense."');";
//$result = $mysqli->query($sql);
$insql = "insert into YEY_Admin(USER_NAME,PASSWORD,TYPE,COUNT) VALUES ";
//遍历数组 $sheetData
//如果有标题 先删除 unset($sheetData[1]);
foreach($sheetData as $k => $data){
if ($k == 1)
continue;
$insql .= "('".$data['A']."','".$data['B']."','".$data['C']."','".$data['D']."'),";
//一次插入100条数据 减少数据库压力
//if(($k+1 / 100) == 0){
$insql = rtrim($insql,',').';'; //将最后的逗号替换成分好
//插入数据库 并且重置 字符串 $insql
//或者保存到文件中 利用source 命令插入数据库
$result = $mysqli->query($insql);
echo $insql."<br/>";
$insql = "insert into YEY_Admin(USER_NAME,PASSWORD,TYPE,COUNT) VALUES ";
//}
}
?>
第二种库实例
<?php
//include("conn.php");
echo "start0";
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
$inputFileName = 'admin.xlsx';
echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using PHPExcel_Reader_Excel2007<br />';
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($inputFileName);
echo '<hr />';
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 2);//设置超时时间
$mysqli->real_connect('localhost', 'root', 'sp728948', 'Kinder');
mysqli_query($mysqli,'set names utf8');
//$sql = "INSERT INTO `YEY_Course` (`NAME`, `TYPE`, `DATE`, `CLASS_NUM`, `TIME`, `TEACHER_NUM`, `INTRODUCTION`, `SCHOOL_LICENSE`) VALUES ('".$cname."','".$ctype."', '".$cdate."', '".$cclassnum."', '".$ctime."', '".$cteachernum."', '".$cintroduction."', '".$cshoollicense."');";
//$result = $mysqli->query($sql);
$insql = "insert into YEY_Admin(USER_NAME,PASSWORD,TYPE,COUNT) VALUES ";
//遍历数组 $sheetData
//如果有标题 先删除 unset($sheetData[1]);
foreach($sheetData as $k => $data){
if ($k == 1)
continue;
$insql .= "('".$data['A']."','".$data['B']."','".$data['C']."','".$data['D']."'),";
//一次插入100条数据 减少数据库压力
//if(($k+1 / 100) == 0){
$insql = rtrim($insql,',').';'; //将最后的逗号替换成分好
//插入数据库 并且重置 字符串 $insql
//或者保存到文件中 利用source 命令插入数据库
$result = $mysqli->query($insql);
echo $insql."<br/>";
$insql = "insert into YEY_Admin(USER_NAME,PASSWORD,TYPE,COUNT) VALUES ";
//}
}
?>