PHPExcel export “Unreadable Content” Excel File

[color="#222222"][font="Arial,"][size="2"]I used PHPExcel extension in Yii2 project and i have created component of PHPExcel object but this component export "Unreadable Content" Excel file.[/size][/font][/color]

[color="#222222"][font="Arial,"][size="2"]I have read all possible solution for this issue from these links:[/size][/font][/color]

  • PHPExcel - .xlsx file downloads unreadable content
  • PHPExcel creates ‘unreadable content’
  • Excel found unreadable content with PHPEXcel
  • PHPExcel unreadable content

[color="#222222"][font="Arial,"]I getting following "Unreadable content" Excel file :[/font][/color]

[font="Arial"] [/font][color="#222222"][font="Arial,"]

[/font][/color]

[color="#222222"][font="Arial,"]

[/font][/color]

[color="#222222"][font="Arial,"][size="2"]And my code is,[/size][/font][/color]

[color="#222222"][font="Arial,"][size="2"]component ExcelGrid.php :[/size][/font][/color]





    <?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.

"Content-Type" for excel 2007 should be "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".

I already used this header content-type, but not working.

Try removing the last closing php tag ("?>") in your countryExcel.php.

I have try this but not working.

Sorry, I have no idea.

Try open the output with a normal text editor, and you’ll probably see what’s going on, I hope.