PHPExcel (1) export function PHPExcel export function
Some forms in the database are often exported to excel files during Web development. When I used the yii Framework for web development, I found an application PHPExcel, which can import and export excel files and meet the requirements. I will use PHPExcel to export data myself.
1, first download the application, github link: https://github.com/PHPOffice/PHPExcel
2. create the PHPexcel directory under the protected/extensions path and decompress the downloaded PHPExcel file.
3. copy all the extracted content in the classes directory to the protected, extensions, and PHPexcel directories.
4. in the Controller file XXX. Controller. php, first introduce the relevant file
5. compile method Download (The following is part of the code in the development process)
Public function actionDownload ($ option) {// Create new PHPExcel object $ objPHPExcel = new PHPExcel (); // Set properties $ objPHPExcel-> getProperties () -> setCreator ("Maarten Balliauw")-> setLastModifiedBy ("Maarten Balliauw")-> setTitle ("Office 2007 XLSX Test Document ") -> 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 "); // $ objPHPExcel-> getActiveSheet ()-> mergeCells ('A1: g1 '); // $ objPHPExcel-> getActiveSheet ()-> getStyle ('A1')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER ); // $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('C')-> setAutoSize (true); // $ objPHPExcel-> getActiveSheet () -> getColumnDimension ('A')-> setWidth (20); // $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('e')-> setWidth (20 ); $ subjects = Subject: model ()-> findAll ("headteacher =: name", array (": name" => Yii: app () -> session ['name']); // find the relevant course group foreach ($ subjects as $ item1) {$ criteria = new CDbCriteria; // Create a CDbCriteria object $ criteria-> addCondition ("suid =: id"); $ criteria-> params [': id'] = $ item1-> id; $ criteria-> select = '*'; // search for the selected answer information based on the returned parameters if ($ option = 1) {$ criteria-> order = 'CID '; // $ criteria-> limit = 3; $ B = Selectcourse: model ()-> findAll ($ criteria); $ objPHPExcel-> setActiveSheetIndex (0) -> setCellValue ('A1', 'topic information')-> setCellValue ('A2 ', 'Student ID')-> setCellValue ('B2', 'Student name ') -> setCellValue ('C2 ', 'course name')-> setCellValue ('D2', 'Question name')-> setCellValue ('E2', 'topic time '); $ count = 2; foreach ($ B as $ item2) {$ count + = 1; $ l1 = "". "$ count"; $ l2 = "B ". "$ count"; $ l3 = "C ". "$ count"; $ l4 = "D ". "$ count"; $ l5 = "E ". "$ count"; $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ($ l1, $ item2-> sid)-> setCellValue ($ l2, Student: model () -> find (array ('condition '=> 'Id = '. $ item2-> sid,)-> name)-> setCellValue ($ l3, $ item1-> name)-> setCellValue ($ l4, Course: model () -> find (array ('condition '=> 'cid = '. $ item2-> cid,)-> Cname)-> setCellValue ($ l5, $ item2-> apply_time) ;}} if ($ option = 2) {$ criteria-> order = 'Sid '; // $ criteria-> limit = 3; $ B = Selectcourse: model ()-> findAll ($ criteria ); $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('A1', 'topic information')-> setCellValue ('A2 ', 'Student ID ') -> setCellValue ('B2', 'Student name')-> setCellValue ('C2 ', 'course name')-> setCellValue ('D2', 'Question name ') -> setCellValue ('E2', 'topic time'); $ count = 2; foreach ($ B as $ item2) {$ count + = 1; $ l1 = "". "$ count"; $ l2 = "B ". "$ count"; $ l3 = "C ". "$ count"; $ l4 = "D ". "$ count"; $ l5 = "E ". "$ count"; $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ($ l1, $ item2-> sid)-> setCellValue ($ l2, Student: model () -> find (array ('condition '=> 'Id = '. $ item2-> sid,)-> name)-> setCellValue ($ l3, $ item1-> name)-> setCellValue ($ l4, Course: model () -> find (array ('condition '=> 'cid = '. $ item2-> cid,)-> Cname)-> setCellValue ($ l5, $ item2-> apply_time) ;}}// Rename sheet $ objPHPExcel-> getActiveSheet () -> setTitle ('student topic information'); // 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 = "student selection information .xls"'); header ('cache-Control: max-age = 0 '); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output ');}
6. do not forget to add the action download in the user access control.
public function accessRules(){array('allow', // allow admin user to perform 'admin' and 'delete' actions 'actions' => array('download'), 'roles' => array('...'), ),}
7. write links in the corresponding view file
$ Option);?>
8. export the data in an excel file.
The above is an application of PHPExcel, because it was the first time in contact and was not skilled in using it, it is inevitable that there will be some improper, I hope you can point out, make progress together!