php数据导入导出之excel(csv文件)

有时写程序时后台要求把大量数据导入数据库中，比如考试 成绩、电话簿等一般都是存放在excel中的数据，这时 我们可把excel导出成csv文件，然后通过以下程序 即可批量导入数据到数据库中

上传cvs并导入到数据库中，测试成功（部分代码不规范 ，如PHP_SELF那里要改写成

$_SERVER [ " PHP_SELF " ] ）

PHP代码
<? php
$fname   =   $_FILES [ ' MyFile ' ][ ' name ' ];
$do   =   copy ( $_FILES [ ' MyFile ' ][ ' tmp_name ' ] , $fname );
if  ( $do )
{
echo " 导入数据成功
" ;
}  else  {
echo   "" ;
}
?>
< form ENCTYPE = " multipart/form-data "  ACTION = " <?php echo "" .$PHP_SELF. "" ; ?> "  METHOD = " POST " >

导入CVS数据  < input NAME = " MyFile "  TYPE = " file " >   < input VALUE = " 提交 "  TYPE = " submit " >



</ form >
<?
error_reporting ( 0 );
// 导入CSV格式的文件
$connect = mysql_connect ( " localhost " , " a0530093319 " , " 123456 " ) or  die ( " could not connect to database " );
mysql_select_db ( " a0530093319 " , $connect ) or  die  ( mysql_error ());
$fname   =   $_FILES [ ' MyFile ' ][ ' name ' ];
$handle = fopen ( " $fname " , " r " );
while ( $data = fgetcsv ( $handle , 10000 , " , " ))
{
$q = " insert into test (code,name,date) values ('$data[0]','$data[1]','$data[2]') " ;
mysql_query ( $q ) or  die  ( mysql_error ());

}
fclose ( $handle );
?>

 用php将数据库导出成excel，测试完全成功

PHP代码

 

<? php
$DB_Server   =   " localhost " ;
$DB_Username   =   " a0530093319 " ;
$DB_Password   =   " 123456 " ;
$DB_DBName   =   " a0530093319 " ;
$DB_TBLName   =   " member " ;

$savename   =   date ( " YmjHis " );
$Connect   =  @ mysql_connect ( $DB_Server ,   $DB_Username ,   $DB_Password )
or  die ( " Couldn't connect. " );
mysql_query ( " Set Names 'gb2312' " );

$file_type   =   " vnd.ms-excel " ;
$file_ending   =   " xls " ;
header ( " Content-Type: application/$file_type " );
header ( " Content-Disposition: attachment; filename= " . $savename . " .$file_ending " );
header ( " Pragma: no-cache " );
header ( " Expires: 0 " );

$now_date   =   date ( " Y-m-j H:i:s " );
$title   =   " 数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date " ;

$sql   =   " Select * from $DB_TBLName " ;
$ALT_Db   =  @ mysql_select_db ( $DB_DBName ,   $Connect )
or  die ( " Couldn't select database " );
$result   =  @ mysql_query ( $sql , $Connect )
or  die ( mysql_error ());
echo ( " $title " );
$sep   =   " " ;
for  ( $i   =   0 ;  $i   <   mysql_num_fields ( $result );  $i ++ ) {
echo   mysql_field_name ( $result , $i )  .   " " ;
}
print ( " " );
$i   =   0 ;
while ( $row   =   mysql_fetch_row ( $result )) {
$schema_insert   =   "" ;
for ( $j = 0 ;  $j < mysql_num_fields ( $result ); $j ++ ) {
if ( ! isset ( $row [ $j ]))
$schema_insert   .=   " NULL " . $sep ;
elseif  ( $row [ $j ]  !=   "" )
$schema_insert   .=   " $row[$j] " . $sep ;
else
$schema_insert   .=   "" . $sep ;
}
$schema_insert   =   str_replace ( $sep . " $ " ,   "" ,   $schema_insert );
$schema_insert   .=   " " ;
print ( trim ( $schema_insert ));
print   " " ;
$i ++ ;
}
return  ( true );
?>