phpexcel导出类

简介

在tp5框架中利用 phpexcel导出，节省导出代码行数。

代码

类代码

<?php
/**
 * 导出数据公共方法 phpexcel
 * csv 不支持分页
 */
namespace app\common\behavior;

class Export {
	private $exportData;
	private $columnArray;
	public $excelObj;
	private $style;
	private $paging;
	public function __construct($paging = false) {
		vendor('phpexcel.PHPExcel');
		Vendor("phpexcel.PHPExcel.IOFactory");
		Vendor("phpexcel.PHPExcel.Reader.Excel5");
		Vendor("phpexcel.PHPExcel.Cell.DataType");
		if (!is_bool($paging)) {
			$this->paging = false;
		} else {
			$this->paging = $paging;
		}
		$this->excelObj = new \PHPExcel();
	}
	public function checkParam($param) {
		$defaultParam = ['title' => '', 'list' => ''];
		$exportData = array_merge($defaultParam, array_intersect_key($param, $defaultParam));

		if (!isset($param['page']) || !is_numeric($param['page'])) {
			$exportData['page'] = 0;
		}
		if (!isset($param['line']) || !is_numeric($param['line'])) {
			$exportData['line'] = isset($this->exportData['line']) ? $this->exportData['line'] : 0;
		}
		$this->exportData = $exportData;
	}
	public function setStyle($title, $style) {
		foreach ($title as $key => $value) {
			if (isset($style[$key])) {
				$this->style[$key] = $style[$key];
			}
		}
	}
	public function export($param) {
		$this->checkParam($param);
		$excelObj = $this->excelObj;
		$index = $this->exportData['page'];
		$title = $this->exportData['title'];
		$list = $this->exportData['list'];
		$this->setColumn();

		//设置excel
		$excelObj->setActiveSheetIndex($index);
		$objSheet = $excelObj->getActiveSheet()->setTitle('分页' . ($index + 1));

		//设置格式
		if ($this->style) {
			$styleKeys = array_keys($this->style);
			$objSheet->getDefaultColumnDimension()->setAutoSize(true);
			$titleArrays = array_keys($title);
			foreach ($styleKeys as $key => $value) {
				$titleKey = array_search($value, $titleArrays);
				$column = $this->columnArray[$titleKey];
				$objSheet->getColumnDimension($column)->setWidth($this->style[$value]['width']);
			}
		}
		//sheet数据填写
		if (0 === $this->exportData['line']) {
			//获取全部数据
			$data = $this->getExportData(1);
		} else {
			if (!$this->paging) {
				//不分页 获取list
				$data = $this->getExportData(2);
			} else {
				//分页 获取全部数据
				$data = $this->getExportData(1);
			}
		}
		$data = array_values($data);
		$index = 1;
		if (!$this->paging && $this->exportData['line'] > 0) {
			$index += $this->exportData['line'];
		}
		foreach ($data as $rk => $row) {
			$rowNum = $rk + $index;
			foreach ($row as $key => $value) {
				$value = mb_convert_encoding($value, "UTF-8");
				//$value = iconv("GBk2312", "UTF-8", $value);
				$objSheet->setCellValue($this->columnArray[$key] . $rowNum, $value, \PHPExcel_Cell_DataType::TYPE_STRING);
			}
			$this->exportData['line']++;
		}
	}
	/*type 1 获取全部 2 仅获取list 3 仅获取title*/
	private function getExportData($type = 1) {
		$title = $this->exportData['title'];
		$list = $this->exportData['list'];

		//统一数据
		$titleKeysArray = array_filter(array_flip(array_keys($title)), function ($v, $k) {
			return [$k => ''];
		}, ARRAY_FILTER_USE_BOTH);
		foreach ($list as $key => &$value) {
			$item = array_intersect_key($value, $titleKeysArray);
			$item = array_merge($titleKeysArray, $item);
			$value = array_values($item);
		}
		$data = false;
		switch ($type) {
		case 1:
			$data = array_reverse($list, true);
			$data[] = array_values($title);
			$data = array_reverse($data, true);
			break;
		case 2:
			$data = array_values($list);
			break;
		case 3:
			$data = array_values($title);
			break;
		}
		return $data;
	}
	public function save($fileName) {
		$exportObj = $this->excelObj;
		//$filename = iconv("gbk", "utf-8", $filename);
		$fileName = mb_convert_encoding($fileName, "UTF-8");

		header("Content-Type: application/force-download");
		header("Content-Type: application/octet-stream");
		header("Content-Type: application/download");
		header("Content-Transfer-Encoding:utf-8");
		header("Pragma: no-cache");
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename=' . $fileName);
		header('Cache-Control: max-age=0');
		ob_end_clean();
		$objWrite = \PHPExcel_IOFactory::createWriter($exportObj, 'Excel5');
		$objWrite->save('php://output');
		exit;
	}
	private function setColumn() {
		$index = 65;
		$title = $this->exportData['title'];
		$list = $this->exportData['list'];
		$column = [];
		$size = 26;
		$forTimes = count($title) <= $size ? count($title) : $size;
		for ($i = 0; $i < $forTimes; $i++) {
			$str = chr($index + $i);
			$column[] = $str;
		}
		if (count($title) > $size) {
			$time = intval(count($title) / $size) - 1;
			if ($time > 1) {
				for ($i = 0; $i < $time; $i++) {
					$indexStr = $column[$time - 1];
					for ($j = 0; $j < 26; $j++) {
						$str = chr($index + $j);
						$column[] = $indexStr . $str;
					}
				}
			}
			$mod = count($title) % $size;
			if ($mod > 0) {
				$indexStr = $column[$time + 1];
				for ($i = 0; $i < $mod; $i++) {
					$str = chr($index + $i);
					$column[] = $indexStr . $str;
				}
			}
		}
		$this->columnArray = $column;
	}
}

调用代码

public function doExport() {
		//session 获取查询直接导出
		//调用导出类处理
		$title = [
			'uid' => 'NO',
			'title' => '街道',
			'duties' => '职称',
			'realname' => '姓名',
			'mobile' => '电话',
		];
		$requestData = Session::get('user_cultutral_mang_param');
		if (empty($requestData)) {
			exit("无数据");
		}
		$countPage = ceil($requestData['count'] / $requestData['pageSize']);
		$style = [
			'uid' => ['width' => 10],
			'title' => ['width' => 30],
			'duties' => ['width' => 50],
			'realname' => ['width' => 30],
			'mobile' => ['width' => 20],
		];
		$export = new Export();
		$export->setStyle($title, $style);
		$excelObj = $export->excelObj;
		for ($page = 0; $page < $countPage; $page++) {
			$requestData['page'] = $page + 1;
			$data = $this->getListData($requestData, false);
			$data = $this->formatData($data, 2);
			$list = $data['list'];
			$data = [
				'title' => $title,
				'list' => $list,
			];
			$export->export($data);
		}
		$fileName = "文化管理员列表_" . time() . ".xls";
		$export->save($fileName);
	}

备注

样式仅设置并处理了width，csv导出有手机中文乱码问题，推荐都用phpexcel。