• A new version of Full Calendar is now available.

    See the details here

Export to CSV - Customizing

  • Views Views: 10,466
  • Last updated Last updated:

Navigation

  • There may be times when you want to manipulate the data exported by the list CSV Export option. To do this, just create a file named list_[listID#]_csv_export.php and save it in the ./plugins/fabrik_list/listcsv/scripts folder. This file will then be included in the csvexport.php code and can act as a post-processing script.

    This feature is similar to the Fabrik List 'listcsv' plugin - except that it can be used to manipulate the entire exported CSV file - not just one row at a time.

    In most cases you should be able to include all your custom php code in one file. However, in this example there are a series of Joomla Menu items that all use the same Fabrik List, each with different Elements set to 'Include in list' - in order to filter the menu's list columns to include only certain Elements, depending on the menu item. So in that case, use the list_#_csv_export.php file to first create a case statement that will include an appropriate 2nd custom php file, containing the post-processing code to be used for the menu item selected.

    With that in mind, below is an example of a Fabrik List (with Id of 1) where we want to process the CSV export data and use the PHPExcel library to convert it to a true Excel Spreadsheet (xlsx file) - complete with document properties, formatted headings and cells, locked header and read-only columns, password protection, etc.

    Also note that any variables and classes available to the downloadFile() function are also available to your custom code. For example... $filePath has already been set as the full path and filename of CSV export file.

    You must include your own download processing to download the 'final product' - as shown in the example below. However, keep in mind that your custom php file is included in the downloadFile() function of csvexport.php BEFORE the normal download routine. So, unless your custom code ends by 'killing' the php execution, the 'normal' download code will also be processed. This way, if certain conditions are not met in your code, the normal download process can be executed.

    So as an example...
    First create a new file named 'list_1_csv_export.php' and save it in the ./plugins/fabrik_list/listcsv/scripts folder.

    list_1_csv_export.php...
    PHP:
    $user = JFactory::getUser();
    /* get the menu Itemid and set the case function based on the menu item selected */
    $menuId = $this->app->getUserStateFromRequest('Itemid','0',false,'menu');
    $incfile = '';
    if((int) $menuId > 0){
    switch ($menuId) {
    case '2205':
    $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_hospital.php';
    // $filename will be used as name of the Excel file created (less extension)
    $filename = 'hospital_'.$user->id;
    $filetype = 'Hospital';
    break;
    case '2206':
    $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_nursing.php';
    $filename = 'nursingl_'.$user->id;
    $filetype = 'Nursing/Rehab';
    break;
    case '2207':
    $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_practice.php';
    $filename = 'practice_'.$user->id;
    $filetype = 'Medical Practice';
    break;
    default:
    break;
    }
    }
    /* Check that 2nd include file exists */
    if(file_exists(JPATH_ROOT.$incfile)){
    require JPATH_ROOT.$incfile;
    }
    /* If JPATH_ROOT.$incfile does not exist, the normal download will proceed */
    ?>
    Example for case '1': JPATH_ROOT/plugins/fabrik_list/listcsv/scripts/csv_export_hospital.php...
    PHP:
    <?php
    // Get upload temp path (containing the CSV export file)
    // Needed when writing the saved file with a different filename
    $config = JFactory::getConfig();
    $tmp_path = $config->get( 'tmp_path' );

    // Import PHPExcel library
    jimport('phpexcel.library.PHPExcel');

    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    $objReader = PHPExcel_IOFactory::createReader('CSV');

    // Load the CSV export file into the phpExcel Reader
    $objPHPExcel = $objReader->load($filePath);

    // assign the active sheet to var $sheet
    $sheet = $objPHPExcel->getActiveSheet();

    /* Set Excel document properties
    * This section is optional. The values can either be hard-coded
    * or insert php code here to query tables and pass the retrieved
    * values to these phpExcel functions.
    */
    $objPHPExcel->getProperties()->setCreator("My Project")
    ->setLastModifiedBy($user->name." [".$user->username."]")
    ->setTitle("My Project Report")
    ->setSubject("My Project - Type:".$filetype)
    ->setDescription("My Project Description")
    ->setKeywords("Project PHPExcel php")
    ->setCategory("Reports");
    // sheet title
    // shown in navigation tab at bottom of workbook - limit 31 characters
    $sheet->setTitle(substr($filetype,0,31));

    // save row and column count to variables
    $highestRowCount = $sheet->getHighestRow();
    $highestColumnCount = $sheet->getHighestColumn();

    // Freezes row 1 and columns A-D
    // so header and read-only columns remain fixed when scrolled
    $sheet->freezePane( "E2" );

    // Style row 1 (Heading)
    $sheet->getStyle("A1:{$highestColumnCount}1")->applyFromArray(
    array(
    'fill' => array(
    'type' => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array('rgb' => 'FFFF00')
    )
    )
    );

    /* This is part of trick to use column 'D' cell like a tooltip.
    * By setting the background color to the same as the foreground color it
    * will appear to be empty - but it really contains the 'Job Description'
    * (a detailed description of the row's content) and will display that
    * description above the spreadsheet when column D is clicked in any row.
    * (See attachments)
    */
    $sheet->getStyle("D1:D{$highestRowCount}")->applyFromArray(
    array(
    'fill' => array(
    'type' => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array('rgb' => '000000')
    )
    )
    );

    /* This is styling for the 'read-only' columns of the spreadsheet */
    $sheet->getStyle("A2:C{$highestRowCount}")->applyFromArray(
    array(
    'fill' => array(
    'type' => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array('rgb' => 'F9FAEB')
    )
    )
    );

    /* The export contains internal ID in 1st column A - Hide it */
    $sheet->getColumnDimension("A")->setVisible(false);

    /* Sets the width of various columns as needed to accommodate numeric data
    * or to prevent characters from wrapping in words in the header columns
    */
    $sheet->getColumnDimension("B")->setWidth(9.75);
    $sheet->getColumnDimension("C")->setWidth(40);
    $sheet->getColumnDimension("D")->setWidth(1.7);
    $sheet->getColumnDimension("E")->setWidth(40);
    $sheet->getColumnDimension("G")->setWidth(9.75);
    $sheet->getColumnDimension("K")->setWidth(9.75);
    $sheet->getColumnDimension("M")->setWidth(10);

    /* Sets style and formatting for columns with numeric data */
    $sheet->getStyle("F1:L{$highestRowCount}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $sheet->getStyle("F1:K{$highestRowCount}")->getNumberFormat()->setFormatCode('#,##0.00');
    $sheet->getStyle("L1:L{$highestRowCount}")->getNumberFormat()->setFormatCode('#,##0.0');

    /* Creates a popup comment in column D of header to instruct how to use the column D 'tooltip' */
    $sheet->getComment("D1")->getText()->createTextRun("Click in this column to view description of selected row");
    $sheet->getComment("D1")->setWidth("350px");
    $sheet->getComment("D1")->setHeight("24px");

    /* Wordwrap the heading cells */
    $sheet->getRowDimension(1)->setRowHeight(45);
    $sheet->getStyle("A1:{$highestColumnCount}1")
    ->getAlignment()
    ->setWrapText(true);

    /* Password protect sheet */
    $sheet->getProtection()->setPassword('PHPExcel');
    $sheet->getProtection()->setSheet(true);

    /* Unprotect editable cells */
    $sheet->getStyle("E2:{$highestColumnCount}{$highestRowCount}")->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

    /* You could add some code here that sets the file extension
    * (and which PHPExcel writer to use), based on user preference set elsewhere.
    * But for this example it is just hard-coded as xlsx.
    */

    /* Initialize the PHPExcel Writer */
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    $fullFilename = $filename.'.xlsx';
    $fullPathFile = $tmp_path.'/'.$fullFilename;

    /* Write the formatted sheet to an Excel file */
    $objWriter->save($fullPathFile);

    /* disconnect worksheet and unload PHPExcel */
    $objPHPExcel->disconnectWorksheets();
    unset($objPHPExcel);

    /* Delete original csv export file */
    unlink($filePath);

    /* Set HTML headers and force download of Excel file
    * skip this if you don't want to immediately download
    */
    header('Content-Description: File Transfer');
    header('Content-type: application/vnd.ms-excel');
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/force-download");
    header("Content-Type: application/download");
    header("Content-Disposition: attachment;filename={$fullFilename}");
    header("Content-Transfer-Encoding: binary ");
    header('Content-Length: ' . filesize($fullPathFile));

    /* flush output before reading file */
    while(ob_get_level()) ob_end_clean();
    flush();

    /* readfile (and force the download) */
    readfile($fullPathFile);

    /* This is where you could include some php to also optionally save
    * the file to a special user folder or 'store' for future use or purchase.
    */

    /* delete file then kill the php process to prevent 'normal' csv file from being downloaded */
    unlink($fullPathFile);
    die;
    ?>
    While this example uses PHPExcel, the post-processing script can be used to run any code using any Library or API - for example, to use data contained in the CSV Export to batch-process images or create whatever type of file you need to create - so long as you have the tools to do so!

    Examples of 'before' and 'after' can be seen in the attachments.
Back
Top