1. 导出excel的实现方法
（1）使用phpexcel封装工具类导出excel
（2）使用爬虫爬取页面再处理封装工具类导出excel
（3）使用页面渲染后处理html添加头部信息生成excel文件的js导出
（4）使用GridView视图组件自带的导出功能
2.代码实现（使用GridView视图组件自带的导出功能）
<?= kartik\grid\GridView::widget([
'tableOptions' => ['class' => 'table table-striped', 'style'=>'font-size:12px;'],
'layout' => "<div class=\"pull-left div_title\" >库存盘点清单</div><div class=\"pull-right\">{toolbar}</div><div class=\"clearfix\"></div>{items}",
'export'=>[
'label'=>'导出',
'target'=>kartik\grid\GridView::TARGET_BLANK,
],
'exportConfig'=>[
\kartik\grid\GridView::EXCEL => [
'label' => Yii::t('app', '导出Excel'),
'icon' =>'file-excel-o',
'iconOptions' => ['class' => 'text-success'],
'showHeader' => true,
'showPageSummary' => true,
'showFooter' => true,
'showCaption' => true,
'filename' => Yii::t('app', '库存盘点清单'),
'alertMsg' => Yii::t('app', '将生成并下载Excel文件'),
'options' => ['title' => Yii::t('app', 'Microsoft Excel 95+')],
'mime' => 'application/vnd.ms-excel',
'config' => [
'worksheet' => Yii::t('app', '库存盘点清单'),
'cssFile' => ''
]
],
],
'striped'=>false,
'hover'=>true,
'showHeader'=>true,
'showFooter'=>false,
'showPageSummary' => false,
'showOnEmpty'=>true,
'emptyText'=>'当前没有数据！',
'emptyTextOptions'=>['style'=>'color:red;font-weight:bold;text-align:center;'],
'dataProvider' => $dataProvider,
'columns' => $columns,
]); ?>
优点：
（1）代码实现简单，只需在GridView中配置即可
（2）导出的Excel文件格式与GridView相同，包括对齐方式，自动实现统计导出
缺点：
（1）导出按钮的放置位置不方便调整，只能放置在紧贴GridView的位置
3.代码实战（复用searchModel进行数据查询，并使用phpexcel封装工具类导出excel）
（1）封装的Excel导出工具类代码如下：
<?php
namespace core\components;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Style_Alignment;
use PHPExcel_Reader_Excel5;
use PHPExcel_RichText;
class MyExcelHelper extends \yii\base\Component{
/**
* 将二维数组的数据转化为excel表格导出
* @param $data
* @param $excel_name
* @param $headers
* @param $options
*/
public static function array2excel($data, $excel_name, $headers, $options, $style_options){
$objPHPExcel = new PHPExcel();
ob_start();
if (!isset($options['creator'])){
$objPHPExcel->getProperties()->setCreator('creator');
}else{
$objPHPExcel->getProperties()->setCreator($options['creator']);
}
if (isset($options['last_modified_by'])){
$objPHPExcel->getProperties()->setCreator('last_modified_by');
}else{
$objPHPExcel->getProperties()->setCreator($options['last_modified_by']);
}
if (isset($options['title'])){
$objPHPExcel->getProperties()->setCreator('title');
}else{
$objPHPExcel->getProperties()->setCreator($options['title']);
}
if (isset($options['subject'])){
$objPHPExcel->getProperties()->setCreator('subject');
}else{
$objPHPExcel->getProperties()->setCreator($options['subject']);
}
if (isset($options['description'])){
$objPHPExcel->getProperties()->setCreator('description');
}else{
$objPHPExcel->getProperties()->setCreator($options['description']);
}
if (isset($options['keywords'])){
$objPHPExcel->getProperties()->setCreator('keywords');
}else{
$objPHPExcel->getProperties()->setCreator($options['keywords']);
}
if (isset($options['category'])){
$objPHPExcel->getProperties()->setCreator('category');
}else{
$objPHPExcel->getProperties()->setCreator($options['category']);
}
$header_keys = array_keys($headers);
foreach ($header_keys as $header_index => $header_key){
$index_ascii = $header_index + 65;
$index_chr = chr($index_ascii);
$header_value = $headers[$header_key];
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($index_chr.'1', $header_value);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($index_chr)->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getStyle($index_chr.'1')->applyFromArray([
'font'=>[
'bold' => true
],
'alignment'=>[
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
]
]);
if (isset($style_options['h_align'][$header_key])){
if ($style_options['h_align'][$header_key] == 'left'){
$h_align = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
}elseif ($style_options['h_align'][$header_key] == 'center'){
$h_align = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
}elseif ($style_options['h_align'][$header_key] == 'right'){
$h_align = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
}else{
$h_align = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
}
$objPHPExcel->setActiveSheetIndex(0)->getStyle($index_chr)->applyFromArray([
'alignment'=>[
'horizontal' => $h_align
]
]);
}
}
$data_row_index = 2;
foreach ($data as $row_index => $row){
$data_keys = array_keys($row);
foreach ($data_keys as $column_index => $data_key){
if ($column_index>=26){
throw new \yii\base\Exception('EXCEL表格超过26列');
}
$index_ascii = $column_index + 65;
$index_chr = chr($index_ascii);
$value = $row[$data_key];
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($index_chr . $data_row_index, $value);
}
$data_row_index++;
}
if (isset($options['summary'])){
$summary_keys = array_keys($options['summary']);
foreach ($summary_keys as $summary_index => $summary_key){
$index_ascii = $summary_index + 65;
$index_chr = chr($index_ascii);
$summary_flag = $options['summary'][$summary_key];
if ($summary_flag){
$summary_value = \core\components\ArrayHelper::sumByColumn($data, $summary_key);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($index_chr . $data_row_index, $summary_value);
}
}
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
ob_end_clean();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $excel_name . '.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit;
}
/**
* 将excel表格转化为二维数组的数据
* @param $excel_path
* @param $data
* @param $header
*/
public static function excel2array($excel_path, $header_keys){
if(!file_exists($excel_path)){
throw new \yii\base\Exception('该EXCEL不存在！');
}
$PHPReader = new \PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($excel_path)){
$PHPReader = new PHPExcel_Reader_Excel5();
if(!$PHPReader->canRead($excel_path)){
throw new \yii\base\Exception('该EXCEL不可读');
}
}
$PHPExcel = $PHPReader->load($excel_path);
$currentSheet = $PHPExcel->getSheet(0);
$max_column_index = $currentSheet->getHighestColumn();
$max_row_index = $currentSheet->getHighestRow();
$data = array();
for($row_index=2; $row_index<=$max_row_index; $row_index++ ){
for($column_chr='A'; $column_chr<=$max_column_index; $column_chr++){
$column_ord = ord($column_chr);
$column_index = $column_ord - 65;
$key = $column_chr.$row_index;
$value = $currentSheet->getCell($key)->getValue();
if($value instanceof PHPExcel_RichText){
$value = $value->__toString();
}
$data[$row_index-1][$header_keys[$column_index]] = $value;
}
}
return $data;
}
}
（2）导出业务逻辑类：
<?php
namespace backend\models;
use Yii;
class SheetExportAdapter{
//在适配器中引用数据查询模型
public $searchModel;
//构造方法，传入数据查询模型，引用进行数据的查询
public function __construct($searchModel)
{
$this->searchModel = $searchModel;
}
/**
* 导出业务逻辑接口
*/
public function export(){}
}
class WmsPartiallyProductInSheetExport extends SheetExportAdapter
{
/**
* 导出类的业务逻辑实现，先从搜索模型中获取参数，再调用搜索模型进行数据查询，最后调用导出工具类进行导出
*/
public function export(){
$params = [];
$params['WmsPartiallyProductInSheetSearch']['search_type'] = $this->searchModel->search_type;
$params['WmsPartiallyProductInSheetSearch']['common_producer_info_id'] = $this->searchModel->common_producer_info_id;
$params['WmsPartiallyProductInSheetSearch']['common_producer_herb_info_id_product'] = $this->searchModel->common_producer_herb_info_id_product;
$params['WmsPartiallyProductInSheetSearch']['begin_at'] = $this->searchModel->begin_at;
$params['WmsPartiallyProductInSheetSearch']['end_at'] = $this->searchModel->end_at;
$dataProvider = $this->searchModel->search($params, true);
$data = [];
foreach ($dataProvider->getModels() as $model){
$wms_partially_product_in_sheet_number = $model->wms_partially_product_in_sheet_number;
if ($model->is_del == 1) {
$wms_partially_product_in_sheet_number .= '('.$model->stock_origin_type.')(已作废)';
}else{
$wms_partially_product_in_sheet_number .= '('.$model->stock_origin_type.')';
}
$common_producer_herb_info_name_product = $model->common_producer_herb_info_name_product;
$common_producer_herb_grade_name_product = $model->common_producer_herb_grade_name_product;
$common_producer_herb_place_info_name = $model->common_producer_herb_place_info_name;
if (\core\models\WmsManager::PIECE_TYPE_STANDARD == $model->piece_type){
$piece_type_name = '标准件';
}elseif(\core\models\WmsManager::PIECE_TYPE_OFF_STANDARD == $model->piece_type){
$piece_type_name = '非标准件';
}else{
$piece_type_name = '未设置';
}
if (!\core\models\WmsManager::getIsShowWeightPerPackage($model->piece_type)){
$wms_partially_product_in_sheet_weight_per_package = '无';
}else{
$wms_partially_product_in_sheet_weight_per_package = \common\models\Base::weightBcdiv($model->wms_partially_product_in_sheet_weight_per_package);
}
if (empty($model->wms_partially_product_in_sheet_package_number)){
$wms_partially_product_in_sheet_package_number = '未设置';
}else{
$wms_partially_product_in_sheet_package_number = \core\models\WmsManager::showTotalPackageNumber($model->wms_partially_product_in_sheet_package_number, $model->standard_package_number, $model->off_standard_package_number, $model->piece_type);
}
$wms_partially_product_in_sheet_in_weight = \common\models\Base::weightBcdiv($model->wms_partially_product_in_sheet_in_weight);
$modelDetail = \core\models\WmsStockDetailInfo::findNotDel()->where([
'wms_stock_detail_info_relation_good_in_sheet_number' => $model->wms_partially_product_in_sheet_number,
'common_producer_material_type_info_id' => \core\models\WmsStockDetailInfo::wmsMaterialType()['partiallyProductType']])->one();
$surplus_weight = $modelDetail&&$modelDetail->wms_stock_detail_info_weight?\common\models\Base::weightBcdiv($modelDetail->wms_stock_detail_info_weight):0;
if (empty($model->wms_partially_product_in_sheet_product_in_date)){
$wms_partially_product_in_sheet_product_in_date = '未知';
}else{
$wms_partially_product_in_sheet_product_in_date = date('Y-m-d', $model->wms_partially_product_in_sheet_product_in_date);
}
$wms_partially_product_in_sheet_status_name = (!$model->wms_partially_product_in_sheet_status||$model->wms_partially_product_in_sheet_status==0)?'未确认入库':"已入库";
$wmsPartiallyProductInSheetQualityCheckNumber = $model->getWmsPartiallyProductInSheetQualityCheckNumber();
if ('<span style="color: red;">未质检</span>' == $wmsPartiallyProductInSheetQualityCheckNumber){
$wmsPartiallyProductInSheetQualityCheckNumber = '未质检';
}
$data[] = [
'wms_partially_product_in_sheet_number'=>$wms_partially_product_in_sheet_number,
'common_producer_herb_info_name_product'=>$common_producer_herb_info_name_product,
'common_producer_herb_grade_name_product'=>$common_producer_herb_grade_name_product,
'common_producer_herb_place_info_name'=>$common_producer_herb_place_info_name,
'piece_type_name'=>$piece_type_name,
'wms_partially_product_in_sheet_weight_per_package'=>$wms_partially_product_in_sheet_weight_per_package,
'wms_partially_product_in_sheet_package_number'=>$wms_partially_product_in_sheet_package_number,
'wms_partially_product_in_sheet_in_weight'=>$wms_partially_product_in_sheet_in_weight,
'surplus_weight'=>$surplus_weight,
'wms_partially_product_in_sheet_product_in_date'=>$wms_partially_product_in_sheet_product_in_date,
'wms_partially_product_in_sheet_status_name'=>$wms_partially_product_in_sheet_status_name,
'wmsPartiallyProductInSheetQualityCheckNumber'=>$wmsPartiallyProductInSheetQualityCheckNumber,
];
}
$excel_name = '半成品入库单';
$headers = [
'wms_partially_product_in_sheet_number'=>'入库单号',
'common_producer_herb_info_name_product'=>'半成品名称',
'common_producer_herb_grade_name_product'=>'半成品等级',
'common_producer_herb_place_info_name'=>'半成品产地',
'piece_type_name'=>'计件类型',
'wms_partially_product_in_sheet_weight_per_package'=>'包装规格',
'wms_partially_product_in_sheet_package_number'=>'入库件数',
'wms_partially_product_in_sheet_in_weight'=>'入库重量(公斤)',
'surplus_weight'=>'剩余重量(公斤)',
'wms_partially_product_in_sheet_product_in_date'=>'入库日期',
'wms_partially_product_in_sheet_status_name'=>'入库状态',
'wmsPartiallyProductInSheetQualityCheckNumber'=>'质检号',
];
$options = [
'creator'=>'中国汉广集团IT信息中心',
'last_modified_by'=>'中国汉广集团IT信息中心',
'title'=>$excel_name,
'subject'=>$excel_name,
'description'=>'半成品入库单',
'keywords'=>'半成品入库单',
'category'=>'半成品入库单',
'summary'=>[
'wms_partially_product_in_sheet_number'=>false,
'common_producer_herb_info_name_product'=>false,
'common_producer_herb_grade_name_product'=>false,
'common_producer_herb_place_info_name'=>false,
'piece_type_name'=>false,
'wms_partially_product_in_sheet_weight_per_package'=>false,
'wms_partially_product_in_sheet_package_number'=>true,
'wms_partially_product_in_sheet_in_weight'=>true,
'surplus_weight'=>true,
'wms_partially_product_in_sheet_product_in_date'=>false,
'wms_partially_product_in_sheet_status_name'=>false,
'wmsPartiallyProductInSheetQualityCheckNumber'=>false,
]
];
$style_options = [
'h_align'=>[
'wms_partially_product_in_sheet_number'=>'left',
'common_producer_herb_info_name_product'=>'center',
'common_producer_herb_grade_name_product'=>'center',
'common_producer_herb_place_info_name'=>'center',
'piece_type_name'=>'center',
'wms_partially_product_in_sheet_weight_per_package'=>'right',
'wms_partially_product_in_sheet_package_number'=>'right',
'wms_partially_product_in_sheet_in_weight'=>'right',
'surplus_weight'=>'right',
'wms_partially_product_in_sheet_product_in_date'=>'center',
'wms_partially_product_in_sheet_status_name'=>'center',
'wmsPartiallyProductInSheetQualityCheckNumber'=>'center',
]
];
//调用导出工具类进行导出
\core\components\MyExcelHelper::array2excel($data, $excel_name, $headers, $options, $style_options);
}
}
（3）控制器行为：
public function actionExport($serialized_model){
$wmsPartiallyProductInSheetSearch = unserialize($serialized_model);
$wmsPartiallyProductInSheetExport = new \backend\models\WmsPartiallyProductInSheetExport($wmsPartiallyProductInSheetSearch);
$wmsPartiallyProductInSheetExport->export();
}
优点：
（1）自己实现导出工具类，容易掌控代码，实现了封装的思想
（2）使用导出适配器，进一步封装了导出逻辑