Export gridview data to csv

Yii 2.0 General Discussions
1

Hello,

i will export data from a gridview to csv. I wrote a action in my controller:

    public function actionCsvexport(){       
        $searchModel = new ScanPortsSearch();
        header('Content-type: text/csv');
        header('Content-Disposition: attachment; filename="scan-report-' . date('YmdHi') .'.csv"');

        $dataProvider = $searchModel->search($this->request->queryParams);
     
        //csv header
        echo $searchModel->getAttributeLabel("port").";". $searchModel->getAttributeLabel("proto").";". $searchModel->getAttributeLabel("status")." \r\n";
        $da=$dataProvider->query->all();
         foreach ($da as $data) {
            echo "$data->port;$data->proto;$data->status \r\n";
        }
           exit;   
    }

It works fine. If if filtered data in gridview, so the export show all filtered rows. But the sort not works. I call the export-action with a Button on my view. i give them all parameters from view like

ScanPortsSearch[port]=&ScanPortsSearch[proto]=&ScanPortsSearch[service]=&ScanPortsSearch[h_id]=276&id=55&sort=-port

Anybody a idea?

2

Specify $dataProvider->sort->attributes

3

Thank you. I’ve do this with

        $dataProvider->sort->attributes=['port'=>SORT_ASC];

But there is no change.

The other problem i’ve seen, is that the join data from the search model also not found in the data array after i called

$dataProvider->query->all();

Only the data from the ports table will read in the data array. This is the model search function

    public function search($params)
    {
        $query = ScanPorts::find()
        ->select(['ports.*','hosts.hostname', 'hosts.ip AS hostip'])
        ->innerJoin('hosts', 'ports.h_id = hosts.id')
        ->where(['ports.s_id'=>$_GET['id']]);

        // add conditions that should always apply here
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort' => ['attributes' => ['port','hosts.hostname']],
            'pagination' => [
                'pageSize' => Yii::$app->SysFunc->getSystemField('maxrowsgrid'),
            ],
        ]);

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            'id' => $this->id,
            'h_id' => $this->h_id,
        ]);

        $query->andFilterWhere(['like', 'port', $this->port])
            ->andFilterWhere(['like', 'proto', $this->proto])
            ->andFilterWhere(['like', 'service', $this->service])
            ->andFilterWhere(['like', 'h_id', $this->h_id]);

        return $dataProvider;
    }
4

I’m still learning this (Yii2) but i think the first example in this section, with(orders) will bring in the rest of the related columns.
https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#joining-with-relations

Here is what I found out:
Sorting attributes from the related table may need something like this (attribute declared in search class in my case)

		$dataProvider->sort->attributes['attribute'] = [
			'asc' =>    ['table.column' => SORT_ASC],
			'desc' =>   ['table.column' => SORT_DESC],
		];

I only used $query->joinWith('relation'); so perhaps lazy loading in my case.

A computed field from my select statement

		$dataProvider->sort->attributes['vhid'] = [
			'asc' =>    ['vhid' => SORT_ASC],
			'desc' =>   ['vhid' => SORT_DESC],
		];

I don’t know why sorting on port didn’t work (you already have it declared in sort->attributes).

5

Thank you for your answer.

Now i found a solution.

First i set the pagination to false. Like

$dataProvider->pagination->pagesize=false;

Then i don’t use

$dataProvider->query->all()

but

$dataProvider->getModels();

Now the array contains all records, with filtering and sorting.

6

Dear Sascha,

I got more flexible way if our model has relation, it is the complete action:

    public function actionCsvexport(){       
        
        $searchModel = new OrganizationWallLocationSearch();
        
        header('Content-type: text/csv');
        header('Content-Disposition: attachment; filename="Wall-Location-Report-' . date('YmdHi') .'.csv"');

        $dataProvider = $searchModel->search($this->request->queryParams);
        
        $dataProvider->pagination->pagesize=false;
     
        //csv header
        echo 
        $searchModel->getAttributeLabel("no.").";". 
        $searchModel->getAttributeLabel("organization_id").";". 
        $searchModel->getAttributeLabel("organization").";". 
        $searchModel->getAttributeLabel("location_id").";". 
        $searchModel->getAttributeLabel("address").";". 
        $searchModel->getAttributeLabel("contact_person").";". 
        $searchModel->getAttributeLabel("role").";". 
        $searchModel->getAttributeLabel("phone").";". 
        $searchModel->getAttributeLabel("email").";". 
        $searchModel->getAttributeLabel("status")." \r\n";
        
        $da = $dataProvider->getModels();
        
        $no = 0;
        foreach ($da as $key => $data) {
            $no++;
            echo "".$no.";";
            echo "".$data->organization_id.";";
            echo "".$data->organization->name.";";
            echo "".$data->id.";";
            echo "".$data->location.";";
            echo "".$data->organization->contactPerson->name.";";
            echo "".$data->organization->contactPerson->appointment.";";
            echo "".$data->organization->telephone.";";
            echo "".$data->organization->email.";";
            echo "$data->status \r\n";
        }
           exit;   
    }

Hope my code above will help you all significantly, especially due to most of export extensions no longer available for yii2.

7

all() ignores pagination but should still respect sorting if applied correctly.

Use:

$da = $dataProvider->getModels();

instead of:

$da = $dataProvider->query->all();

Because getModels() uses the ActiveDataProvider sorting/filtering configuration automatically.