/**
* 创建目录
* @param $path 路径
* @param int $mode 目录权限
* @param bool $recursive
* @return bool
* @throws \Exception
* @author zhangao@dycd.com
*/
public function createDirectory($path, $mode = 0775, $recursive = true)
{
if (is_dir($path)) {
return true;
}
$parentDir = dirname($path);
if ($recursive && !is_dir($parentDir)) {
$this->createDirectory($parentDir, $mode, true);
}
try {
$result = mkdir($path, $mode);
chmod($path, $mode);
} catch (\Exception $e) {
throw new \Exception("Failed to create directory '$path': " . $e->getMessage(), $e->getCode(), $e);
}
return $result;
}
/**
* 过滤字符串去除emoji表情等...
* @param $string
* @return string
* @author zhangao@dycd.com
*/
public function handleString($string){
$charArr = preg_split('/(?
$final = [];
foreach($charArr as $char) {
//if($char != ' ' && preg_match('/[0-9 a-z A-Z \x{4e00}-\x{9fa5}]/u',$char)) {
//暂时允许带空格
if($char == '—' || $char == '-' || preg_match('/[0-9 a-z A-Z \x{4e00}-\x{9fa5}]/u',$char)) {
$final[] = $char;
}
}
return implode('',$final);
}
/**
* 下载文件
* @param $fileName
* @param bool $delDesFile
* @param bool $isExit
* @throws Exception
* @author zhangao@dycd.com
*/
public function download( $fileName, $delDesFile = false, $isExit = true ) {
if ( file_exists( $fileName ) ) {
header( 'Content-Description: File Transfer' );
header( 'Content-Type: application/octet-stream' );
header( 'Content-Disposition: attachment;filename = ' . basename( $fileName ) );
header( 'Content-Transfer-Encoding: binary' );
header( 'Expires: 0' );
header( 'Cache-Control: must-revalidate, post-check = 0, pre-check = 0' );
header( 'Pragma: public' );
header( 'Content-Length: ' . filesize( $fileName ) );
ob_clean();
flush();
readfile( $fileName );
if ( $delDesFile ) {
unlink( $fileName );
}
if ( $isExit ) {
exit;
}
} else {
throw new \Exception($fileName . ' is not exist');
}
}
/**
* 导出excel表格
* @param array $data 需要导出的数据
* @return string 文件存储路径
* @throws Exception
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* @author zhangao@dycd.com
*/
public function exportExcel($data){
Vendor('PHPExcel.PHPExcel');
$objectPHPExcel = new PHPExcel();
//设定缓存模式为经gzip压缩后存入cache
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
$cacheSettings = array();
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);
$objectPHPExcel->setActiveSheetIndex(0);
$chars = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O', 'P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','Al','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
//表格头的输出
foreach($data['map'] as $key=>$val) {
$objectPHPExcel->setActiveSheetIndex(0)->setCellValue($chars[$key].'1',' '.$val['key']);
}
foreach ( $data['rows'] as $k => $val ) {
if(!$val) continue;
$n = $k;
//明细的输出
foreach ($data['map'] as $kk => $vv) {
if(!isset($val[$vv['value']])) continue;
if (!is_numeric($val[$vv['value']])) {
$objectPHPExcel->getActiveSheet()->setCellValue($chars[$kk] . ($n + 2),$this->handleString(' ' . $val[$vv['value']]));
} else {
$objectPHPExcel->getActiveSheet()->setCellValue($chars[$kk] . ($n + 2), ' ' . $val[$vv['value']]);
}
}
}
$objectPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
$objectPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);
$fileName=$data['title'].date('Ymdhis').".xls";
$dir = RUNTIME_PATH.'/files/excel/';
if(!is_dir($dir)) {
$this->createDirectory($dir, 0777);
}
$path = $dir.$fileName;
$objWriter= \PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5');
$objWriter->save($path);
$this->download( $path, true );
}