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

来源：互联网发布：手机淘宝怎么刷新编辑：程序博客网时间：2024/06/10 19:09

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

0 0