PHP # data Excel export policies

: This article mainly introduces some policies for exporting PHP # data Excel. For more information about PHP tutorials, see. PHP # data Excel export policies

In fact, exporting is common in any type of backend systems. normally, excel exports more data for backup and backup, ideally, no business operation on business data should rely on exporting data from the backend business system and then performing manual interference processing...

This type of data export function needs to be encapsulated when conditions are met. there is only one data outlet, which will truly reflect its value when the business rules are adjusted and changed in the future. When a typical application system has a wide variety of data export requirements, it indicates that the business analysis of this application system is not good enough or very poor, when users only want to use the application system to export data to assist their business processes, the worst case is that the distance between the user and the database is separated by an export button...

Environment

1. PHP5.5.14 (cli) (built: Sep 9 201419: 09: 25)

2. PHP Excel 1.7.8 (http://www.codeplex.com/PHPExcel)

Processing logic

In fact, the key point of this type of problem is how to define the export rules and whether the defined rules can adapt to the business process. The most basic way is to abstract the data export process into three basic stages, then, each stage can be further refined:

1. export rule definitions

2. adaptation of business data to export rules

3. export rule parsing structure Excel

Example

This example implements the basic export function without any encapsulation, and does not focus on performance or other scalability issues.

 1. unified data export for excel in the project
* @ Author WangYanCheng * @ version 2015-1-22 */class ExcelComp {/*** constructor */public function _ construct () {require_once 'org/ybygjy/library/excel/PHPExcel. php ';}/*** test entry */public function doTest () {// Construct the original data $ dataArray = $ this-> buildData (); // export $ this-> doExportData ($ dataArray);}/*** parse the transmitted raw data and export * @ param $ dataArr */public function doExportData ($ dataArr) {$ phpObjExcel = new \ PHPExcel (); $ worksSheet = $ PhpObjExcel-> setActiveSheetIndex (0); // Construct the header data _ Begin $ tmpColTitles = []; $ firstDataEntry = $ dataArr [0]; // assign a column index $ colIndex = 0; foreach ($ firstDataEntry as $ key => $ val) {if (preg_match ('/^ _/', $ key )) {continue;} if (is_array ($ val) {// Retrieve the column name under the array $ val = $ val [0]; $ rowNums = count ($ val ); foreach ($ val as $ innerKey => $ innerValue) {$ tmpColTitles [] = array ('parentkey' => $ key, 'key' => $ innerKey, 'colindex '=> $ co LIndex); $ colIndex ++ ;}} else {$ tmpColTitles [] = array ('key' =>$ key, 'coldex '=>$ colIndex ); $ colIndex ++ ;}}for ($ I = 0; $ I <count ($ tmpColTitles); $ I ++) {$ tmpObj = $ tmpColTitles [$ I]; $ key = $ tmpObj ['key']; $ colIndex = $ tmpObj ['colindex ']; $ worksSheet-> setCellValueByColumnAndRow ($ colIndex, 1, $ key );} // Construct the table header data _ End // fill in the cell data $ currRow = 2; foreach ($ dataArr as $ dataEntry) {$ mergeRow = $ dataEntry ['_ DIMENSION']; foreac H ($ tmpColTitles as $ colEntry) {$ key = $ colEntry ['key']; $ colIndex = $ colEntry ['coldex ']; $ parentKey = (isset ($ colEntry ['parentkey'])? $ ColEntry ['parentkey']: null); if (empty ($ parentKey) {$ value = $ dataEntry [$ key]; if ($ mergeRow = 1) {$ worksSheet-> setCellValueByColumnAndRow ($ colIndex, $ currRow, $ value);} else {$ worksSheet-> mergeCellsByColumnAndRow ($ colIndex, $ currRow, $ colIndex, ($ currRow + $ mergeRow-1)-> setCellValueByColumnAndRow ($ colIndex, $ currRow, $ value);} else {$ tmpDataArr = $ dataEntry [$ parentKey]; $ innerRow = $ currRow; for ($ index = 0; $ index <count ($ tmpDataArr); $ index ++) {$ innerDataEntry = $ tmpDataArr [$ index]; $ value = $ innerDataEntry [$ key]; $ worksSheet-> setCellValueByColumnAndRow ($ colIndex, $ innerRow, $ value); $ innerRow ++ ;}}} $ currRow + = $ mergeRow;} header ('content-Type: application/vnd. ms-excel '); header ('content-Type: application/force-download'); header ('content-Type: application/octet-stream '); header ('content-Type: application/download'); header ('content-Disposition: attachment; filename = "HelloWord.xls" '); header ('cache-Control: max-age = 0'); header ('cache-Control: max-age = 1'); header ('cache-Control: no-Cache, must-revalidate '); header ('pragma: public'); $ objWriter = \ PHPExcel_IOFactory: createWriter ($ phpObjExcel, 'excel5'); $ objWriter-> save ('php: // output');}/*** construct test data * @ return multitype: string number multitype: string */private function buildData () {$ rtnData = array ('name' => 'yancheng _ 01 ', 'age' => '20 ', 'addr '=> array ('country' => 'China', 'Vince' => 'Shandong '), array ('country' => 'China ', 'Vince '=> 'Beijing'),' _ dimension' => 2), array ('name' => 'yancheng _ 02 ', 'age' => '21', 'add' => array ('country' => 'China', 'Province '=> 'lanzhou '), array ('country' => 'China', 'Vince '=> 'n'),' _ DIMENSION '=> 2 ), array ('name' => 'yancheng _ 03', 'age' => '22', 'add' => array ('country' => 'China ', 'province '=> 'jiayuguan'), '_ dimension' => 1); return $ rtnData ;}}

The above introduces some of the policies for exporting PHP # data Excel, including some content. I hope to help some friends who are interested in PHP tutorials.