How to Export data in view to excel?

[recci]

Ok this sounds like a difficult one!

I have a view in back end that shows a table with a bunch of columns
in it. It also has a select box that allows the query in the model to
be tailored to 3 possible options and the results are shown in the
table accordingly.

Id like to be able to add an option for the data displayed in the
table to be exported as an xls or comparable format. This would be the
data that is currently shown after the use of the select box so if
this changes then the new data would be exported ect

How would I go about doing this? I no i would probably have to write
some file and then provide a link to it but im not sure of the process
for that.

I think iv found part of the answer here: http://forum.joomla.org/viewtopic.php?f=304&t=411903

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

[Sam Moffatt]

This looks like it might meet your needs:
http://pear.php.net/package/Spreadsheet_Excel_Writer/
http://devzone.zend.com/article/3336

Alternatively you could generate a CSV which will load in excel fine
as well. You could also output it to the browser with a content
disposition of attachment (as noted in the linked post) which will
force the file to be downloaded even if you are generating it on the
fly.

Newer versions of Excel complain if the file type doesn't quite match
the file extension. I've noticed this with my work's local JIRA
instance that it complains about the files with a yes/no/cancel about
the format but if you hit yes loads perfectly fine. Still trying to
work it out but potentially not user friendly.

Cheers,

Sam Moffatt
http://pasamio.id.au

[Hannes Papenberg]

public function export() {
$db = JFactory::getDBO();
$query = 'SELECT * FROM #__table'
$db->setQuery($query);
$items = $db->loadObjectList();

header('Content-type: application/text');
header('Content-Disposition: attachment; filename="export.csv"');
echo HPCSV::create($items);
}

<?php
/**
* Class to export/import data to CSV
*
* @author Hannes Papenberg
* @license GNU/GPL
*/

// no direct access
defined('_JEXEC') or die('Restricted access');

class HPCSV
{
function create($data)
{
$csv = self::create_csv_file_header($data);
foreach ($data as $key=>$val){
$csv .= self::create_csv_file_row($val);
}
return $csv;
}

function read($string)
{
$rows = explode("\n", $string);
foreach($rows as &$row)
{
$row = explode(',',$row);
}
return $rows;
}

function create_csv_file_header($data)
{
$row = "";
if (count($data)>0){
foreach ($data[0] as $key=>$val)
{
if ($row){
$row .= ',' . $key;
}else{
$row .= $key;
}
}
$row .= "\n";
}
return $row;
}


function create_csv_file_row($row)
{
$res = "";
foreach ($row as $key=>$val)
{
if ($res){
$res .= ',' .'"'. $val.'"';
}else{
$res .= '"'.$val.'"';
}
}
$res .= "\n";

return $res;

[recci]

Thanks guys looks like its not that difficult after all eh lol

Hannes: that looks like its exactly what I was looking for!

Not sure how to use it within my existing view, I already have the
data, I just need to get this to execute when the link is clicked and
generate the file on the fly.

[recci]

So if im correct the best way to do this would be to create another
view pass in the data and just execute the class?

[Keith Blackie]

There is a good writeup on exporting to native xls using PHP ... including
sample code

http://www.appservnetwork.com/modules.php?name=News&file=article&sid=8

The code will need to be tweaked to utilize the Joomla framework

You can put your functions in the model and call them from your view but be
sure to block unauthorized access by filtering users else you will be
opening yourself up to people downloading your data without authorization.

Good luck!

[recci]

Keith: Thanks that also looks fairly simple and maybe what I'm after.
Not sure I understand this 100%:

"You can put your functions in the model and call them from your view
but be
sure to block unauthorized access by filtering users else you will be
opening yourself up to people downloading your data without
authorization."

I may need somebody to walk me through some of this, just sort of
really got my head around MVC and I still get confused easily!

This is my model:

<?php

//Makes sure the code is being executed within Joomla only

defined( '_JEXEC' ) or die( 'Restricted access' );

//Pulls in JModel from the joomla framework
jimport('joomla.application.component.model');

//HonoursModelAll is declared as an extension of JModel
class HonoursModelStudentmanage extends JModel
{
var $_data = null; //Used to cache the results from query
var $_pagination = null;
var $_total = null;
var $_search = null;
var $_query = null;

function getData()
{
$pagination =& $this->getPagination();

if (empty($this->data)) {
$query = $this->buildSearch();
$this->_data = $this->_getList($query, $pagination->limitstart,
$pagination->limit);

}
return $this->_data;
}

function buildSearch()
{
if (!$this->_query) {
$search = $this->getSearch();
$status = $this->getStatus();
//Query Joins data from across 5 tables to produce
results shown in the student managment page
$this->_query = "SELECT s.user_id, s.matric_no, s.course_id,
c.title, u1.name student_name, u1.email student_email, a.supervisor
supervisor_id, u2.name

supervisor_name, a.project_id, a.status, d.title course_title
FROM jos_main_students s
LEFT
JOIN jos_users u1 ON u1.id = s.user_id
LEFT
JOIN jos_main_allocations a ON a.student_id = u1.id
LEFT
JOIN jos_main_course c ON c.id = s.course_id
LEFT
JOIN jos_users u2 ON u2.id = a.supervisor
LEFT
JOIN jos_main_projects d ON d.id = a.project_id";

if ($search != '') {

$search = $this->_db->getEscaped( $search, true );

$this->_query .= " WHERE (u1.name LIKE '%{$search}%') OR
(u1.email LIKE '%{$search}%') OR (u2.name LIKE '%{$search}%') OR
(c.title LIKE '%{$search}%') ";
}

if ($status == 2) {


$this->_query .= " WHERE a.status='Not Allocated' OR a.status IS
NULL";
}
else if ($status == 3) {

$this->_query .= " WHERE a.status='Allocated'";
}
}

return $this->_query;
}

function getTotal()
{
if (!$this->_total) {
$query = $this->buildSearch();
$this->_total = $this->_getListCount($query);
}

return $this->_total;
}

function &getPagination() {
if(!$this->_pagination) {
jimport('joomla.html.pagination');
global $mainframe;
$this->_pagination = new JPagination($this->getTotal(),
JRequest::getVar('limitstart', 0), JRequest::getVar('limit',
$mainframe->getCfg('list_limit')));
}

return $this->_pagination;
}

function getSearch()
{
if (!$this->_search) {
global $mainframe, $option;

$search = $mainframe->getUserStateFromRequest( "$option.search",
'search', '', 'string' );
$this->_search = JString::strtolower($search);
}

return $this->_search;
}

function getStatus()
{
if (!$this->_status) {
global $mainframe, $option;

$status = $mainframe-
>getUserStateFromRequest( "$option.get_status", 'get_status', '',
'string' );
$this->_status = JString::strtolower($status);
}

return $this->_status;
}
}


The view:

<?php
//Makes sure the code is being executed within Joomla only

defined( '_JEXEC' ) or die( 'Restricted access' );

//Pulls in JView from the joomla framework
jimport( 'joomla.application.component.view');
//HonoursViewAll is declared as an extension of the JView class
class HonoursViewStudentmanage extends JView
{
function display($tpl = null)
{
//get() looks for the model that shares the same name as the this
view "all"
$rows =& $this->get('data'); //results are returned
$pagination =& $this->get('pagination');//get pagination info
$search = $this->get('search');//gets serch term
$status = $this->get('status');//gets status term in order to set
select box value to last selected option.
$this->assignRef('rows', $rows);// results assigned to the view
$this->assignRef('pagination', $pagination); //pagination info
assigned to view
$this->assign('search', $search);//search term assinged to view in
order to set search box to term last searched for

//create status select box array
$select_status = array(
array('value' => '1', 'text' => 'All'),
array('value' => '2', 'text' => 'Not Allocated'),
array('value' => '3', 'text' => 'Allocated')
);

//assign status array to generic select box
$select = JHTML::_('select.genericList', $select_status,
'get_status', 'class="inputbox" '. '', 'value', 'text', $status );

//assign status select box to the view
$this->assign('select', $select);

parent::display($tpl);//display() calls the output template
}
}

And the template:

<?php
//Makes sure the code is being executed within Joomla only

defined( '_JEXEC' ) or die( 'Restricted access' );

//Sets up toolbars and title in backend administration
JToolBarHelper::title( JText::_( 'Student Management' ),
'generic.png' );
JToolBarHelper::editList();
dump($this->rows);

//this is the backend form for displaying the list of avaible projects
?>

<form action="index.php" method="post" name="adminForm">
<table>
<tr>
<td>
Select Status:
<?php echo $this->select ?>
</td>
<td align="right">
Search:
<input type="text" name="search" value="<?php echo $this->search ?>"
id="search" />
<button type="submit">Go</button>
</td>
<td style="color:#F00; font-style:italic;">
<< Search by Name, Email, Course or Supervisor!
</td>
</tr>
</table>
<table class="adminlist">
<thead>
<tr>
<th width="3%">

</th>
<th width="5%">User_id</th>
<th width="10%">Name</th>
<th width="10%">Matric_no</th>
<th width="10%">Email</th>
<th width="20%">Course</th>
<th width="10%">Supervisor</th>
<th width="20%">Project_id</th>
<th width="7%">Status</th>

</tr>
</thead>
<?php
//Loop through the rows and display the results (includes a link to
each row via id)
jimport('joomla.filter.output');//used to filter ampersands
$k = 0;
for ($i=0, $n=count( $this->rows ); $i < $n; $i++)
{
$row = &$this->rows[$i];
$checked = JHTML::_('grid.id', $i, $row->user_id );
$published = JHTML::_('grid.published', $row, $i );
//$link = 'index.php?option=com_honours&id=' . $row->user_id .
'&view=allocate';
$link = JFilterOutput::ampReplace( 'index.php?option=' . $option .
'&controller=studentmanage&task=edit&cid[]='. $row->user_id );
?>
<tr class="<?php echo "row$k"; ?>">
<td>
<?php echo $checked; ?>
</td>
<td align="center">

<?php echo '<a href="' . $link . '">'. $row->user_id . '</a>'; ?>
</td>
<td>

<?php echo '<a href="' . $link . '">'. $row->student_name. '</
a>'; ?>
</td>
<td align="center">

<?php echo $row->matric_no; ?>
</td>
<td align="left">
<?php echo $row->student_email; ?>
</td>
<td align="left" >
<?php echo $row->title; ?>
</td>
<td >
<?php
if($row->supervisor_name == null)
{
echo 'N/A';
}
else
{
echo $row->supervisor_name;
}

?>
</td>
<td >
<?php
if($row->course_title == null)
{
echo 'N/A';
}
else
{
echo $row->course_title;
}

?>
</td>
<td align="center" >
<?php
$status = $row->status;
if ($status ==null)
{
$status = 'Not Allocated';
}
echo $status;
?>
</td>
</tr>
<?php
$k = 1 - $k;
}
?><tfoot>
<tr>
<td colspan="9"><?php echo $this->pagination->getListFooter(); ?></td>
</tr>
</tfoot>
</table>
<?php echo JHTML::_( 'form.token' );//send token to ensure legit
request ?>
<input type="hidden" name="option" value="<?php echo $option;?>" />
<input type="hidden" name="task" value="" />
<input type="hidden" name="controller" value="studentmanage" />
<input type="hidden" name="boxchecked" value="0" />
</form>

I need to be able to generate an csv or xls file of whatever is
displayed currently by this view.

[Keith Blackie]

One of the issues with exposing functions is that casual hacker can enter
command line requests and cause the page to generate something the original
writer didn't expect .. i.e. a front end download of data .. to combat this,
simply check to see if the person is authorized to access that function. A
simple check of user permissions should be sufficient i.e. .. you can do
this pretty much wherever you want to add that layer of restricted access to
a function:

// prevent unauthorized users from accessing this function
$user=& JFactory::getUser();
// if not logged in, raise an error and return
if ($user->get('guest')){
JError::raiseError(403, JText::_('Access Forbidden'));
return;
}
// if logged in and less than Super Admin raise warning and return
if ($user->get('gid') < 25 ) {
JResponse::setHeader('HTTP/1.0 403', true);
JError::raiseWarning( 403, JText::_('ALERTNOTAUTH') );
return;

}



----- Original Message -----
From: "recci" <radioa...@hotmail.com>
To: "Joomla! General Development" <joomla-de...@googlegroups.com>
Sent: Thursday, April 29, 2010 12:12 PM
Subject: Re: How to Export data in view to excel?

[recci]

ok i get that but what Im struggling with is how to incorporate this
into my model/view above and have it generate a downloadable file.

> ...
>
> read more »

[Keith Blackie]

put the export function into the controller and pass the vars to the model
in your link so it calls the export function

here is a CSV export framework to get you started

<?php
function export() {
//get your model, where you defined your function to read the data
$model = $this->getModel('yourmodel');
//get the data from function you wrote to grab it
$items = $model->getData();
//current document
$document = &JFactory::getDocument();
$doc = &JDocument::getInstance('text');
$document = $doc;
//download file headers
header("Expires: Sun, 1 Jan 2000 12:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . "GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header( "Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=exported_data_" .
date("Ymd").".csv");
$data = "";
/* write code here to parse your data into rows to write
*
*
*
*
*/
// write the data to the document
echo $data." \n";
return;
}
?>

[Ian MacLennan]

A note/hint here - you aren't actually writing a file to the file system.  The link that the user clicks on returns the data in CSV format.

Ian

[recci]

Ok I have my function in the controller. But how exactly do you pass

the vars to the model

in your link so it calls the export function?

I can pass the vars to the export function directly in the link no
problem but that would be a lot of vars to pass in a url!

The only way I know how to pass vars to the model is by submitting the
admin form and via post and the model seems to pick it up
automatically and return the right data for the view.

[Keith Blackie]

It depends upon how you build your component, but this is how it is done on
many:

/index.php?option=com_mycomponent&controller=mycontroller&task=export

If you put this form on the page link will be built automatically for your
button provided you include joomla.javascript.js in the document

<form action="index.php" method="post" name="adminForm"

<input type="hidden" name="option" value="com_mycomponent" />
<input type="hidden" name="view" value="myview" />
<input type="hidden" name="layout" value="mylayout" />
<input type="hidden" name="controller" value="mycontroller" />

<input type="hidden" name="task" value="" />

<input type="button" name="export" value="Export"
onclick="javascript:submitbutton('export')"/>
</form>

[recci]

Thats what i have but i dont see how this this passing anything to the
model? I need to pass two vars to the model that would normaly be set
by a input box and a select.

I tried it this way:

$xlink = JFilterOutput::ampReplace( 'index.php?option=' . $option .
'&controller=studentmanage&task=export&search='. $this-
>search .'&status ='. $this->status);

This is what I have so far but all it returns is an error

In the controller I have tried to use Hannes's csv class by including
it but its not working:

function export() {
//get your model, where you defined your function to read the data

$model = $this->getModel('studentmanage');

//get the data from function you wrote to grab it
$items = $model->getData();

dump($items);

include ("hpcsv.php");

header('Content-type: application/text');
header('Content-Disposition: attachment;
filename="export.csv"');

echo HPCSV::create($items);


}

[Keith Blackie]

the error information might be helpful

----- Original Message -----
From: "recci" <radioa...@hotmail.com>
To: "Joomla! General Development" <joomla-de...@googlegroups.com>
Sent: Thursday, April 29, 2010 4:34 PM
Subject: Re: How to Export data in view to excel?

[recci]

it just says: Firefox can't find the file at
http://localhost:85/Honours_project/administrator/index.php?option=com_honours&controller=studentmanage&task=export.

and an internal server error: 500 via firebug.

It trys to save an empty file if i comment out echo
HPCSV::create($items); so the error must lie with this file.

> For more options, visit this group athttp://groups.google.com/group/joomla-dev-general?hl=en-GB.

[Keith Blackie]

have you verified the naming convention of your files to make sure they are
in the proper place?

Calling this from the administrative panel means the com_honours folder must
exist in the administrator/components path

You should have:

administrator/components/com_honours/controller.php <---this is a basic
controller don't need much in here if you are overriding it
administrator/components/com_honours/controllers/studentmanage.php <--- this
is where your export code resides
administrator/components/com_honours/models/(your models go here) <--- this
is the model that is where your class functions go
administrator/components/com_honours/views/(your views go here) <--- your
views i.e. view.html.php
administrator/components/com_honours/views/tmpl <--- other required php
files to build the page

[recci]

Yes the files are all in the proper place. I have made some progress
on this by using code i found in the joomla.org forums in the link of
my first post.

Its now exporting an xls file yay!! although the output file is a bit
messy. I still dont understand fully how the model is getting the
vars but its working.

Anybody no how i would add column headers output of the function
below? Im gonna have to try and pick this apart.

my export function now looks like this :

function export() {
//get your model, where you defined your function to read the data
$model = $this->getModel('studentmanage');

//get the data from function you wrote to grab it

$rows = $model->getData();

dump($rows);

## 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();

}


On Apr 29, 9:58 pm, "Keith Blackie" <kblac...@resourcecad.com> wrote:
> have you verified the naming convention of your files to make sure they are
> in the proper place?
>
> Calling this from the administrative panel means the com_honours folder must
> exist in the administrator/components path
>
> You should have:
>
> administrator/components/com_honours/controller.php <---this is a basic
> controller don't need much in here if you are overriding it
> administrator/components/com_honours/controllers/studentmanage.php <--- this
> is where your export code resides
> administrator/components/com_honours/models/(your models go here) <--- this
> is the model that is where your class functions go
> administrator/components/com_honours/views/(your views go here) <--- your
> views i.e. view.html.php
> administrator/components/com_honours/views/tmpl <--- other required php
> files to build the page
>
> ----- Original Message -----
> From: "recci" <radioactiv...@hotmail.com>
> To: "Joomla! General Development" <joomla-de...@googlegroups.com>
> Sent: Thursday, April 29, 2010 4:43 PM
> Subject: Re: How to Export data in view to excel?
>

> it just says: Firefox can't find the file athttp://localhost:85/Honours_project/administrator/index.php?option=co....

[Keith Blackie]

array_keys($rows[0]) is presumably returning the column headers, but without
verifying the code, I can't tell you. Perhaps your getData function does not
return the column heads with the data.

[recci]

Yes you are correct that it is array_keys($rows[0]) returning the
column headers but in his original code I had figured that out
but he is using $db->loadAssocList()); in his original code and not
getting his data from the model.

so he will be getting an array of associative arrays but im getting an
array of stdClass objects so there is another level and therefore my
column headers are blank

anyway to get the data back from the model as if i was using db-
>loadAssocList()?

[Keith Blackie]

Perhaps you should incorporate a function in the model to retrieve the
headers, then call it along with the getData method

[recci]

How? Could I not just iterate through and do it that way?

[recci]

Its cool I ended up just using a seperate query in the exoprt function
rather than use the model. This allows me to get slighlty different
info as requried without to much hassle.

thanks for the help

> ...
>
> read more »