有一个比较好用的插件可以实现这个功能，在根目录同级的composer.json文件中添加如下代码：
“moonlandsoft/yii2-phpexcel”: “*”
导入数据到excel,即写入excel文件，代码如下：
<?php // export data only one worksheet. \moonland\phpexcel\Excel::widget([ 'models' => $allModels, 'mode' => 'export', //default value as 'export' 'columns' => ['column1','column2','column3'], //without header working, because the header will be get label from attribute label. 'headers' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'], ]); \moonland\phpexcel\Excel::export([ 'models' => $allModels, 'columns' => ['column1','column2','column3'], //without header working, because the header will be get label from attribute label. 'headers' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'], ]); // export data with multiple worksheet. \moonland\phpexcel\Excel::widget([ 'isMultipleSheet' => true, 'models' => [ 'sheet1' => $allModels1, 'sheet2' => $allModels2, 'sheet3' => $allModels3 ], 'mode' => 'export', //default value as 'export' 'columns' => [ 'sheet1' => ['column1','column2','column3'], 'sheet2' => ['column1','column2','column3'], 'sheet3' => ['column1','column2','column3'] ], //without header working, because the header will be get label from attribute label. 'headers' => [ 'sheet1' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'], 'sheet2' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'], 'sheet3' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'] ], ]); \moonland\phpexcel\Excel::export([ 'isMultipleSheet' => true, 'models' => [ 'sheet1' => $allModels1, 'sheet2' => $allModels2, 'sheet3' => $allModels3 ], 'columns' => [ 'sheet1' => ['column1','column2','column3'], 'sheet2' => ['column1','column2','column3'], 'sheet3' => ['column1','column2','column3'] ], //without header working, because the header will be get label from attribute label. 'headers' => [ 'sheet1' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'], 'sheet2' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'], 'sheet3' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'] ], ]);
如果需要写入文件，添加”fileName” => “excel.xlsx”, 这里的 $allModels1是一个多维数组：
即： [[‘column1’=>1, ‘column2’=>2, ‘column3’=>3],[‘column1‘=>1, ‘column2‘=>2, ‘column3‘=>3]]
很蛋疼的是通过这个写入excel,不能制定当前激活sheet,还有就是会额外写入一个sheet.
对于导出Excel内容，即读取excel，就比较简单了，这个方法可以把数据转换成数组：
<?php $data = \moonland\phpexcel\Excel::import($fileName, $config); // $config is an optional $data = \moonland\phpexcel\Excel::widget([ 'mode' => 'import', 'fileName' => $fileName, 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel. 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric. 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet. ]); $data = \moonland\phpexcel\Excel::import($fileName, [ 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel. 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric. 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet. ]); // import data with multiple file. $data = \moonland\phpexcel\Excel::widget([ 'mode' => 'import', 'fileName' => [ 'file1' => $fileName1, 'file2' => $fileName2, 'file3' => $fileName3, ], 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel. 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric. 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet. ]); $data = \moonland\phpexcel\Excel::import([ 'file1' => $fileName1, 'file2' => $fileName2, 'file3' => $fileName3, ], [ 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel. 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric. 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet. ]);
参照链接： https://packagist.org/packages/moonlandsoft/yii2-phpexcel
Be the First to comment.
