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

Source: Internet
Author: User
Tags border color php language php class

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 ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.