Export excel

Permalink 1 user found helpful
I want to export some data to excel. Since C5 doesn't have such functionality I'm trying to use PHPExcel library. I uploaded it to libraries folder and loading it with Loader::library('PHPExcel'); But I'm getting this error:

require_once(\updates\concrete5.5.2.1\concrete\config/../helpers/p_h_p_excel__reference.php): failed to open stream: No such file or directory in \updates\concrete5.5.2.1\concrete\libraries\loader.php on line 268
PHP Fatal error: require_once(): Failed opening required '\updates\concrete5.5.2.1\concrete\config/../helpers/p_h_p_excel__reference.php' (include_path='/libraries/3rdparty;\updates\concrete5.5.2.1\concrete\config/../libraries/3rdparty;.;C:\php5\pear') in \updates\concrete5.5.2.1\concrete\libraries\loader.php on line 268

Suggestions?

egnexas
 
mkly replied on at Permalink Reply
mkly
You have to sort out your include directories and include statements in the php excel library.
egnexas replied on at Permalink Reply
egnexas
What do you mean by "sort out"? PHPExcel uses Autoloader, could that be somehow interfering with C5?
mkly replied on at Permalink Reply
mkly
Pretty much that. Typically you go into the library and see how if wants to include it's individual files and adapt as needed. There isn't like a cookie cutter method as it depends on the library.

You can load the autoloader in the on_start method of your package if you are using a package. Or site_process.php is another way. Or you can not use autloader and set your php include paths.

This type of stuff usually requires a pretty decent knowledge of php. There isn't like an automatic way.
jyoung replied on at Permalink Reply
jyoung
@egnexas

Did you ever find a solution to your problem? I'm having the same problem reading an .xlsx file and not sure how to "sort this out" either.

Thanks,
wardog
jordanlev replied on at Permalink Reply
jordanlev
When I need to "export to excel", I just create a CSV file -- Excel opens them just fine (assuming all you need is raw data, not a formatted spreadsheet). I know it's not an option for every situation, but sometimes the simplest solution is best.
jyoung replied on at Permalink Reply
jyoung
What finally worked for me was to include some some libraries when I made the call to the PHPExcel. The .xls was working fine, but was having issues with the .xlsx files. Probably dont need all of these but would rather have too many than not enough ;)

public function loadArray($file_name)
{
Loader::library('PHPExcel');
Loader::library('PHPExcel/Reader/Excel5');
Loader::library('PHPExcel/Reader/Excel2007');
Loader::library('PHPExcel/Reader/Excel2007/Chart');
Loader::library('PHPExcel/Reader/Excel2007/Theme');
Loader::library('PHPExcel/RichText');
Loader::library('PHPExcel/Shared/Escher');
Loader::library('PHPExcel/Worksheet');
Loader::library('PHPExcel/Writer/Excel5');
Loader::library('PHPExcel/Writer/Excel2007');
Loader::library('PHPExcel/Writer/PDF');
Loader::library('PHPExcel/ReferenceHelper');
Loader::model('product/model', 'core_commerce');

$objReader = PHPExcel_IOFactory::load($file_name);
$sheetData = $objReader->getActiveSheet()->toArray(null,true,true,true);

var_dump($sheetData);
}

I realize this is just a snapshot in time but it now works for both .xls and .xlsx files.

Happy Coding!
jordanlev replied on at Permalink Reply
jordanlev
This is really funny -- I ran into this exact same problem, and found this answer on google. Thank you *SO* much for posting your solution here -- this solved my problem as well!

That autoloader thing is tricky... my understanding is that every call to spl_register_autoload adds a new autoload responder to a queue, and I think because C5's spl_register_autoload gets called first (before PHPExcel's), then its autoloader always gets called first -- and C5's autoload will throw an error if it doesn't find the proper library.
So the ideal solution would be a way to tell the autoload queue to use PHPExcel's first, and then fall back to C5's... but I have no clue how to do this.
lackadaize replied on at Permalink Reply
lackadaize
Hi jordanlev. Do you mind telling me your method of exporting to .csv? I'm new to the whole form data export thing altogether and have a client specifically asking for .csv to be automatically exported to an external database.
jordanlev replied on at Permalink Reply
jordanlev
Assuming you've already retrieved your data and put it into an array (actually an array of arrays -- the outer array would be the "rows", and within each "row" array are the "fields"), here's a function that will create the CSV and download it:
function download_csv($data) {
   header('Content-type: text/csv');
   header('Content-Disposition: attachment; filename="contact_form_submissions.csv"');
   //output the column headings
   echo '"First Column Title","Second Column Title", "Etc", "Etc"' . PHP_EOL;
   //output each row of data
   foreach ($data as $row) {
   foreach ($data as $row) {
      foreach ($row as $field) {
         echo '"' . str_replace('"', '""', $field) . '",';
      }
      echo PHP_EOL;
   }
   //halt PHP execution so C5 doesn't output anything else (like the theme HTML, for example)
   exit;


Note that you would need to put this function in the right place -- you can't just plop it into a theme template, for example -- rather it must be a controller action (either a "single page" controller, or a block controller). If you're not sure what that all means, let me know, and tell me where it is exactly you want this download to take place (like, where in your site would a user be clicking the 'download CSV' button?).
mesuva replied on at Permalink Reply
mesuva
I wrote a blog post on this kind of thing the other day - it's more focused on outputting pages and attributes, but the overall approach is the same and it covers creating a single page to do the export:
http://www.mesuva.com.au/blog/concrete5/exporting-concrete5-pages-a...