Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: [HOW TO] Export your table to csv / text file

[HOW TO] Export your table to csv / text file 18 Oct 2012 21:14 #4647

  • VeCrea
  • VeCrea's Avatar
  • Offline
  • Platinum Member
  • Absolute JCook fan
  • Posts: 473
  • Thank you received: 100
  • Karma: 30
Man i had the worst time of my life going through this...
What i needed
The user was to be able to use filter, data range and so on, and then being able to get a csv file with what was displayed as a result of all the filtering.
What i had to do
Bunch of code modification, but it works now. Also a modification of jDom regarding the display of the toggle fields. Let's dig into this :

Find the default.php file of the table you want
Should be in (administrator)/the_name_of_your_component/views/the_name_of_the_view/tmpl/default.php

Add the bunch of code between headerDeclarations and form
// no direct access
defined('_JEXEC') or die('Restricted access');

you_component_Helper::headerDeclarations();
?>

<form action="<?php echo(JRoute::_("index.php")); ?>" method="post" name="adminForm" id="adminForm">
becomes
// no direct access
defined('_JEXEC') or die('Restricted access');

you_component_Helper::headerDeclarations();
?>
<script>
jQuery(document).ready(function() {
    jQuery('#convert').click(function() {
	    var href = 'url_of_your_site/libraries/your_name/export.php?csv=';
		var data = jQuery('#grid').tableToCSV();
		data = data.replace(/\s+/g, '');
		data = data.replace(/\\r/g, "\r\n");
		href += encodeURIComponent(data);
		jQuery(this).attr('href', href);
		});
});
jQuery.fn.tableToCSV = function() {
	 var csv = '';
	 var headers = [];

	  jQuery("#grid").find('th').each(function() {
		   var header = '';
				var $th = jQuery(this);
				var text = $th.text();
				var header = '"' + text + '"';
				headers.push(header);
			});
	   csv += headers.join(',');
	   csv += '\\r\n';
	   
	   jQuery("#grid").find('tr').not('tr:first-child').each(function() {
	     jQuery(this).find('td').each(function() {
		    var row = jQuery(this).text();
			if(!jQuery(this).is('td:last-child')) {
			    row += ',';
			} else {
				row += '\\r\n';
			}
			csv += row;
		 });
	  });
	return csv;
};

</script>
<form action="<?php echo(JRoute::_("index.php")); ?>" method="post" name="adminForm" id="adminForm">

Add a link that triggers the export

Place this line of code where you want your link to export to appear.
            <div><a id="convert">Sauvez en CSV</a></div>
Export.php
Create a new folder in url_of_your_site/libraries/ and call it by your name
Then create a new file (export.php) in url_of_your_site/libraries/your_name/
The content of this file should be
<? 
if(empty($_REQUEST['csv'])){
	exit;
}
header('Content-Type: application/text; charset=utf-8');
header('Content-Disposition: attachment; filename="table.txt"');

$csv = $_GET['csv'];
$csv = utf8_decode($csv);
echo $csv;
?>
You can see there that you decide the name of the file (table.txt). I saw that some people append the time() so that each downloaded file is unique, if you need it.

If needed, modify jDom for the Toggle Fields
in administrator/your_component_name/dom/html/grid/bool.php,
comment the part that displays the image and the alt attribute, and use the alt attribute to give the link a name
        $html .= "<img " /*src='<%IMAGE_SOURCE%>' border='0' "alt='<%ALT%>'"*/
			.	" title='" . htmlspecialchars($title, ENT_COMPAT, 'UTF-8') . "' /><%ALT%>" .LN;
Last Edit: 18 Oct 2012 21:21 by VeCrea.
The administrator has disabled public write access.
The following user(s) said Thank You: twev

Re: [HOW TO] Export your table to csv / text file 18 Oct 2012 21:24 #4648

  • VeCrea
  • VeCrea's Avatar
  • Offline
  • Platinum Member
  • Absolute JCook fan
  • Posts: 473
  • Thank you received: 100
  • Karma: 30
Just think that with a bit of code optimisation and some improvements, this could easily get into cook's core, maybe with a button in the toolbar ?
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 20 Oct 2012 10:03 #4683

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 68
Hi VeCrea,

I haven't had chance to work through this yet but nice work!

One suggestion, if you want to include this across your component, it may be wise to write the jQuery function in a separate js file and simply include it in any/all pages you wish to use it.
VeCrea wrote:
You can see there that you decide the name of the file (table.txt). I saw that some people append the time() so that each downloaded file is unique, if you need it.
RE: appending the current time to the file name, that was only done if the csv files were being output into a web directory, i.e. not downloaded directly from the browser, and was to avoid overwriting the file. I think that this was the overwrite param (true/false) I included in the bare bones function that I wrote if I remember correctly. Just to clarify, in my original bare bones php script, it only uses this parameter if the download param were set to false.

One other thing about jQuery, why not pass jQuery ($) in the function call like;
jQuery(document).ready(function($) {....
}
Then, instead of using jQuery as your selector throughout you can safely use $ to refer to it without any mootools collisions...

Also, it would be a good idea to place
// no direct access
defined('_JEXEC') or die('Restricted access');
int the export.php and maybe even check for a a valid token.

Anyway,l well done! K+1

Best

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
Last Edit: 20 Oct 2012 10:05 by JoomGuy.
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 20 Oct 2012 15:08 #4684

  • VeCrea
  • VeCrea's Avatar
  • Offline
  • Platinum Member
  • Absolute JCook fan
  • Posts: 473
  • Thank you received: 100
  • Karma: 30
audibleid wrote:
One suggestion, if you want to include this across your component, it may be wise to write the jQuery function in a separate js file and simply include it in any/all pages you wish to use it.
audibleid wrote:
RE: appending the current time to the file name, that was only done if the csv files were being output into a web directory, i.e. not downloaded directly from the browser, and was to avoid overwriting the file. I think that this was the overwrite param (true/false) I included in the bare bones function that I wrote if I remember correctly. Just to clarify, in my original bare bones php script, it only uses this parameter if the download param were set to false.
audibleid wrote:
One other thing about jQuery, why not pass jQuery ($) in the function call like;
jQuery(document).ready(function($) {....
}
Then, instead of using jQuery as your selector throughout you can safely use $ to refer to it without any mootools collisions...
audibleid wrote:
Also, it would be a good idea to place
// no direct access
defined('_JEXEC') or die('Restricted access');
int the export.php and maybe even check for a a valid token.

You're right about all this. Was just happy to get it done, as it was a top priority.
Now that it works, it can only be improved.
Thanks for your input on this
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 10 Jan 2013 09:26 #6383

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 68
Fellow cooks,

This is now covered also in the docs thanks to @admin. It's available in French & English here: www.j-cook.pro/docs/tutorials and includes creation of the task button too!

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 18 Jan 2013 22:25 #6473

  • dyoungers
  • dyoungers's Avatar
  • Offline
  • Premium Member
  • Posts: 123
  • Thank you received: 16
  • Karma: 0
Finally got around to adding an export function to my list view and I have the export working but there is one problem and I'm not sure where to start on this.

When I export without an intermediate file, i.e. output = TRUE, after the download completes the export is triggered again when try to change the filter criteria (e.g. to set up for a different download.)

Seems this must be related to the fact that the export code ends by closing the $output and then stops processing with an exit(). My guess is the task is still hanging around somewhere somewhere and the next time the controller is called, it thinks an export is being requested.

Is there a safe way to reset things at some point in the process?

Thanks!
Dave
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 18 Jan 2013 22:32 #6474

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 68
Hi Dave,

Sorry, some questions I'm afraid...

How are you calling it and how is function setup? Does it live in the model and called through controller or are you running from helper?

What code are you basing your function on?

Have you seen the tutorial in the docs section? It covers creating an export task/button: www.j-cook.pro/docs/tutorials

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 18 Jan 2013 23:22 #6475

  • dyoungers
  • dyoungers's Avatar
  • Offline
  • Premium Member
  • Posts: 123
  • Thank you received: 16
  • Karma: 0
sorry should have mentioned it ... following the tutorial for adding a custom task so as you say. The controller has loads the items using the appropriate model and then calls the export function in the helper (which ends with an exit()

I tried removing the exit() but the download never happened ...
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 19 Jan 2013 01:13 #6476

  • dyoungers
  • dyoungers's Avatar
  • Offline
  • Premium Member
  • Posts: 123
  • Thank you received: 16
  • Karma: 0
additional info ...

I have also added code to skip the export if the list is empty and have code in the controller's export function that does this

switch($this->getLayout() .'.'. $this->getTask())
{
case 'signups.export':
$this->applyRedirection($result, array(
'com_volunteersignup.signups.signups',
'com_volunteersignup.signups.signups'
), array(
'cid[]' => null
));
break;
}

If the list is empty, the export function in the helper file is not called, and thus, the exit() isn't executed and everything proceeds through the normal Joomla path. When this happens, I can use the filters without any problem.

In all cases the other tasks in the task bar work as expected, which is what I'd expect since they set the task when they are used.

Dave
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 19 Jan 2013 23:03 #6481

  • dyoungers
  • dyoungers's Avatar
  • Offline
  • Premium Member
  • Posts: 123
  • Thank you received: 16
  • Karma: 0
After digging around a little, I saw that that the javascript function resetFilters and the search button's onclick were calling the form's submit function directly whereas the task buttons were calling Joomla.submitform with a task name.

On a whim, I changed the code in resetFilters and the onclick to call the Joomla function and it now works the way I want it to. This seems like a logical change but if I'm doing something wrong I'd appreciate feedback as to why it's a bad idea :)

Anyway, the change I made was in three places in the list view's filter PHP code, e.g. signups_filters.php in my case.

Two occurrences in the javascript function resetFilters where I changed
document.adminForm.submit() to Joomla.submitform('reset')

and one in the HTML where I changed the search button's onclick from
this.form.submit() to Joomla.submitform('search')

I just made up new task names (reset and search) thinking that they made sense and hoping that nothing would break

Dave
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 19 Jan 2013 23:23 #6482

  • dyoungers
  • dyoungers's Avatar
  • Offline
  • Premium Member
  • Posts: 123
  • Thank you received: 16
  • Karma: 0
oops, forgot to mention that I also changed the getSubmitAction() function in html.php (JDOMHtml class) to this:
function getSubmitAction()
{
	return " Joomla.submitform('submit');";
}

Still testing so I'll see if I broke anything ...
Last Edit: 19 Jan 2013 23:25 by dyoungers.
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 20 Jan 2013 01:03 #6483

  • dyoungers
  • dyoungers's Avatar
  • Offline
  • Premium Member
  • Posts: 123
  • Thank you received: 16
  • Karma: 0
appears to be working but all in all, it seems as if this should be a simple matter of resetting the task somewhere in this framework

enough for a while ... will wait to be enlightened by someone here :)

Dave
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 30 Jan 2013 13:02 #6558

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 68
Hi @dyoungers

Which tutorial are you following, @VeCrea's or the one in the cook tutorial?

G
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 30 Jan 2013 13:23 #6560

  • dyoungers
  • dyoungers's Avatar
  • Offline
  • Premium Member
  • Posts: 123
  • Thank you received: 16
  • Karma: 0
I'm following cook tutorial
The administrator has disabled public write access.

Re: [HOW TO] Export your table to csv / text file 30 Jan 2013 13:50 #6561

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 68
Hi,

Have you tried
$model->setState('context', 'anotherlayout.default');
?

Can you post the rest of this code so I can check it...?dyoungers wrote:
additional info ...

I have also added code to skip the export if the list is empty and have code in the controller's export function that does this

switch($this->getLayout() .'.'. $this->getTask())
{
case 'signups.export':
$this->applyRedirection($result, array(
'com_volunteersignup.signups.signups',
'com_volunteersignup.signups.signups'
), array(
'cid[]' => null
));
break;
}
Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
Last Edit: 30 Jan 2013 13:51 by JoomGuy.
The administrator has disabled public write access.
  • Page:
  • 1
  • 2
Time to create page: 0.134 seconds

Get Started