//...
use yii\filters\AccessControl;
use yii\filters\VerbFilter;
use yii\helpers\Html;
class ItemController extends Controller
{
public function behaviors()
{
return [
'access' => [
'class' => AccessControl::className(),
'rules' => [
[
'actions' => [/* ... */ 'export'],
'allow' => true,
'roles' => ['@'], // @ = Authenticated users
],
],
],
//...
];
}
//...
public function actionExport($format='csv')
{
switch($format) {
case 'excel':
$this->actionExportExcel();
break;
case 'csv':
default:
$this->actionExportCsv();
break;
} // endswitch
}
public function actionExportExcel()
{
$model = Item::find()->All();
$filename = 'Data-'.Date('YmdGis').'-Items.xls';
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=".$filename);
echo '
id
name
description
created
modified
status
user_id
';
foreach($model as $data){
echo '
'.Html::encode($data['id']).'
'.Html::encode($data['name']).'
'.Html::encode($data['description']).'
'.Html::encode($data['created']).'
'.Html::encode($data['modified']).'
'.Html::encode($data['status']).'
'.Html::encode($data['user_id']).'
';
//
';
}
public function actionExportCsv()
{
$model = Item::find()->All();
$filename = 'Data-'.Date('YmdGis').'-Items.csv';
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=".$filename);
echo "id,name,description,created,modified,status,user_id\n";
foreach($model as $data){
echo $this->getCsvField($data['id']);
echo ',' . $this->getCsvField($data['name']);
echo ',' . $this->getCsvField($data['description']);
echo ',' . $this->getCsvField($data['created']);
echo ',' . $this->getCsvField($data['modified']);
echo ',' . $this->getCsvField($data['status']);
echo ',' . $this->getCsvField($data['user_id']) . "\n";
}
}
private function getCsvField($field)
{
return ( (strpos($field, "'") !== false) ? "'".$field."'" : $field );
}
}
== Method 2 (HTML table) ==
// Usage:
// actionOutputCSV([
// ["Car1", "Car2", "Car3"],
// ["Volvo 1", "BMW 1", "Toyota 1"],
// ["Volvo 2", "BMW 2", "Toyota 2"],
// ["Volvo 3", "BMW 3", "Toyota 3"],
// ],'download.csv');
// Source: See more at: https://arjunphp.com/create-download-csv-files-php/#sthash.f6JCTy4J.dpuf
function actionOutputCSV($data, $file_name = 'file.csv')
{
// Output headers so that the file is downloaded rather than displayed
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=$file_name");
// Disable caching - HTTP 1.1
header("Cache-Control: no-cache, no-store, must-revalidate");
// Disable caching - HTTP 1.0
header("Pragma: no-cache");
// Disable caching - Proxies
header("Expires: 0");
// Start the ouput
$output = fopen("php://output", "w");
// Then loop through the rows
foreach ($data as $row) {
// Add the rows to the body
//fputcsv($output, $row); // here you can change delimiter/enclosure
fputcsv($output, $row, "\t"); // here you can change delimiter/enclosure
}
// Close the stream off
fclose($output);
}
// Usage:
// actionOutputExcel([
// ["Car1", "Car2", "Car3"],
// ["Volvo 1", "BMW 1", "Toyota 1"],
// ["Volvo 2", "BMW 2", "Toyota 2"],
// ["Volvo 3", "BMW 3", "Toyota 3"],
// ], ["Car1", "Car2", "Car3"], 'download.xls');
//
function actionOutputExcel($data, $fields, $file_name = 'file.xls')
{
// Output headers so that the file is downloaded rather than displayed
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=".$file_name);
// Disable caching - HTTP 1.1
header("Cache-Control: no-cache, no-store, must-revalidate");
// Disable caching - HTTP 1.0
header("Pragma: no-cache");
// Disable caching - Proxies
header("Expires: 0");
// Start the ouput
$output = fopen("php://output", "w");
fwrite($output, ''."\n");
if (!empty($fields) && count($fields) > 0) {
// Add the fields row to the body
fwrite($output, "\n");
fwrite($output, "\n ");
fwrite($output, implode(" \n ", $fields));
fwrite($output, " \n \n");
fwrite($output, "\n");
}
// Loop through the rows of data
foreach ($data as $row) {
// encode for HTML entities
foreach ($row as $key => $cell) {
$row[$key] = Html::encode($cell);
}
// Add the data rows to the body
fwrite($output, "\n ");
fwrite($output, implode(" \n ", $row));
fwrite($output, " \n \n");
}
fwrite($output, '
');
// Close the stream off
fclose($output);
}
== Method 3 (Native Excel) ==
Using PHPExcel, we can export data to Excel native format. Add the following to the ''require'' section in the ''[app]/composer.json'' file:
"phpoffice/phpexcel": "*"
Perform a composer update: $composer update
Files should be copied to ''vendor/phpoffice/phpexcel'' folder.
Add actions to your controller:
class PriceController extends Controller
{
//...
public function actionDownloadSample($file_name = 'file.xls')
{
$fields = ["item_code", "description", "cost"];
$data = [
$fields,
["prod_Mazda", "Mazda", "26000"],
["prod_Toyota", "Toyota", "24000"],
];
$path_parts = pathinfo($file_name);
switch($path_parts['extension']) {
default:
case 'csv':
//...
break;
case 'html':
//...
break;
case 'xls':
$this->actionOutputExcel($data, $file_name);
break;
}
}
function actionOutputExcel($data, $file_name = 'file.xls')
{
date_default_timezone_set('America/Los_Angeles');
$docExcel = new \PHPExcel(); // requires \vendor\phpoffice\phpexcel\Classes\PHPExcel
$docExcel->setActiveSheetIndex(0);
$docExcel->getActiveSheet()->fromArray($data, null, 'A1');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$file_name.'"');
header('Cache-Control: max-age=0');
// Output data
$writer = \PHPExcel_IOFactory::createWriter($docExcel, 'Excel5');
$writer->save('php://output');
}
}
In your view, call it like this:
' . Yii::t('app', 'Download Sample') . ' ',
'#',
['class' => 'btn btn-default dropdown-toggle', 'data-toggle'=>"dropdown", 'role'=>"button", 'aria-haspopup'=>"true", 'aria-expanded'=>"false"]
) ?>
=== References ===
* [[https://blog.mayflower.de/561-Import-and-export-data-using-PHPExcel.html|Import and Export Data Using PHPExcel]]