[subbiahpradeep]

Hi All,

How am i able have a report of the submitted form data and export that report to an excel sheet.

I need to have an export option and while clicking on it i need to submit some values in a form like year, month, category and get the result based on that and have a link in the result page which exports my result to excel sheet.

anyone help in this.

Thanks.

[akace]

Hi,

did you find a solution to get an output document in excel?
It should be possible to upload an own excel sheet as standard form. Then the information of the submitted data could be inserted to that sheet.
I don´t know if this could be realized, but this wouls be a great solution.
Thanks for any suggest how I can solve this issue.

thanks a lot...

[aayala]

search on sourceforge or codeples on phpclasess there are many solutions to export data as excell (something usefull remember excel can reas CSV format)

[pmUser2000]

yes, you can extend the functionality using php classes to generate the excel report you want.
Use trigger to run your personalized php classes.

[jhonatantirado]

Use this trigger:

Code: Select all

function cleanData(&$str) 
{ 
	$str = preg_replace("/\t/", "\\t", $str); 
	$str = preg_replace("/\r?\n/", "\\n", $str); 
	if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; 
} 

# filename for download 
$filename = "YOURFILENAME_" . date('Ymd') . ".xls"; 
header("Content-Disposition: attachment; filename=\"$filename\""); 
header("Content-Type: application/vnd.ms-excel"); 
$flag = false; 
$data = @@MYCASEVARIABLE;
//THIS MUST BE AN ARRAY OF ARRAYS, LIKE A RESULT SET FROM A MYSQL QUERY
foreach($data as $row) 
{ 
	if(!$flag) { 
	# display field/column names as first row 
	echo implode("\t", array_keys($row)) . "\n"; 
        $flag = true;
 	} 
 	array_walk($row, 'cleanData'); 
	echo implode("\t", array_values($row)) . "\n"; 
}
exit;

-------------------------------------------------------------------------

Fire the trigger and you will be prompet to download an Excel file, with the first row with the name of your columns.

Regards

[jhonatantirado]

I got this working together: unserialize data and export to Excel. Just in case someone is interested.

The following trigger reads APP_DATA field from APPLICATION table, unserialize it and then exports it to Excel.

Code: Select all

$columnas=array('NOMBRE','DNI','RAZONSOCIAL','NROTARJETA','AREA','FECHAPERMISO','OBSERVACION','NMOTIVO','HSALIDA','HRETORNO');//ARRAY OF CASE VARIABLES STORED IN APP_DATA
$pro_uid = '7044982914c6e9436a40906053295944';//PROCESS ID
$AREA=@@AREA;//DEPARTMENT ID
$DESDE=@@DESDE;//SINCE DATE
$HASTA=@@HASTA;//UNTIL DATE

$query="SELECT APP_NUMBER, APP_DATA FROM APPLICATION 
    WHERE PRO_UID='$pro_uid' AND APP_INIT_USER IN
    (SELECT USR_UID FROM USERS WHERE DEP_UID='$AREA') AND 
    (APP_CREATE_DATE BETWEEN '$DESDE' AND '$HASTA') AND 
    APP_UID IN (SELECT APP_UID FROM APP_DELEGATION 
    WHERE PRO_UID='$pro_uid' AND DEL_INDEX>=5) 
    ORDER BY APP_CREATE_DATE DESC";  
//PROCESS $pro_uid CASES BY USERS BELONGING TO DEPARTMENT $AREA, WHICH CASES HAVE REACHED TASK 5 (DEL_INDEX=5)

if ($AREA=='1') //ALL AREAS
{
   $query="SELECT APP_NUMBER, APP_DATA FROM APPLICATION 
       WHERE PRO_UID='$pro_uid' AND (APP_CREATE_DATE BETWEEN '$DESDE' AND '$HASTA') AND 
       APP_UID IN (SELECT APP_UID FROM APP_DELEGATION WHERE PRO_UID='$pro_uid' AND DEL_INDEX>=5) 
       ORDER BY APP_CREATE_DATE DESC";
}

$result = executeQuery($query); 
@@PERMISOS = array();
if (is_array($result) && count($result) > 0)
{
    $indicex = 1;
    foreach ($result as $row){
        $aVars = unserialize($row['APP_DATA']);//UNSERIALIZE CASE DATA
        $temp= array();
        $temp['CASE_NUMBER']= $row['APP_NUMBER'];
        foreach($aVars as $key => $value){
            if(in_array($key,$columnas)){
            $temp[$key]=$value;
            }
        }
        @@PERMISOS[$indicex] = $temp;//BUILD ARRAY
        $indicex++;
    }
}

function cleanData(&$str) //REMOVE BAD CHARACTERS FROM DATA
{ 
	$str = preg_replace("/\t/", "\\t", $str); 
	$str = preg_replace("/\r?\n/", "\\n", $str); 
	if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; 
} 

$filename = "MYFILENAME" . date('Ymd') . ".xls"; 
header("Content-Disposition: attachment; filename=\"$filename\""); 
header("Content-Type: application/vnd.ms-excel"); 
$flag = false; 
$data = @@PERMISOS;
foreach($data as $row) 
{ 
	if(!$flag) { 
	     echo implode("\t", array_keys($row)) . "\n"; 
             $flag = true;
 	} 
 	array_walk($row, 'cleanData'); 
	echo implode("\t", array_values($row)) . "\n"; 
}
exit;//PROMPT DOWNLOAD

[pmUser2000]

awesome

[luguisaro]

Hi, I am having problems using this code inside a ProcessMaker trigger, since it contains Exit(); terminates the case, as it makes the whole php code "die".

Is there any way to replace that Exit(); to generate the document?

PS: I already tried to use Break, echo, var_dump, flush, but the only one that works is Exit.

Here is my code.

Thanks


@=grd_creacion_borrador = "";
@=grd_creacion_borrador = array();
$ConsultaBoton2 = @@CLAVE2;
$ConsultaBotonExcel = @@CLAVE1;
$filtro_acs = @@ddl_ac_genera_borrador_label;
$condicionFiltro = "";
$exportarExcel = @@Ddl_validacion_informe_correcto;


if ($ConsultaBoton2 == 1){

if ($filtro_acs == "TODOS"){
$condicionFiltro = "WHERE 1 = 1;";
}else{
$condicionFiltro = "WHERE A.CODIGO_PROYECTO = "."'".$filtro_acs."';";
}


$query = "SELECT
A.CODIGO_PROYECTO PROYECTO,
A.PAIS_ACS_LABEL PAIS,
IFNULL(B.TXT_DEFICIENCIA_GRD_SEGUIMIENTO,A.DEFICIENCIA) DEFICIENCIA,
IFNULL(CONCAT(B.TXT_REMEDIACION_GRD_SEGUIMIENTO, ' ', B.FECHA_SEGUIMIENTO_AUDITOR, ' ', B. DESCRIPCION_GRD_SEGUIMIENTO),A.PLAN_DE_REMEDIACION) PLAN_REMEDIACION,
B.TXT_AVANCE_ANT AVANCE_ANTERIOR,
B.DDL_AVANCE_GRD_SEGUIMIENTO_LABEL AVANCE,
B.DDL_ESTADO_GRD_SEGUIMIENTO_LABEL ESTADO
FROM PMT_TBL_GRID_CARGA_ACS A
LEFT JOIN
(SELECT A.*,B.FECHA_SEGUIMIENTO_AUDITOR
FROM PMT_TBL_GRID_ACS_AUDITOR A
INNER JOIN PMT_TBL_FECHAS_SEGUIMIENTO B ON B.APP_NUMBER = A.APP_NUMBER
WHERE A.APP_NUMBER IN (
SELECT MAX(APP_NUMBER) FROM PMT_TBL_GRID_ACS_AUDITOR
GROUP BY DDL_AUDITORIA
)
)B ON B.DDL_AUDITORIA = A.CODIGO_PROYECTO "
.$condicionFiltro;

$aUsers = executeQuery($query);

if (is_array($aUsers) and count($aUsers) > 0) {
for ($i = 1; $i <= count($aUsers); $i++) {
@=grd_creacion_borrador[$i] = array(
'PROYECTO_BORRADOR' => $aUsers[$i]['PROYECTO'],
'PAIS_BORRADOR' => $aUsers[$i]['PAIS'],
'DEFICIENCIA_BORRADOR' => $aUsers[$i]['DEFICIENCIA'],
'PLAN_REMEDIACION_BORRADOR' => $aUsers[$i]['PLAN_REMEDIACION'],
'AVANCE_ANTERIOR_BORRADOR' => $aUsers[$i]['AVANCE_ANTERIOR'],
'AVANCE_BORRADOR' => $aUsers[$i]['AVANCE'],
'ESTADO_BORRADOR' => $aUsers[$i]['ESTADO']
);
}
}

if ($exportarExcel == 1) {


function cleanData(&$str) //REMOVE BAD CHARACTERS FROM DATA
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

$filename = "ReporteACS" . date('Ymd') . ".xls";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
$flag = false;
$data = @@grd_creacion_borrador;
$out = fopen("php://output", 'w');
foreach($data as $row)
{
if(!$flag) {
echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
array_walk($row, 'cleanData');
echo mb_convert_encoding(implode("\t", array_values($row)) . "\n",'UCS-2LE', 'UTF-8');
$result = mb_convert_encoding(implode("\t", array_values($row)) . "\n",'UCS-2LE', 'UTF-8');

}
exit; // this line is the one I want to avoid.

}


PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', '812950483629639035de292022920593');

}

[ethanpresberg]

Did you try using a return?

[luguisaro]

yes, and it doesn't work. i also tried to put it in switch to use break, and it doesn't work either.

[faithfulomoy]

Hello Guys,

Is there a way to do this for Processmaker 4? I having been trying for some time now.

Processmaker 4 has no triggers.

Thanks.