PHP导出Excel类 Excel.class.php

<?php

namespace Common\Cls;

/**
 * 生成excel文件操作
 *
 * @author wesley wu
 * @date 2013.12.9
 */
class Excel {

	private $limit = 10000;

	public function download($data, $fileName) {
		// 设置正确的HTTP头
		header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
		header("Content-Disposition: attachment; filename=\"" . $this->convertEncoding($fileName, 'utf-8', 'gbk') . ".xlsx\"");
		header("Expires: 0");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Cache-Control: private", false);
		header("Pragma: public");

		echo $this->generateExcel($data, $fileName);
	}

	private function generateExcel($data, $fileName) {
		// 开始生成Excel XML格式的数据
		$xml = '<?xml version="1.0" encoding="gbk"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">';
		$xml .= '<Worksheet ss:Name="' . $this->convertEncoding($fileName, 'utf-8', 'gbk') . '"><Table>';
		$guard = 0;
		foreach ($data as $v) {
			$guard++;
			if ($guard == $this->limit) {
				ob_flush();
				flush();
				$guard = 0;
			}
			$xml .= $this->_addRow($v);
		}
		$xml .= '</Table></Worksheet></Workbook>';

		return $xml;
	}

	private function _addRow($row) {
		$cells = "";
		foreach ($row as $k => $v) {
			$cells .= "<Cell><Data ss:Type=\"String\">" . $this->convertEncoding($v, 'utf-8', 'gbk') . "</Data></Cell>\n";
		}
		return "<Row>\n" . $cells . "</Row>\n";
	}

	private function convertEncoding($text, $fromEncoding, $toEncoding) {
		return mb_convert_encoding($text, $toEncoding, $fromEncoding);
	}

	function buildData($header = [], $data = []) {
		$arr = [];
		foreach ($data as $item) {
			$arr[] = array_values($item);
		}
		if ($header) array_unshift($arr, $header);
		return $arr;
	}
}

form.on('submit(export)', function (e) {
	e.field.export = 'on'
	query = parseParams(e.field)
	console.log(query)
	location.href = '__ACTION__/?' + query
	return false
})

parseParams = function (data) {
	try {
		var tempArr = []
		for (var i in data) {
			var key = encodeURIComponent(i)
			var value = encodeURIComponent(data[i])
			tempArr.push(key + '=' + value)
		}
		var urlParamsStr = tempArr.join('&')
		return urlParamsStr
	} catch (err) {
		return ''
	}
}

if (\I('export') == 'on') {
	$header = ['日期', '项目', '工程机械', '开机时间', '关机时间', '台时', '驾驶员', '审核状态', '项目经理', '审核时间'];
	$data = \M('job') // 这里不要使用视图模型viewModel，会导致field字段顺序错乱
		->field("job_startdate ,job_prj_id_ ,job_car_id_ ,job_startdatetime ,job_enddatetime ,job_hours ,job_drv_id_ ,job_status_ ,job_mgr_id_ ,job_statusdatetime")
		->where($map)
		->order("job_startdate desc,job_id desc")
		->select();
	$excel = new Excel;
	$data = $excel->buildData($header, $data);
	$excel->download($data, '施工日志' . \date('YmdHis'));
	die;
}