Loading ...
Sorry, an error occurred while loading the content.
 

Speed up excel export

Expand Messages
  • binoy
    Hi friends, Iam trying to export the datas from mysql to excel file with different color for different columns. All are working if the datas are low (about
    Message 1 of 1 , Jul 13, 2009
      Hi friends,

      Iam trying to export the datas from mysql to excel file with different color for different columns. All are working if the datas are low (about 2000), but i need to export about 8000 records from the mysql. when i try to take this mcuh record i need to wait abt 5 minutes. How can i speed up this process,
      Iam pasting my code here. Please give an advice.



      <?php
      include ('config.php');
      $select = "SELECT Tariff, Service, DeliveryDate, DeliveryTime, POD FROM pod order by Date asc";   
      $export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
      $fields = mysql_num_fields ( $export );

      $row =0;

      require_once "class.writeexcel_workbook.inc.php";
      require_once "class.writeexcel_worksheet.inc.php";
      $fname = tempnam("/tmp", "panes.xls");
      $workbook = &new writeexcel_workbook($fname);
      $worksheet1 =& $workbook->addworksheet('Panes 1');

          for ( $i = 1; $i < $fields; $i++ ){   
              $headerfieldname = mysql_field_name( $export , $i );
                $worksheet1->write(0, $i, $headerfieldname, '');
          }
         
          $cnt =1;
             
          $format1 =& $workbook->addformat(array('fg_color' =>'rose'));
          $format2 =& $workbook->addformat(array('fg_color' =>'yellow'));
          $format3 =& $workbook->addformat(array('fg_color' =>'purple'));
           $format4 =& $workbook->addformat(array('fg_color' =>'white'));           

          while( $row = mysql_fetch_row( $export ) )  {  

              $selectcolor = "SELECT colorname, col FROM colorscheme where row='".$cnt."' order by col";
             
              $exportcolor = mysql_query ( $selectcolor );
              $newcolors = mysql_fetch_object($exportcolor);
              $column  = $newcolors->col;
             
              for($q=1; $q <=14; $q++)  {           
             
          
                  switch($newcolors->colorname) {
                      case 'rose' :
                          $format = $format1;
                          break;                   
                      case 'yellow' :
                          $format = $format2;
                          break;
                      case 'purple' :
                          $format = $format3;
                          break;                         
                      default:
                          $format = $format4;
                          break;       
                  }
                 
                  $newcolors = mysql_fetch_object($exportcolor);
                  $column  = $newcolors->col;
                 
               
                  $worksheet1->write($cnt, $q, $row[$q], $format );   
                     
              }               
              $cnt++;   
          
                 
          }
         
          $workbook->close();
          header("Content-Type: application/x-msexcel; name=\"".'Log-'.date('dmy', time()).".xls\"");
          header("Content-Disposition: inline; filename=\"".'Log-'.date('dmy', time()).".xls\"");
          $fh=fopen($fname, "rb");
          fpassthru($fh);
          unlink($fname);   

      With Regards
      Binoy.M.V



      Looking for local information? Find it on Yahoo! Local http://in.local.yahoo.com/

      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.