记录一下以前做的后台excel格式导出统计信息的功能，也是最近同事问到了相关东西，一时之间竟忘了具体的细节，因此记录一下；
大家知道，excel导出数据的功能，后台几乎是必须功能，一般都是点击后，生成文件然后自动下载，
如果是数据量小的话，一下子便可请求完成，从而下载到本地；
但是，如果数据量特别大的时候，页面就必须一直在等待，直到写入excel成功，
这样便影响了后台使用者无法操作其他页面，为此，对excel导出做了以下功能优化：
生成excel文件的方法有很多，暂不一一记录，只是记录本次的方法；
这里用到了table的html格式，以及相应的excel的声明
（隐约记得其他的方法用office07打开的时候好像是乱码，后面尝试用csv格式文件，可还是乱码，所以用了table的形式）
文件的开头：
1 $struserdata = <<<Eof 2 3 xmlns:x="urn:schemas-microsoft-com:office:excel" 4 xmlns="http://www.w3.org/TR/REC-html40"> 5 6 PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 7 8 9 10 12 13 14当然，文件中间就是一些tr td 标签了。
<p><span> 场景：</span></p> <p><span> 用户点击 生成excel后，跳转到下载页面，程序在后台执行，用户可不必等待生成完成，可执行其他操作；</span></p> <p><span> 下载页面可看到文件生成的进度以及是否可下载状态</span></p> <p><span> 思路：</span></p> <p><span> 点击 生成excel，显示下载页面 ---> show_download方法</span></p> <p><span> 生成excel ---> create_excel 方法</span></p>
show_download方法中调用 create_excel方法，而show_download 方法中，自己用了一下命令行执行程序的方式,
利用php命令行的方式，把参数传递给 create_excel方法
<span><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;</span>
而在create_excel方法中：
需填写以下代码：
<span><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></span>
调用相关的api得到数据：
<span><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> }</span>但是怎么显示相应的文件生成进度呢，怎么知道文件到底生成好了没有呢？
这里，我用到的方法是，在写入数据文件的时候data.xsl，每个数据文件都生成一个对应的文件进度文件，暂且称为flag_data.xsl;
思路：
查看文件的进度方法：
1 public function execscript_process(){ 2 $this->load->library('smarty'); 3 $file_arr_str = array(); 4 $file_arr_process = array(); 5 $file_arr_name = array(); 6 $file_arr = array(); 7 $refresh_flag = 'yes'; 8 $uid = $_REQUEST['uid']; 9 $url_dir = STATISTIC_EXPORT_FILE_DIR.$uid .'/';//@todo 10 if(!is_dir($url_dir)){ 11 @mkdir($url_dir,0777); 12 } 13 $files = scandir($url_dir); 14 15 if(!empty($files)){ 16 foreach ($files as $key => $value) { 17 if($value!='.' && $value!='..'){ 18 if(substr($value, 0 , 5)=="flag_"){ 19 $file_size = filesize($url_dir . $value); 20 if(!empty($file_size)){ 21 $fhandle = fopen($url_dir . $value, 'rb+'); 22 fseek($fhandle, -1, SEEK_END); 23 $fstr = ''; 24 while(($c = fgetc($fhandle)) !== false) { 25 if($c == "\n" && $fstr) break; 26 $fstr = $c . $fstr; 27 fseek($fhandle, -2, SEEK_CUR); 28 } 29 fclose($fhandle); 30 $fstr = trim($fstr); 31 $fstr_arr_str = explode(',', $fstr); 32 $file_arr_process[] = 100 * number_format($fstr_arr_str[0]/$fstr_arr_str[1],2).'%'; 33 $file_arr_name[] = substr($value,5); 34 } 35 } 36 } 37 } 38 39 foreach ($file_arr_process as $key => $value) { 40 if($value != '100%'){ 41 $refresh_flag = 'no'; 42 break; 43 } 44 } 45 } 46 47 $file_arr = array( 48 'process' => $file_arr_process, 49 'name' => $file_arr_name, 50 'refresh_flag' => $refresh_flag 51 ); 52 $file_arr_json = json_encode($file_arr); 53 echo $file_arr_json; 54 } View Code文件的下载就好说了，既然已经都生成成功，下载的方法如下：
<span><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> }</span>本地本来已经测试完毕，可上线后，却出现了奇怪的问题；
<p><span> 现象描述：</span></p> <p><span> 当在后台点击生成文件，跳转到下载页的时候，因为下载页是显示文件进度的页面，</span><br /><span> 竟然出现有时候有刚刚点击的文件进度，有时候没有，就感觉没有生成相应的文件一样；</span></p> <p><span> 解决方法：</span></p> <p><span> 因为数据文件和进度文件都是生成在程序的某个文件夹file中，所以读取的时候都是读取的文件夹下的文件，从而判断显示进度；</span></p> <p><span> 后面才知道，由于后台程序有两台服务器，导致读取以及下载的时候找不到相应的文件夹，两个服务器相应的文件夹弄个共享目录就可以了</span></p>
由于下载的文件多了，导致文件夹下的文件越来越多，而原来生成的文件是没有价值的，所以加了个定期删除文件的功能，只保留近七天的文件
当然可以用crontab，只不过我比较懒，是在点击生成文件的时候，判断了一下文件夹中的过期文件，从而删除
<span><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> }</span>大文件的导出大体就是这个样子，欢迎大家吐槽，共同交流；
当时在用命令行执行方法的时候，也参考了一下相应的资料，记录一下；
<span>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</span>
remove means
How to set the computer to automatically connect to WiFi
How to use html for web navigation
What are the methods to implement operator overloading in Go language?
Detailed explanation of nginx configuration
Introduction to javascript special effects code
Recommended order for learning c++ and c language
How to buy Bitcoin
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
