Export data to an XLS-formatted Excel file using Phpexcel

What is Phpexcel?

Phpexcel is a PHP class library for working with Office Excel documents, based on the Microsoft OPENXML Standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Excel (BIFF). xls, Excel (Officeopenxml). xlsx, CSV, Libre/openoffice Calc. ODS, Gnumeric, PDF , HTML, and so on. Download Phpexcelphpexcel's official website address is: http://phpexcel.codeplex.com/, we can download the required files from it. I downloaded the version of 1.8. After downloading, unzip him to the root of the website.   Development Background Note This development is the thinkphp framework used to export all content according to the criteria of the query. There is also an import feature that I will write separately. Please refer to the comments in the Code section for more detailed instructions. 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 Header $phpexcel->getactivesheet () Settitle (' Earth Sunshine Tip technical Support ');//Set Header $phpexcel->getactivesheet ()->setcellvalue (' A1 ', ' Meal Certificate word ')                            ->setcellvalue (' B1 ', ' unit name ')     & nbsp                      ->setcellvalue (' C1 ', ' legal representative ')                            ->SETCELlvalue (' D1 ', ' city ')                            ->s Etcellvalue (' E1 ', ' region ')                            -& Gt;setcellvalue (' F1 ', ' address ')                           &NBSP ; ->setcellvalue (' G1 ', ' category ')                           &NB Sp ->setcellvalue (' H1 ', ' Notes (business Scope) ')                         &NBS P  ->setcellvalue (' I1 ', ' certification authorities ')                         and nbsp  ->setcellvalue (' J1 ', ' Start date ')                         and nbsp  ->setcellvalue (' K1 ', ' End Date ')                         and NBsP  ->setcellvalue (' L1 ', ' Food Safety manager ')                       &NBSP ;    ->setcellvalue (' M1 ', ' license ')                       and nbsp    ->setcellvalue (' N1 ', ' issuing date ')                       & nbsp    ->setcellvalue (' O1 ', ' contact phone ')                       and nbsp    ->setcellvalue (' P1 ', ' usable area ')                       and nbsp    ->setcellvalue (' Q1 ', ' number of employees ')                            ->setcellvalue (' R1 ', ' Change Case ')                     & nbsp      ->setcellvalue (' S1 ', ' licence status ')                 &NBSP          ->setcellvalue (' T1 ', ' affiliated regulatory departments ');//Get the data you need to export from the database $list= $db->where ($where) Select ();//use foreach to start writing data from the second line, 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 '])                            ->setcellvalue (' B '. $i, $val [' Danwei '])           &NBS P                ->setcellvalue (' C '. $i, $val [' Faren '])       &NBSP ;                    ->setcellvalue (' D '. $i, $val [' Dz_chengshi ']) &nbsp ;                        ->setcellvalue (' E '. $i, $val [ ' Dz_diqu ')                            ->SETCELLV Alue (' F '. $i, $val [' Dizhi '])   &NBSP                        ->setcellvalue (' G '. $i, $val [' Leibie ']                            ->setcellvalue (' H ') $i, $val [' Beizhu ']                            -&GT Setcellvalue (' I '. $i, $val [' Fazheng '])                            ->setcellvalue (' J '. $i, $val [' Qs_nian ']. ' -'. $val [' Qs_yue ']. ' -'. $val [' Qs_ri ']                          ->se Tcellvalue (' K '. $i, $val [' Zz_nian ']. ' -'. $val [' Zz_yue ']. ' -'. $val [' Zz_ri ']                          ->se Tcellvalue (' L '. $i, $val [' Anquan '])                         &NBS P  -≫setcellvalue (' M '. $i, $val [' Zhizheng '])                       &NBS P    ->setcellvalue (' N '. $i, $val [' Fz_nian ']. ' -'. $val [' Fz_yue ']. ' -'. $val [' Fz_ri ']                          ->se Tcellvalue (' O '. $i, $val [' Dianhua '])                         &NB Sp  ->setcellvalue (' P '. $i, $val [' Shiyongmianji '])                   &NB Sp        ->setcellvalue (' Q '. $i, $val [' Renshu '])               &NBS P            ->setcellvalue (' R '. $i, $val [' Biangeng '])           &N Bsp                ->setcellvalue (' S '. $i, $val [' Chizheng '])       &N Bsp                    ->setcellvalue (' T '. $i, $val [' Keshi ']);    $i + +;}   $obj _writer = phpexcel_iofactory::createwriter ($phpexcel, ' Excel5 '); $filename = ' Export '. Date (' y-m-d '). ". XLS ";//filename  //set Headerheader (" 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 ');//output die ();//Planting perform copy code export file preview:   Other related property settings reference (from network material) copy code// Set document basic properties        $objProps = $phpexcel->getproperties ();        $objProps->setcreator ("Zhanggong District Medical Insurance Bureau");        $objProps->setlastmodifiedby ("Zhanggong District Medical Insurance Bureau"); &nbSp      $objProps->settitle ("Zhanggong District Health Insurance bureau staff monthly increase or decrease change report");        $objProps->setsubject ("Zhanggong District Health Insurance bureau staff monthly increase or decrease change report");        $objProps->setdescription ("Zhanggong District Health Insurance bureau staff monthly increase or decrease change report");        $objProps->setkeywords ("Zhanggong District Health Insurance bureau staff monthly increase or decrease change 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 width/row height $objphpexcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true);   $objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (a);  $objPHPExcel Getactivesheet ()->getrowdimension (3)->setrowheight (+)  phpexcel How to set the style copy code $OBJSTYLEA1 = $phpexcel- >getactivesheet ()->getstyle (' A1 ');        $objStyleA 1->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER);   Set the level to its $objstylea1->getalignment ()->setvertical (Phpexcel_style_alignment::vertical_center);    //set Vertical to its $objfonta1 = $objStyleA 1->getfont ();  $objFontA 1->setname (' Arial ');  $objFontA 1- >setsize (18); $objFontA 1->setbold (true); Copy code   phpexcel How to set the 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  

A companion tour, a free dating site: www.jieberu.com

Push family, free tickets, scenic spots: www.tuituizu.com

Export data to an XLS-formatted Excel file using Phpexcel