有好几种方法,我就讲我用过的两个方法
方法一:
把excel导成csv格式,csv文件用记事本打开的话,里面的数据都是用逗号格开的
<?php
$handle = fopen ("test.csv","r");
$sql="insert into scores(idcard,names,num,sex,nation,score) values('";
while ($data = fgetcsv ($handle, 1000, ","))
{
$num = count ($data);
for ($c=0; $c < $num; $c++)
{
if($c==$num-1) {$sql=$sql.$data[$c]."')";break;}
$sql=$sql.$data[$c]."','";
}
mysql_query($sql);
}
fclose ($handle);
?>第二种用com技术:
$extension=strtolower($extension);//扩展名改成小写
$image_name = date("YmdHis").$class.$exam_id.$extension;//定义上传之后的文件名,即将文件改名后保存
strtolower($image_name);//扩展名改成小写
copy($img,$uploaddir.$image_name); //将文件从服务器临时目录拷贝到目的路径
unlink($img); //删除服务器上的临时文件
//读取xls文件输入数据库
$query="select * from info_student where userID like '$class%'";
$result=@mssql_query($query);
$total_student=@mssql_num_rows($result);//算出年级的人数 //读取这次考试的各项信息
$query="select * from stu_exam_instruction where ID='$exam_id'";
$result=@mssql_query($query);
$row=@mssql_fetch_array($result);
$term=$row['term'];
$subject=$row['subject'];
$times=$row['times'];
$exam_time=$row['exam_date']; $workbook = "c:\\".$image_name; $sheet = "sheet1"; #实例化一个组件的对象
$ex = new COM("Excel.sheet") or Die ("Did not connect"); #打开工作本使我们可使用它
$wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open"); #读单元格数据
$sheets = $wkb->Worksheets($sheet); #Select the sheet
$sheets->activate; #Activate it
//$cell->activate; #Activate the cell .If this, you can modify the cell data. //$year=date("y");
for($i=3;$i<=$total_student+2;$i++)
{
$name=$sheets->Cells($i,B);$name=$name->value;
$xuehao=$sheets->Cells($i,A);$xuehao=$xuehao->value;
$score=$sheets->Cells($i,E);$score=$score->value;
$tmp=$sheets->Cells($i,C);$tmp=$tmp->value;
if(strlen($tmp)==1)
if(strlen($xuehao)==1) $userID=$class."0".$tmp."0".$xuehao;
else $userID=$class."0".$tmp.$xuehao;
else
if(strlen($xuehao)==1) $userID=$class.$tmp."0".$xuehao;
else $userID=$class.$tmp.$xuehao;
//$class=substr($userID,0,4); $query="insert into stu_score(stu_id,name,subject,times,score,term,exam_time) values('$userID','$name','$subject','$times','$score','$term','$exam_time')";
@mssql_query($query) or die("error");
}
$ex->application->ActiveWorkbook->Close("False");
unset ($ex);
方法一:
把excel导成csv格式,csv文件用记事本打开的话,里面的数据都是用逗号格开的
<?php
$handle = fopen ("test.csv","r");
$sql="insert into scores(idcard,names,num,sex,nation,score) values('";
while ($data = fgetcsv ($handle, 1000, ","))
{
$num = count ($data);
for ($c=0; $c < $num; $c++)
{
if($c==$num-1) {$sql=$sql.$data[$c]."')";break;}
$sql=$sql.$data[$c]."','";
}
mysql_query($sql);
}
fclose ($handle);
?>第二种用com技术:
$extension=strtolower($extension);//扩展名改成小写
$image_name = date("YmdHis").$class.$exam_id.$extension;//定义上传之后的文件名,即将文件改名后保存
strtolower($image_name);//扩展名改成小写
copy($img,$uploaddir.$image_name); //将文件从服务器临时目录拷贝到目的路径
unlink($img); //删除服务器上的临时文件
//读取xls文件输入数据库
$query="select * from info_student where userID like '$class%'";
$result=@mssql_query($query);
$total_student=@mssql_num_rows($result);//算出年级的人数 //读取这次考试的各项信息
$query="select * from stu_exam_instruction where ID='$exam_id'";
$result=@mssql_query($query);
$row=@mssql_fetch_array($result);
$term=$row['term'];
$subject=$row['subject'];
$times=$row['times'];
$exam_time=$row['exam_date']; $workbook = "c:\\".$image_name; $sheet = "sheet1"; #实例化一个组件的对象
$ex = new COM("Excel.sheet") or Die ("Did not connect"); #打开工作本使我们可使用它
$wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open"); #读单元格数据
$sheets = $wkb->Worksheets($sheet); #Select the sheet
$sheets->activate; #Activate it
//$cell->activate; #Activate the cell .If this, you can modify the cell data. //$year=date("y");
for($i=3;$i<=$total_student+2;$i++)
{
$name=$sheets->Cells($i,B);$name=$name->value;
$xuehao=$sheets->Cells($i,A);$xuehao=$xuehao->value;
$score=$sheets->Cells($i,E);$score=$score->value;
$tmp=$sheets->Cells($i,C);$tmp=$tmp->value;
if(strlen($tmp)==1)
if(strlen($xuehao)==1) $userID=$class."0".$tmp."0".$xuehao;
else $userID=$class."0".$tmp.$xuehao;
else
if(strlen($xuehao)==1) $userID=$class.$tmp."0".$xuehao;
else $userID=$class.$tmp.$xuehao;
//$class=substr($userID,0,4); $query="insert into stu_score(stu_id,name,subject,times,score,term,exam_time) values('$userID','$name','$subject','$times','$score','$term','$exam_time')";
@mssql_query($query) or die("error");
}
$ex->application->ActiveWorkbook->Close("False");
unset ($ex);
解决方案 »
- 求助,帮我看看为啥不显示
- php导出doc文件图片问题
- tpl 无法显示数据库内容 急急急
- php ---〉〉〉failed to open stream: No such file or directory in
- 时间格式如2005-12-11 怎么和数据库里的datetime格式比较?
- 加急:执行一个php连接mysql的语句结果提示;Fatal error: Call to undefined function: mysql_connect()
- GetImageSize函数可否打开远程图片?
- 有一份北京的工作,需要懂php+mySql+linux,有兴趣的请和我联系
- php怎样取得一个目录下的所有文件的名字
- 谁能帮我详细的解释一下第五行到最后代码
- 请教一个sql 查询应该怎么写.
- 求计算页面在线时长的方法
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
就看你的xls文件结构怎么样了。
参考这里http://www.linuxforum.net/books/mysqlmanual/manual_Reference.html#LOAD_DATA