PHP# Some strategies for exporting data to Excel

PHP#dataExcelSome strategies for exporting

Export is actually more common in any type of back-end system. Under normal circumstances, the data exported by excel should be used more Make backups and drafts. Ideally, any business operation on business data should not rely on exporting data from the back-end business system and then manually interfering with it, but the reality is very skinny. . .

Functions such as data export need to be encapsulated if possible. There is only one data export, and its value will truly be reflected in subsequent adjustments and changes to business rules. When a typical application system has various data export requirements, it means 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 most terrifying situation is The distance between the user and the database will be just 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. Whether this set of rules can adapt to business processes, the most basic way is to abstract the data export process into 3 basic stages, and then each stage can be gradually refined:

1. Definition of export rules

2.Adaptation of business data and export rules

3.Export rule analysis structureExcel

Example

This example implements the basic export function. There is no encapsulation, no focus on performance or other scalability issues.

<?php
namespace org\ybygjy\comp\excel;

/**
 * Excel组件封装
 * <p>1.统一处理工程中针对excel的数据导出</p>
 * @author WangYanCheng
 * @version 2015-1-22
 */
class ExcelComp {
	/**
	 * 构造函数
	 */
	public function __construct() {
		require_once 'org/ybygjy/library/excel/PHPExcel.php';
	}
	/**
	 * 测试入口
	 */
	public function doTest() {
		//构造原始数据
		$dataArray = $this->buildData();
		//导出
		$this->doExportData($dataArray);
	}
	/**
	 * 解析传递的原始数据并导出
	 * @param $dataArr
	 */
	public function doExportData($dataArr) {
		$phpObjExcel = new \PHPExcel();
		$worksSheet = $phpObjExcel->setActiveSheetIndex(0);
		//构造表头数据_Begin
		$tmpColTitles = [];
		$firstDataEntry = $dataArr[0];
		//分配列索引
		$colIndex = 0;
		foreach($firstDataEntry as $key => $val) {
			if (preg_match('/^_/', $key)) {
				continue;
			}
			if (is_array($val)) {
				//取array下的列名称
				$val = $val[0];
				$rowNums = count($val);
				foreach ($val as $innerKey => $innerValue) {
					$tmpColTitles[] = array(
						'parentKey' => $key,
						'key' => $innerKey,
						'colIndex' => $colIndex
					);
					$colIndex++;
				}
			} else {
				$tmpColTitles[] = array(
					'key'=>$key,
					'colIndex'=>$colIndex
				);
				$colIndex++;
			}
		}
		for($i = 0; $i < count($tmpColTitles); $i++) {
			$tmpObj = $tmpColTitles[$i];
			$key = $tmpObj[&#39;key&#39;];
			$colIndex = $tmpObj[&#39;colIndex&#39;];
			$worksSheet->setCellValueByColumnAndRow($colIndex,1,$key);	
		}
		//构造表头数据_End
		//填充单元格数据
		$currRow = 2;
		foreach ($dataArr as $dataEntry) {
			$mergeRow = $dataEntry['_DIMENSION'];
			foreach ($tmpColTitles as $colEntry) {
				$key = $colEntry['key'];
				$colIndex = $colEntry['colIndex'];
				$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');
	}
	/**
	 * 构造测试数据
	 * @return multitype:multitype:string number multitype:multitype:string
	 */
	private function buildData() {
		$rtnData = array(
			array(
				'name'=>'YanCheng_01',
				'age'=>'20',
				'addr'=>array(
					array(
						'country'=>'China',
						'province'=>'ShanDong'
					),
					array(
						'country'=>'China',
						'province'=>'BeiJing'
					)
				),
				'_DIMENSION'=>2
			),
			array(
				'name'=>'YanCheng_02',
				'age'=>'21',
				'addr'=>array(
					array(
						'country'=>'China',
						'province'=>'LanZhou'
					),
					array(
						'country'=>'China',
						'province'=>'NingXia'
					)
				),
				'_DIMENSION'=>2
			),
			array(
				'name'=>'YanCheng_03',
				'age'=>'22',
				'addr'=>array(
					array(
						'country'=>'China',
						'province'=>'JiaYuGuan'
					)
				),
				'_DIMENSION'=>1
			)
		);
		return $rtnData;
	}
}

The above introduces some strategies for PHP# data Excel export, including aspects of the content. I hope it will be helpful to friends who are interested in PHP tutorials.