rickeryu/phpspread

将PHP导出到Excel或将Excel导入PHP

维护者

详细信息

github.com/rickerzy/yii2-phpspread

源代码

问题

安装次数: 0

依赖关系: 0

建议者: 0

安全性: 0

星标: 0

关注者: 1

分支: 0

公开问题: 0

类型:yii2-extension

1.4.2 2018-10-17 06:23 UTC

Requires

MIT 2d4961173941ed45738446d27d764a7f100592df

  • Moh Khoirul Anam <moh.khoirul.anaam.woop@gmail.com>
  • rickeryu <ricker.zy.woop@gmail.com>

excelxlsimportextensionexportyii2

This package is not auto-updated.

Last update: 2024-09-15 08:55:27 UTC

README

基于项目moonlandsoft/yii2-phpexcel并合并了pull request；

将PHP导出到Excel或将Excel导入PHP。用于生成或加载Excel文件的Excel小部件。

属性

字符串 $mode 是导出模式或导入模式。有效值是 'export' 和 'import'。

布尔值 $isMultipleSheet 用于设置带有多个工作表的导出Excel。

数组 $properties 用于设置Excel对象的属性。

数组 $models Model对象或DataProvider对象，包含大量数据。

数组 $columns 从模型中获取属性，此有效值仅为模型上存在的属性。如果未设置，则将模型的所有属性都设置为列。

数组 $headers 用于设置第一行的标题列。如果想要自定义标题，则设置此值。如果未设置，则标题将获取模型属性的标签。

字符串|数组 $fileName 是导出或导入的文件名。多个文件名仅适用于导入模式，在导出模式下不适用。

字符串 $savePath 是保存文件的目录，或者您可以留空以将文件设置为附件。

字符串 $format 用于导出的Excel。有效值是 'Excel5', 'Excel2007', 'Excel2003XML', '00Calc', 'Gnumeric'。

布尔值 $setFirstTitle 用于设置第一行的标题列。列将在第一行上有标题。

布尔值 $asAttachment 用于将Excel文件设置为下载模式。

布尔值 $setFirstRecordAsKeys 用于将Excel文件的第一条记录设置为每行数组的键。如果想要设置具有第一条记录的记录列的键，如果未设置，则使用Excel的字母列作为标题。

布尔值 $setIndexSheetByName 用于通过工作表名称或数组结果设置工作表索引（如果工作表不止一个）。

字符串 $getOnlySheet 是要获取数据的工作表名称。这只会获取具有相同名称的工作表。

数组|格式化器 $formatter 用于将模型属性值格式化为可显示文本的格式化器。这可以是 [[Formatter]] 实例或用于创建 [[Formatter]] 实例的配置数组。如果未设置此属性，则将使用 "formatter" 应用程序组件。

安装

安装此扩展的首选方式是通过composer。

运行以下命令之一：

php composer.phar require --prefer-dist moonlandsoft/yii2-phpexcel "*"

或

"moonlandsoft/yii2-phpexcel": "*"

将以下内容添加到您的 composer.json 文件的要求部分。

用法

导出数据

将数据导出到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' 和页脚（TODO）。字符串模式中的有效布局是 'attribute:format:header:footer(TODO)'。

<?php
  
\moonland\phpexcel\Excel::export([
   	'models' => Post::find()->all(),
      	'columns' => [
      		'author.name:text:Author Name',
      		[
      				'attribute' => 'content',
      				'header' => 'Content Post',
      				'format' => 'text',
      				'width' => 10, //设置表格宽度
      				'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))));

TODO

  • 为导出数据中的列添加页脚参数。