CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 [TIP] :: The simplest – easy – fast way to export to Excel sheet

Print topic Send  topic

Author Message
beshoo

Posts: 68
Posted: 12/12/2009, 2:15 PM

All of us have this challenge to export your report to csv file , the old solution is to export al hall page with cleaning the CSS and images and make the record limits to 5000 record so you can assure that this is the full records that your clients needs.
Well I don’t like the headache . so I invent an new method to do that which is very simple to make and very handy .
3 steps that is all, now we start counting :)

1- In your global functions file put this lovely function
function exportMysqlToCsv($sql,$filename = 'export.csv')  
{  
    $db=new clsDBWebecon();	  
	$sql_exec = $db->query($sql.'  LIMIT 0, 1');  
	$cols  = @array_keys(@mysql_fetch_assoc($sql_exec));  
	$table = @implode(',',$cols)."\n";  
	$count_col = count($cols);  
	if(!$count_col) print get_text(no_work_don);  
    $sql_exec = $db->query($sql. '  LIMIT 0, 20000');    
	    while ($db->next_record())  
        {  
		$col_array = NULL;  
			 for($i = 0 ; $i < $count_col ; $i++)  
			 {  
				$col_array[] = $db->f($cols[$i]);  
			 }  
		$table .= @implode(',',$col_array)."\n";  
        }  
    
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
    header("Content-Length: " . strlen($table));  
    // Output to browser with appropriate mime type, you choose ;)  
    header("Content-type: text/x-csv");  
    //header("Content-type: text/csv");  
    //header("Content-type: application/csv");  
    header("Content-Disposition: attachment; filename=$filename");  
      
	echo $table;  
    exit;  
  
}  
  

Tips : clsDBWebecon(); is my connection name , you know what you have to do :)
2- Make your select statement , and just select the column that you needs to be shown in the Excel sheet . do the inner join using the index to speed up the selection process.
Tips : use “select foo as Column_Name” taking in consideration that the mysql column name is the Excel column name.
3- when / where you like to make the download process please pass your sql statement to the function as following :


if(CCgetParam(export_to_csv) == 'yes')  
{  
 exportMysqlToCsv($sql,'Export_Report('.rand(3232,232344).').csv');  
}

That’s it :) :)
_________________
beshoo Love PHP and CC 4.2
View profile  Send private message
Aleister

Posts: 73
Posted: 12/16/2009, 2:14 PM

Very nice! Thank you!
View profile  Send private message
quasimidi


Posts: 151
Posted: 12/16/2009, 11:02 PM

Nice and simple, thanks for this great tip!

Please edit the topic header to inlcude [TIP] like.
_________________
RS
View profile  Send private message
ckroon

Posts: 868
Posted: 12/17/2009, 10:26 AM

I just pasted the function code to the bottom of the Functions file found under Common Files.
Published it.

But it can't find it.

Undefined Function error.

Am I missing something?

_________________
Walter Kempees...you are dearly missed.
View profile  Send private message
beshoo

Posts: 68
Posted: 12/17/2009, 10:49 AM

Dear all :) i am glad cos i make you happy ,
regarding Mr. ckroon , it seem there is some thing missing as the function is so clear and has no error . please recheck the code :) 8-)
_________________
beshoo Love PHP and CC 4.2
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.