PHP imports Excel into database and exports database data to Excel, excel data export_PHP tutorial

PHP imports Excel into database and exports database data to Excel, export excel data

1. Import

　Importing requires the use of a component that can read Excel. There are also better components on the Internet. Here I share the one I use: Download Extraction code: vxyn. (Note that the two files are referenced)

<?<span>php
</span><span>//</span><span>传入要导入的Excel的文件名</span>
<span>function</span> import_to_DB(<span>$filename</span><span>) {
    </span><span>require_once</span>'reader.php'<span>;
    </span><span>$data</span> = <span>new</span> Spreadsheet_Excel_Reader();     <span>//</span><span>创建读取Excel的对象</span>
    <span>$data</span>->setOutputEncoding('utf-8');    <span>//</span><span>设置读取Excel内容后输出的字符编码</span>
    <span>$data</span>->read("data/Excel/{<span>$filename</span>}.xls"<span>);
    </span><span>$db</span> = <span>mysql_connect</span>('localhost', '用户名', '密码') or <span>die</span>("Could not connect to database."); <span>//</span><span>连接数据库   </span>
    <span>mysql_query</span>("set names 'uft8'"); <span>//</span><span>输出中文   </span>
    <span>mysql_select_db</span>('数据库名'); <span>//</span><span>选择数据库   </span>
    <span>error_reporting</span>(<span>E_ALL</span> ^ <span>E_NOTICE</span><span>);
    </span><span>for</span> (<span>$i</span> = 1; <span>$i</span> <= <span>$data</span>->sheets[0]['numRows']; <span>$i</span>++<span>) {
        </span><span>echo</span> <span>$data</span>->sheets[0]['cells'][<span>$i</span><span>][列数];
        </span><span>//</span><span>这里可以把每一行相应列的值插到数据库中，如：</span>
        <span>/*</span><span>
        $sql="insert "表名" values(对应项...)";
        mysql_query($sql);
        可加上错误判断
        </span><span>*/</span><span> 
    }  
</span>?>        

In short, if you can read the value of the corresponding column $data->sheets[0][row][column] in each row of the table, the insertion operation will be easy.

2. Export

　Export can use the MIME protocol to easily export table files without relying on any components. Set the header in the following format to export to Excel, and the browser will download it at the same time

<span>header</span>('Content-type: text/html; charset=utf-8'<span>);
</span><span>header</span>("Content-type:application/vnd.ms-excel;charset=UTF-8"); <span>//</span><span>application/vnd.ms-excel指定输出Excel格式</span>
<span>header</span>("Content-Disposition:filename=表格文件名.xls"); <span>//</span><span>输出的表格名称</span>

The complete code is as follows:

<?<span>php 
</span><span>header</span>('Content-type: text/html; charset=utf-8'<span>);
</span><span>header</span>("Content-type:application/vnd.ms-excel;charset=UTF-8"<span>); 
</span><span>header</span>("Content-Disposition:filename=表格文件名.xls"<span>);
</span><span>$conn</span> = <span>mysql_connect</span>("localhost","root","数据库密码") or <span>die</span>("不能连接数据库"<span>);
</span><span>mysql_select_db</span>("数据库名", <span>$conn</span><span>);
</span><span>mysql_query</span>("set names 'UTF-8'"<span>);
</span><span>$sql</span>="select * from 表名 where 条件"<span>;

</span><span>$result</span>=<span>mysql_query</span>(<span>$sql</span><span>);
</span><span>echo</span> "表头1\t表头2\t表头3\n"<span>;
</span><span>while</span>(<span>$row</span>=<span>mysql_fetch_array</span>(<span>$result</span><span>)){
    </span><span>echo</span> <span>$row</span>[0]."\t".<span>$row</span>[1]."\t".<span>$row</span>[2]."\n"<span>;
}
</span>?>

Here, t is actually a format change, and n is a line break. Set the link to this php file in a web page, and when clicked, the browser will automatically save the passed stream as an Excel file.

http://www.bkjia.com/PHPjc/1019642.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1019642.htmlTechArticlePHP imports Excel into the database and exports the database data to Excel. Excel data export 1. Import and import need to use the ability to read Excel components, there are also relatively good ones online, share them here...