Excel export related data

Yii 2.0 General Discussions
1

I’ve got a function that exports data from a grid view to excel using phpExcel. It works fine if I don’t export table related data. But I can’t get it to work with related data.

What I have right now:

In model AppProcessos:




    public function getMyramo()

    {

        return $this->hasOne(AppRamos::className(), ['id' => 'ramo']);

    }

In model AppProcessosSearch:





public $myramo;

....

$query->joinWith(['myramo']);

...

$query->andFilterWhere(['like', 'app_ramos.nome', $this->myramo]);

In Controller:




    public function actionExcel()

    {

        $searchModel = new AppProcessosSearch();

        $dataProvider = $searchModel->search(Yii::$app->session->get('AppProcessos' . '_filters'));

        $dataProvider->setSort(Yii::$app->session->get('AppProcessos' . '_sort'));

        utils::toExcel($dataProvider, ['numprocesso','myramo.nome']); <-This is the problem. myramo.nome is not recognized

    }

In class utils:




public static function toExcel($model, $campos)

{


    $model->setPagination(false);

    $objPHPExcel = new \PHPExcel();

    $objPHPExcel->getProperties()->setTitle("Exportação de dados do Sistema")

        ->setSubject("Exportação de dados do Sistema")

        ->setDescription("Exportação de dados do Sistema")

        ->setKeywords("Exportação de dados do Sistema")

        ->setCategory("Skeyra");


    $objPHPExcel->setActiveSheetIndex(0);


    if ($model->getCount() > 0): 

        $col = 0;

        foreach ($campos as $coluna) {


            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $model->getModels()[0]->getAttributeLabel($coluna));

            $col++;

        }

        $lin = 2;

    endif;


    //foreach ($model->getModels() as $record) {

    foreach ($model->models as $record) {

        $col = 0;

        foreach ($campos as $coluna) {

            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $lin, $record->$coluna);

            $col++;

        }

        $lin++;

    }


    $objPHPExcel->getActiveSheet()->setTitle('Dados');

    $objPHPExcel->setActiveSheetIndex(0);

    // Redirect output to a client’s web browser (Excel2007)

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    header('Content-Disposition: attachment;filename="' . 'sk_export' . '.xlsx"');

    header('Cache-Control: max-age=0');


    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    $objWriter->save('php://output');

}

Can anyone tell me please how to export myramo.nome?

2

In this part of the program, "myramo.nome" will not be treated as you expect, because "$coluna" will be "myramo.nome" which is not an attribute name of your model.

3

You’re right of course.

Do you have any idea on how to achieve the same result with some other approach?

4

ArrayHelper::getValue() may be a solution.




    foreach ($model->models as $record) {

        $col = 0;

        foreach ($campos as $coluna) {

            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $lin, ArrayHelper::getValue($record, $coluna));

            $col++;

        }

        $lin++;

    }

Not tested, though.

http://www.yiiframework.com/doc-2.0/guide-helper-array.html#getting-values

5

It works!! That’s it.

Arrayhelper does the trick.

I would never remember to use arrayhelper.

Thank you softark!