composer require phpoffice/phpexcel 下载phpexcel类文件
定义命名空间，
调用phpexel类导入导出
<?php

namespace down;

use PHPExcel;
use PHPExcel_IOFactory;

/**
* Excel导入导出
* Class Excel
* @package service
* @date 2017/03/22 15:32
*/
class Excel
{
/**
* 导入数据(xlsx,xls,csv)
* @param string $filename 文件名
* @param array $filedList 字段
* @param bool $isCutting 是否切割数组
* @param int $size 切割数
* @return array $data
*/
public static function upload($filename, $filedList = [], $isCutting = false, $size = 2000)
{
$ext = pathinfo($filename, PATHINFO_EXTENSION);
switch ($ext['extension']) {
case 'xlsx':
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($filename);//加载文件
break;
case 'xls':
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($filename);//加载文件
break;
case 'csv':
$objReader = PHPExcel_IOFactory::createReader('CSV');
$objPHPExcel = $objReader->setInputEncoding('GBK')->load($filename);//加载文件
break;
default:
return [];
break;
}
$highestRow = $objPHPExcel->getSheet()->getHighestRow();//取得总行数
$highestColumn = $objPHPExcel->getSheet()->getHighestColumn();//取得总列数
$cellList = [];
foreach ($filedList as $k => $v) $cellList[] = self::IntToChr($k);
if ($cellList[count($filedList) - 1] != $highestColumn) return [];
$data = [];
for ($i = 0; $i < $highestRow - 1; $i++) {
for ($j = 0; $j < count($cellList); $j++) {
$data[$i][$filedList[$j]] = $objPHPExcel->getActiveSheet()->getCell($cellList[$j] . ($i + 2))->getFormattedValue();
if (is_object($data[$i][$filedList[$j]])) $data[$i][$filedList[$j]] = $data[$i][$filedList[$j]]->__toString();
$data[$i][$filedList[$j]] = preg_replace("/(\s|\ \;|　|\xc2\xa0)/", "", $data[$i][$filedList[$j]]);
}
}
if ($isCutting) return array_chunk($data, $size);
return $data;
}

/**
* 导出数据(xlsx,xls,csv)
* @param array $data 导出数据
* @param array $filedList 字段
* @param array $title 列表头
* @param string $filename 导出文件名
* @param string $ext 文件后缀名
*/
public static function export($data = [], $filedList = [], $title = [], $filename = '导出数据', $ext = 'xlsx')
{
$filename = $filename . '-' . date('YmdHis');
$filetitle = iconv('UTF-8', 'GBK', $filename);
switch ($ext) {
case 'xlsx':
$type = 'Excel2007';
break;
case 'xls':
$type = 'Excel5';
break;
default:
$type = 'CSV';
self::exportCsv($data, $title, $filename . '.' . $ext);
break;
}
$cell = self::getCellName();
$objPHPExcel = new PHPExcel();
$objActSheet = $objPHPExcel->getActiveSheet();
array_unshift($data, $title);
$i = 1;
foreach ($data as $key) {
foreach ($filedList as $k => $v) {
$objActSheet->setCellValue($cell[$k] . $i, $key[$v]);
}
$i++;
}
ob_end_clean();//清除缓冲区,避免乱码
header("pragma:public");
header("Expires: 0");
header("Content-type: text/html; charset=utf-8");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/download");
header("Content-type:application/vnd.ms-excel;charset=utf-8;name={$filetitle}.{$ext}");
header("Content-Disposition:attachment;filename={$filename}.{$ext}");
header("Content-Transfer-Encoding:binary");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $type);
$objWriter->save('php://output');
exit;
}

/**
* CSV导出数据
* @param array $data 导出的数据
* @param array $titleList 文件表头
* @param string $filename 导出文件名
*/
protected static function exportCsv($data = [], $titleList = [], $filename = '导出数据.csv')
{
if (count($data) > 50000) exit;
ob_end_clean();//清除缓冲区,避免乱码
header("pragma:public");
header("Expires: 0");
header("Content-type:text/html;charset=utf-8");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/download");
header("Content-type:application/vnd.ms-excel;charset=utf-8;name={$filename}");
header("Content-Disposition:attachment;filename={$filename}");
header("Cache-Control:max-age=0");
header("Content-Transfer-Encoding:binary");
$file = fopen('php://output', 'a');
$limit = 1000;
$calc = 0;
$title = [];
$tarr = [];
foreach ($titleList as $v) {
$title[] = iconv('UTF-8', 'GBK//IGNORE', $v);
}
fputcsv($file, $title);
foreach ($data as $v) {
$calc++;
if ($limit == $calc) {
ob_flush();
flush();
$calc = 0;
}
foreach ($v as $t) {
$tarr[] = iconv('UTF-8', 'GBK//IGNORE', $t);
}
fputcsv($file, $tarr);
unset($tarr);
}
fclose($file);
exit;
}

/**
* 生成Excel列标
* @param int $index 索引值
* @param int $start 字母起始值
* @return string 返回字母
*/
protected static function IntToChr($index, $start = 65)
{
$str = '';
if (floor($index / 26) > 0) {
$str .= self::IntToChr(floor($index / 26) - 1);
}
return $str . chr($index % 26 + $start);
}

/**
* Excel列标(A~BZ)
* @return array 返回字母
*/
protected static function getCellName()
{
$cellName = [];
$startL = 'A';
while ($startL != 'CA') $cellName[] = $startL++;
return $cellName;
}
}

ddd.zip ( 4.29 MB 下载：488 次 )