Export to CSV - Customizing

$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 */

?>

<?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;

?>