怕自己以后忘了留个代码,留个备份(虽然是很渣的代码)。
从excel中导入数据到数据库
//链接数据库
define("HOST","主机名");
define("USER","用户名");
define("PASS","密码");
define("DBNAME","数据库名");
$link=mysql_connect(HOST,USER,PASS) or die("数据库链接失败");
mysql_select_db(DBNAME,$link);
mysql_set_charset("utf8");
//导入excelphp相关文件
require_once './Excel/PHPExcel.php';
require_once './Excel/PHPExcel/IOFactory.php';
require_once './Excel/PHPExcel/Reader/Excel5.php';
$objReader = PHPExcel_IOFactory::createReader('Excel5');
//选择你要导入的excel的路径(相对与该程序文件的)
$objPHPExcel = PHPExcel_IOFactory::load("./excel/yhu.xls");
$objActSheet = $objPHPExcel->getActiveSheet();
//$objPHPExcel = $objReader->load(); //$filename可以是上传的文件,或者是指定的文件
//$sheet = $objPHPExcel->getSheet(0);
$highestRow = $objActSheet->getHighestRow(); // 取得总行数
$highestColumn = $objActSheet->getHighestColumn(); // 取得总列数
//定义数组 根据电话号码开头生成相应的手机邮箱
$yidong=array(134,135,136,137,138,139,147,150,151,152,157,158,159,182,187,188,178);
$liantong=array(130,131,132,145,155,156,185,186);
$dianxin=array(133,153,180,181,189);
//定义一个变量来查看数据插入的次数
$aa=0;
//循环读取excel文件,读取一条,插入一条
for($j=1;$j<=$highestRow;$j++)
{
//读取用户的姓名(姓名在A列)
$a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
//读取用户的手机号(手机号在b列)
$b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
//取出手机号的前3个值与数组进行比对确定手机邮箱
$c=substr($b, 0 , 3);
if (in_array($c,$yidong))
{
$d= $b."@139.com";
}
elseif(in_array($c,$liantong))
{
$d= $b."@wo.com";
}
elseif(in_array($c,$dianxin))
{
$d= $b."@189.com";
}
执行sql语句实现数据库的添加
$sql="insert into me_users(email,user_name,mobile_phone) values ('$d','$a','$b')";
mysql_query($sql);
$aa++;
}
//输出插入数据的条数
echo "$aa";
从数据库中导出excel表格
我是先查出数据在存储到excel表格中去的
//设置编码方式
header("Content-type:text/html;charset=utf-8");
//连接数据库
define("HOST","主机名");
define("USER","用户名");
define("PASS","密码");
define("DBNAME","数据库名");
//导入excelphp相关的文件
include('./Excel/PHPExcel.php');
include("./Excel/PHPExcel/Writer/Excel5.php");
$link=mysql_connect(HOST,USER,PASS) or die("数据库链接失败");
mysql_select_db(DBNAME,$link);
mysql_set_charset("utf8");
//sql语句查出数据
$sql="select id,name, sex order by id asc";
$result=mysql_query($sql);
$objPHPExcel = new PHPExcel();
//定义表头
$objPHPExcel->setActiveSheetIndex()
->setCellValue('A1', "编号")
->setCellValue('B1', "姓名")
->setCellValue('C1', "性别");
//循环添加数据
foreach($result as $k => $v){
$num=$k+2;
$objPHPExcel->setActiveSheetIndex()
->setCellValue('A'.$num, $v['id'])
->setCellValue('B'.$num, $v['name'])
->setCellValue('c'.$num, $v['sex']);
}
$m_objPHPExcel = new PHPExcel();
//按时间生成文件名
$m_strOutputExcelFileName = date('Y-m-j_H_i_s').".xls";
//选择生成excel文件的路径
$m_strOutputPath = "./exl/";
//生成excel文件
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save($m_strOutputPath.$m_strOutputExcelFileName);
//运行完成后输出完成
echo "完成";