Joomla Excel Document Types using PHPExcel

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.
big-pete
Joomla! Apprentice
Joomla! Apprentice
Posts: 7
Joined: Tue Feb 17, 2009 8:13 pm

Joomla Excel Document Types using PHPExcel

Postby big-pete » Sat Aug 22, 2009 2:37 am

I recently needed to allow my users to download data in an Excel format. Now CVS works fine with Excel and is simple to implement but I wanted the formatting advantage that Excel offered. I also wanted to work within the Joomla 1.5 MVC paradigm to make things more or less standard. After searching around for PHP Excel classes I found PHPExcel which is a pretty decent product and is free to boot. Now I just needed to shoehorn it into Joomla. My approach was as follows:

  • Downloaded, extracted and copied the PHPExcel 'Classes' folder to an new folder under <Joomla Root>\libraries called phpexcel. So after coping the PHPExcel 'Classes' folder the <Joomla Root>\libraries folder has the following: <Joomla Root>\libraries\phpexcel\Classes
  • Created 4 new document types (one detailed below) in the <Joomla Root>\libraries\joomla\document folder to allow the PHPExcel class to handle creation of data downloads for XLS, XLSX, CVS and PDF file types.
  • Created the necessary Views to handle each new document type where appropriate in my component (this is the key more or less).

To demonstrate one of the document types created in the <Joomla Root>\libraries\joomla\document folder here is the one for XLS (for Excel versions < 2007 although they work for 2007 as well). I created a <Joomla Root>\libraries\joomla\document\xls folder and inside that I place a file called 'xls.php' containing the follow code:

Code: Select all

<?php
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();

//Include Joomla Classes
JLoader::import('joomla.filesystem.folder');

//Include PHPExcel classes
JLoader::import('phpexcel.Classes.PHPExcel');
JLoader::import('phpexcel.Classes.PHPExcel.IOFactory');

class JDocumentXLS extends JDocument
{
  private $_name = 'export';
  private $_phpexcel = null;
  /*------------------------------------------------------------------------------------------------------------
  Method Name : __construct
  Purpose     : Class Constructor
  Parameter   : $options
  Returns     : None
  Revisions   :
  ------------------------------------------------------------------------------------------------------------*/
  function __construct($options = array())
  { parent::__construct($options);
    $this->setMimeEncoding('application/vnd.ms-excel');
    $this->setType('xls');
    if(isset($options['name'])) $this->setName($options['name']);
  }
   /*------------------------------------------------------------------------------------------------------------
  Method Name : getName
  Purpose     : To get the current filename for the excel file
  Parameter   : None
  Returns     : String
  Revisions   :
  ------------------------------------------------------------------------------------------------------------*/
  function getName()
  { return $this->_name;
  }
/*------------------------------------------------------------------------------------------------------------
  Method Name : getPhpExcelObj
  Purpose     : To get the PHPExcel object for use
  Parameter   : None
  Returns     : Object
  Revisions   :
  ------------------------------------------------------------------------------------------------------------*/
  function &getPhpExcelObj()
  { if(!$this->_phpexcel) $this->_phpexcel = new PHPExcel();
    return $this->_phpexcel;
  }
/*------------------------------------------------------------------------------------------------------------
  Method Name : render
  Purpose     : To render the document
  Parameter   : @param boolean  $cache    If true, cache the output
  Parameter   : @param array   $params   Associative array of attributes
  Returns     : String
  Revisions   :
  ------------------------------------------------------------------------------------------------------------*/
  function render($cache = false, $params = array())
  { //Write out response headers
    JResponse::setHeader('Pragma', 'public', true);
    JResponse::setHeader('Expires', '0', true);
    JResponse::setHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0', true);
    JResponse::setHeader('Content-Type', 'application/force-download', true);
    JResponse::setHeader('Content-Type', 'application/octet-stream', true);
    JResponse::setHeader('Content-Type', 'application/download', true);
    JResponse::setHeader('Content-Transfer-Encoding', 'binary', true);
    //Set workbook properties to some defaults if not currently set
    //Currently all these properties are not set for the Excel5 (xls) writer but are here in case future versions do
    $objPhpExcel =& $this->getPhpExcelObj();
    $config = new JConfig();
    $workbook_properties = $objPhpExcel->getProperties();
    if(!$workbook_properties->getCategory()) $workbook_properties->setCategory('Exported Report From '. $config->sitename);
    if($workbook_properties->getCompany() == 'Microsoft Corporation' && $config->sitename) $workbook_properties->setCompany($config->sitename);
    if($workbook_properties->getCreator() == 'Unknown Creator' && $config->sitename) $workbook_properties->setCreator($config->sitename);
    if(!$workbook_properties->getDescription()) $workbook_properties->setDescription($this->getDescription());
    if(!$workbook_properties->getLastModifiedBy()) $workbook_properties->setLastModifiedBy($config->sitename);
    if(!$workbook_properties->getSubject()) $workbook_properties->setSubject($this->getTitle());
    if($workbook_properties->getTitle() == 'Untitled Spreadsheet' && $this->getTitle()) $workbook_properties->setTitle($this->getTitle());
    $objPhpExcel->setProperties($workbook_properties);
    //Get the Excel 5 type IO object to write out the binary document
    $objWriter = PHPExcel_IOFactory::createWriter($objPhpExcel, 'Excel5');
    if(JFolder::exists($config->tmp_path)) $objWriter->setTempDir($config->tmp_path);
    //Save the file to the PHP Output Stream and read the stream back in to set the buffer
    ob_start();
    $objWriter->save('php://output');
    $buffer = ob_get_contents();
    ob_end_clean();
    JResponse::setHeader('Content-disposition', 'attachment; filename="' . $this->getName() . '.' . $this->getType() . '"; size=' . strlen($buffer) . ';', true);
    return $buffer;
  }
/*------------------------------------------------------------------------------------------------------------
  Method Name : setPhpExcelObj
  Purpose     : To set the PHPExcel object for use
  Parameter   : $objPhpExcel
  Returns     : None
  Revisions   :
  ------------------------------------------------------------------------------------------------------------*/
  function setPhpExcelObj($objPhpExcel)
  { $this->_phpexcel = $objPhpExcel;
  }
   /*------------------------------------------------------------------------------------------------------------
  Method Name : setName
  Purpose     : To set the current filename for the excel file
  Parameter   : $name
  Returns     : None
  Revisions   :
  ------------------------------------------------------------------------------------------------------------*/
  function setName($name)
  { $this->_name = JFilterOutput::stringURLSafe($name);
  }
}
OK, so now I needed a View to use with my new xls document type. I won't go into the MVC model but in one of my components View/<View Folder Name> folders I created a view.xls.php file with the following code:

Code: Select all

<?php
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();

//Import needed Joomla classes
jimport('joomla.application.component.view');
jimport('joomla.utilities.date');
jimport('joomla.filesystem.file');

class ViewUsers extends JView
{   /*------------------------------------------------------------------------------------------------------------
  Method Name : __construct
  Purpose     : Class Constructor
  Parameter   : None
  Returns     : None
  Revisions   :  ------------------------------------------------------------------------------------------------------------*/
  function __construct()
  { parent::__construct();
  }
  /*------------------------------------------------------------------------------------------------------------
  Method Name : display
  Purpose     : To display the page
  Parameter   : $tpl - template to use
  Returns     : None
  Revisions   :
  ------------------------------------------------------------------------------------------------------------*/
   function display($tpl = null)
   { global $mainframe;
    //Get the user requesting this view
    $user = JFactory::getUser();
     //Get and set the document properties
    $document = &JFactory::getDocument();
    $date = new JDate();
    $download_desc = JText::sprintf('DOWNLOAD DESC', JText::_('USERS'), $user->name, $date->toFormat('%Y-%m-%d'), $user->name);
    $document->setDescription($download_desc);
    $document->setName(JText::_('USERS') . '-' . $date->toFormat('%Y-%m-%d'));
    $download_title = JText::sprintf('DOWNLOAD TITLE', JText::_('USERS'), $user->name);
    $document->setTitle($download_title);
    //Get the PHPExcel object to set some properties
    $objPhpExcel =& $document->getPhpExcelObj();
    $objPhpExcel->getProperties()->setCreator($user->name)->setLastModifiedBy($user->name);
    $objPhpExcel->setActiveSheetIndex(0);
    $objPhpExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C' . $download_desc);
    $objPhpExcel->getActiveSheet()->setTitle(JText::_('USERS'));
    //Assign the data from the model to the view template - getData(false) in this model gets all rows ignoring pagination otherwise getData would return only the current data page on the screen
    $this->assignRef('rows', $this->getModel()->getData(false));
    //Assign the PHPExcel object for use in the template
    $this->assignRef('phpexcel', $objPhpExcel);
    //I use the 'excel' template in the <View Folder Name>/tmpl folder for a variety of document types to provide consistency between those document types (xls, xlsx, cvs, pdf) generated by PHPExcel but you could use 'xls' here if you preferred instead to create a specific XLS template.
    if(is_null($tpl)) $tpl = 'excel';
    //Display the results
    parent::display($tpl);
  }
}
So the final piece was to create an excel template in the <View Folder Name>/tmpl folder. So I created an file called default_excel.php in the <View Folder Name>/tmpl folder with the following code:

Code: Select all

<?php defined('_JEXEC') or die('Restricted access'); ?>
<?php
  $objPhpExcel = $this->phpexcel;
  //Setup header row
  $column_index = 0;
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('USERS.USERNAME'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('LMS_GROUP_USERS.FIRST_NAME'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('LMS_GROUP_USERS.LAST_NAME'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.CON_POSITION'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.ADDRESS'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.SUBURB'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.STATE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.COUNTRY'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.POSTCODE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.TELEPHONE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.FAX'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.WEBPAGE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('CONTACT_DETAILS.MOBILE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('USERS.EMAIL'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('USERS.REGISTERDATE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('USERS.LASTVISITDATE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('ACL ROLE'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('GROUP'));
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, 1, JText::_('ENABLED'));
?>
<?php
  for ($i = 0, $n = count($this->rows); $i < $n; $i++)
  { $row = &$this->rows[$i];
    $row->registerDate = ($datetimestamp = strtotime($row->registerDate)) ? date('Y-m-d', $datetimestamp) : '';
    $user_enabled = ($row->block) ? JText::_('No') : JText::_('Yes');
?>
<?php
  //Write out data rows
  $column_index = 0;
  $row_index = $i + 2;
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->username);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->first_name);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->last_name);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->con_position);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->address);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->suburb);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->state);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->country);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->postcode);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->telephone);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->fax);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->webpage);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->mobile);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->email);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->registerDate);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->lastvisitDate);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->com_acl_role_name);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $row->groupname);
  $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index++, $row_index, $user_enabled);
?>
<?php } ?>
<?php
  //Set autosizes on the columns
  for($i = 0; $i < $column_index; $i++) $objPhpExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setAutoSize(true);
  //Calulate the column widths to attempt to autosize them
  $objPhpExcel->getActiveSheet()->calculateColumnWidths();
?>
This last template part is arbitrary as your data will be different but I thought it would be useful to provide an example. Basically you'll have to learn the PHPExcel object model (its requirements & quirks too) to take full advantage of this but it is quite flexible.

I have to say Joomla is pretty amazing it its ability to be easily extended and providing Excel data downloads is something most customers have come to expect from modern data applications. Hope this helps some of you out there. Good luck!

User avatar
pe7er
Joomla! Master
Joomla! Master
Posts: 21848
Joined: Thu Aug 18, 2005 8:55 pm
Location: Nijmegen, The Netherlands
Contact:

Re: Joomla Excel Document Types using PHPExcel

Postby pe7er » Sat Aug 22, 2009 12:44 pm

PHPExcel is IMHO indeed an excellent OOP library to create Excel files.
I have used it in many applications, but only for administrator use in Joomla's back-end.

One note: I ran into problems when I tried to export huge amounts of SQL data to an Excel file.
I tried both PHP (to screen, so your browser asks you to save/open excel) & File (directly on the server) as output formats, but got blank files/ or no files at all.
Exporting 2500 rows with 15 columns seemed to much for my server.
At the PHPExcel pages I read that you'll need about 10kb PHP memory per cell...
Kind Regards,
Peter Martin, Global Moderator
https://db8.nl - Joomla specialist, Nijmegen, Nederland
Developer of Options Manager Lite https://db8.eu/download/file/options-manager-lite

big-pete
Joomla! Apprentice
Joomla! Apprentice
Posts: 7
Joined: Tue Feb 17, 2009 8:13 pm

Re: Joomla Excel Document Types using PHPExcel

Postby big-pete » Sat Aug 22, 2009 1:10 pm

pe7er wrote:One note: I ran into problems when I tried to export huge amounts of SQL data to an Excel file. I tried both PHP (to screen, so your browser asks you to save/open excel) & File (directly on the server) as output formats, but got blank files/ or no files at all. Exporting 2500 rows with 15 columns seemed to much for my server. At the PHPExcel pages I read that you'll need about 10kb PHP memory per cell...
Peter, thanks for the heads up. It seems they are trying to address this memory issue and have made some progress (although more work needs to be done). Also, it seems when writing out as a PDF that TCPDF gobbles up a lot of memory. While my application currently only has modest amounts of test data I'm leaning toward disabling PDF data output until if and when a better PDF solution is found (Not to mention that formatting a PDF with many data columns to fit across one page is a pain >:().

verdy
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Thu Apr 09, 2009 10:05 am

Re: Joomla Excel Document Types using PHPExcel

Postby verdy » Tue Sep 22, 2009 3:39 pm

Hello
My English is not good but I go tried. :)

I integrated(joined) PHPExcel into my code as kept silent indicated it, but I have a small or big problem :( : when I extract my data, the obtained file is not good; when I want to open it ,
it gives me this message:
The format of the file which you try to open < < monFichier.xls > > is different from that specify by the extension of the file

Somebody has an idea of the fact what that can indeed be?

Please help me ! this probleme make me busy :-[

User avatar
sderrico
Joomla! Apprentice
Joomla! Apprentice
Posts: 24
Joined: Sat Dec 26, 2009 4:01 pm
Location: Montreal, Canada
Contact:

Re: Joomla Excel Document Types using PHPExcel

Postby sderrico » Fri Apr 23, 2010 9:11 pm

This post is fantastic!
Thank you Peter!
Sebastien D'Errico
IT Development Consultant
sebastien@hollox.net
(438) 882-8687

adigitchile
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Wed Jun 16, 2010 10:22 pm

Re: Joomla Excel Document Types using PHPExcel

Postby adigitchile » Wed Jun 16, 2010 10:29 pm

I great post.

I'm developing a module that read an xlsx file with the PHPExcel_Reader_Excel2007 class but it seems that joomla in some way is interfiring with phpexcel code. The result is that phpExcel is not reading the worksheets at all. If I try the code outside joomla it works perfect.

Here's the code:

Code: Select all

$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setLoadSheetsOnly( array("Input") );
$objPHPExcel = $objReader->load($file);
echo $objPHPExcel->getSheetCount(); 


This prints 0.
$file is a variable that exists containing the path to the excel file, and off course 'Input' sheet exists .

I have another test working with PHPExcel_IOFactory::load($path); Is that maybe Excel2007 is not working on Joomla?

Has anybody solve this kind of problem?

adigitchile
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Wed Jun 16, 2010 10:22 pm

Re: Joomla Excel Document Types using PHPExcel

Postby adigitchile » Wed Jun 16, 2010 11:09 pm

I will answer myself:

The solution: It seems that in recent PHPExcel versions there is an error in PHPExcel/Shared/File.php line 78.

Comment the line and then it will work:

//$returnValue = realpath($pFilename);

Thanks to a post of joelrsimpson at phpexcel.codeplex.com

korkunov
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Fri Jan 21, 2011 9:01 am

Re: Joomla Excel Document Types using PHPExcel

Postby korkunov » Sun Jan 30, 2011 11:21 pm

Hi. I am a beginner and bad know MVC.
Plz, explain me in what directories to place 2 (view.xls.php) and 3 (default_excel.php) files.
Thanks.

User avatar
sderrico
Joomla! Apprentice
Joomla! Apprentice
Posts: 24
Joined: Sat Dec 26, 2009 4:01 pm
Location: Montreal, Canada
Contact:

Re: Joomla Excel Document Types using PHPExcel

Postby sderrico » Mon Jan 31, 2011 2:39 am

Ho Ho Korkunov!

Here an example of what it may look a component called "shopper" on the administration side:

/joomla/administrator/components/com_shopper/shopper.php
/joomla/administrator/components/com_shopper/controllers/cart.php
/joomla/administrator/components/com_shopper/models/cart.php
/joomla/administrator/components/com_shopper/tables/cart.php
/joomla/administrator/components/com_shopper/views/customer/view.html.php
/joomla/administrator/components/com_shopper/views/customer/tmpl/display.php
/joomla/administrator/components/com_shopper/views/customer/tmpl/edit.php
/joomla/administrator/components/com_shopper/views/manager/view.html.php
/joomla/administrator/components/com_shopper/views/manager/tmpl/display.php
/joomla/administrator/components/com_shopper/views/manager/tmpl/edit.php

About your question, those two files should be:
/joomla/administrator/components/com_shopper/views/manager/view.xls.php
/joomla/administrator/components/com_shopper/views/manager/tmpl/default_excel.php

Also, a friendly warning, do not use any capital letter in file, it can generate a lot of trouble :)

Good luck!
Sébastien
Sebastien D'Errico
IT Development Consultant
sebastien@hollox.net
(438) 882-8687

seeb
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Fri Dec 21, 2007 2:57 am

Re: Joomla Excel Document Types using PHPExcel

Postby seeb » Thu Jun 16, 2011 5:59 pm

You can give the address url of component?

TNX. Sebastian


Return to “Joomla! 1.5 Coding”

Who is online

Users browsing this forum: No registered users and 5 guests