Use PHPExcel to export data to excel files in xls format

Use PHPExcel to export data to excel file in xls format

When developing a business license management system in a certain place, the data needs to be exported into an excel file. Although I used import and export once when I was doing the ERP of a certain group a year ago, I was too busy at that time to write a blog. Years have passed and I have almost forgotten about it, so I will take advantage of today to write this use in the blog as a reference for future development, so as not to need to export and find information when using it again, and also to give other colleagues a reference. Author: DragonDean, blog address: http://www.cnblogs.com/dragondean/

What is PHPExcel?

PHPExcel is a PHP class library used to operate Office Excel documents. It is 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, etc.

Download PHPExcel

The official website address of phpexcel is: http://phpexcel.codeplex.com/, from which we can download the required files. I downloaded version 1.8. After downloading, unzip it to the root directory of the website.

Development background description

This development uses the ThinkPHP framework to export all content based on the query conditions. There is also an import function, I will write another article about it. For more detailed instructions, please refer to the comments in the code section.

The code for the export part is as follows:

Copy code

//Import related files

require_once $_SERVER['DOCUMENT_ROOT'].'/PHPExcel/Classes/PHPExcel.php';

//Instantiation

$phpexcel = new PHPExcel();

//Set the ratio title

$phpexcel->getActiveSheet()->setTitle('Yichuang Technology prompts technical support');

//Set header

$phpexcel->getActiveSheet() ->setCellValue('A1','Meal Certificate Word')

-& gt; setcellvalue ('b1', 'unit name')

-& gt; setcellvalue ('c1', 'legal representative')

-& gt; setcellvalue ('d1', 'city')

-& gt; setcellvalue ('e1', 'region')

-& gt; setcellvalue ('f1', 'address')

-& gt; setcellvalue ('g1', 'category')

-& gt; setcellvalue ('h1', 'remark (business scope)'

-& gt; setcellValue ('i1', 'issuing authority')

-& gt; setcellvalue ('j1', 'start date')

-& gt; setcellvalue ('k1', 'end date')

-& gt; setcellvalue ('l1', 'food safety manager')

-& gt; setcellvalue ('m1', 'whether to proof')

-& gt; setcellValue ('n1', 'issuing certificate date')

-& gt; setcellvalue ('o1', 'contact number')

-& gt; setcellvalue ('p1', 'use area')

-& gt; setcellvalue ('q1', 'number of employees' "

-& gt; setcellvalue ('r1', 'change situation')

-& gt; setcellvalue ('s1', 'holding the certificate')

-& gt; setcellValue ('t1', '' belonging to the regulatory department ');

//Get the data that needs to be exported from the database

$list=$db->where($where)->select();

//Use foreach to start writing data from the second row, because the first row is the header

$i=2;

foreach($list as $val){

$phpexcel->getActiveSheet() ->setCellValue('A'.$i,'吉'.$val['czz_nian'].$val['czz_hao'])

-& gt; setcellvalue ('b'. $ I, $ value ['danwei'])

-& gt; setcellvalue ('c'. $ I, $ valr ['faren']

-& gt; setcellValue ('d'. $ I, $ value ['dz_chengshi'])

-& gt; setcellValue ('e'. $ I, $ value ['dz_diqu'])

-& gt; setcellValue ('f'. $ I, $ value ['dizhi'])

-& gt; setcellvalue ('g'. $ I, $ value ['leibie'])

-& gt; setcellvalue ('h'. $ I, $ value ['beizhu'])

-& gt; setcellValue ('I'. $ I, $ value ['fazheng'])

-& gt; setcellvalue ('k'. $ I, $ value ['zz_nian'].'- '. $ Val [' zz_yue '] .'-'. $ Val ['zz_ri'])

-& gt; setcellvalue ('l'. $ I, $ value ['anquan'])

-& gt; setcellvalue ('m'. $ I, $ value ['zhizheng']

->setCellValue('N'.$i, $val['fz_nian'].'-'.$val['fz_yue'].'-'.$val['fz_ri'])

-& gt; setcellValue ('o'. $ I, $ value ['dianhua'])

-& gt; setcellvalue ('p'. $ I, $ value ['shiyongmianji'])

-& gt; setcellvalue ('q'. $ I, $ value ['renshu']

-& gt; setcellValue ('r'. $ I, $ value ['biangngng'])

-& gt; setcellvalue ('s'. $ I, $ valr ['chizheng']

-& gt; setcellvalue ('t'. $ I, $ value ['keshi']);

$i++;

}

$obj_Writer = PHPExcel_IOFactory::createWriter($phpexcel,'Excel5');

$filename ='Export'. date('Y-m-d').".xls";//File name

//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');//Output

die();//Planting execution

Copy code

Exported file preview:

Reference for other related attribute settings (from online information)

Copy code

//Set the basic properties of the document

$objProps = $phpexcel->getProperties();

$objProps->setCreator("Zhanggong District Medical Insurance Bureau");

$objProps->setLastModifiedBy("Zhanggong District Medical Insurance Bureau");

$objProps->setTitle("Monthly increase and decrease report for employees of Zhanggong District Medical Insurance Bureau");

$objProps->setSubject("Monthly increase and decrease report for employees of Zhanggong District Medical Insurance Bureau");

$objProps->setDescription("Monthly increase and decrease report for employees of Zhanggong District Medical Insurance Bureau");

$objProps->setKeywords("Monthly increase and decrease report for employees of Zhanggong District Medical Insurance Bureau");

$objProps->setCategory("Change Report");

Copy code

How to merge/separate cells in PHPExcel

$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');

How to set column width/row height in PHPExcel

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(16);

How to set styles in PHPExcel

Copy code

$objStyleA1 = $phpexcel->getActiveSheet()->getStyle('A1');

$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //Set the horizontal alignment

$objStyleA1->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //Set vertical alignment

$objFontA1 = $objStyleA1->getFont();

$objFontA1->setName('宋体');

$objFontA1->setSize(18); $objFontA1->setBold(true);

Copy code

How to set borders in PHPExcel

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

Please search Baidu for more information. If you have any questions, please correct me!

Use PHPExcel to export data to excel files in xls format_PHP tutorial

Use PHPExcel to export data to excel file in xls format