通过 
搜索
搜索malyshev-soft / yii2-phpexcel
将PHP导出到Excel或将Excel导入到PHP8.3
0.2.0
2024-04-03 16:46 UTC
Requires
- php: ^8.3
- phpoffice/phpspreadsheet: *
- yiisoft/yii2: *
This package is auto-updated.
Last update: 2024-09-04 11:47:16 UTC
README
为支持PHP 8.3而创建的存储库的分支
Yii2 PHP Excel
将PHP导出到Excel或将Excel导入到PHP。用于生成Excel文件的Excel小部件或用于加载Excel文件。
属性
字符串 $mode 是导出模式或导入模式。有效值是 'export' 和 'import'
布尔值 $isMultipleSheet 用于设置导出Excel时使用多个工作表。
数组 $properties 用于设置Excel对象的属性。
数组 $models 模型对象或包含大量数据的DataProvider对象。
数组 $columns 用于从模型获取属性，此处的有效值仅为模型上存在的属性。如果未设置，则将模型的全部属性设置为列。
数组 $headers 用于设置第一行的表头列。如果要自定义表头，则设置此属性。如果未设置，表头将获取模型属性的标签。
字符串|数组 $fileName 是导出或导入的文件名。多个文件名仅用于导入模式，在导出模式下不适用。
字符串 $savePath 是保存文件的目录，或者您可以留空以将文件设置为附件。
字符串 $format 用于导出Excel。有效值是 'Xls','Xlsx','Xml','Ods','Slk','Gnumeric','Csv', 和 'Html'。
布尔值 $setFirstTitle 用于设置第一行的标题列。列将在第一行有表头。
布尔值 $asAttachment 用于设置Excel文件为下载模式。
布尔值 $setFirstRecordAsKeys 用于将Excel文件的第一条记录设置为每行的数组键。如果您想将记录列的键设置为第一条记录，如果未设置，则使用Excel的字母列作为表头。
布尔值 $setIndexSheetByName 用于通过工作表名称设置工作表索引或数组结果，如果工作表不止一个。
字符串 $getOnlySheet 是要获取数据的工作表名称。这仅获取具有相同名称的工作表。
数组|格式化器 $formatter 用于将模型属性值格式化为可显示文本的格式化器。这可以是 [[Formatter]] 的实例或创建 [[Formatter]] 实例的配置数组。如果未设置此属性，则将使用 "formatter" 应用程序组件。
安装
安装此扩展的首选方式是通过 composer。
执行以下操作
php composer.phar require --prefer-dist malyshev-soft/yii2-phpexcel "*"
或
"malyshev-soft/yii2-phpexcel": "*"
将其添加到您的 composer.json 文件的require部分。
用法
导出数据
将数据导出到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'] ], ]);
导出数据的新功能，如果您熟悉yii gridview，则可以使用此功能。与gridview数据列相同。数组模式的列有效参数是 'attribute', 'header', 'format', 'value', 和 footer (待定)。
<?php \moonland\phpexcel\Excel::export([ 'models' => Post::find()->all(), 'columns' => [ 'author.name:text:Author Name', [ 'attribute' => 'content', 'header' => 'Content Post', 'format' => 'text', 'value' => function($model) { return ExampleClass::removeText('example', $model->content); }, ], 'like_it:text:Reader like this content', 'created_at:datetime', [ 'attribute' => 'updated_at', 'format' => 'date', ], ], 'headers' => [ 'created_at' => 'Date Created Content', ], ]);
导入数据
导入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. ]);
结果示例请参考上面的代码
// only one sheet or specified sheet.
Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2));
// data with multiple worksheet
Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)), [Sheet2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)));
// data with multiple file and specified sheet or only one worksheet
Array([file1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)), [file2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)));
// data with multiple file and multiple worksheet
Array([file1] => Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)), [Sheet2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2))), [file2] => Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)), [Sheet2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2), [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2))));
待定
- 在导出数据时添加底部参数列。