有好几种方法,我就讲我用过的两个方法
方法一:
把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);
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