html代码
<!doctype html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="Generator" content="EditPlus®"> <meta name="Author" content=""> <meta name="Keywords" content=""> <meta name="Description" content=""> <title>Document</title> </head> <body> <center> <br><br><br> <br><br><br> <form method="post" action="a.php" enctype="multipart/form-data"> <input type="button" value="下载" οnclick="download()"> <input type='button' value="加一个" οnclick="jia()"><br><br id="j"> 上传:<input type="file" name='file' id='file'><br><br> <input type='submit' value="OK"> </form> </center> </body> <script src="./jq.js"></script> <script> var i=1; function jia(){ $("#j").after('上传:<input type="file" name="file'+i+'" id="file'+i+'"><br><br>'); i++; } function download(){ location.href='b.php'; } </script> </html>
php代码上传
<?php header("Content-type:text/html;charset=utf-8");//设置编码 $dsn="mysql:dbname=book;host=localhost"; $db=new PDO($dsn,'root','root',array(PDO::MYSQL_ATTR_INIT_COMMAND=>'SET NAMES \'UTF8\''));//连接数据库,设置传递数据库的数据为utf8的编码格式 //引入excel类 include './PHPExcel.php'; //引用类文件 include './PHPExcel/IOFactory.php';//引用相应的类文件 /*echo $_FILES["file"]["name"] . "<br />"; echo $_FILES["file"]["type"] . "<br />"; echo ($_FILES["file"]["size"] / 1024) . " Kb<br />"; echo $_FILES["file"]["tmp_name"]. "<br />";*/ foreach($_FILES as $key => $val){ $str = substr($val['name'],strrpos($val['name'],'.')+1); if ($val["error"] > 0) { echo $val['name']."上传发生错误". "<br />"; continue; } if($str != 'xls'){ echo '必须是xls文件'. "<br />"; continue; } if (file_exists(iconv("UTF-8","GB2312",'./xls/'.$val["name"]))) { echo $val['name'] . "已存在". "<br />"; continue; } else { move_uploaded_file($val["tmp_name"],iconv("UTF-8","GB2312",'./xls/'.$val["name"])); echo $val["name"],'以上传成功!'; } $xlsPath = iconv("UTF-8","GB2312",'./xls/'.$val["name"]); //指定要读取的exls路径 //$type = 'Excel2007'; //设置要解析的Excel类型 Excel5(2003或以下版本)或Excel2007 $type = 'Excel5'; $xlsReader = PHPExcel_IOFactory::createReader($type); $Sheets = $xlsReader->load($xlsPath); //开始读取 $Sheet = $Sheets->getSheet(0)->toArray(); //读取第一个工作表(注意编号从0开始) 如果读取多个可以做一个循环0,1,2,3.... //得到二维数组,每个小数组是excel表格内容的一行 里面包含此行的每列的数据 echo '<pre>'; //print_r($Sheet);die; foreach($Sheet as $k=>$v){ $res=$db->exec("INSERT INTO a (data) values('".$v[1]."')");//写入数据库 if($res){ //print_r($Sheet); //执行成功后,输出数据 } } } ?>
php代码下载
<?php header("Content-type:text/html;charset=utf-8");//设置编码 function exportExcel($filename,$content){ header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Type: application/vnd.ms-execl"); header("Content-Type: application/force-download"); header("Content-Type: application/download"); header("Content-Disposition: attachment; filename=".$filename); header("Content-Transfer-Encoding: binary"); header("Pragma: no-cache"); header("Expires: 0"); echo $content; } $dsn="mysql:dbname=book;host=localhost"; $db=new PDO($dsn,'root','root',array(PDO::MYSQL_ATTR_INIT_COMMAND=>'SET NAMES \'UTF8\''));//实例化pdo对象 $res=$db->query("select * from a");//从数据库查询数据 $str = "id\t数据\t\n"; $str = iconv('utf-8','gb2312',$str);//iconv() 是编码转换函数 $row=$res->fetchAll(); foreach($row as $key => $val){ $id = iconv('utf-8','gb2312',$val['id']); $data = iconv('utf-8','gb2312',$val['data']); $str .= $id."\t".$data."\t\n"; } $filename = date('Ymd').'.xls'; exportExcel($filename,$str); //生成excel表格