//导出
function export_Excel($data,$title){
$path = dirname(__FILE__); //找到当前脚本所在路径
$PHPExcel = new PHPExcel(); //实例化PHPExcel类,类似于在桌面上新建一个Excel表格
$PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
$PHPSheet->setTitle($title); //给当前活动sheet设置名称
//循环给表格赋值(包括标题)
foreach ($data as $key=>$val){
foreach ($val as $k=>$v){
$PHPSheet->setCellValue($k,$v);
}
}
$PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');//按照指定格式生成Excel文件,‘Excel2007’表示生成2007版本的xlsx,‘Excel5’表示生成2003版本Excel文件
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
//header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
header('Content-Disposition: attachment;filename='.$title.'.xlsx');//告诉浏览器输出浏览器名称
header('Cache-Control: max-age=0');//禁止缓存
$PHPWriter->save("php://output");
}
/**
* 数据导入
* @param string $file excel文件
* @param string $sheet
* @return string 返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
function importExecl($filename='', $filedList = [], $isCutting = false, $size = 2000){
$ext = pathinfo($filename, PATHINFO_EXTENSION);
switch ($ext) {
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[] = 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;
}
/**
* 生成Excel列标
* @param int $index 索引值
* @param int $start 字母起始值
* @return string 返回字母
*/
function IntToChr($index, $start = 65)
{
$str = '';
if (floor($index / 26) > 0) {
$str .= IntToChr(floor($index / 26) - 1);
}
return $str . chr($index % 26 + $start);
}