Export data to an XLS-formatted Excel file using Phpexcel
In the development of a business license management system at the time 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 passed I also forget the almost, so take advantage of this use today to write to the blog, As a reference for future development, so as to avoid re-use when you need to export to find information, but also to other counterparts a reference. Author: dragondean, blog address: http://www.cnblogs.com/dragondean/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 (' Yi Chuang Technology Tips technical support ');//Set Header $phpexcel->getactivesheet ()->setcellvalue (' A1 ', ' Meal certificate word ')->setcellvalue (' B1 ') , ' unit name ')->setcellvalue (' C1 ', ' legal representative ')->setcellvalue (' D1 ', ' City ')->setcellvalue (' E1 ', ' region ') Setcellvalue (' F1 ', ' address ')->setcelLValue (' G1 ', ' category ')->setcellvalue (' H1 ', ' Notes (business Scope) ')->setcellvalue (' I1 ', ' issuing authority ')->setcellvalue (' J1 ', ' Start date ') )->setcellvalue (' K1 ', ' End Date ')->setcellvalue (' L1 ', ' Food Safety manager ')->setcellvalue (' M1 ', ' whether to license ') Setcellvalue (' N1 ', ' issuing date ')->setcellvalue (' O1 ', ' Contact phone ')->setcellvalue (' P1 ', ' area ')->setcellvalue (' Q1 ', ' Number of employees ')->setcellvalue (' R1 ', ' change Situation ')->setcellvalue (' S1 ', ' license status ')->setcellvalue (' T1 ', ' affiliated Regulatory Department ');// 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 '])->setcellvalue (' C ' $i, $val [' Faren '])->setcellvalue (' D '. $i, $val [' Dz_chengshi ']) Setcellvalue (' E '. $i, $val [' Dz_diqu '])->setcellvalue (' F '. $i, $val [' Dizhi '])->setcellvalue (' G '. $i, $val [' Leibie '])->setcellvalue (' H '. $i, $val [' Beizhu '])->setcellvalue (' I ' $i, $val [' Fazheng '])->setcellvalue (' J. $i, $val [' Qs_nian ']. ' -'. $val [' Qs_yue ']. ' -'. $val [' Qs_ri '])->setcellvalue (' K '. $i, $val [' Zz_nian ']. ' -'. $val [' Zz_yue ']. ' -'. $val [' Zz_ri '])->setcellvalue (' L '. $i, $val [' Anquan '])->setcellvalue (' M ' $i, $val [' Zhizheng ']) Setcellvalue (' N '. $i, $val [' Fz_nian ']. ' -'. $val [' Fz_yue ']. ' -'. $val [' Fz_ri '])->setcellvalue (' O '. $i, $val [' Dianhua '])->setcellvalue (' P ' $i, $val [' Shiyongmianji ']) Setcellvalue (' Q '. $i, $val [' Renshu '])->setcellvalue (' R ' $i, $val [' Biangeng '])->setcellvalue (' S '. $i, $val [' Chizheng '])->setcellvalue (' T '. $i, $val [' Keshi ']); $i + +;} $obj _writer = Phpexcel_iofactory::createwriter ($phpexcel, ' Excel5 '); $filename = ' Export '. Date (' y-m-d '). ". XLS ";//file name//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"); $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 statement"); 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 (12); $objPHPExcel->getactivesheet ()->getrowdimension (3)->setrowheight (16); Phpexcel How to set 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 (' Song-Body '); $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 Copy code more information please Baidu, if there is a problem welcome correction!
http://www.bkjia.com/PHPjc/905597.html www.bkjia.com true http://www.bkjia.com/PHPjc/905597.html techarticle use Phpexcel to export data to an XLS format Excel file in the development of a business license management system at a time need to export data to Excel files, although a year ago to do a group of ER ...
