엑셀 업로드 다운로드-PHP 튜토리얼-php.cn

html 코드

<!doctype html>
<<span>html </span>lang=<span>"en"</span>>
 <<span>head</span>>
  <<span>meta </span>charset=<span>"UTF-8"</span>>
  <<span>meta </span>name=<span>"Generator" </span>content=<span>"EditPlus?"</span>>
  <<span>meta </span>name=<span>"Author" </span>content=<span>""</span>>
  <<span>meta </span>name=<span>"Keywords" </span>content=<span>""</span>>
  <<span>meta </span>name=<span>"Description" </span>content=<span>""</span>>
  <<span>title</span>>Document</<span>title</span>>
 </<span>head</span>>
 <<span>body</span>>
 <<span>center</span>>
     <<span>br</span>><<span>br</span>><<span>br</span>>

     <<span>br</span>><<span>br</span>><<span>br</span>>
     <<span>form </span>method=<span>"post" </span>action=<span>"a.php" </span>enctype=<span>"multipart/form-data"</span>>
         <<span>input </span>type=<span>"button" </span>value=<span>"下载" </span>onclick=<span>"</span><span>download</span>()<span>"</span>>
         <<span>input </span>type=<span>'button' </span>value=<span>"加一个" </span>onclick=<span>"</span><span>jia</span>()<span>"</span>><<span>br</span>><<span>br </span>id=<span>"j"</span>>
         上传：<<span>input </span>type=<span>"file" </span>name=<span>'file' </span>id=<span>'file'</span>><<span>br</span>><<span>br</span>>
        <<span>input </span>type=<span>'submit' </span>value=<span>"OK"</span>>
     </<span>form</span>>
 </<span>center</span>>
 </<span>body</span>>
 <<span>script </span>src=<span>"./jq.js"</span>></<span>script</span>>
<<span>script</span>>
    <span>var </span><span>i</span><span>=</span><span>1</span>;
    <span>function </span><span>jia</span>(){
        $(<span>"#j"</span>).after(<span>'上传：<input type="file" name="file'</span><span>&#43;</span><span>i</span><span>&#43;</span><span>'" id="file'</span><span>&#43;</span><span>i</span><span>&#43;</span><span>'"><br><br>'</span>);
        <span>i</span><span>++</span>;
    }
    <span>function </span><span>download</span>(){
        <span>location</span>.<span>href</span><span>=</span><span>'b.php'</span>;
    }
</<span>script</span>>
</<span>html</span>>

로그인 후 복사

php 코드 업로드

<span><?php
</span><span>header(</span><span>"Content-type:text/html;charset=utf-8"</span><span>);</span><span>//设置编码
</span><span>$dsn=</span><span>"mysql:dbname=book;host=localhost"</span><span>;
</span><span>$db=new </span><span>PDO(</span><span>$dsn</span><span>,</span><span>'root'</span><span>,</span><span>'root'</span><span>,</span><span>array</span><span>(PDO</span><span>::</span><span>MYSQL_ATTR_INIT_COMMAND</span><span>=></span><span>'SET NAMES </span><span>\'</span><span>UTF8</span><span>\'</span><span>'</span><span>));</span><span>//连接数据库，设置传递数据库的数据为utf8的编码格式
</span><span>//引入excel类
</span><span>include </span><span>'./PHPExcel.php'</span><span>;   </span><span>//引用类文件
</span><span>include </span><span>'./PHPExcel/IOFactory.php'</span><span>;</span><span>//引用相应的类文件
</span><span>   /*echo $_FILES["file"]["name"] . "<br />";
</span><span>    echo $_FILES["file"]["type"] . "<br />";
</span><span>    echo ($_FILES["file"]["size"] / 1024) . " Kb<br />";
</span><span>    echo $_FILES["file"]["tmp_name"]. "<br />";*/
</span><span></span><span>foreach</span><span>(</span><span>$_FILES as $key </span><span>=> </span><span>$val</span><span>){
</span><span></span><span>$str = </span><span>substr(</span><span>$val</span><span>[</span><span>'name'</span><span>],strrpos(</span><span>$val</span><span>[</span><span>'name'</span><span>],</span><span>'.'</span><span>)</span><span>+</span><span>1</span><span>);
</span><span></span><span>if </span><span>(</span><span>$val</span><span>[</span><span>"error"</span><span>] </span><span>> </span><span>0</span><span>)
</span><span>        {
</span><span></span><span>echo $val</span><span>[</span><span>'name'</span><span>]</span><span>.</span><span>"上传发生错误"</span><span>. </span><span>"<br />"</span><span>;
</span><span></span><span>continue</span><span>;
</span><span>        }
</span><span></span><span>if</span><span>(</span><span>$str != </span><span>'xls'</span><span>){
</span><span></span><span>echo </span><span>'必须是xls文件'</span><span>. </span><span>"<br />"</span><span>;
</span><span></span><span>continue</span><span>;
</span><span>        }
</span><span></span><span>if </span><span>(file_exists(iconv(</span><span>"UTF-8"</span><span>,</span><span>"GB2312"</span><span>,</span><span>'./xls/'</span><span>.$val</span><span>[</span><span>"name"</span><span>])))
</span><span>        {
</span><span></span><span>echo $val</span><span>[</span><span>'name'</span><span>] </span><span>. </span><span>"已存在"</span><span>. </span><span>"<br />"</span><span>;
</span><span></span><span>continue</span><span>;
</span><span>        }
</span><span></span><span>else
</span><span></span><span>{
</span><span>            move_uploaded_file(</span><span>$val</span><span>[</span><span>"tmp_name"</span><span>],iconv(</span><span>"UTF-8"</span><span>,</span><span>"GB2312"</span><span>,</span><span>'./xls/'</span><span>.$val</span><span>[</span><span>"name"</span><span>]));
</span><span></span><span>echo $val</span><span>[</span><span>"name"</span><span>],</span><span>'以上传成功！'</span><span>;
</span><span>        }
</span><span></span><span>$xlsPath = </span><span>iconv(</span><span>"UTF-8"</span><span>,</span><span>"GB2312"</span><span>,</span><span>'./xls/'</span><span>.$val</span><span>[</span><span>"name"</span><span>]); </span><span>//指定要读取的exls路径
</span><span>        //$type = 'Excel2007'; //设置要解析的Excel类型 Excel5(2003或以下版本)或Excel2007
</span><span></span><span>$type = </span><span>'Excel5'</span><span>;
</span><span></span><span>$xlsReader = </span><span>PHPExcel_IOFactory</span><span>::</span><span>createReader(</span><span>$type</span><span>);
</span><span></span><span>$Sheets = $xlsReader-></span><span>load(</span><span>$xlsPath</span><span>);
</span><span></span><span>//开始读取
</span><span></span><span>$Sheet = $Sheets-></span><span>getSheet(</span><span>0</span><span>)</span><span>-></span><span>toArray(); </span><span>//读取第一个工作表(注意编号从0开始) 如果读取多个可以做一个循环0,1,2,3....
</span><span></span><span>        //得到二维数组,每个小数组是excel表格内容的一行 里面包含此行的每列的数据
</span><span></span><span>echo </span><span>'<pre class="brush:php;toolbar:false">'</span><span>;
</span><span></span><span>//print_r($Sheet);die;
</span><span></span><span>foreach</span><span>(</span><span>$Sheet as $k</span><span>=></span><span>$v</span><span>){
</span><span></span><span>$res=$db-></span><span>exec(</span><span>"INSERT INTO a (data) values('"</span><span>.$v</span><span>[</span><span>1</span><span>]</span><span>.</span><span>"')"</span><span>);</span><span>//写入数据库
</span><span></span><span>if</span><span>(</span><span>$res</span><span>){
</span><span></span><span>//print_r($Sheet); //执行成功后，输出数据
</span><span></span><span>}
</span><span>        }
</span><span>    }
</span><span>?></span>

로그인 후 복사

php 코드 다운로드

<span><?php
</span><span>header(</span><span>"Content-type:text/html;charset=utf-8"</span><span>);</span><span>//设置编码
</span><span>function </span><span>exportExcel(</span><span>$filename</span><span>,</span><span>$content</span><span>){
</span><span>    header(</span><span>"Cache-Control: must-revalidate, post-check=0, pre-check=0"</span><span>);
</span><span>    header(</span><span>"Content-Type: application/vnd.ms-execl"</span><span>);
</span><span>    header(</span><span>"Content-Type: application/force-download"</span><span>);
</span><span>    header(</span><span>"Content-Type: application/download"</span><span>);
</span><span>    header(</span><span>"Content-Disposition: attachment; filename="</span><span>.$filename</span><span>);
</span><span>    header(</span><span>"Content-Transfer-Encoding: binary"</span><span>);
</span><span>    header(</span><span>"Pragma: no-cache"</span><span>);
</span><span>    header(</span><span>"Expires: 0"</span><span>);
</span><span></span><span>echo $content</span><span>;
</span><span></span><span>}
</span><span></span><span></span><span>$dsn=</span><span>"mysql:dbname=book;host=localhost"</span><span>;
</span><span>$db=new </span><span>PDO(</span><span>$dsn</span><span>,</span><span>'root'</span><span>,</span><span>'root'</span><span>,</span><span>array</span><span>(PDO</span><span>::</span><span>MYSQL_ATTR_INIT_COMMAND</span><span>=></span><span>'SET NAMES </span><span>\'</span><span>UTF8</span><span>\'</span><span>'</span><span>));</span><span>//实例化pdo对象
</span><span>$res=$db-></span><span>query(</span><span>"select * from a"</span><span>);</span><span>//从数据库查询数据
</span><span>$str = </span><span>"id</span><span>\t</span><span>数据</span><span>\t\n</span><span>"</span><span>;
</span><span>$str = </span><span>iconv(</span><span>'utf-8'</span><span>,</span><span>'gb2312'</span><span>,</span><span>$str</span><span>);</span><span>//iconv() 是编码转换函数
</span><span>$row=$res-></span><span>fetchAll();
</span><span>foreach</span><span>(</span><span>$row as $key </span><span>=> </span><span>$val</span><span>){
</span><span></span><span>$id = </span><span>iconv(</span><span>'utf-8'</span><span>,</span><span>'gb2312'</span><span>,</span><span>$val</span><span>[</span><span>'id'</span><span>]);
</span><span></span><span>$data = </span><span>iconv(</span><span>'utf-8'</span><span>,</span><span>'gb2312'</span><span>,</span><span>$val</span><span>[</span><span>'data'</span><span>]);
</span><span></span><span>$str .= $id.</span><span>"</span><span>\t</span><span>"</span><span>.$data.</span><span>"</span><span>\t\n</span><span>"</span><span>;
</span><span>}
</span><span>$filename = </span><span>date(</span><span>'Ymd'</span><span>)</span><span>.</span><span>'.xls'</span><span>;
</span><span>exportExcel(</span><span>$filename</span><span>,</span><span>$str</span><span>); </span><span>//生成excel表格</span>

로그인 후 복사

위 내용은 내용의 측면을 포함하여 엑셀 업로드 및 다운로드에 대해 소개하고 있으니 PHP 튜토리얼에 관심이 있는 친구들에게 도움이 되었으면 좋겠습니다.