该工具集成了PHPExcel的功能,可自动冻结首行、自动处理超长数字、导出到下载、导出到文件目录
话不多说直接上代码
<?php
namespace func;
require_once('PHPExcel.php');
use PHPExcel;
use PHPExcel_Cell;
use PHPExcel_Style_Alignment;
/**
* Excel表格导出工具
* Class ExcelExporter
* @package func
*/
class ExcelExporter
{
const UPLOAD_SITE_URL = $_SERVER['HTTP_HOST'];
/**
* 导出到http下载
* @param $name
* @param $data
* @param $header
* @throws \PHPExcel_Writer_Exception
*/
public static function exportHttp($name,$data,$header){
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename='.$name.'.xlsx');
$objWriter = self::createDataWriter($data,$header);
//下载文件在浏览器窗口
$objWriter->save('php://output');
}
/**
* 导出到文件目录
* @param $name
* @param $data
* @param $header
* @param string $path
* @return string
* @throws \PHPExcel_Writer_Exception
*/
public static function exportFile($name,$data,$header,$path = ''){
$relativePath = '/'.($path?:'daily').'/';
$fileDir = BASE_UPLOAD_PATH.$relativePath;
if(!file_exists($fileDir)){
mkdir($fileDir,0755,true);
}
$fileName = $name.date('-YmdHis').'.xlsx';
$objWriter = self::createDataWriter($data,$header);
$objWriter->save($fileDir.$fileName);
return UPLOAD_SITE_URL.$relativePath.$fileName;
}
private static function createDataWriter($data,$header){
//set_time_limit(0);
ini_set('memory_limit','1024M');
$objPHPExcel = new PHPExcel();
//单元格居中和设置字体和字体大小
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);//居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setShrinkToFit(true);
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(false);
$objPHPExcel->getDefaultStyle()->getFont()->setName('Microsoft YaHei Light')->setSize(11);//设置字体
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
$sheet->getRowDimension(1)->setRowHeight(30);//设置行高
$sheet->getDefaultColumnDimension()->setAutoSize(true);//设置宽度
$i=0;
foreach ($header as $key=>$val) {
$t_key = PHPExcel_Cell::stringFromColumnIndex($i);
$sheet->getStyle($t_key.'1')->getFont()->setBold(true);//设置加粗
$sheet->getColumnDimension($t_key)->setAutoSize(true);//设置宽度
$sheet->setCellValue($t_key.'1',$val);
$i++;
}
$a = 2; //合并起始行
//设置导出内容
foreach ($data as $key=> $item){
if($key%1000 == 0){
ob_flush();
flush();
}
if($item['sub_rows']>1){
$sheet->getRowDimension($a)->setRowHeight(20*$item['sub_rows']);
}
$i=0;
foreach ($header as $head_key=>$val){
$t_key = PHPExcel_Cell::stringFromColumnIndex($i);
//字符化显示0开头的数字、长数字
if(preg_match("/^0\\d+$/",$item[$head_key]) || (is_numeric($item[$head_key]) && strlen($item[$head_key])>10)){
$sheet->setCellValueExplicit($t_key.$a,$item[$head_key]??'');
}else{
$sheet->setCellValue($t_key.$a,$item[$head_key]??'');
}
$i++;
}
$a++;
}
$sheet->freezePane('A2');
//生成excel文件
return \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
}
}
发表评论