Forums

Hi,

I'm wondering if you could show me how to attach the results of a form submission as an excel file (or .csv). This way the excel file can automatically go to say, a co-worker or back to the user, without having to give them access to the backend of my website. Thanks!

Nick

Hi nicohenry,

There have been a couple of threads on this in the last ten days or so. Though not particularly about Excel or CSV files. If you can create the file, these threads will tell you how to attaceh it to an eamil.

Bob

I know how to export the file from the backend in Joomla, but I'm doubting that's what you're referring to. As far as writing some sort of code in to make that happen, I wouldn't know how to do that off the top of my head. Can you provide any help on that?

Also, is there any way you could provide a link to these other threads you are referring to? I have done extensive searching on this topic and haven't been able to find anything that's super useful on this topic. Any help is much appreciated!

Nick

Hi Nick,

Sorry, I'd be starting from scratch in doing that. Tell us a bit about your need here - it seeems odd to send an Excel file with a single form result in it. What's the recipient going to do with it?

Try an advanced search on 'attachment' looking at postings in the last week. There was one this morning reporting suceces in this.

Bob

Hi Nick,

First, this thread will show how to attach any file to the email :
http://www.chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=2&t=14336

so our objective now is to create this CSV file and place it some where at the server, I need to know if you need the CSV to have all records or only the last record ?

another approach is to send a link to your co-worker to grab the latest CSV data through another form, here is how to make another form grab all the data from some table to a CSV file :
http://www.chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=5&t=12071#p20244

Regards
Max

Hi, thanks for the help both of you.

To answer your questions, I'm hoping to get the last record in excel form and have it dynamically emailed as an attachment to various co-workers. We'll get about 10-20 submissions that need to be batched together into one excel file, which I figure is manageable. My co-workers can either add the info as the emails flow in, or maybe use a merge option within excel to combine the records.

The other suggestion about emailing the link might also be useful, though. I tried inserting that code in a new form and all, clicked the link, and a .csv did in fact open. All of the column headers correspond to my form fields, but there is no other information in there (i.e., the actual information that was submitted). Any ideas?

Thanks!

Nick

Hi Nick,

Good, lets go with the 2nd solution then, do you have a table connected to your original form and has some previously stored data ?

Regards
Max

Hi Max,

Yes I have a table connected to the form. When a submission is made, a new record is created and it properly stores all of the data that I set it up to store.

Nick

Hi Nick,

show me the code you used in the CSV export form then!

Regards
Max

Hi Max,

The name of the form and table is BLXRegistration. The code I added into the CSV form is:

<?php
global $mainframe;
   $database =& JFactory::getDBO();

   include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
   //echo $_POST['formid'];
   /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
   $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
   $formname = $database->loadResult();*/
   
   $tablename = 'jos_chronoforms_BLXRegistration';
   $tables = array( $tablename );
   $result = $database->getTableFields( $tables );
   $table_fields = array_keys($result[$tablename]);
   
   $database->setQuery( "SELECT * FROM ".$BLXRegistration."");
   $datarows = $database->loadObjectList();
   
   $titcol = 0;
   foreach($table_fields as $table_field){
      if($titcol){$csvline .=",";}
      $csvline .= $table_field;
      $titcol++;
   }
   $csvline .="\n";
         
   $datacol = 0;
   $rowcount = 1;
   foreach($datarows as $datarow){
      foreach($table_fields as $table_field){
         if($datacol){$csvline .=",";}
         $csvline .= '"'.addslashes($datarow->$table_field).'"';
         $datacol++;
      }
      $csvline .="\n";
      $datacol = 0;
      $rowcount++;
   }
   
   if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "Opera";
   }
   elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "IE";
   } else {
      $UserBrowser = '';
   }
   $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
   @ob_end_clean();
   ob_start();

   header('Content-Type: ' . $mime_type);
   header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

   if ($UserBrowser == 'IE') {
      header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      header('Pragma: public');
   }
   else {
      header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Pragma: no-cache');
   }
   print $csvline;
   exit();
?>

I would like to add a link to the email that goes out so that, upon clicking on the link, a CSV for that particular record is downloaded. That won't necessarily be the LAST record though, so I'm thinking that might be difficult or impossible.
If that's too much, I'd be happy with a link that downloads a CSV of all the records. Thanks!

Nick

Hi Nick,

Ok, please try this code:

    <?php
    global $mainframe;
       $database =& JFactory::getDBO();

      
       
       $tablename = 'jos_chronoforms_BLXRegistration';
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       
       $database->setQuery( "SELECT * FROM ".$tablename."");
       $datarows = $database->loadObjectList();
       
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=",";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";


             
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .=",";}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }
       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
    ?>

it may not be possible to get the last record only because we are not sure if the CSV will be generated once the record is saved, so the last record may not be really the "last" one, I wish you understand my last statement!🙂

Regards
Max