Export report to Excel

Hey,

I want to export report to Excel. How to do that?

http://www.yiiframework.com/extension/phpexcel/

Thank you. One question, how to remove menu bar when I export data to Excel? I don’t want menu bar on the top when export report to Excel.

If you want a simple lightweight export capability then try this class I created which exports a CActiveRecord data set to xlsx format

amc




class ExcelExporter {


    const CRLF = "\r\n";


    /**

     * Output active record resultset to an xml based excel file

     *

     * @param $data - active record data set

     * @param $header - boolean show/hide header

     * @param $filename - name of output filename

     */

    public static function sendAsXLS($data, $header, $filename) {

        $export = self::xls($data, $header);

        self::sendHeader($filename, strlen($export), 'vnd.ms-excel');

        echo $export;

    }


   

     * Send file header 

     * 

     * @param $filename - filename for created file

     * @param $length - size of file

     * @param $type - mime type of exported data

     */

    private static function sendHeader($filename, $length, $type='octet-stream') {

        header("Content-type: application/$type");

        header("Content-Disposition: attachment; filename=$filename");

        header("Content-length: $length");

        header("Pragma: no-cache");

        header("Expires: 0");

    }


  

    /**

     * Private method to create xls string from active record data set

     *

     * @param  $data - active record data set

     * @param  $header - boolean to show/hide header

     */

    private static function xls($data, $header) {

        $str = "<?xml version='1.0' encoding='utf-8'?>" . self::CRLF .

               "<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>" . self::CRLF .

               "<ss:Worksheet ss:Name='Sheet1'>" . self::CRLF .

               "<ss:Table>" . self::CRLF;


        foreach($data as $row) {

            // check if header row required

            if ($header) {

                $str .= "<ss:Row>" . self::CRLF;

                foreach($row->attributes as $k=>$v) {

                     $str .= self::xlsCell($k);

                }

                $str .= "</ss:Row>" . self::CRLF;

                // reset header

                $header = false;

            }

            $str .= "<ss:Row>" . self::CRLF;

            // output values in row

            foreach($row->attributes as $k=>$v) {

                $str .= self::xlsCell($v);

            }

            $str .= "</ss:Row>" . self::CRLF;

        }

        $str .= "</ss:Table>" . self::CRLF .

                   "</ss:Worksheet>" . self::CRLF .

                   "</ss:Workbook>";

        return $str;

    }


    /**

     * Private method to create xls cell string

     *

     * @param <type> $v - value to encode in cell

     */

    private static function xlsCell($v) {

        $t = (is_numeric($v)) ? "Number" : "String";

        return "<ss:Cell><ss:Data ss:Type='" . $t . "'>" . $v . "</ss:Data></ss:Cell>" . self::CRLF;

    }


}

?>




How to use this?

I copy your class to extensions/excel

and in my view file

<?php

Yii::import(‘application.extensions.excel’);

?>

But it’s not working.

Copy class to Components folder. Then create a controller method which is to be used to export data to excel

e.g.




function actionXXX()

{

    // generate a resultset

    $data = SomeModel::model()->findAll();


    // render data to xlsx format and echo resultant file back to browser.

    ExcelExporter::sendAsXLS($data, true, 'data.xlsx');

}



Mmmmm

How large is the dataset passed to the method?? The method will render all attributes in the data set, so obviously only select attributes in the result set which should be exported to excel (e.g. don’t try exporting blob fields for example).

I regularly use it to export datasets of 1000 records with 28 columns (resulting in a 2Mb excel file with no problems.

amc

:(

Thanks for helping me.

Class not working. Excel file is empty =(

thank you amc this is that i just was lookin for… is realy nice, the recomentations that i have, is it should be an xls becouse in xlsx it did not work for me. add the action in the rules of the controller and be careful with this

 * Send file header 


 * 


 * @param &#036;filename - filename for created file


 * @param &#036;length - size of file


 * @param &#036;type - mime type of exported data


 */

is like this

 /*


 * Send file header 


 * 


 * @param &#036;filename - filename for created file


 * @param &#036;length - size of file


 * @param &#036;type - mime type of exported data


 */

the file did work for me in openoffice just in MS Office

remember to call the action from the browser is lik this http://127.0.0.1/acredita_saas/index.php?r=internacional/XXX

Thank you very much amc, your code really helped me, it saved me a lot of time when I really needed it :)

Hi all, since I (or more exactly my client) still had some problems with Excel export, I found an example showing how to do it in HTML, and it seems to work. Client could open it with both OpenOffice and MS Excel. So I adapted it to fit amc’s class, which I liked, and added little features like:

  • ability to display a title at the top of the sheet, also triggers displaying of rows count and export date

  • ablity to only export some fields, not all of them, using their attributeLabel instead of their real name

  • the .xls extension is automatically added, if it’s not mentioned

The parameters order is slightly different:


$data = SomeModel::model()->findAll();

$fields = array(

	'field1',

	'field2',

);


ExcelExporter::sendAsXLS('data', $data, 'Data', true, $fields);

If it’s not a good way to do the export, maybe the little features I added can be useful to someone :)


<?php


class ExcelExporter

{

	const CRLF = "\r\n";


	/**

	 * Outputs active record resultset to an xml based excel file

	 *

	 * @param $filename - name of output filename

	 * @param $data - active record data set

	 * @param $title - title displayed on top

	 * @param $header - boolean to show/hide header

	 * @param $fields - array of fields to export

	 */

	public static function sendAsXLS($filename, $data, $title = false, $header = false, $fields = false)

	{

		$export = self::xls($data, $title, $header, $fields);

		self::sendHeader($filename, strlen($export), 'vnd.ms-excel');

		echo $export;

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

	}


	/**

	 * Send file header

	 *

	 * @param $filename - filename for created file

	 * @param $length - size of file

	 * @param $type - mime type of exported data

	 */

	private static function sendHeader($filename, $length, $type = 'octet-stream')

	{

		if (strtolower(substr($filename, -4)) != '.xls')

			$filename .= '.xls';


		header("Content-type: application/$type");

		header("Content-Disposition: attachment; filename=$filename");

		header("Content-length: $length");

		header('Pragma: no-cache');

		header('Expires: 0');

	}


	/**

	 * Private method to create xls string from active record data set

	 *

	 * @param $data - active record data set

	 * @param $title - title displayed on top

	 * @param $header - boolean to show/hide header

	 * @param $fields - array of fields to export

	 */

	private static function xls($data, $title, $header, $fields)

	{

		$str = '<html>' . self::CRLF

		. '<head>' . self::CRLF

		. '<meta http-equiv="content-type" content="text/html; charset=utf-8">' . self::CRLF

		. '</head>' . self::CRLF

		. '<body style="text-align:center">' . self::CRLF;


		if ($title)

			$str .= "<b>$title</b><br /><br />" . self::CRLF

			. Yii::t('main', 'export_lines') . ': ' . count($data) . '<br />' . self::CRLF

			. Yii::t('main', 'export_date') . ': ' . Yii::app()->dateFormatter->formatDateTime($_SERVER['REQUEST_TIME']) . '<br /><br />' . self::CRLF;


		if ($data)

		{

			$str .= '<table style="text-align:left" border="1" cellpadding="0" cellspacing="0">' . self::CRLF;


			if (!$fields)

				$fields = array_keys($data[0]->attributes);


			if ($header)

			{

				$str .= '<tr>' . self::CRLF;

				foreach ($fields as $field)

					$str .= '<th>' . $data[0]->getAttributeLabel($field) . '</th>' . self::CRLF;

				$str .= '</tr>' . self::CRLF;

			}


			foreach ($data as $row)

			{

				$str .= '<tr>' . self::CRLF;

				foreach ($fields as $field)

					$str .= '<td>' . $row->$field . '</td>' . self::CRLF;

				$str .= '</tr>' . self::CRLF;

			}


			$str .= '</table>' . self::CRLF;

		}


		$str .= '</body>' . self::CRLF

		. '</html>';


		return $str;

	}

}

Great post!

I want to implement filtering how will i do this?

eg.

Filter by date and status.

thanx.

I guess you’d just have to modify the query :




$data = SomeModel::model()->findAll();



with whatever suits your needs ;)

I have this code to filter the vouchers with status set as ‘1’




	function actionPrint()

	{


		$data = Vouchers::model()->findAll('status=:status', array(':status'=>1)));


		$fields = array(

				'voucher_no',

				'first_name',

				'last_name',

				'purchase_date',

				'date_used',

				'barcode'

		);

		

		ExcelExporter::sendAsXLS('Vouchers', $data, 'List of Vouchers', true, $fields);	

	}



and i want to add new fields for filter which is date, where the value of date will came from the datepicker fields on search actions.

this is my _search code:




<?php $form=$this->beginWidget('CActiveForm', array(

	'action'=>Yii::app()->createUrl($this->route),

	'method'=>'get',

)); ?>


		STATUS <?php echo $form->dropDownList($model,'status', array(''=>'All', '0' => 'UNUSED', '1' => 'USED'),array('submit' => ''));?>

		SELECT DATE : 

			<?php

			

            $this->widget('zii.widgets.jui.CJuiDatePicker', array(

                'name'=>'VoucherList[date_used]',

                'options'=>array(

                    'showAnim'=>'fold',

    				'dateFormat'=>'m/d/yy',

                ),

    			'value'=>$model->date_used,

            ));

            ?>

		<?php echo CHtml::submitButton(' GO '); ?>

		<?php echo CHtml::button('EXPORT USED VOUCHER', array('submit' => array('voucherList/print'))); ?>	




<?php $this->endWidget(); ?>



Hi folks!

Thank you all for providing us with this wonderful solution! :)

I apreciate it very much.

Thx again and best regards!

Hi, i’m trying to use this module, it works fine, but now I want to specify columns.

My model has fk values, that I want to reach using:

model->fkRela->attribute format

But I can’t get it work.

in my controller:

        &#036;data = Alumno::model()-&gt;findAll();


        &#036;fields = array(


                            'apellido',


                            'nombre',


                            'fkGrado-&gt;descripcion',


                            'fecha_nacimiento',


                            'nro_documento',


                            'fk_familia_id'


            );

the error is:

I’n not sure how this line of code in ExcelExporter should be used to retrive related data:


 

    foreach ($fields as $field)

       $str .= '<td>' . $row->$field . '</td>' . self::CRLF;



Any suggestion ?

Best Regards