PHPExcel导出yii2中的“Unreadable Content”Excel文件

Question

I used PHPExcel extension in Yii2 project and i have created component of PHPExcel object but this component export "Unreadable Content" Excel file.

I have read all possible solution for this issue from these links:

1. PHPExcel - .xlsx file downloads unreadable content
2. PHPExcel creates 'unreadable content'
3. https://stackoverflow.com/questions/32103447/excel-found-unreadable-content-with-phpexcel
4. PHPExcel unreadable content

I getting following "Unreadable content" Excel file :

And my code is,

component ExcelGrid.php :

<?php    
namespace app\components;

use Yii;
use Closure;
use yii\i18n\Formatter;
use yii\base\InvalidConfigException;
use yii\helpers\Url;
use yii\helpers\Html;
use yii\helpers\Json;
use yii\helpers\ArrayHelper;
use yii\widgets\BaseListView;
use yii\base\Model;
use \PHPExcel;
use \PHPExcel_IOFactory;
use \PHPExcel_Settings;
use \PHPExcel_Style_Fill;
use \PHPExcel_Writer_IWriter;
use \PHPExcel_Worksheet;

class ExcelGrid extends \yii\grid\GridView
{
    public $columns_array;
    public $properties;
    public $filename='excel';
    public $extension='xlsx';
    private $_provider;
    private $_visibleColumns;
    private $_beginRow = 1;
    private $_endRow;
    private $_endCol;
    private $_objPHPExcel;
    private $_objPHPExcelSheet;
    private $_objPHPExcelWriter;

    public function init(){
        parent::init();
    }

    public function run(){
        //$this->test();
        if (function_exists('mb_internal_encoding')) {
            $oldEncoding=mb_internal_encoding();
            mb_internal_encoding('utf8');
        }
        ob_start();
        $this->init_provider();
        $this->init_excel_sheet();
        $this->initPHPExcelWriter('Excel2007');
        $this->generateHeader();
        $row = $this->generateBody();
        $writer = $this->_objPHPExcelWriter;
        $this->setHttpHeaders();
        ob_end_clean();
        $writer->save('php://output');      
        if (function_exists('mb_internal_encoding'))
            mb_internal_encoding($oldEncoding); 
exit;
        Yii::$app->end();
        //$writer->save('test.xlsx');
        parent::run();
    }

    public function init_provider(){
        $this->_provider = clone($this->dataProvider);
    }
    public function init_excel_sheet(){
        $this->_objPHPExcel=new PHPExcel();
        $creator = '';
        $title = '';
        $subject = '';
        $description = 'Excel Grid';
        $category = '';
        $keywords = '';
        $manager = '';
        $created = date("Y-m-d H:i:s");
        $lastModifiedBy = '';
        extract($this->properties);
        $this->_objPHPExcel->getProperties()
        ->setCreator($creator)
        ->setTitle($title)
        ->setSubject($subject)
        ->setDescription($description)
        ->setCategory($category)
        ->setKeywords($keywords)
        ->setManager($manager)
        //->setCompany($company)
        ->setCreated($created)
        ->setLastModifiedBy($lastModifiedBy);
        $this->_objPHPExcelSheet = $this->_objPHPExcel->getActiveSheet();
    }
    public function initPHPExcelWriter($writer)
    {
        $this->_objPHPExcelWriter = PHPExcel_IOFactory::createWriter(
        $this->_objPHPExcel,
        $writer
        );
    }
    public function generateHeader(){
        $this->setVisibleColumns();
        $sheet = $this->_objPHPExcelSheet;
        $colFirst = self::columnName(1);
        $this->_endCol = 0;
        foreach ($this->_visibleColumns as $column) {
            $this->_endCol++;
            $head = ($column instanceof \yii\grid\DataColumn) ? $this->getColumnHeader($column) : $column->header;
            $cell = $sheet->setCellValue(self::columnName($this->_endCol) . $this->_beginRow, $head, true);
        }
        $sheet->freezePane($colFirst . ($this->_beginRow + 1));
    }

    public function generateBody()
    {
        $columns = $this->_visibleColumns;
        $models = array_values($this->_provider->getModels());
        if (count($columns) == 0) {
        $cell = $this->_objPHPExcelSheet->setCellValue('A1', $this->emptyText, true);
        $model = reset($models);
        return 0;
        }
        $keys = $this->_provider->getKeys();
        $this->_endRow = 0;
        foreach ($models as $index => $model) {
            $key = $keys[$index];
            $this->generateRow($model, $key, $index);
            $this->_endRow++;
        }
        // Set autofilter on
        $this->_objPHPExcelSheet->setAutoFilter(
        self::columnName(1) .
        $this->_beginRow .
        ":" .
        self::columnName($this->_endCol) .
        $this->_endRow
        );
        return ($this->_endRow > 0) ? count($models) : 0;
    }

    public function generateRow($model, $key, $index)
    {
        $cells = [];
        /* @var $column Column */
        $this->_endCol = 0;
        foreach ($this->_visibleColumns as $column) {
        if ($column instanceof \yii\grid\SerialColumn || $column instanceof \yii\grid\ActionColumn) {
            continue;
        } else {
            $format = $column->format;
            $value = ($column->content === null) ?
            $this->formatter->format($column->getDataCellValue($model, $key, $index), $format) :
            call_user_func($column->content, $model, $key, $index, $column);
        }
        if (empty($value) && !empty($column->attribute) && $column->attribute !== null) {
            $value =ArrayHelper::getValue($model, $column->attribute, '');
        }
        $this->_endCol++;
        $cell = $this->_objPHPExcelSheet->setCellValue(self::columnName($this->_endCol) . ($index + $this->_beginRow + 1),
        strip_tags($value), true);
        }
    }

    protected function setVisibleColumns()
    {
        $cols = [];
        foreach ($this->columns as $key => $column) {
            if ($column instanceof \yii\grid\SerialColumn || $column instanceof \yii\grid\ActionColumn) {
                continue;
            }
            $cols[] = $column;
        }
        $this->_visibleColumns = $cols;
    }

    public function getColumnHeader($col)
    {
        if(isset($this->columns_array[$col->attribute]))
            return $this->columns_array[$col->attribute];

        /* @var $model yii\base\Model */
        if ($col->header !== null || ($col->label === null && $col->attribute === null)) {
            return trim($col->header) !== '' ? $col->header : $col->grid->emptyCell;
        }
        $provider = $this->dataProvider;
        if ($col->label === null) {
            if ($provider instanceof ActiveDataProvider && $provider->query instanceof ActiveQueryInterface) {
            $model = new $provider->query->modelClass;
            $label = $model->getAttributeLabel($col->attribute);
            } else {
                $models = $provider->getModels();
                if (($model = reset($models)) instanceof Model) {
                $label = $model->getAttributeLabel($col->attribute);
                } else {
                $label =$col->attribute;
                }
            }
        } else {
            $label = $col->label;
        }
        return $label;
    }
    public static function columnName($index)
    {
        $i = $index - 1;
        if ($i >= 0 && $i < 26) {
            return chr(ord('A') + $i);
        }
        if ($i > 25) {
            return (self::columnName($i / 26)) . (self::columnName($i % 26 + 1));
        }
        return 'A';
    }

    protected function setHttpHeaders()
    {
        header("Cache-Control: no-cache");
        header("Pragma: no-cache");
        header("Content-Type: application/{$this->extension}; charset=utf-8");
        header("Content-Disposition: attachment; filename={$this->filename}.{$this->extension}");
        header("Expires: 0");
    }
}

View file countryExcel.php :

<?php

\app\components\ExcelGrid::widget([ 
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
     'extension'=>'xlsx',
     'filename'=>'country-list'.date('Y-m-dH:i:s'),
     'properties' =>[
     //'creator' =>'',
     //'title'  => '',
     //'subject'  => '',
     //'category' => '',
     //'keywords'  => '',
     //'manager'  => '',
     ],
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],

        'country_name',
        [
            'attribute' => 'created_at',
            'value' => function ($data) {
                return Yii::$app->formatter->asDateTime($data->created_at);
            },
        ],
        [
            'attribute' => 'created_by',
            'value' => 'createdBy.user_login_id',
        ],
        [
            'attribute' => 'updated_at',
            'value' => function ($data) {
                return (!empty($data->updated_at) ? Yii::$app->formatter->asDateTime($data->updated_at) : Yii::t('stu', ' (not set) '));
            },
        ],
        [
            'attribute' => 'updated_by',
            'value' => 'updatedBy.user_login_id',
        ],
    ],
]);

?>

Controller File CountryController.php :

<?php

class CountryController extends Controller
{

    .....
    .......

    public function actionExcel()
    {
        $searchModel = new CountrySearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->renderPartial('CountryExportExcel', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }

    ........
    .....
}

?>

Thanks in Advance.

Answer 1

Your file seems a correct xlsx file opened with an editor like notepad++ could be a charcter encoding problem

Try change the encode

    if (function_exists('mb_internal_encoding')) {
        $oldEncoding=mb_internal_encoding();
        mb_internal_encoding('utf8');
    }

using a value for cp1250 -- to cp1258

encoding for microsoft in US-EN
(I hope your excel country installation config )