Export a search results to Excel

Hello there!

I’m trying to get a simple Excel output from a view/model/admin filtered query.

I have put on _search.php an additional button:




echo CHtml::submitButton( 'Export to Excel', array( 'name' => 'export' ) );

So I’m trying to catch it on controller actionAdmin():




	public function actionAdmin()

	{

		$model = new Invoice('search');

		$model->unsetAttributes();


		if( isset( $_GET['Invoice'] ) )

			$model->attributes = $_GET['Invoice'];


		if( isset( $_GET[ 'export' ] ) )

		{

			header( "Content-Type: application/vnd.ms-excel; charset=utf-8" );

			header( "Content-Disposition: inline; filename=\"test.xls\"" );


			$dataProvider = $model->search();

			$dataProvider->pagination = False;


            foreach( $dataProvider->data as $data )

            	echo $data->class, "\t", $data->branch, "\t", $data->number, "\t", $data->total, "\n";


			Yii::app()->end();

		}


		$this->render('admin',array(

			'model'=>$model,

		));

	}

The problem seems to be at [font=“Courier New”]if( isset( $_GET[ ‘export’ ] ) )[/font] 'cos it never download when you press the Export submit button… or I’m troubling with an ajax subjacent request from search-form?

Thanks for any clue,

cbi

The form shown in the search box is submitted via ajax, as defined by this snippet of JavaScript at the end of a similar page:


$('.search-form form').submit(function(){

	$.fn.yiiGridView.update('user-grid', {

		data: $(this).serialize()

	});

	return false;

});

What you could do is place either a link (or any other clickable element that doesn’t submit the form) which opens the page in a new window at this URL: youryiiapp.local/controller/admin?export=1

That way, the headers would be set correctly, and your Excel file should download

Right! But this will take in account the parameters of the query (sorting and other search parameters) ??

Thank you,

cbi

OK, in which case scrap what I said before. The quickest way to test that your excel is being generated correctly is to pass


'ajaxUpdate'=>false

when creating your CGridView.

This will prevent the ajax update of the grid, so when you submit your form, the page will reload. This has the advantage of the above solution, allowing the headers to be set correctly, as well as passing the filtering parameters.

This solve one problem…: why the enigmatic $_GET[ ‘export’ ] comes empty. Now, for the complete solution and to keep ajax search working I workaround these steps:

[list=1]

[*] change Yii CRUD scaffolding at [font="Courier New"]views/<model_name>/admin.php[/font] from:


$('.search-form form').submit(function(){

	$.fn.yiiGridView.update('some-grid', {

		data: $(this).serialize()

	});

	return false;

}); 

to:




$('#submit-button').click(function(){ 

	$.fn.yiiGridView.update('some-grid', {

		data: $('.search-form form').serialize()

	});

	return false;

});

[*] at [font="Courier New"]views/<model_name>/_search.php[/font] add an [font="Courier New"]id[/font] to the Search submit button element, like:




<?php echo CHtml::submitButton('Search', array( 'id' => 'submit-button' )); ?>

notice it must match with JS selector on step 1.

[*] add an export button, to catch it up and process the report file:




<?php echo CHtml::submitButton( 'Exportar', array( 'name' => 'export' ) ); ?>

[*] on the Controller, now [font=“Courier New”]actionAdmin()[/font] will recieve the request, and if ‘export’ button was pressed, will export all record that fulfill the query:




	public function actionAdmin()

	{

		$model = new Invoice('search');

		$model->unsetAttributes();


		if( isset( $_GET['Invoice'] ) )

			$model->attributes = $_GET['Invoice'];

			

		if( isset( $_GET[ 'export' ] ) )

		{

			header( "Content-Type: application/vnd.ms-excel; charset=utf-8" );

			header( "Content-Disposition: inline; filename=\"test.xls\"" );


			$dataProvider = $model->search();

			$dataProvider->pagination = False;


            foreach( $dataProvider->data as $data )

            	echo $data->class, "\t", $data->branch, "\t", $data->number, "\t", $data->total, "\n";

            	

			Yii::app()->end();

		}


		$this->render('admin',array(

			'model'=>$model,

		));

	}



[/list]

Thank you georgebuckingham !

cbi

hello cbi,

did u use phpexcel for this??

Hi mithila,

no, I didn’t. I just change the header and dump csv data:




	header( "Content-Type: application/vnd.ms-excel; charset=utf-8" );

	header( "Content-Disposition: inline; filename=\"test.xls\"" );


	foreach( $dataProvider->data as $data )

		echo $data->class, "\t", $data->branch, "\t", $data->number, "\t", $data->total, "\n";


	Yii::app()->end();

Notice that $dataProvider is the return value from the search() method of model class.

cbi

Thanks for replying cbi.

Your code worked for me but the problem is that it exports everything from the grid into just one cell of the excel. Also i want only what is filtered to be exporteded and not all the data Can u please help me with this…

This is my controlller code


public function actionGenexpur()

	{ $model=new Purchases('search'); 

$model->unsetAttributes();  

		

	  header( "Content-Type: application/vnd.ms-excel; charset=utf-8" ); 

                       header( "Content-Disposition: inline; filename=\"test.xls\"" ); 

 

                        $dataProvider = $model->search1(); 

                      $dataProvider->pagination = False; 


            foreach( $dataProvider->data as $data ) 

                echo $data->invoice_no, "\t", $data->date1, "\t", $data->purchase_id, "\t", $data->quantity,"\n"; 

                 

				                          Yii::app()->end(); 

						

	}

What Excel/spreadsheet version have you installed?

You can try with:


header( "Content-Disposition: inline; filename=\"test.csv\"" );



Did you notice that you call $model->search1() instead, perhaps, $model->search() ??

Best,

cbi

I tried with test.csv. Now each row is exported to an individual row but all the columns are coming in one cell still. :( I’m using Excel 2007. Another problem is that the entire grid gets exported where as i just want the filtered values to get exported.

Thanks again.

Try to replace with a comma instead of "\t".

Tried that, same result :(

Perhaps, Windows configuration expects a semicolon [;] instead of "\t" ?? Or you need to enclose within double-quote characters. It may help you: http://en.wikipedia.org/wiki/Comma-separated_values

ps: BTW, I use MS-Office 2003, spanish version and "\t" & "\n" works fine.

Thanks for posting your solution to this task, cbi! It works perfectly.