I have experienced large file data export (background execution, automatic generation)

Source: Internet
Author: User
Tags xsl
First, preface

Record a previous do in the background Excel format Export statistics function, also recently colleague asked related things, in the moment unexpectedly forget the specific details, so record;

As you know, Excel exports the function of the data, the background is almost necessary to function, usually after the click, generate files and then automatically download,

If it is a small amount of data, you can request the completion of a complete, download to local;

However, if the amount of data is particularly large, the page must be waiting until it is written to excel successfully,

This affects the background user cannot manipulate the other pages, for this reason, the following functional optimizations for Excel export:

  1. Excel export is divided into two parts: generating an Excel file and downloading an Excel file
  2. Excel file generation in the background of the program execution, the front-end does not have to wait, can do other background operations
  3. Increase the download file page, show the progress of Excel file generation, after completion, you can download the generated Excel file
  4. After the file is generated, click Download to download the appropriate file

Second, generate Excel file

There are many ways to generate Excel files, not a single record, just record the method;

This uses the table's HTML format, and the corresponding Excel declaration

(Vaguely remember other methods with office07 open when it seems to be garbled, after the attempt to use CSV format file, can still be garbled, so the form of table)

The beginning of the file:

   
    1
    $struserdata= <<<
    Eof
    2         3xmlns:x= "Urn:schemas-microsoft-com:office:excel"
    4xmlns= "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             
    
     
    
    Ten         
    
     A         
     -         
     -         
     the
     -         
     -
    

View Code

End of File:

   
        
    
         1
    
         $struserdata = <<<
    
         Eof
    
         2         
   
        
Eof;
3 4 5 6Eof;

View Code

Of course, there are some TR TD labels in the middle of the file.

Third, let the program in the background to execute

Scene:

After the user clicks generate Excel, jumps to the download page, the program executes in the background, the user may not have to wait for the build to complete, can perform other actions;

Download page to see the progress of file generation and whether the status can be downloaded

Ideas:

Click Generate Excel to display the download page---> Show_download method

Generate Excel---> Create_excel methods

The Create_excel method is called in the Show_download method, and the Show_download method uses the command line to execute the program.

Use the PHP command line to pass parameters to the Create_excel method

    
      
      1
      // 
       $cmd = "/usr/bin/php/home/xxx/xxx.php". $strjoin. ">/dev/null &"; 
      2 
      //$a =exec ($cmd, $out, $returndata); 
      3 
      4 
      5 
      $command = "/usr/bin/php". Statistic_export_script_dir. "
 xxx.php "." ". 
      $strjoin . "'". 
 " " . 
      $uid . 
 " ".  
      $action . " & " 
      ; 
      6 
      $process = 
      proc_open( 
      $command, 
      array(), 
      $pipes
      ); 
      7 
      $var = 
      proc_get_status( 
      $process
      ); 
      8 
      Proc_close ( 
      $process
      ); 
      9

      $pid = 
      intval( 
      $var[' pid ']) +1; 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
     
    

In the Create_excel method:

You need to fill in the following code:

 
     
  
      1
  
      Set_time_limit (0); 
  
      //
  Maximum timeout for 
      canceling script run time
  
      2
  
      3
  
      Ignore_user_abort (
  
      TRUE); 
  
      //
  
      run in the background, not affected by user close browser
 
     

Call the relevant API to get the data:

 
     
  
      1
  
      $statistic = 
  
      call_user_func(
  
      array(' Shellscript ', ' Get_result '),
  
      $ URL,
  
      $params
  
      );
  
      2
  
      if (!
  
      Is_object (
  
      $statistic) | |
  
      isset (
  
      $statistic->data->
  
      items)) {
  
      3
  
      usleep( 400000);
  
      //
  
      Stop 400 milliseconds
  
      4
  
      $statistic = 
  
      call_user_func(
  
      array(' Shellscript ', ' Get_result '),
  
      $url,
  
      $params
  
      );
  
      5 }
 
     

Iv. show the progress of the file generation

But how to show the corresponding file generation progress, how to know that the file is actually generated?

Here, I use the method is to write the data file when the data.xsl, each data file generated a corresponding file progress file, temporarily known as flag_data.xsl;

Ideas:

  1. The first time the API is requested, the number of times to be requested is determined based on the total sum returned, as well as the pagesize.
  2. This will know the number of times to request the API (paging request API), while writing the data file, while writing a progress file flag_data.xsl;
    The data format is approximately (comma-delimited)
    1,5
    2,5
    ...
  3. And then show the file progress, read the progress file, so that the data file to know the overall progress
  4. Front-end JS processing, a few seconds to read the corresponding method (if all 100% progress, can stop the request method), so as to achieve dynamic view of the progress of the file generation

To view a file's Progress method:

     
      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
      Ten
      if(!
      Is_dir(
      $url _dir
      )){
       One@
      mkdir(
      $url _dir, 0777
      );
       A
              }
       -
      $files=
      Scandir(
      $url _dir
      );
       -
       the
      if(!
      Empty(
      $files
      )){
       -
      foreach(
      $files
       as
      $key=
      $value
      ) {
       -
      if(
      $value! = '. ' &&
      $value!='..'
      ){
       -
      if(
      substr(
      $value, 0, 5) = = "Flag_"
      ){
       +
      $file _size=
      filesize(
      $url _dir.
      $value
      );
       -
      if(!
      Empty(
      $file _size
      )){
       +
      $fhandle=
      fopen(
      $url _dir.
      $value, ' rb+ '
      );
       A
      fseek(
      $fhandle,-1,
      seek_end);
       at
      $fstr= ''
      ;
       -
       while((
      $c=
      fgetc(
      $fhandle)) !==
      false
      ) {
       -
      if(
      $c= = "\ n" &&
      $fstr)
       Break
      ;
       -
      $fstr=
      $c.
      $fstr
      ;
       -
      fseek(
      $fhandle,-2,
      seek_cur);
       -
                                  }
       in
      fclose(
      $fhandle
      );
       -
      $fstr=
      Trim(
      $fstr
      );
       to
      $fstr _arr_str=
      Explode(',',
      $fstr
      );
       +
      $file _arr_process[] = 100 *
      Number_format(
      $fstr _arr_str[0]/
      $fstr _arr_str[1],2). ' %'
      ;
       -
      $file _arr_name[] =
      substr(
      $value, 5
      );
       the
                               }
       *
                          }
       $
                      }
      Panax Notoginseng
                  }
       -
       the
      foreach(
      $file _arr_process
       as
      $key=
      $value
      ) {
       +
      if(
      $value! = ' 100% '
      ){
       A
      $refresh _flag= ' No '
      ;
       the
       Break
      ;
       +
                      }
       -
                  }
       $
              }
       $
       -
      $file _arr=
      Array
      (
       -' Process ' =
      $file _arr_process,
       the' Name ' + =
      $file _arr_name,
       -' Refresh_flag ' =
      $refresh _flag
      Wuyi
                  );
       the
      $file _arr_json= Json_encode (
      $file _arr
      );
       -
      Echo
      $file _arr_json
      ;
       Wu}
     

View Code

V. Download file

Download the file to say, since the success has been generated, the download method is as follows:

      
       1
        Public
       function
       execscript_download () {
       2
       $filename=
       $_request[' filename '
       ];
       3
       $uid=
       $_request[' UID '
       ];
       4
       $file _dir= Statistic_export_file_dir.
       $uid.' /'.
       $filename
       ;
       5
       if(!
       file_exists(
       $file _dir
       )){
       6
       Header("content-type:text/html; Charset=utf-8 "
       );
       7
       Echo"File not found!"
       ;
       8
       Exit
       ; 
       9}
       Else
        {
       Ten
       Ini_set("Memory_limit", "500M"
       ); 
        One
       Header(' Content-description:file Transfer '
       );
        A
       Header(' Content-type:application/octet-stream '
       );
        -
       Header(' Content-disposition:attachment; Filename= '.
       basename(
       $file _dir
       ));
        -
       Header(' Content-transfer-encoding:binary '
       );
        the
       Header(' Expires: '.
       gmdate(' d, D M Y h:i:s '). ' GMT '
       );
        -
       Header(' Cache-control:must-revalidate,post-check=0, pre-check=0 '
       );
        -
       Header(' Pragma:public '
       );
        -
       Header(' Content-length: '.
       filesize(
       $file _dir
       ));
        +
       ReadFile(
       $file _dir
       );
        -
               }
        +
        A}
      

Vi. problems arising after the launch

The Local has already been tested, can go online, but there are strange problems;

Description of the phenomenon:

When you click the Generate file in the background, jump to the download page, because the download page is the page showing the progress of the file,
Unexpectedly appears sometimes has just clicked the file progress, sometimes does not have, the feeling did not produce the corresponding file;

Workaround:

Because both the data file and the progress file are generated in a folder file of the program, so the reading is the file under the Read folder, thus judging the display progress;

Later, because the background program has two servers, resulting in reading and downloading when the corresponding folder can not be found, two servers corresponding folder to get a shared directory on it

Vii. corresponding follow-up optimization

Due to the number of files downloaded, resulting in more and more files under the folder, and the original generated files are worthless, so added a regular deletion of the file function, only the last seven days of the file

Of course, you can use crontab, but I am more lazy, is in the click to generate files, determine the folder of outdated files, thereby deleting

      
       1
        Public
       function
       execscript_process_show () {
       2
       $this->load->library (' Smarty '
       );
       3
       $uid=
       $_request[' UID '
       ];
       4
       $url _dir= Statistic_export_file_dir.
       $uid.' /';
       //
       @todo
       5
       if(!
       Is_dir(
       $url _dir
       )){
       6@
       mkdir(
       $url _dir, 0777
       );
       7
               }        
       8
       $files=
       Scandir(
       $url _dir
       );
       9
       if(!
       Empty(
       $files
       )){
       Ten
       foreach(
       $files
        as
       $key=
       $value
       ) {
        One
       if(
       $value! = '. ' &&
       $value!='..'
       ){
        A
       foreach(
       $files
        as
       $key=
       $value
       ) {
        -
       if(
       $value! = '. ' &&
       $value!='..'
       ){
        -
       if(
       substr(
       $value, 0, 5)! = "Flag_"
       ){
        the
       $filenamedate=
       substr(
       $value, 0,10
       );
        -
       $today=
       Date(' y-m-d ',
        Time
       ());
        -
       $filenamedate=
       Date(' y-m-d ',
       Strtotime(
       $filenamedate) + (statistic_file_expire_day-1) *24*3600
       );
        -
       if(
       $today>
       $filenamedate){
       //
       File Expiration
        +@
       unlink(
       $url _dir.
       $value
       );
        -@
       unlink(
       $url _dir. ' Flag_ '.
       $value
       );
        +
                                       }
        A
                                   }
        at
                               }
        -
                           }                    
        -
                       }
        -
                   }
        -
               }
        -
        in
       $this->smarty->assign (' uid ',
       $uid
       );
        -
       $this->smarty->display (' Interact/statistic/execscript.tpl '
       );
        to}
      

Eight, PostScript

Large file export is basically this appearance, welcome everybody spit groove, common exchange;

At that time in the command line to execute the method, also refer to the corresponding information, record;

 
       
        
  http://blog.csdn.net/yysdsyl/article/details/4636457http://www.codesky.net/article/201202/163385.htmlhttp:// www.cnblogs.com/zdz8207/p/3765567.htmlhttp://blog.163.com/mojian20040228@126/blog/static/ 4112219320097300922992/http://php.net/manual/en/features.commandline.phphttp://blog.csdn.net/yangjun07167/ article/details/5603425http://blog.csdn.net/yunsongice/article/details/5445448http://www.cppblog.com/amazon/ archive/2011/12/01/161281.aspxhttp://blog.51yip.com/tag/proc_openhttp://www.justwinit.cn/post/1418/http:// Limboy.me/tech/2010/12/05/php-async.html
 
       

The above describes my experience of large file data export (background execution, automatic generation), including the aspects of the content, I hope to be interested in PHP tutorial friends helpful.

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.