PHP导出常用的导出类（PHPExcel，PhpSpreadsheet -->通过composer自行拉取到对应的项目下）
1：PHPExcel基类
<?php
namespace LYJ\Leyangjun;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Writer_Excel2007;
use PHPExcel_IOFactory;
use PHPExcel_Reader_IReadFilter;
use PHPExcel_Cell_DataType;
/**
* excel导出
*
* Class Excel
* @package Mt\Lib
*
* 使用方式
* use Mt\Lib\Excel;
* $Excel = Excel::getInstance(['is_xlsx'=>true]);
* $data = [['name'=>'cbs'],['name' => 'cbss']];
* $Excel->export('cbstest.xlsx', $data, ['name'=>'名字']);
*/
class Excel
{
private static $instances;
private $objExcel;
private $objWriter;
private $is_xlsx = false;//是否要用Excel2007，默认为Excel5
public function __contruct() {
}
// 单例模式，不能用系统自带的因为权限问题。
public static function getInstance()
{
$className = get_called_class();
$args = func_get_args();
//若$args中有resource类型的参数,则无法区分同一个类的不同实例
$key = md5($className . ':' . serialize($args));
if (!isset(self::$instances[$key])) {
//PHP_VERSION >= 5.6.0
self::$instances[$key] = new $className(...$args);
}
return self::$instances[$key];
}
public function __construct($config=null){
if(isset($config['is_xlsx']) && $config['is_xlsx']){
$this->is_xlsx = true;
}
}
/**
* 把数据格式化为指定的格式
* @param $obj_sheet 'PHPExcel_Worksheet'
* @param $column
* @param $row
* @param $item
* @param $format
* @param $row_data
*/
private function format_item(&$obj_sheet,$column,$row,$item,$format,$row_data){
$format_arr = explode(':',$format);
$format = $format_arr[0];
switch($format){
//把时间戳转成日期时间格式
case 'datetime':
$result = date('Y-m-d H:i:s',$item);
$obj_sheet->setCellValueExplicitByColumnAndRow($column,$row,$result,PHPExcel_Cell_DataType::TYPE_STRING2);
break;
//把时间戳转成日期格式
case 'date':
$result = date('Y-m-d',$item);
$obj_sheet->setCellValueExplicitByColumnAndRow($column,$row,$result,PHPExcel_Cell_DataType::TYPE_STRING2);
break;
//把秒数转成'N天N时N分N秒'时长格式
case 'duration':
$result = '';
$day = (int)($item/86400);
$hour = (int)(($item%86400)/3600);
$minute = (int)(($item%3600)/60);
$sec = (int)($item%60);
if($day > 0){
$result .= $day.'天';
}
if($day > 0 || $hour > 0){
$result .= $hour.'时';
}
if($day > 0 || $hour > 0 || $minute > 0){
$result .= $minute.'分';
}
$result .= $sec.'秒';
$obj_sheet->setCellValueExplicitByColumnAndRow($column,$row,$result,PHPExcel_Cell_DataType::TYPE_STRING2);
break;
//把数据转成超链接，格式：text_item@url:link_item_key:tooltip_item_key
case 'url':
$result = $item;
$link_item_key = isset($format_arr[1]) ? $format_arr[1] : '';
$link_item = isset($row_data[$link_item_key]) ? $row_data[$link_item_key] : '';
$tooltip_item_key = isset($format_arr[2]) ? $format_arr[2] : '';
$tooltip_item = isset($row_data[$tooltip_item_key]) ? $row_data[$tooltip_item_key] : '';
$obj_sheet->setCellValueExplicitByColumnAndRow($column,$row,$result,PHPExcel_Cell_DataType::TYPE_STRING2);
if($result && $link_item){
$obj_sheet->getCellByColumnAndRow($column,$row)->getHyperlink()->setUrl($link_item);
}
if($result && $link_item && $tooltip_item){
$obj_sheet->getCellByColumnAndRow($column,$row)->getHyperlink()->setTooltip($tooltip_item);
}
break;
//不管是数字还是字符串都转为字符串格式
case 'string':
$result = $item;
$obj_sheet->setCellValueExplicitByColumnAndRow($column,$row,$result,PHPExcel_Cell_DataType::TYPE_STRING2);
break;
default:
$result = $item;
$data_type = is_numeric($item) && $item <= 4294967295 ? PHPExcel_Cell_DataType::TYPE_NUMERIC
: PHPExcel_Cell_DataType::TYPE_STRING2;
$obj_sheet->setCellValueExplicitByColumnAndRow($column,$row,$result,$data_type);
break;
}
return $result;
}
/**
* 导出为Excel文件
* 若有指定$header，且$header元素没指定key则$data列的顺序要与$header对应；
* 若有指定$header,且$header元素有指定key，则key要与数据库表字段一致
* @param $filename
* @param $data
* @param array $header
* @param string $dir 导出到目录(默认是导出到标准输出流)
*/
public function export($filename,$data,$header=array(),$dir = null){
$this->objExcel = new PHPExcel();
$this->objWriter = $this->is_xlsx ? new \PHPExcel_Writer_Excel2007($this->objExcel) : new \PHPExcel_Writer_Excel5($this->objExcel);
$this->objExcel->setActiveSheetIndex(0);
$objActSheet = $this->objExcel->getActiveSheet();
$row = 1;
if($header){
$column = 0;
foreach($header as $item){
$objActSheet->setCellValueExplicitByColumnAndRow($column,$row,$item);
$objActSheet->getStyleByColumnAndRow($column,$row)->getFont()->setBold(true);
$column++;
}
$row++;
}
if($data){
$header_keys = array_keys($header);
foreach($data as $row_data){
$column = 0;
if($header){
reset($row_data);
foreach($header_keys as $key){
$item = '';
$format = '';
if(is_numeric($key)){
$current_row = each($row_data);
$item = $current_row['value'];
}
else{
list($row_key,$format) = explode('@',$key);
$item = $row_data[$row_key];
}
$this->format_item($objActSheet,$column,$row,$item,$format,$row_data);
$column++;
}
}
else{
foreach($row_data as $item){
$data_type = is_numeric($item) && $item <= 4294967295 ? PHPExcel_Cell_DataType::TYPE_NUMERIC
: PHPExcel_Cell_DataType::TYPE_STRING2;
$objActSheet->setCellValueExplicitByColumnAndRow($column,$row,$item,$data_type);
$column++;
}
}
$row++;
}
}
if ($dir) {
if (is_writable($dir)) {
$filename = $dir . $filename;
$this->objWriter->save($filename);
}
return;
}
header('Content-Type: application/force-download');
header('Content-Type: application/octet-stream');
header('Content-Type: application/download');
header('Content-Disposition:inline;filename="'.$filename.'"');
header('Content-Transfer-Encoding: binary');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: no-cache');
$this->objWriter->save('php://output');
}
public function import($filename){
$this->objExcel = \PHPExcel_IOFactory::load($filename);
$worksheet = $this->objExcel->getActiveSheet()->toArray();
return $worksheet;
}
/**
* 读取excel转换成数组,和import方法不同,该方法会读取指定的行数,避免占用内存过大
* @param string $excelFile 文件路径
* @param string $excelType excel后缀格式: CSV,
* @param int $startRow 开始读取的行数
* @param int $endRow 结束读取的行数
* @param array $columns 读取列
* @return array
*/
public function readFromExcel($excelFile, $excelType = null, $startRow = 1, $endRow = null, $columns = array())
{
if ($excelType == 'CSV') {
return $this->readFromCsv($excelFile, $startRow, $endRow, $columns);
}
$excelReader = \PHPExcel_IOFactory::createReader($excelType);
// 设置过滤器
$perf = new PHPExcelReadFilter();
$perf->startRow = $startRow;
$perf->endRow = $endRow;
$perf->columns = $columns;
$excelReader->setReadFilter($perf);
$phpexcel = $excelReader->load($excelFile);
$activeSheet = $phpexcel->getActiveSheet();
if (!$endRow) {
$endRow = $activeSheet->getHighestRow(); //总行数
}
$highestColumn = $activeSheet->getHighestColumn(); //最后列数所对应的字母，例如第2行就是B
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); //总列数
$columnNums = array();
foreach ($columns as $c) {
$columnNums[] = \PHPExcel_Cell::columnIndexFromString($c);
}
$data = array();
for ($row = $startRow; $row <= $endRow; $row++) {
for ($col = 0; $col < $highestColumnIndex; $col++) {
if ($columnNums && !in_array(($col + 1), $columnNums)) {
continue;
}
$data[$row][] = (string)$activeSheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $data;
}
/**
* 读取excel转换成数组,和import方法不同,该方法会读取指定的行数,避免占用内存过大
* @param string $excelFile 文件路径
* @param int $startRow 开始读取的行数
* @param int $endRow 结束读取的行数
* @param array $columns 读取列
* @return array
*/
private function readFromCsv($excelFile, $startRow = 1, $endRow = null, $columns = array())
{
$columnNums = array();
foreach ($columns as $c) {
$columnNums[] = \PHPExcel_Cell::columnIndexFromString($c);
}
$result = array();
$f = fopen($excelFile, "r");
$row = 0;
while ($line = fgets($f)) {
$row++;
if ($row < $startRow) {
continue;
}
if ($endRow && $row > $endRow) {
continue;
}
$line = mb_convert_encoding($line, 'UTF-8', 'GBK');
$content = explode(',', $line);
if (!$columnNums) {
$result[] = $content;
continue;
}
$tmp = array();
foreach ($columnNums as $columnNum) {
$tmp[] = $content[$columnNum - 1];
}
$result[] = $tmp;
}
return $result;
}
}
/**
* 读取excel过滤器
*/
class PHPExcelReadFilter implements PHPExcel_Reader_IReadFilter
{
public $startRow = 1;
public $endRow = null;
public $columns = array();
public function readCell($column, $row, $worksheetName = '')
{
if ($row < $this->startRow) {
return false;
}
if ($this->endRow && $row > $this->endRow) {
return false;
}
if ($this->columns && !in_array($column, $this->columns)) {
return false;
}
return true;
}
}
## 2：PhpSpreadsheet基类
```php
<?php
namespace LYJ\Leyangjun;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
class PhpSpreadsheetExcel
{
/**
* 导出Excel
* @param string $fileName 文件名称
* @param array $headArr Excel标题头数组 [['name'=>'标题1', 'width'=>20]] width默认可以没有
* @param array $data 数据内容
* @param array $options 自定义配置信息 ['width'=>15] 目前只有宽度，后续可以增加，可以设置默认宽度
* @param string $suffix 文件后缀，xlsx 和 xls
* @return bool
*/
static function exportExcel($fileName = '', $headArr = [], $data = [], $options = [], $suffix = 'xlsx')
{
@ini_set('memory_limit', '2048M');
@set_time_limit(0);
if (!$headArr || !$data || !is_array($data)) {
return false;
}
$fileName .= "_" . date("YmdHis");
$spreadsheet = new Spreadsheet();
$objPHPExcel = $spreadsheet->getActiveSheet();
// 设置表头
$colum = 'A';
foreach ($headArr as $v) {
$objPHPExcel->setCellValue($colum . '1', $v);
$colum++;
}
$column = 2;
// 行写入
foreach ($data as $key => $rows) {
$span = 'A';
// 列写入
foreach ($rows as $keyName => $value) {
$objPHPExcel->setCellValue($span . $column, $value);
$span++;
}
$column++;
}
// 重命名表（UTF8编码不需要这一步）
if (mb_detect_encoding($fileName) != "UTF-8") {
$fileName = iconv("utf-8", "gbk//IGNORE", $fileName);
}
// 清理缓存
ob_end_clean();
if ($suffix == 'xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$class = "\PhpOffice\PhpSpreadsheet\Writer\Xlsx";
} elseif ($suffix == 'xls') {
header('Content-Type:application/vnd.ms-excel');
$class = "\PhpOffice\PhpSpreadsheet\Writer\Xls";
}
header('Content-Disposition: attachment;filename="' . $fileName . '.' . $suffix . '"');
header('Cache-Control: max-age=0');
$writer = new $class($spreadsheet);
$writer->save('php://output');
// 删除清空 释放内存
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
/**
* 导出csv文件
* @param string $fileName 文件名称
* @param array $headArr Excel标题头数组
* @param array $data 数据内容
* @return mixed
*/
public function exportCsv($fileName = '', $headArr = [], $data = [])
{
@ini_set('memory_limit', '2048M');
@set_time_limit(0);
if (!$headArr || !$data || !is_array($data)) {
return false;
}
try {
setlocale(LC_ALL, 'en_US.UTF-8');
// 输出 Excel 文件头
$fileName = empty($fileName) ? date('YmdHis') : $fileName . '_' . date('YmdHis');
$fileName = $fileName . ".csv";
$string = "";
$string .= implode(',', $headArr) . "\n";//首先写入表格标题栏
foreach ($data as $key => $value) {
foreach ($value as $k => $val) {
$value[$k] = $val;
}
$string .= implode(",", $value) . "\n"; //用英文逗号分开
}
ob_end_clean();
header("Content-type:text/csv");
header("Content-Disposition:attachment;filename=" . $fileName);
header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
header('Expires:0');
header('Pragma:public');
echo iconv('UTF-8', 'GBK//IGNORE', $string);
exit;
} catch (\Exception $e) {
return $e->getMessage();
}
}
/**
* 使用PHPEXECL导入
*
* @param string $file 文件地址
* @param int $sheet 工作表sheet(传0则获取第一个sheet)
* @param int $columnCnt 列数(传0则自动获取最大列)
* @param array $options 操作选项
* array mergeCells 合并单元格数组
* array formula 公式数组
* array format 单元格格式数组
*
* @return mixed
*/
static function importExcel($file = '', $sheet = 0, $columnCnt = 0, &$options = ['format'])
{
try {
/* 转码 */
$file = iconv("utf-8", "gb2312", $file);
if (empty($file) OR !file_exists($file)) {
throw new \Exception('文件不存在!');
}
/** @var Xlsx $objRead */
$objRead = IOFactory::createReader('Xlsx');
if (!$objRead->canRead($file)) {
/** @var Xls $objRead */
$objRead = IOFactory::createReader('Xls');
if (!$objRead->canRead($file)) {
throw new \Exception('只支持导入Excel文件！');
}
}
/* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
empty($options) && $objRead->setReadDataOnly(true);
/* 建立excel对象 */
$obj = $objRead->load($file);
/* 获取指定的sheet表 */
$currSheet = $obj->getSheet($sheet);
if (isset($options['mergeCells'])) {
/* 读取合并行列 */
$options['mergeCells'] = $currSheet->getMergeCells();
}
if (0 == $columnCnt) {
/* 取得最大的列号 */
$columnH = $currSheet->getHighestColumn();
/* 兼容原逻辑，循环时使用的是小于等于 */
$columnCnt = Coordinate::columnIndexFromString($columnH);
}
/* 获取总行数 */
$rowCnt = $currSheet->getHighestRow();
$data = [];
/* 读取内容 */
for ($_row = 1; $_row <= $rowCnt; $_row++) {
$isNull = true;
for ($_column = 1; $_column <= $columnCnt; $_column++) {
$cellName = Coordinate::stringFromColumnIndex($_column);
$cellId = $cellName . $_row;
$cell = $currSheet->getCell($cellId);
//if (isset($options['format'])) {
/* 获取格式 */
$format = $cell->getStyle()->getNumberFormat()->getFormatCode();
/* 记录格式 */
$options['format'][$_row][$cellName] = $format;
//}
if (isset($options['formula'])) {
/* 获取公式，公式均为=号开头数据 */
$formula = $currSheet->getCell($cellId)->getValue();
if (0 === strpos($formula, '=')) {
$options['formula'][$cellName . $_row] = $formula;
}
}
if (isset($format) && 'm/d/yyyy' == $format) {
/* 日期格式翻转处理 */
$cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
}
$data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
if (!empty($data[$_row][$cellName])) {
$isNull = false;
}
}
/* 判断是否整行数据为空，是的话删除该行数据 */
if ($isNull) {
unset($data[$_row]);
}
}
return $data;
} catch (\Exception $e) {
return $e->getMessage();
}
}
}
