Use Phpexcel to export data to an XLS-formatted Excel file

When developing a business license management system in a place, you need to export data to Excel files. Although a year ago to do a group of ERP when used to import and export, but too busy at that time to write a blog, a year later I also forget about the same, so take advantage of today's use of this to write to the blog, As a future development of a reference, to avoid the need to use the time to export to find information, but also to other peers a reference. Author: dragondean, blog address: http://www.cnblogs.com/dragondean/  What is Phpexcel? Phpexcel is a PHP class library that is used to manipulate office Excel documents, based on Microsoft's OPENXML Standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Excel (BIFF). xls, Excel 2007 (Officeopenxml). xlsx, CSV, Libre/openoffice Calc. ODS, Gnumeric, PDF , HTML, and so on. Download Phpexcel Phpexcel's official website address is: http://phpexcel.codeplex.com/, we can download the required files from. I downloaded the version of 1.8. After downloading, unzip him to the root directory of the Web site.   Development background The development is the thinkphp framework used to export all the content based on the conditions of the query. There is also an import function that I will write separately. For more detailed instructions, refer to the comments in the Code section. The code for the   export section is as follows: Copy code//import related files require_once $_server[' document_root '. ' /phpexcel/classes/phpexcel.php '; Instantiate $phpexcel = new Phpexcel (); Set than the title $phpexcel->getactivesheet ()->settitle (' Yi Chuang Technology Tips technical support '); Set Header $phpexcel->getactivesheet ()->setcellvalue (' A1 ', ' Dinner card ')               & nbsp            ->setcellvalue (' B1 ', ' unit name')                            ->setcellvalue (' C1 ', ' legal representative ']                            ->SETCELLV Alue (' D1 ', ' city ')                            ->set Cellvalue (' E1 ', ' region ')                            -&G T;setcellvalue (' F1 ', ' address ')                           &NBSP ; ->setcellvalue (' G1 ', ' categories ')                           &N Bsp ->setcellvalue (' H1 ', ' Memo (Scope of Business) ')                         &NB Sp  ->setcellvalue (' I1 ', ' certification authority ')                             ->setcellvalue (' J1 ', ' Start date ')                            ->setcellvalue (' K1 ', ' End Date ')                            ->setcellvalue (' L1 ', ' Food Safety manager ')                     &NB Sp      ->setcellvalue (' M1 ', ' license ')                            ->setcellvalue (' N1 ', ' certification date ')                            ->setcellvalue (' O1 ', ' contact phone ')                            ->setcellvalue (' P1 ', ' usable area ')                            ->setcellvalue (' Q1 ', ' number of employees ')                            ->setcellvalue (' R1 ', ' Change Case ')                            ->setcellvalue (' S1 ', ' hold the certificate ')                            ->setcellvalue (' T1 ', ' subordinate supervision Department '); Obtain data that needs to be exported from the database $list = $db->where ($where)->select (); Use foreach to start writing the data from the second row, because the first row is the header $i = 2; foreach ($list as $val) {    $phpexcel->getactivesheet ()->setcellvalue (' A '. $i, ' Kat '. $val [' Czz_nian '].$ val[' Czz_hao ']                            ->set Cellvalue (' B '. $i, $val [' Danwei '])                         &NBS P  ->setcellvalue (' C '. $i, $val [' Faren '])                     &NBS P      ->setcellvalue (' D ' $i, $val [' Dz_chengshi '])       &nbsp                    ->setcellvalue (' E '. $i, $val [' Dz_diqu '])   &N Bsp                        ->setcellvalue (' F '. $i, $val [' Dizhi ' ]                            ->setcellvalue (' G ' . $i, $val [' Leibie '])                            -&G T;setcellvalue (' H '. $i, $val [' Beizhu '])                            ->setcellvalue (' I ' $i, $val [' Fazheng '])                   & nbsp        ->setcellvalue (' J '. $i, $val [' Qs_nian ']. -'. $val [' Qs_yue ']. ' -'. $val [' Qs_ri ']]                            ->s Etcellvalue (' K '. $i, $val [' Zz_nian ']. -'. $val [' Zz_yue ']. ' -'. $val [' Zz_ri ']]                            ->s Etcellvalue (' L '. $i, $val [' Anquan '])                         &N Bsp  ->setcellvalue (' M '. $i, $val [' Zhizheng '])                     & nbsp      ->setcellvalue (' N '. $i, $val [' Fz_nian ']. -'. $val [' Fz_yue ']. ' -'. $val [' Fz_ri ']]                            ->s Etcellvalue (' O '. $i, $val [' Dianhua '])                         and nbsp  ->setcellvalue (' P '. $i, $val [' Shiyongmianji '])                   &NB Sp        ->setcellvalue (' Q '. $i, $val [' Renshu '])               &NB Sp           ->setcellvalue (' R ' $i, $val [' Biangeng '])                 & nbsp          ->setcellvalue (' S '. $i, $val [' Chizheng '])             & nbsp              ->setcellvalue (' T ' $i, $val [' Keshi ']);     $i + +; }   $obj _writer = Phpexcel_iofactory::createwriter ($phpexcel, ' Excel5 '); $filename = ' Export '. Date (' y-m-d '). XLS ";//filename  //Set Header header (" Content-type:application/force-download ");  header (" Content-type: Application/octet-stream ");  header (" Content-type:application/download ");  header (' Content-disposition : Inline;filename= "'. $filename. '");   Header ("content-transfer-encoding:binary");  header ("last-modified:"). Gmdate ("D, D M Y h:i:s"). "GMT");  header ("Cache-control:must-revalidate, Post-check=0, pre-check=0");  header ("Pragma:no-cache");   $obj _writer->save (' PHP:Output ')//Export Die ()///Plant Execution Copy code exported file preview:     Other related property settings reference (from network data) copy code//Set document basic properties        $ Objprops = $phpexcel->getproperties ();        $objProps->setcreator ("Zhanggong District Medical Insurance Bureau");        $objProps->setlastmodifiedby ("Zhanggong District Medical Insurance Bureau");        $objProps->settitle ("Zhanggong District Health Insurance Bureau employee monthly change or decrease in the report");        $objProps->setsubject ("Zhanggong District Health Insurance Bureau employee monthly change or decrease in the report");        $objProps->setdescription ("Zhanggong District Health Insurance Bureau employee monthly change or decrease in the report");        $objProps->setkeywords ("Zhanggong District Health Insurance Bureau employee monthly change or decrease in the report");        $objProps->setcategory ("Change report");  copy code phpexcel How to merge/detach cells $objPHPExcel-> Getactivesheet ()->mergecells (' A18:e22 ');  $objPHPExcel->getactivesheet ()->unmergecells (' A18:E22 ') ;  Phpexcel How to set column/row height $objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true);   $objPHPExcel->getactivesheet ()->getcolumndimension (' D ')-≫setwidth ();  $objPHPExcel->getactivesheet ()->getrowdimension (3)->setrowheight (; ) Phpexcel How to set the style copy code $objStyleA 1 = $phpexcel->getactivesheet ()->getstyle (' A1 ');        $objStyleA 1->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER);  //setting level to its $objStyleA 1->getalignment ()->setvertical (Phpexcel_style_alignment::vertical_center);    //set Vertical to its $objFontA 1 = $objStyleA 1->getfont ();  $objFontA 1->setname (' Arial ');  $objFontA 1- >setsize (18); $objFontA 1->setbold (TRUE); Copy code     Phpexcel How to set Border copy code $objActSheet->getstyle (' A2 ')->getborders ()->gettop ()-> Setborderstyle (Phpexcel_style_border::border_thin);    $objActSheet->getstyle (' A2 ')->getborders ()->getleft ()->setborderstyle (phpexcel_style_ Border::border_thin);    $objActSheet->getstyle (' A2 ')->getborders ()->getright ()->setborderstyle (phpexcel_style_ Border::border_thin);    $objActSheet->getstyle (' A2 ')->getborders ()->getbottom ()->setborderstyle (Phpexcel_style _border::border_thin);   $objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->getleft () ()->getcolor ()-> Setargb (' FF993300 ')//Set border color Copy code more information please Baidu, if there are questions welcome to correct me!