export to excel

For Joomla! 1.5 Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general
Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
Locked
MrTripi
Joomla! Intern
Joomla! Intern
Posts: 93
Joined: Thu Jul 17, 2008 2:26 pm

export to excel

Post by MrTripi » Wed Jun 17, 2009 6:07 pm

was just wondering if Joomla 1.5 provides any classes for exporting tables to excel.

jeffchannell
Joomla! Ace
Joomla! Ace
Posts: 1964
Joined: Tue Jun 09, 2009 2:21 am
Location: WV
Contact:

Re: export to excel

Post by jeffchannell » Wed Jun 17, 2009 6:15 pm

Exporting your database to excel? Your host should have phpMyAdmin installed, and that has Excel export capability built in. :)
http://jeffchannell.com - Joomla Extensions & Support
http://biziant.com - Open Joomla Firewall/IDS
Unsolicited private messages/emails = hire me to fix your problem.
καλλιστι

MrTripi
Joomla! Intern
Joomla! Intern
Posts: 93
Joined: Thu Jul 17, 2008 2:26 pm

Re: export to excel

Post by MrTripi » Wed Jun 17, 2009 6:20 pm

True, phpmyadmin would do the trick. Not every user is comfortable touching their database though.

The component displays statistical data on one of my plugins.

I'd like to include a link for exporting the resulting table into excel so that we can generate graphs,etc.

jeffchannell
Joomla! Ace
Joomla! Ace
Posts: 1964
Joined: Tue Jun 09, 2009 2:21 am
Location: WV
Contact:

Re: export to excel

Post by jeffchannell » Wed Jun 17, 2009 6:24 pm

Ah, I see... try using a content-type header of "csv" and use comma-delimited text. Excel understands this (albeit with no formulas, etc, AFAIK).
http://jeffchannell.com - Joomla Extensions & Support
http://biziant.com - Open Joomla Firewall/IDS
Unsolicited private messages/emails = hire me to fix your problem.
καλλιστι

MrTripi
Joomla! Intern
Joomla! Intern
Posts: 93
Joined: Thu Jul 17, 2008 2:26 pm

Re: export to excel

Post by MrTripi » Wed Jun 17, 2009 6:39 pm

soooo...

have the component write a .php file in the temp directory then provide a link to it?

Code: Select all

<?php header('Content-type: application/csv');?>
<table>
<tr><td>

blahblahblah

</td><tr>
</table>


User avatar
radiant_tech
Joomla! Guru
Joomla! Guru
Posts: 531
Joined: Sat Dec 15, 2007 3:02 pm
Location: Washington DC Metro

Re: export to excel

Post by radiant_tech » Wed Jun 17, 2009 7:12 pm

You could add a php class that handles data exports to your component. For instance, I've used IAM_CSVDump to automate data exports to CSV files. And similarly, I have used ExcelWriterXML to build complex Excel files in the back-end.

MrTripi
Joomla! Intern
Joomla! Intern
Posts: 93
Joined: Thu Jul 17, 2008 2:26 pm

Re: export to excel

Post by MrTripi » Wed Jun 17, 2009 7:23 pm

nvm, I just wrote an xml file and threw in a few tables with notepad. it seemed to do the trick ;)

MrTripi
Joomla! Intern
Joomla! Intern
Posts: 93
Joined: Thu Jul 17, 2008 2:26 pm

Re: export to excel

Post by MrTripi » Wed Jun 17, 2009 7:31 pm

Code: Select all

srand ((double) microtime( )*1000000);
$random_number = rand( );

$myFile = '../tmp/'.$random_number.'.xls';

$fh = fopen($myFile, 'w') or die("can't open file");
$stringData = $output;
fwrite($fh, $stringData);
fclose($fh);
works :D

zak_the_man
Joomla! Intern
Joomla! Intern
Posts: 55
Joined: Mon Feb 11, 2008 3:49 pm
Contact:

Re: export to excel

Post by zak_the_man » Sat Jun 20, 2009 7:05 pm

Or you could use the SQL 2 Excel component:

http://extensions.joomla.org/extensions ... 13/details

User avatar
dam-man
Joomla! Exemplar
Joomla! Exemplar
Posts: 7961
Joined: Fri Sep 09, 2005 2:13 pm
Location: The Netherlands
Contact:

Re: export to excel

Post by dam-man » Mon Jun 22, 2009 10:41 am

Or in your own component you can have this function:

Code: Select all

	###############################################################
## This function will generate a report of invoices XLS Format
	
	function generateReport(){
		
		global $mainframe;
		
		## Make DB connections
		$db    = JFactory::getDBO();
		
		$sql = 'Your Query Goes Here..';
		
		$db->setQuery($sql);
		$rows = $db->loadAssocList();
		
		## If the query doesn't work..
		if (!$db->query() ){
			echo "<script>alert('Please report your problem.');
			window.history.go(-1);</script>\n";		 
		}	
		
		## Empty data vars
		$data = "" ;
		## We need tabbed data
		$sep = "\t"; 
		
		$fields = (array_keys($rows[0]));
		
		## Count all fields(will be the collumns
		$columns = count($fields);
		## Put the name of all fields to $out.  
		for ($i = 0; $i < $columns; $i++) {
		  $data .= $fields[$i].$sep;
		}
		
		$data .= "\n";
		
		## Counting rows and push them into a for loop
		for($k=0; $k < count( $rows ); $k++) {
			$row = $rows[$k];
			$line = '';
			
			## Now replace several things for MS Excel
			foreach ($row as $value) {
			  $value = str_replace('"', '""', $value);
			  $line .= '"' . $value . '"' . "\t";
			}
			$data .= trim($line)."\n";
		}
		
		$data = str_replace("\r","",$data);
		
		## If count rows is nothing show o records.
		if (count( $rows ) == 0) {
		  $data .= "\n(0) Records Found!\n";
		}
		
		## Push the report now!
		$this->name = 'export-invoices';
		header("Content-type: application/octet-stream");
		header("Content-Disposition: attachment; filename=".$this->name.".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
		header("Lacation: excel.htm?id=yes");
		print $data ;
		die();	
	}

Robert Dam - Joomla Forum Moderator
Dutch Boards | Joomla Coding Boards | English Support Boards

User avatar
imNotScott
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Wed Sep 21, 2005 12:06 pm
Location: Frederick, MD

Re: export to excel

Post by imNotScott » Tue Nov 10, 2009 12:13 am

This was exactly what I needed, thanks!
~ Scott

The key to success is sincerity. Once you learn how to fake that you've got it made.

ericgibert
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Thu Nov 18, 2010 2:24 pm

Re: export to excel

Post by ericgibert » Thu Nov 18, 2010 2:29 pm

Dear Dan-man,

Is there a typo at the line:

header("Lacation: excel.htm?id=yes");

Lacation or Location?

Anyway, I have implemented that script and it works fine (thank you!) but for one caveat: I associated it behind a button and once completed, whatever I do, the page behaves as if I click on the button again! i.e. always asking me to "open or save the XL file".

I tried to replace the die by return or by setRedirect without much success.
Any better way to cleanly quit?

Thank you

Eric

rajshivnesh
Joomla! Apprentice
Joomla! Apprentice
Posts: 14
Joined: Wed Sep 01, 2010 9:23 pm

Re: export to excel

Post by rajshivnesh » Tue Nov 23, 2010 10:10 pm

instead of this line:

$data = str_replace("\r","",$data);

use the below line of code to go to new line:

$data = str_replace("\r\n","",$data);

fa89
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Sun Mar 27, 2011 1:48 pm

Re: export to excel

Post by fa89 » Thu Apr 28, 2011 10:12 am

thank you very much dam-man your function helped me a lot.
it's very useful :)

fa89
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Sun Mar 27, 2011 1:48 pm

Re: export to excel

Post by fa89 » Thu Apr 28, 2011 11:01 am

hi,
I just want to know if there is a possibility to change the color of the first line that contains the names of fields and the color of the first column in the excel file generated.
i use the function proposed by dam-man .

User avatar
tarantino
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Wed Aug 12, 2009 8:44 pm
Contact:

Re: export to excel

Post by tarantino » Sat Oct 08, 2011 8:42 pm

dam-man wrote:Or in your own component you can have this function:

Code: Select all

	###############################################################
## This function will generate a report of invoices XLS Format
	
	function generateReport(){
		
		global $mainframe;
		
		## Make DB connections
		$db    = JFactory::getDBO();
		
		$sql = 'Your Query Goes Here..';
		
		$db->setQuery($sql);
		$rows = $db->loadAssocList();
		
		## If the query doesn't work..
		if (!$db->query() ){
			echo "<script>alert('Please report your problem.');
			window.history.go(-1);</script>\n";		 
		}	
		
		## Empty data vars
		$data = "" ;
		## We need tabbed data
		$sep = "\t"; 
		
		$fields = (array_keys($rows[0]));
		
		## Count all fields(will be the collumns
		$columns = count($fields);
		## Put the name of all fields to $out.  
		for ($i = 0; $i < $columns; $i++) {
		  $data .= $fields[$i].$sep;
		}
		
		$data .= "\n";
		
		## Counting rows and push them into a for loop
		for($k=0; $k < count( $rows ); $k++) {
			$row = $rows[$k];
			$line = '';
			
			## Now replace several things for MS Excel
			foreach ($row as $value) {
			  $value = str_replace('"', '""', $value);
			  $line .= '"' . $value . '"' . "\t";
			}
			$data .= trim($line)."\n";
		}
		
		$data = str_replace("\r","",$data);
		
		## If count rows is nothing show o records.
		if (count( $rows ) == 0) {
		  $data .= "\n(0) Records Found!\n";
		}
		
		## Push the report now!
		$this->name = 'export-invoices';
		header("Content-type: application/octet-stream");
		header("Content-Disposition: attachment; filename=".$this->name.".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
		header("Lacation: excel.htm?id=yes");
		print $data ;
		die();	
	}

thank you for this but for the unicode like arabian languages the exported xls file is not true words?like this : ط¹ط¨ط§ط³غŒ
i changes the code to :
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
but again not working ?

any solutions?
http://en.P30doctor.com Joomla Extensions Developer
Do not tell God that i have a great problem, tell the problem that you have a great God.

swadhwa
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Thu Feb 09, 2012 5:53 pm

Re: export to excel

Post by swadhwa » Thu Feb 09, 2012 5:59 pm

Hi All

I am trying to using the csv download script in my controller. I have tried by using headers as well php function defined above. It is not working even for simple case.

Here is my function:
function workdownload(){
//global $mainframe;
$data='test1,test2,test3,';
//$model = $this->getModel('listworkdownload');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file.xls");
header("Pragma: no-cache");
header("Expires: 0");
header("Lacation: excel.htm?id=yes");
print $data ;
//$mainframe->close();
}

Please help me.

Looking Forward.

Regards
S.Wadhwa

sunil_007
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Fri Aug 10, 2012 4:33 pm

Re: export to excel

Post by sunil_007 » Sat Aug 11, 2012 8:01 pm

Hi there, I have use the function given by dan-man, it works and export the file in .xls format. but when I try to open the file.. it gives this error =
"the file you are trying to open is in a different format than specified by the file extension xls ......"

How to avoid this error, for smooth opening of file... what to write in the code...?

Regards
Sunil S.

User avatar
Chris Davenport
Joomla! Ace
Joomla! Ace
Posts: 1385
Joined: Thu Aug 18, 2005 8:57 am
Location: Shrewsbury, Shropshire, United Kingdom

Re: export to excel

Post by Chris Davenport » Sun Aug 12, 2012 7:37 am

What is actually being exported here is CSV and not XLS. Perhaps try changing

Code: Select all

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$this->name.".xls");
to

Code: Select all

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=".$this->name.".csv");
Chris.
Chris Davenport

Davenport Technology Services http://www.davenporttechnology.com/
Lion Coppice http://www.lioncoppice.org/

ra1
Joomla! Apprentice
Joomla! Apprentice
Posts: 43
Joined: Fri Aug 31, 2012 11:04 am

Re: export to excel

Post by ra1 » Fri Aug 31, 2012 12:19 pm

this script is not working for me bcz in my component i hv already used one sql statment and i dont know how to use 2 sql statment in one component .. i am working with virtuemart and i want to store data in both database and excel file.. so plz help me,........ thnxxx


Locked

Return to “Joomla! 1.5 Coding”