Hi,
i am generating report in my application.and in report i am exporting that data into excel…
i am not using any extension for that…but actual problem is that,some of my data is like: 5/6 ,8/5 like that…so in windows pc, it will take as a date in particular that cell…
in ubuntu pc, it works properly…
so solve this i want to use phpexcel extension .i have already download that and put it into my extension folder…but now what to do…i dont know…pls suggest…
give proper sollution …
Thank you.
Usually adding an ’ apostrophy at the beginning stops excel from autocorrecting the cell. Try this maybe.
Bizley Sir,
as u say i use it already but still it prints like,'5/6 .
if suppose i give colspan=2 then also it not works in windows machine…
i am attaching my excel file and my action in controller file…and excel view file.
then help me…
this is the issue regarding windows machine…ubuntu it works…
here is my action code in controller file, in which i am generating excel file
/*****************************************/
public function actionStudMonthlyAllsubjectAttendenceReport()
{
$baseUrl = Yii::app()->baseUrl;
$cs = Yii::app()->getClientScript();
$cs->registerCssFile($baseUrl.'/css/report.css');
$model = new Attendence;
$model->scenario = 'studMonthlyAttendenceReport';
$this->performAjaxValidation($model);
$month = $month_value=$query=null;
$student= $subject_data= $sid=$subjectidp=$subjectid=array();
$subject=$subjectp=$sem=$branch=$div=0;
$org_id = Yii::app()->user->getState('org_id');
$status = false;
if(!empty($_POST['Attendence']['att_month']) && !empty($_REQUEST['Attendence']['branch_id']) && !empty($_REQUEST['Attendence']['div_id']) && isset($_POST['Attendence']) || isset($_REQUEST['branch_id']))
{
if(!empty($_REQUEST['branch_id']))
{
$branch = $_REQUEST['branch_id'];
$query .="student_transaction_branch_id=".$branch. " AND ";
}
else
{
$branch =$_POST['Attendence']['branch_id'];
$query .="student_transaction_branch_id=".$branch. " AND ";
}
if(!empty($_REQUEST['sem_name_id']))
{
$sem = $_REQUEST['sem_name_id'];
$query .="student_academic_term_name_id=".$sem. " AND ";
}
else
{
$sem =$_POST['Attendence']['sem_name_id'];
$query .="student_academic_term_name_id=".$sem." AND ";
}
if(!empty($_POST['Attendence']['div_id']))
{
$div = $_POST['Attendence']['div_id'];
$query .="student_transaction_division_id=".$div. " AND ";
}
else
{
$div = $_REQUEST['div_id'];
$query .="student_transaction_division_id=".$div. " AND ";
}
if(!empty($_POST['Attendence']['att_month']) || !empty($_REQUEST['month']))
{
if(!empty($_REQUEST['month']))
{
$month = $_REQUEST['month'];
}
else
{
$month = $_POST['Attendence']['att_month'];
}
$student=Yii::app()->db->createCommand()
->select('student_transaction_id')
->from('student_transaction st')
->join('student_info stud_info', 'stud_info.student_info_transaction_id = st.student_transaction_id')
->where($query.'st.student_transaction_organization_id='.$org_id.' AND st.student_transaction_detain_student_flag NOT IN (1,2) order by stud_info.student_enroll_no ASC')
->queryAll();
foreach($student as $s )
{
$sid[]=$s['student_transaction_id'];
}
$subject=Yii::app()->db->createCommand()
->select('subject_master_id')
->from('subject_master sm')
->where('sm.subject_master_organization_id='.$org_id.' AND sm.subject_master_branch_id='.$branch.' AND sm.subject_master_academic_terms_name_id='.$sem.' AND sm.subject_master_type_id=1')
->queryAll();
$subject_p=Yii::app()->db->createCommand()
->select('subject_master_id')
->from('subject_master sm')
->where('sm.subject_master_organization_id='.$org_id.' AND sm.subject_master_branch_id='.$branch.' AND sm.subject_master_academic_terms_name_id='.$sem.' AND subject_master_type_id=2')
->queryAll();
foreach($subject as $sub)
{
$subjectid[]=$sub['subject_master_id'];
}
foreach($subject_p as $sub)
{
$subjectidp[]=$sub['subject_master_id'];
}
$chk_record = Attendence::model()->findAll(array('condition'=>'month(attendence_date)='.$month.' AND branch_id='.$branch.' AND div_id='.$div.' AND year(attendence_date)='.date('Y')));
if($chk_record)
{
$status = true;
}
if(isset($_REQUEST['excel'])) //export excel
{
Yii::app()->request->sendFile(date('YmdHis').'.xls',
$this->renderPartial('stud_monthly_allsubject_attendence_report_excel',
array(
'month'=>$month,
'sid'=>$sid,
'sem'=>$sem,
'branch'=>$branch,
'subjectid'=>$subjectid,
'subjectidp'=>$subjectidp,
'div'=>$div,
'status'=>$status,
),true));
}
else
{
$this->render('stud_monthly_allsubject_attendence_report_view',array('model'=>$model,'month'=>$month,'sid'=>$sid,'branch'=>$branch,'div'=>$div,'sem'=>$sem,'subjectid'=>$subjectid,'subjectidp'=>$subjectidp,'status'=>$status));
}
} //end month/moths
} //main if
else
{
$this->render('stud_monthly_allsubject_attendence_report_view',array('model'=>$model,'month'=>$month,'sid'=>$sid,'branch'=>$branch,'sem'=>$sem,'div'=>$div,'subjectid'=>$subjectid,'subjectidp'=>$subjectidp,'status'=>$status));
}
}
/***********************************/
Thanks.
What about wrapping the data in double quotes? And when adding code here use the Code Snippet tag, it will be easier to read.
sorry for my code ,i dont know about snipepet code…
but no i have tried all those stuff like ', " “,=”", etc…
can u pls help me out to use phpexcel extension in my code …
pls its urgent…
need to use phpexcel extension only…because it works proper in all machines(ubuntu,windows…).
Pls Bizley Sir, help me out.
Thanks
Code snippet icon looks like this: <>
Find it in the editor menu.
First of all make sure you bind all the parameters you use in your query to prevent SQL injection. But the problem for now is the excel part so follow instructions here:
http://www.yiiframework.com/wiki/101/how-to-use-phpexcel-external-library-with-yii/
Download the PHPExcel library and place it under the protected/extensions folder. Then you can use the code from TestController class from the example above. See the comments there for additional solutions.
hey , i have already download and put it in extension folder…main thing is that how to use now in my report …i really dont know…
they said ,use that action and run it…but in my report how can put it exactly…
Thanks
Hi All,
i have use extension PHPExcel,put it in extension folder,and then in usercontroller file i ve write action Test() and execute it.but gives me an error like:
require_once(PHPExcel.php): failed to open stream: No such file or directory
while the file PHPExcel is exist in that path properly still gives an error…
public function actionTest()
{
Yii::import('application.extensions.PHPExcel.*');
//include 'PHPExcel/IOFactory.php';
include('PHPExcel/Classes/PHPExcel.php');
// get a reference to the path of PHPExcel classes
$phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.Classes');
// Turn off our amazing library autoload
spl_autoload_unregister(array('YiiBase','autoload'));
//include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("PDF Test Document")
->setSubject("PDF Test Document")
->setDescription("Test document for PDF, generated using PHP classes.")
->setKeywords("pdf php")
->setCategory("Test result file");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Hello')
->setCellValue('B2', 'world!')
->setCellValue('C1', 'Hello')
->setCellValue('D2', 'world!');
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A4', 'Miscellaneous glyphs')
->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/pdf');
header('Content-Disposition: attachment;filename="01simple.pdf"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('php://output');
Yii::app()->end();
spl_autoload_register(array('YiiBase','autoload'));
}
CAn anyone has solution for this?
Thanks.
Hi Janvi,
Please read "PHPExcel developer documentation.doc" that is included in the PHPExcel download.
When you set the value of a cell with "setCellValue()" function, PHPExcel tries to set the datatype automatically. It works well most of the time. But sometimes you have to set it explicitly.
And why did you change the sample code of the wiki?
http://www.yiiframework.com/wiki/101/how-to-use-phpexcel-external-library-with-yii/
Hi,
yes,i have change that code because it gives me an error,so i m trying to change…after ur reply i ve again set the same code but still it not works…
public function actionTest()
{
//
// get a reference to the path of PHPExcel classes
$phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.Classes');
// Turn off our amazing library autoload
spl_autoload_unregister(array('YiiBase','autoload'));
//
// making use of our reference, include the main class
// when we do this, phpExcel has its own autoload registration
// procedure (PHPExcel_Autoloader::Register()<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/wink.gif' class='bbc_emoticon' alt=';)' />
include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("PDF Test Document")
->setSubject("PDF Test Document")
->setDescription("Test document for PDF, generated using PHP classes.")
->setKeywords("pdf php")
->setCategory("Test result file");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Hello')
->setCellValue('B2', 'world!')
->setCellValue('C1', 'Hello')
->setCellValue('D2', 'world!');
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A4', 'Miscellaneous glyphs')
->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet,
// so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/pdf');
header('Content-Disposition: attachment;filename="01simple.pdf"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('php://output');
Yii::app()->end();
//
// Once we have finished using the library, give back the
// power to Yii...
spl_autoload_register(array('YiiBase','autoload'));
}
i dont know some how there is a problem …
This code assumes that the directory structure is like this:
protected
components
config
controllers
extensions
phpexcel
Classes
PHPExcel
....
PHPExcel.php
(Documentation)
(Examples)
gii
messages
....
Please make sure that you have installed PHPExcel in the right place.
Hi,
my path is not proper…again now i have download yii-phpexcel-master.zip file and PHPExcel-1.8.0.zip file,and i have set the path in XPHPExcel.php file.
and put action in my controller file. then simple file of excel is downloaded.
action is given below:
public function actionCreateExcel()
{
Yii::import('ext.phpexcel.XPHPExcel');
//$phpExcel = XPHPExcel::createPHPExcel();
$objPHPExcel= XPHPExcel::createPHPExcel();
$objPHPExcel->getProperties()->setCreator("Janvi")
->setLastModifiedBy("Janvi Kapopara")
->setTitle("Report")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Report')
->setCellValue('B2', 'Rudra Softech!')
->setCellValue('C1', 'Hello')
->setCellValue('D2', 'world!')
->setCellValue('E2','Janvi Kapopara');
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A4', 'janvi')
->setCellValue('A5', 'hello janvi');
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Yii::app()->end();
}
but now the main question rise that how can i put my dynamic data that generate in my report and iwant to export that via excel…
now can any body give solution…
i am attaching my reportexcelfile so that u can get easily what i want in my excel sheet.
Thanks.