Yii 2 Table Export
Method 1 (HTML table)
Export data to a CSV/Excel file:
//... 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 '<table border="1" width="100%"> <thead> <tr> <th>id</th> <th>name</th> <th>description</th> <th>created</th> <th>modified</th> <th>status</th> <th>user_id</th> </tr> </thead>'; foreach($model as $data){ echo ' <tr> <td>'.Html::encode($data['id']).'</td> <td>'.Html::encode($data['name']).'</td> <td>'.Html::encode($data['description']).'</td> <td>'.Html::encode($data['created']).'</td> <td>'.Html::encode($data['modified']).'</td> <td>'.Html::encode($data['status']).'</td> <td>'.Html::encode($data['user_id']).'</td> </tr> '; //<td><img src="'.Yii::$app->request->baseUrl.'/uploads/'.$data['foto'].'" width="100px"></td> } echo '</table>'; } 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, '<table border="0" width="100%">'."\n"); if (!empty($fields) && count($fields) > 0) { // Add the fields row to the body fwrite($output, "<thead>\n"); fwrite($output, "<tr>\n <th>"); fwrite($output, implode("</th>\n <th>", $fields)); fwrite($output, "</th>\n</tr>\n"); fwrite($output, "</thead>\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, "<tr>\n <td>"); fwrite($output, implode("</td>\n <td>", $row)); fwrite($output, "</td>\n</tr>\n"); } fwrite($output, '</table>'); // 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:
<?php use yii\helpers\Html; //... ?> <!-- Download Sample Button --> <div class="btn-group"> <?= Html::a('<i class="glyphicon glyphicon-download d-icon"></i> ' . Yii::t('app', 'Download Sample') . ' <span class="caret">', '#', ['class' => 'btn btn-default dropdown-toggle', 'data-toggle'=>"dropdown", 'role'=>"button", 'aria-haspopup'=>"true", 'aria-expanded'=>"false"] ) ?> <ul class="dropdown-menu"> <li><?= Html::a(Yii::t('app', 'CSV Format'), ['download-sample', 'file_name' => 'prices-audina.csv'], ['class' => '', 'data-pjax' => '0']) ?></li> <li><?= Html::a(Yii::t('app', 'Excel Format'), ['download-sample', 'file_name' => 'prices-audina.xls'], ['class' => '', 'data-pjax' => '0']) ?></li> <li><?= Html::a(Yii::t('app', 'HTML Format'), ['download-sample', 'file_name' => 'prices-audina.html'], ['class' => '', 'data-pjax' => '0']) ?></li> </ul> </div>