Forums

Hi lads,

I have done plenty of reading this morning but I am at a loss about what my issue is here.

Stripped it right down:
- Form Code is just a button.
- On Submit Code After is:

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

               include_once JPATH_BASE.DS.'/administrator/components/com_chronocontact/excelwriter/'."Writer.php";
               //echo $_POST['formid'];
               //$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
               $database->setQuery( "SELECT Resource, ResourceName, ResourceGroup FROM jos_chronoforms_RACIRoles" );
               $formname = "anyform";//$database->loadResult();
               
               $tablename = "jos_chronoforms_RACIRoles";
               $tables = array( $tablename );
               $result = $database->getTableFields( $tables );
               $table_fields = array_keys($result[$tablename]);
               
               $database->setQuery( "SELECT Resource, ResourceName, ResourceGroup FROM ".$tablename."" );
               $datarows = $database->loadObjectList();
               
               $xls =& new Spreadsheet_Excel_Writer();
               $xls->setVersion(8); // this fixes the 255 limit issue!🙂
               $xls->send("ChronoForms - ".$formname." - ".date("j_n_Y").".xls");
               $format =& $xls->addFormat();
               $format->setBold();
               $format->setColor("blue");
               if (strlen($formname) > 10){$formname = substr($formname,0,10);};
               $sheet =& $xls->addWorksheet($formname.' at '.date("m-d-Y"));
               $sheet->setInputEncoding('utf-8');

               $titcol = 0;
               foreach($table_fields as $table_field){
                  $sheet->writeString(0, $titcol, $table_field, $format);
                  $titcol++;
               }
                     
                     
               $datacol = 0;
               $rowcount = 1;
               foreach($datarows as $datarow){
                  foreach($table_fields as $table_field){
                     $sheet->writeString($rowcount, $datacol, $datarow->$table_field, 0);
                     $datacol++;
                  }
                  $datacol = 0;
                  $rowcount++;
               }
                     
               $xls->close();
               exit;
        ?>

My table jos_chronoforms_RACIRoles has just three columns: Resource, ResourceName, ResourceGroup.

This is what is generated in Excel (see attached). I can generate CSVs without any problems.

Any ideas? Using ChronoContact 3.1 RC5.5.

Cheers,

Brian.

Hi Brian,

Its not easy to fix the excel code issues but try to comment this line of code:

$xls->setVersion(8);

if it doesn't work then I suggest you write some code to import to MS Excel XML formatted file, this should make the file opened with excel fine too!

Regards
Max

Hi Brian,

There was a long thread on this a couple of months ago with a variety of fixes in it.

I've found it can help to write cusom code to export - copying and modifying the ChronoForms code. The main problem seems to be memory overloads. Saving the file for later downloading can help as you write each record to disk rather than holding the whole file in memory; and in one case I found that breaking the database read into 2000 record chunks was needed as reading the whole 8000-odd records at once was causing the break.

Bob

Thanks Bob/Max. I'll take a look at it again - I might go CSV for the moment.

I read all the threads and tried everything in them (including Max's fix above) but none of them seem to work. The test I did was only exporting a couple of dozen records!

Anyhow, if I find a fix I'll post it back here.

Cheers,

Brian.