本文共计729个文字，预计阅读时间需要3分钟。
phpExcel类定义：- 私有属性：$footer, $sEncoding, $bConvertTypes, $sWorksheetTitle- 公共构造函数：接受编码、类型转换和表作为参数
Excel.class.php
\n
";
private $footer = "
";
private $sEncoding = 'UTF-8';
private $bConvertTypes = false;
private $sWorksheetTitle;
public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Sheet1') {
$this->bConvertTypes = $bConvertTypes;
$this->setEncoding($sEncoding);
$this->setWorksheetTitle($sWorksheetTitle);
}
public function setEncoding($sEncoding) {
$this->sEncoding = $sEncoding;
}
public function setWorksheetTitle($title) {
$title = preg_replace("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
$title = substr($title, 0, 31);
$this->sWorksheetTitle = $title;
}
public function generate($filename = 'excel-export', $array = []) {
$filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);
header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");
echo stripslashes(sprintf($this->header, $this->sEncoding));
echo "\n
sWorksheetTitle . "\">\n
|
" . $v . "
|
\n" . $cells . "
\n"; foreach ($array as $key => $value) { $cells = ""; foreach ($value as $k => $v) { $type = 'String'; if ($this->bConvertTypes === true && is_numeric($v)) { $type = 'Number'; } $v = htmlentities($v, ENT_COMPAT, $this->sEncoding); $cells .= "\n"; } echo "\n"; } echo "
\n
\n";
echo $this->footer;
}
/**
* 生成excel表格
*
* @param array $title
* 不带下标的数组
* @param array $data
* 不带下标的数据二维数组
* @param string $file_name
* 文件名,不需要带扩展名
*
* @return void
*/
public function export($title, $data, $file_name) {
// 获取客户当前系统的版本
// 默认把文字编码由utf8转成gbk
// 如果是windows则进行转换,否则不进行转换
$conv = $this->get_os_info();
if ($conv) {
header("Content-type:application/vnd.ms-excel;charset=GBk");
} else {
header("Content-type:application/vnd.ms-excel;charset=UTF-8");
}
header("Content-Disposition:attachment; filename=$file_name.xls");
// 输出标题
if (!empty($title) && is_array($title)) {
foreach ($title as $value) {
$this->exportXls($value, $conv);
}
$this->printEnter();
}
if (!empty($data) && is_array($data)) {
foreach ($data as $item) {
$list = is_array($item) ? array_values($item) : [];
if (!empty($list)) {
foreach ($list as $row) {
$this->exportXls($row, $conv);
}
}
$this->printEnter();
}
}
exit();
}
/**
* 把字符串转换成gbk格式,并输出tab空格
*
* @param string $str
*/
private function exportXls($str, $conv = true) {
// chr(9) tab空格
if ($conv) { // 需要转换
echo mb_convert_encoding($str, 'gbk', 'UTF-8') . chr(9);
} else { // 不需要转换
echo $str . chr(9);
}
}
/**
* 输出回车/换行
*/
private function printEnter() {
echo chr(13);
}
/**
* 获取客户当前操作系统版本,true表示windows系统,false表示非windows系统
*
* @access public
* @return boolean true | false
*/
private function get_os_info() {
// 默认初始化为windows系统
$return = true;
$os = "";
$Agent = $_SERVER["HTTP_USER_AGENT"];
if (eregi('win', $Agent) && strpos($Agent, '95')) {
$os = "Windows 95";
} elseif (eregi('win 9x', $Agent) && strpos($Agent, '4.90')) {
$os = "Windows ME";
} elseif (eregi('win', $Agent) && ereg('98', $Agent)) {
$os = "Windows 98";
} elseif (eregi('win', $Agent) && eregi('nt 5.0', $Agent)) {
$os = "Windows 2000";
} elseif (eregi('win', $Agent) && eregi('nt', $Agent)) {
$os = "Windows NT";
} elseif (eregi('win', $Agent) && eregi('nt 5.1', $Agent)) {
$os = "Windows XP";
} elseif (eregi('win', $Agent) && ereg('32', $Agent)) {
$os = "Windows 32";
} elseif (eregi('linux', $Agent)) {
$os = "Linux";
} elseif (eregi('unix', $Agent)) {
$os = "Unix";
} elseif (eregi('sun', $Agent) && eregi('os', $Agent)) {
$os = "SunOS";
} elseif (eregi('ibm', $Agent) && eregi('os', $Agent)) {
$os = "IBM OS/2";
} elseif (eregi('Mac', $Agent) && eregi('PC', $Agent)) {
$os = "Macintosh";
} elseif (eregi('PowerPC', $Agent)) {
$os = "PowerPC";
} elseif (eregi('AIX', $Agent)) {
$os = "AIX";
} elseif (eregi('HPUX', $Agent)) {
$os = "HPUX";
if (eregi('NetBSD', $Agent)) {
$os = "NetBSD";
} elseif (eregi('BSD', $Agent)) {
$os = "BSD";
} elseif (ereg('OSF1', $Agent)) {
$os = "OSF1";
} elseif (ereg('IRIX', $Agent)) {
$os = "IRIX";
} elseif (eregi('FreeBSD', $Agent)) {
$os = "FreeBSD";
}
}
if (!empty($os)) {
if (stristr($os, 'Win')) {
$return = true;
} else {
$return = false;
}
}
return $return;
}
}
