首页 > 后端开发 > php教程 > 我所经历的大文件数据导出(后台执行,自动生成)

我所经历的大文件数据导出(后台执行,自动生成)

WBOY
发布: 2016-07-30 13:31:57
原创
1414 人浏览过

一、前言

    记录一下以前做的后台excel格式导出统计信息的功能,也是最近同事问到了相关东西,一时之间竟忘了具体的细节,因此记录一下;

    大家知道,excel导出数据的功能,后台几乎是必须功能,一般都是点击后,生成文件然后自动下载,

    如果是数据量小的话,一下子便可请求完成,从而下载到本地;

    但是,如果数据量特别大的时候,页面就必须一直在等待,直到写入excel成功,

    这样便影响了后台使用者无法操作其他页面,为此,对excel导出做了以下功能优化:

  1. excel导出分成两部分内容:生成excel文件和下载excel文件
  2. excel的文件生成在程序后台执行,前端不必等待,可进行其他后台操作
  3. 增加下载文件页面,显示excel文件生成的进度,完成后,方可下载生成的excel文件
  4. 文件生成后,点击下载方可下载相应的文件

 二、生成excel文件

    生成excel文件的方法有很多,暂不一一记录,只是记录本次的方法;

    这里用到了table的html格式,以及相应的excel的声明

    (隐约记得其他的方法用office07打开的时候好像是乱码,后面尝试用csv格式文件,可还是乱码,所以用了table的形式)

    文件的开头:

<spanmicrosoft yahei><span> 1</span><span>$struserdata</span> = Eof
<span> 2</span>          3         xmlns:x="urn:schemas-microsoft-com:office:excel"
<span> 4</span>         xmlns="http://www.w3.org/TR/REC-html40">
<span> 5</span><span> 6</span>         span>PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<span> 7</span>         
<span> 8</span>         
<span> 9</span>             <meta http-equiv="Content-type" c>
<span>10</span>         <style>
<span>11         </style>
<span>12</span>         
<span>13</span>         
<span>14</span>         <div align="center" x:publishsource="Excel">
<span>15</span><span>16</span>         <table x:str border="1" cellpadding="0" cellspacing="0" width="100%">
<span>17</span> Eof;<spanmicrosoft yahei>View Code<p><spanmicrosoft yahei>    文件的结尾:</spanmicrosoft></p>
<div><spanmicrosoft yahei><img src="http://image.codes51.com/Article/image/20150810/20150810094650_3369.gif" alt=""><img src="http://image.codes51.com/Article/image/20150810/20150810094650_3369.gif" alt=""><p></p>
<pre class="brush:php;toolbar:false"><spanmicrosoft yahei><span>1</span><span>$struserdata</span> = Eof
<span>2</span>         </spanmicrosoft>
登录后复制
3
4 5 6 Eof; View Code

    当然,文件中间就是一些tr td 标签了。

 三、让程序在后台执行

    场景:

        用户点击 生成excel后,跳转到下载页面,程序在后台执行,用户可不必等待生成完成,可执行其他操作;

        下载页面可看到文件生成的进度以及是否可下载状态

    思路:

        点击 生成excel,显示下载页面  ---> show_download方法

        生成excel ---> create_excel 方法

    show_download方法中调用 create_excel方法,而show_download 方法中,自己用了一下命令行执行程序的方式,

    利用php命令行的方式,把参数传递给 create_excel方法

<spanmicrosoft yahei><span>1</span><span>//</span><span> $cmd = "/usr/bin/php  /home/xxx/xxx.php " . $strjoin . "  >/dev/null & ";
</span><span>2</span><span> // $a=exec($cmd, $out, $returndata);</span><span>3</span><span>4</span><span>5</span><span>$command</span> = "/usr/bin/php ".STATISTIC_EXPORT_SCRIPT_DIR."xxx.php " . "'" .<span>$strjoin</span> ."'". " " . <span>$uid</span> . " ". <span>$action</span> ."  & "<span>;
</span><span>6</span><span>$process</span> = <span>proc_open</span>(<span>$command</span>, <span>array</span>(),<span>$pipes</span><span>); 
</span><span>7</span><span>$var</span> = <span>proc_get_status</span>(<span>$process</span><span>); 
</span><span>8</span><span>proc_close</span>(<span>$process</span><span>);
</span><span>9</span><span>$pid</span> = <span>intval</span>(<span>$var</span>['pid'])+1;</spanmicrosoft>
登录后复制

    而在create_excel方法中:

    需填写以下代码:

<spanmicrosoft yahei><span>1</span><span>set_time_limit</span>(0); <span>//</span><span>取消脚本运行时间的超时上限</span><span>2</span><span>3</span><span>ignore_user_abort</span>(<span>TRUE</span>); <span>//</span><span>后台运行,不受用户关闭浏览器的影响</span></spanmicrosoft>
登录后复制

    调用相关的api得到数据:

<spanmicrosoft yahei><span>1</span><span>$statistic</span> = <span>call_user_func</span>(<span>array</span>('shellscript','get_result'),<span>$url</span>,<span>$params</span><span>);
</span><span>2</span><span>if</span>(!<span>is_object</span>(<span>$statistic</span>) || !<span>isset</span>(<span>$statistic</span>->data-><span>items)){
</span><span>3</span><span>usleep</span>(400000);<span>//</span><span>停止400毫秒</span><span>4</span><span>$statistic</span> = <span>call_user_func</span>(<span>array</span>('shellscript','get_result'),<span>$url</span>,<span>$params</span><span>);
</span><span>5</span> }</spanmicrosoft>
登录后复制

 四、显示文件生成进度

    但是怎么显示相应的文件生成进度呢,怎么知道文件到底生成好了没有呢?

    这里,我用到的方法是,在写入数据文件的时候data.xsl,每个数据文件都生成一个对应的文件进度文件,暂且称为flag_data.xsl;

    思路:

  1. 第一次请求api的时候,根据返回的total总数,以及pagesize,确定要请求的次数count;
  2. 这样便可知道要请求api的次数(分页请求api),在写入数据文件的同时,同时写入进度文件flag_data.xsl;   
    数据格式大约是(以逗号分割)
        1,5
        2,5
        ...
  3. 然后显示文件进度的时候,读取进度文件,这样变可知道数据文件大体的进度
  4. 前端js处理时,几秒读取一次相应的方法(如果都100%进度,可停止请求方法),从而实现动态查看文件的生成进度

    查看文件的进度方法:

<spanmicrosoft yahei><span> 1</span><span>public</span><span>function</span><span> execscript_process(){
</span><span> 2</span><span>$this</span>->load->library('smarty'<span>);
</span><span> 3</span><span>$file_arr_str</span> = <span>array</span><span>();
</span><span> 4</span><span>$file_arr_process</span> = <span>array</span><span>();
</span><span> 5</span><span>$file_arr_name</span> = <span>array</span><span>();
</span><span> 6</span><span>$file_arr</span> = <span>array</span><span>();
</span><span> 7</span><span>$refresh_flag</span> = 'yes'<span>;
</span><span> 8</span><span>$uid</span> = <span>$_REQUEST</span>['uid'<span>];
</span><span> 9</span><span>$url_dir</span> = STATISTIC_EXPORT_FILE_DIR.<span>$uid</span> .'/';<span>//</span><span>@todo</span><span>10</span><span>if</span>(!<span>is_dir</span>(<span>$url_dir</span><span>)){
</span><span>11</span>             @<span>mkdir</span>(<span>$url_dir</span>,0777<span>);
</span><span>12</span><span>        }
</span><span>13</span><span>$files</span> = <span>scandir</span>(<span>$url_dir</span><span>);
</span><span>14</span><span>15</span><span>if</span>(!<span>empty</span>(<span>$files</span><span>)){
</span><span>16</span><span>foreach</span> (<span>$files</span><span>as</span><span>$key</span> => <span>$value</span><span>) {
</span><span>17</span><span>if</span>(<span>$value</span>!='.' && <span>$value</span>!='..'<span>){
</span><span>18</span><span>if</span>(<span>substr</span>(<span>$value</span>, 0 , 5)=="flag_"<span>){
</span><span>19</span><span>$file_size</span> = <span>filesize</span>(<span>$url_dir</span> . <span>$value</span><span>);
</span><span>20</span><span>if</span>(!<span>empty</span>(<span>$file_size</span><span>)){
</span><span>21</span><span>$fhandle</span> = <span>fopen</span>(<span>$url_dir</span> . <span>$value</span>, 'rb+'<span>);
</span><span>22</span><span>fseek</span>(<span>$fhandle</span>, -1,<span> SEEK_END);
</span><span>23</span><span>$fstr</span> = ''<span>;
</span><span>24</span><span>while</span>((<span>$c</span> = <span>fgetc</span>(<span>$fhandle</span>)) !== <span>false</span><span>) {
</span><span>25</span><span>if</span>(<span>$c</span> == "\n" && <span>$fstr</span>) <span>break</span><span>;
</span><span>26</span><span>$fstr</span> = <span>$c</span> . <span>$fstr</span><span>;
</span><span>27</span><span>fseek</span>(<span>$fhandle</span>, -2,<span> SEEK_CUR);
</span><span>28</span><span>                            }
</span><span>29</span><span>fclose</span>(<span>$fhandle</span><span>);
</span><span>30</span><span>$fstr</span> = <span>trim</span>(<span>$fstr</span><span>);
</span><span>31</span><span>$fstr_arr_str</span> = <span>explode</span>(',', <span>$fstr</span><span>);
</span><span>32</span><span>$file_arr_process</span>[] = 100 * <span>number_format</span>(<span>$fstr_arr_str</span>[0]/<span>$fstr_arr_str</span>[1],2).'%'<span>;
</span><span>33</span><span>$file_arr_name</span>[] = <span>substr</span>(<span>$value</span>,5<span>);
</span><span>34</span><span>                         }
</span><span>35</span><span>                    }
</span><span>36</span><span>                }
</span><span>37</span><span>            }
</span><span>38</span><span>39</span><span>foreach</span> (<span>$file_arr_process</span><span>as</span><span>$key</span> => <span>$value</span><span>) {
</span><span>40</span><span>if</span>(<span>$value</span> != '100%'<span>){
</span><span>41</span><span>$refresh_flag</span> = 'no'<span>;
</span><span>42</span><span>break</span><span>;
</span><span>43</span><span>                }
</span><span>44</span><span>            }
</span><span>45</span><span>        }
</span><span>46</span><span>47</span><span>$file_arr</span> = <span>array</span><span>(
</span><span>48</span>             'process' => <span>$file_arr_process</span>,
<span>49</span>             'name' => <span>$file_arr_name</span>,
<span>50</span>             'refresh_flag' => <span>$refresh_flag</span><span>51</span><span>            );
</span><span>52</span><span>$file_arr_json</span> = json_encode(<span>$file_arr</span><span>);
</span><span>53</span><span>echo</span><span>$file_arr_json</span><span>;
</span><span>54</span>     }</spanmicrosoft>
登录后复制
View Code

 五、下载文件

    文件的下载就好说了,既然已经都生成成功,下载的方法如下:

<spanmicrosoft yahei><span> 1</span><span>public</span><span>function</span><span> execscript_download(){
</span><span> 2</span><span>$filename</span> = <span>$_REQUEST</span>['filename'<span>];
</span><span> 3</span><span>$uid</span> = <span>$_REQUEST</span>['uid'<span>];
</span><span> 4</span><span>$file_dir</span> = STATISTIC_EXPORT_FILE_DIR.<span>$uid</span>.'/'.<span>$filename</span><span>;
</span><span> 5</span><span>if</span> (!<span>file_exists</span>(<span>$file_dir</span><span>)){
</span><span> 6</span><span>header</span>("Content-type: text/html; charset=utf-8"<span>);
</span><span> 7</span><span>echo</span> "File not found!"<span>;
</span><span> 8</span><span>exit</span><span>; 
</span><span> 9</span>         } <span>else</span><span> {
</span><span>10</span><span>ini_set</span>("memory_limit","500M"<span>); 
</span><span>11</span><span>header</span>('Content-Description: File Transfer'<span>);
</span><span>12</span><span>header</span>('Content-Type: application/octet-stream'<span>);
</span><span>13</span><span>header</span>('Content-Disposition: attachment; filename='.<span>basename</span>(<span>$file_dir</span><span>));
</span><span>14</span><span>header</span>('Content-Transfer-Encoding: binary'<span>);
</span><span>15</span><span>header</span>('Expires: ' . <span>gmdate</span>('D, d M Y H:i:s') . ' GMT'<span>);
</span><span>16</span><span>header</span>('Cache-Control: must-revalidate,post-check=0, pre-check=0'<span>);
</span><span>17</span><span>header</span>('Pragma: public'<span>);
</span><span>18</span><span>header</span>('Content-Length: ' . <span>filesize</span>(<span>$file_dir</span><span>));
</span><span>19</span><span>readfile</span>(<span>$file_dir</span><span>);
</span><span>20</span><span>        }
</span><span>21</span><span>22</span>     }</spanmicrosoft>
登录后复制

 六、上线后出现的问题

    本地本来已经测试完毕,可上线后,却出现了奇怪的问题;

    现象描述:

        当在后台点击生成文件,跳转到下载页的时候,因为下载页是显示文件进度的页面,
        竟然出现有时候有刚刚点击的文件进度,有时候没有,就感觉没有生成相应的文件一样;

    解决方法:

        因为数据文件和进度文件都是生成在程序的某个文件夹file中,所以读取的时候都是读取的文件夹下的文件,从而判断显示进度;

        后面才知道,由于后台程序有两台服务器,导致读取以及下载的时候找不到相应的文件夹,两个服务器相应的文件夹弄个共享目录就可以了

 七、相应的后续优化

    由于下载的文件多了,导致文件夹下的文件越来越多,而原来生成的文件是没有价值的,所以加了个定期删除文件的功能,只保留近七天的文件

    当然可以用crontab,只不过我比较懒,是在点击生成文件的时候,判断了一下文件夹中的过期文件,从而删除

<spanmicrosoft yahei><span> 1</span><span>public</span><span>function</span><span> execscript_process_show(){
</span><span> 2</span><span>$this</span>->load->library('smarty'<span>);
</span><span> 3</span><span>$uid</span> = <span>$_REQUEST</span>['uid'<span>];
</span><span> 4</span><span>$url_dir</span> = STATISTIC_EXPORT_FILE_DIR.<span>$uid</span> .'/';<span>//</span><span>@todo</span><span> 5</span><span>if</span>(!<span>is_dir</span>(<span>$url_dir</span><span>)){
</span><span> 6</span>             @<span>mkdir</span>(<span>$url_dir</span>,0777<span>);
</span><span> 7</span><span>        }        
</span><span> 8</span><span>$files</span> = <span>scandir</span>(<span>$url_dir</span><span>);
</span><span> 9</span><span>if</span>(!<span>empty</span>(<span>$files</span><span>)){
</span><span>10</span><span>foreach</span> (<span>$files</span><span>as</span><span>$key</span> => <span>$value</span><span>) {
</span><span>11</span><span>if</span>(<span>$value</span>!='.' && <span>$value</span>!='..'<span>){
</span><span>12</span><span>foreach</span> (<span>$files</span><span>as</span><span>$key</span> => <span>$value</span><span>) {
</span><span>13</span><span>if</span>(<span>$value</span>!='.' && <span>$value</span>!='..'<span>){
</span><span>14</span><span>if</span>(<span>substr</span>(<span>$value</span>, 0 , 5)!="flag_"<span>){
</span><span>15</span><span>$filenamedate</span> = <span>substr</span>(<span>$value</span>, 0,10<span>);
</span><span>16</span><span>$today</span> = <span>date</span>('Y-m-d',<span>time</span><span>());
</span><span>17</span><span>$filenamedate</span> = <span>date</span>('Y-m-d',<span>strtotime</span>(<span>$filenamedate</span>)+(STATISTIC_FILE_EXPIRE_DAY-1)*24*3600<span>);
</span><span>18</span><span>if</span>(<span>$today</span>><span>$filenamedate</span>){<span>//</span><span>文件过期</span><span>19</span>                                     @<span>unlink</span>(<span>$url_dir</span> . <span>$value</span><span>);
</span><span>20</span>                                     @<span>unlink</span>(<span>$url_dir</span> . 'flag_' . <span>$value</span><span>);
</span><span>21</span><span>                                }
</span><span>22</span><span>                            }
</span><span>23</span><span>                        }
</span><span>24</span><span>                    }                    
</span><span>25</span><span>                }
</span><span>26</span><span>            }
</span><span>27</span><span>        }
</span><span>28</span><span>29</span><span>$this</span>->smarty->assign('uid',<span>$uid</span><span>);
</span><span>30</span><span>$this</span>->smarty->display('interact/statistic/execscript.tpl'<span>);
</span><span>31</span>     }</spanmicrosoft>
登录后复制

 八、后记

    大文件的导出大体就是这个样子,欢迎大家吐槽,共同交流;

    当时在用命令行执行方法的时候,也参考了一下相应的资料,记录一下;

<spanmicrosoft yahei>http://blog.csdn.net/yysdsyl/article/details/4636457

http://www.codesky.net/article/201202/163385.html

http://www.cnblogs.com/zdz8207/p/3765567.html

http://blog.163.com/mojian20040228@126/blog/static/4112219320097300922992/

http://php.net/manual/en/features.commandline.php

http://blog.csdn.net/yangjun07167/article/details/5603425

http://blog.csdn.net/yunsongice/article/details/5445448

http://www.cppblog.com/amazon/archive/2011/12/01/161281.aspx

http://blog.51yip.com/tag/proc_open

http://www.justwinit.cn/post/1418/

http://limboy.me/tech/2010/12/05/php-async.html</spanmicrosoft>
登录后复制

 

以上就介绍了我所经历的大文件数据导出(后台执行,自动生成),包括了方面的内容,希望对PHP教程有兴趣的朋友有所帮助。

相关标签:
来源:php.cn
上一篇:(六)面向对象的设计原则之二 下一篇:Nginx的Web缓存服务与新浪网的开源NCACHE模块(1)
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
最新问题
相关专题
更多>
热门推荐
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板