CSV export: edit specific cell in a row

eulanaz · Oct 20, 2016

Dear all,

I need to add some text to my exported data in CSV. In order to do that, I found the following link:

http://fabrikar.com/forums/index.php?wiki/export-to-csv-customizing/

With this method I can do any needed customization and save the file in a .xlsx format.

My problem is that I can't see anything despite I followed right the instructions.

Here my code:

list_11_csv_export.php (saved in \plugins\fabrik_list\listcsv\scripts):

PHP:

<?php $user = JFactory::getUser();
$incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_mylist.php';
$filename = 'mylist'.$user->id;
$filetype = 'mylist';
/*  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 */
?>

csv_export_mylist.php (saved in \plugins\fabrik_list\listcsv\scripts):

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("mylist")
    ->setLastModifiedBy($user->name." [".$user->username."]")
    ->setTitle("mylist Export")
    ->setSubject("mylist - Type:".$filetype)
    ->setDescription("mylist Export")
    ->setKeywords("mylist PHPExcel php")
    ->setCategory("Export");
// 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();

/* 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);

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

Since I'm testing the export custozimazion in my csv_export_mylist.php I will only test the transformation to .xlsx and after that the editing of a specific cell in every row.

Anyone have an idea about what is wrong?

Thanks in advance and kind regards

eulanaZ

troester · Oct 20, 2016

do you have this phpExcel library installed?

gesendet mit Tapatalk

eulanaz · Oct 21, 2016

Hi troester,

you were right! The library was not installed properly!

Unfortunately the problem is still present and nothing happen.

There is myabe something that I miss to do?

Thanks a lot for your support

eulanaZ

eulanaz · Oct 21, 2016

Hi troester,

I found the problem!

Sorry... Was a stupid mistake!

You can close the topic.

Thanks again

eulanaZ

troester · Oct 21, 2016

Can you share your solution?

You can close solved threads by unchecking "open" in Thread Tools.

eulanaz · Oct 24, 2016

Hi troester,

was simply the ID of the form not equal to the correct report! :S.

I'm in two different environment for test and production and I had this carelessness.

Thanks for the information about closure but now I have another issue with the excel file.

Now can I see that my code is ran after the csvexport.php call.

The problem is that the file is not download but is readed on a new Tab on the browser (as a simple text).

There is something wrong?

The new code is here:

list_17_csv_export.php -> called by csvexport.csv

PHP:

<?php

$user = JFactory::getUser();
$incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_myproject.php';
$filename = 'MyProject';
$filetype = 'MyProject';

/*  Check that 2nd include file exists */
if(file_exists($incfile)){
    require $incfile;
}
/* If JPATH_ROOT.$incfile does not exist, the normal download will proceed */
?>

csv_export_myproject.php -> called by the list_17_csv_export.php

I think the problem is on the call

PHP:

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

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);
// $objWriter = new PHPExcel_Writer_Excel5 ( $objPHPExcel );

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

// Set Excel file properties
$objPHPExcel->getProperties()->setCreator("eulanaz");
$objPHPExcel->getProperties()->setLastModifiedBy("eulanaz");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

// Set the currentActiveSheet Title
$sheet->setTitle("MyProject");

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

/* 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.
*/
unlink($fullPathFile);
die;
?>

Thanks in advance for any help.

eulanaZ

PS: I found a little mistake on the procedure described at the link http://fabrikar.com/forums/index.php?wiki/export-to-csv-customizing/:

In the file list_1_csv_export.php, at the following lines:

PHP:

/*  Check that 2nd include file exists */
if(file_exists(JPATH_ROOT.$incfile)){
   require JPATH_ROOT.$incfile;
}

JPATH_ROOT variable is not needed as it is concatenated in the case statement.

troester · Oct 24, 2016

It's looking like all the header stuff is still commented out, I think there's a spare blank
/* Set HTML headers and force download of Excel file
* skip this if you don't want to immediately download
*XX/

(I've removed it in the WIKI now)

eulanaz · Oct 25, 2016

Hi troester,

yes. Was the header commented!

Many thanks for your help!

Can I confirm to all that leaving the comment to the header everything works fine and the file download is a right formatted xlsx.

Thanks again

eulanaZ

CSV export: edit specific cell in a row

eulanaz

Member

troester

Administrator

eulanaz

Member

eulanaz

Member

troester

Administrator

eulanaz

Member

troester

Administrator

eulanaz

Member

Useful links

Members online