phpExcel 实现excel表格和mysql数据库的导入导出

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 ";
    //}
}
?>