Results 1 to 8 of 8

Thread: creating excel file using php..need help..

  1. #1
    Join Date
    Sep 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default creating excel file using php..need help..

    helo there..can anyone help me on how to make excel file using php. the data will be retrieve from multiple table in database. i've done excel file where the data from single table and i've made it. but for multiple table, i don't know how to do it..plz help me...

    e.g: excel code for single table in database.

    Code:
    <? 
    
    include "connectdb.php";
    
    $id = $_REQUEST['id'];
    
    		mysql_select_db('rocincorporation', $dbProd);
    		$SQL = "select a.*,b.vchdescription as vchcompanytype,c.vchdescription as vchcompanystatus,
    				 d.vchdescription as status,e.vchdescription as country
    				 from (rocincorporation.rocincorporation a 
    				left join parameters.parameters b on b.vchcode = a.chrcompanytype and b.vchparametertype = 'Company Category Eng'
    				left join parameters.parameters c on c.vchcode = a.chrcompanystatus and c.vchparametertype = 'Status Of Company'
    				left join parameters.parameters d on d.vchcode = a.chrstatusofcompany and d.vchparametertype = 'CompanyStatus'
    				left join parameters.parameters e on e.vchcode = a.vchcompanycountry and e.vchparametertype = 'Country')
    				where a.companyno='$id'";
    		//echo $SQL;
    
    		$result = mysql_query($SQL, $dbProd)or die(mysql_error());
    		$count = mysql_num_fields($result);
    
    
    for ($i = 0; $i < $count; $i++){
        $header .= mysql_field_name($result, $i)."\t";
    	
    }
    
    
    while($row = mysql_fetch_row($result)){
      $line = '';
      foreach($row as $value){
        if(!isset($value) || $value == ""){
          $value = "\t";
        }else{
    # important to escape any quotes to preserve them in the data.
          $value = str_replace('"', '""', $value);
    # needed to encapsulate data in quotes because some data might be multi line.
    # the good news is that numbers remain numbers in Excel even though quoted.
          $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
      }
      $data .= trim($line)."\n";
    }
    # this line is needed because returns embedded in the data have "\r"
    # and this looks like a "box character" in Excel
      $data = str_replace("\r", "", $data);
    
    
    # Nice to let someone know that the search came up empty.
    # Otherwise only the column name headers will be output to Excel.
    if ($data == "") {
      $data = "\nno matching records found\n";
    }
    
    $header=$header."\n";
    
    $dir = "Data/";
    $fname='Excel-'.$id.'.xls';
    $fp = fopen($dir.$fname,'w');
    fwrite ($fp,$header);
    fwrite ($fp,$data);
    
    
    
    ?>
    Last edited by tech_support; 09-05-2007 at 06:20 AM.

  2. #2
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,625
    Thanks
    6
    Thanked 107 Times in 107 Posts

  3. #3
    Join Date
    Sep 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    erm..codeexploiter , the link that u gave..is it means that i must install the pear??

  4. #4
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,625
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    Yes; you need to install PEAR package.

  5. #5
    Join Date
    Sep 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    is there other way instead of using pear?

  6. #6
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,625
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    You can try .CSV files like the same way you handle the normal file processing in PHP. The only thing is that you need to put comma after each cell value. As you know .CSV files can be opened in Microsoft Excel.

    But if you are looking for generating .XLS files then I think you can try the following options:

    PEAR Spreadsheet_Excel_Writer

    MS-Excel Stream Handler


    Eg of CSV file creation using PHP
    Last edited by codeexploiter; 09-05-2007 at 09:13 AM. Reason: More information

  7. #7
    Join Date
    Sep 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    how about if i want to display the data from various databases...can anyone help me with the code..from the example i've given, the data is from single database only... can somebody help??

  8. #8
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    you can try this. does not use any of the classes codeexploiter said

    PHP Code:
    <?PHP

    include('include/config.php');
    include(
    'include/opendb.php');

    $result mysql_query('SELECT * FROM repair');
    $count mysql_num_fields($result);

    for (
    $i 0$i $count$i++){
        
    $header .= mysql_field_name($result$i)."\t";
    }

    while(
    $row mysql_fetch_row($result)){
      
    $line '';
      foreach(
    $row as $value){
        if(!isset(
    $value) || $value == ""){
          
    $value "\t";
        }else{
    # important to escape any quotes to preserve them in the data.
          
    $value str_replace('"''""'$value);
    # needed to encapsulate data in quotes because some data might be multi line.
    # the good news is that numbers remain numbers in Excel even though quoted.
          
    $value '"' $value '"' "\t";
        }
        
    $line .= $value;
      }
      
    $data .= trim($line)."\n";
    }
    # this line is needed because returns embedded in the data have "\r"
    # and this looks like a "box character" in Excel
      
    $data str_replace("\r"""$data);


    # Nice to let someone know that the search came up empty.
    # Otherwise only the column name headers will be output to Excel.
    //if ($data == "") {
    //  $data = "\nno matching records found\n";
    //}

    # This line will stream the file to the user rather than spray it across the screen
    header("Content-type: application/octet-stream");

    # replace Spreadsheet.xls with whatever you want the filename to default to
    header("Content-Disposition: attachment; filename=$dbname.xls");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo 
    $header."\n".$data

    ?>
    Ryan
    Sevierville, TN

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •