Base On scotthuangzl (ptrnov update)
Postman Excel Export for view or console cronjobs, mail postman
The preferred way to install this extension is through composer.
Either run
php composer.phar require --prefer-dist ptrnov/yii2-postman4excel "*"
or add
"ptrnov/yii2-postman4excel": "*"
to the require section of your composer.json file.
Once the extension is installed, simply use it in your code by :
use ptrnov\postman4excel\Postman4ExcelBehavior;
public function behaviors(){
'export4excel' => [
'class' => Postman4ExcelBehavior::className(),
'downloadPath'=>'your path' //defult "/vendor/ptrnov/yii2-postman4excel/tmp/", Ecample Windows path "d:/folder/"
'widgetType'=>'download' //download web browser, delete before download, tmp_download
//'widgetType'=>'cronjob' //console method, folder tmp_cronjob
//'widgetType'=>'mail' //posman mail, folder tmp_mail
//'widgetType'=>'' //Empty same download, file stay on folder "tmp_mix"
//'prefixStr' => yii::$app->user->identity->username,
//'suffixStr' => date('Ymd-His'),
],
}
// localhost/yourController/test-export
public function actionTestExport()
{
//get data from database
$sqlDataProvider= new ArrayDataProvider([
'allModels'=>\Yii::$app->db->createCommand("
SELECT id, username FROM user
")->queryAll(),
]);
$arySqlDataProvider=$sqlDataProvider->allModels;
/*Array Model Data*/
$excel_dataAll = Postman4ExcelBehavior::excelDataFormat($arySqlDataProvider); (not used for columnGroup just put 'ceils' => $arySqlDataProvider)
$excel_ceilsAll = $excel_dataAll['excel_ceils'];
$excel_title1 = $excel_dataAll['excel_title'];
$excel_title2 = ['ID','USERNAME'];
//Tite Header From Table Name
//$excel_title = $excel_dataAll['excel_title'];
//Tite Header From Modify byself
//$excel_title = ['ID','USERNAME']
//Note 'sheet_title'
//old version : 'sheet_title' => $excel_title,
//new Version : 'sheet_title' => [$excel_title1,$excel_title2,$excel_title3, dst],
$excel_content = [
[
'sheet_name' => 'TEST EXPORT 1 old version',
'sheet_title' => $excel_title1, //old version
'ceils' => $excel_ceilsAll,// atau langsung $arySqlDataProvider;
//for use column Group.
//noted: 'ceils' => "Array Source", not need difinition "Postman4ExcelBehavior::excelDataFormat($array source);"
//'columnGroup'=>Name of Field//column group difinition.
'columnGroup'=>['ID'],//column for grouping.
'autoSize'=>true, //true/false.
//'freezePane' => 'A2',
'headerColor' => Postman4ExcelBehavior::getCssClass("header"), //All Header Color font and Backgroud
'headerColumnCssClass' => [ //old version
[
'id' => Postman4ExcelBehavior::getCssClass('yellow'),
'username' => Postman4ExcelBehavior::getCssClass('green'),
],
[
'ID' => Postman4ExcelBehavior::getCssClass('red'),
'USERNAME' => Postman4ExcelBehavior::getCssClass('green'),
]
],
'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"),
'evenCssClass' => Postman4ExcelBehavior::getCssClass("even"),
],
[
'sheet_name' => 'TEST EXPORT 2 new version ',
//'sheet_title' => [$excel_title1], //new version | one Header
'sheet_title' => [ //new version | two or more Header
$excel_title1,
$excel_title2
], //new version
'ceils' => $excel_ceilsAll,
//'freezePane' => 'E2',
'headerColor' => Postman4ExcelBehavior::getCssClass("header"), //old Version | All Header Color font and Backgroud
/* --------------------------------------------------------
* customize Header properties
* Color Ref: http://dmcritchie.mvps.org/excel/colors.htm
* columnAutoSize=false, width is Active
* 'merge'=>'col,row'
* Content properties, validate on use last column title
* --------------------------------------------------------
*/
'headerStyle' => [ //new version
[ //the first Header
'id' => ['align'=>'CENTER','color-font'=>'0000FF','color-background'=>'FFCCCC','merge'=>'0,2','width'=>'2,0'],
'username' => ['align'=>'left','color-font'=>'FF0000','color-background'=>'CCFF99','merge'=>'2,1','width'=>'32.29','valign'=>'center','wrap'=>true],
],
[ //The second Header
'ID' => ['align'=>'right','color-font'=>'0000FF','color-background'=>'CCFFCC'],
'USERNAME' => ['align'=>'right','color-font'=>'000000','color-background'=>'FFFF99'],
]
],
'contentStyle' => [ //new version
[//the first Content
//'id' => ['align'=>'left'],
//'username' => ['align'=>'right'],
],
[//The second Content
'ID' => ['align'=>'center','color-font'=>'0000FF'],
'USERNAME' => ['align'=>'center','color-font'=>'0000FF'],
],
],
'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"),
'evenCssClass' => Postman4ExcelBehavior::getCssClass("even"),
],
];
$excelFile = "TestExport";
$this->export4excel($excel_content, $excelFile);
}