[Margrate]

Hello All,

Is it possible to generate an excel file via trigger and attach it to the Input document?

Please help me on this topic.

[amosbatto]

There are PHP libraries you can import in your trigger to generate an Excel file, like:
http://www.phpclasses.org/package/2037- ... ormat.html
http://pear.php.net/package/Spreadsheet_Excel_Writer

However, in my experience it is much easier to simply produce a comma-separated-value (CSV) file which can be used in any spreadsheet program. You can create a temporary file with trigger code and then use this interface to upload it is as an input document file.

Does the data come from a grid or from a database query? Either way, the trigger code would be similar, since database queries and grids store their data in the same format (a numbered array of associative arrays). The code would be something like this:

Code: Select all

//function to enclose new lines, tabs, commas, semicolons and beginning and trailing spaces
//inside double quotation marks. Also converts " into "". 
function prepareCsv($str) {
   $str = str_replace('"', '""', $str);
   if (preg_match('/[,;"\n\r]/', $str) or trim($str) != $str) { 
      $str = '"' . $str . '"'; 
   }
   return $str;
} 

if (!isset(@=myGrid) or empty(@=myGrid or count(@=myGrid) == 0) {
   $msg = "Grid is empty or was not submitted.";
    throw new Exception($msg);
    //if want error to be visible to user not using debug mode:
    //G::SendMessageText($msg, "WARNING"); 
    //goto triggerEnd;
}
$inputDocId = 'XXXXXXXXXXXXXXXXXXXX'; //set to UID of Input Doc 
$filePrefix = 'grid_' . date('Y-m-d_H-i-s_');
$csvFilename = tempnam(sys_get_temp_dir(), $filePrefix) . '.csv';
$csvFile = fopen($csvFilename, "w") or die("Error: Unable to open file $csvFilename");

//export the column headers 
$header ='';
foreach (@=myGrid[1] as $fieldName => $fieldValue) {
    $header .= (empty($header) ? '' : '; ') . prepareCsv($fieldName);
}
fwrite($csvFile, $header ."\n");

//export each row
foreach (@=myGrid as $aRow) {
   $sRow = '';
   foreach ($aRow as $fieldValue ) {
     $sRow .= (empty($header) ? '' : ';') . prepareCsv($fieldValue); 
   } 
   fwrite($csvFile, $sRow ."\n");
}
fclose($csvFile);

$params = array (
   'ATTACH_FILE'  => (phpversion() >= "5.5") ? new CurlFile($csvFilename) : '@' . $csvFilename,
   'APPLICATION'  => @@APPLICATION,
   'INDEX'        => 1,
   'USR_UID'      => @@USER_LOGGED,
   'DOC_UID'      => $inputDocId,
   'APP_DOC_TYPE' => 'INPUT',
   'TITLE'        => '',
   'COMMENT'      => ''
);
ob_flush();
$ch = curl_init();
$url = $_SERVER["REQUEST_SCHEME"] .'://'. $_SERVER['HTTP_HOST'] .'/sys'. @@SYS_SYS. '/en/neoclassic/services/upload';
curl_setopt($ch, CURLOPT_URL, $url);
// curl_setopt($ch, CURLOPT_VERBOSE, 1);  //Uncomment to debug
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $params);
// curl_setopt ($ch, CURLOPT_SSL_VERIFYHOST, 1); //Uncomment for SSL
// curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, 1); //Uncomment for SSL
$response = curl_exec($ch);
curl_close($ch);
G::SendMessageText($response, "INFO"); 
unlink($csvFilename);
triggerEnd: