<?php
class tools {
/**
* 描述: 处理导入的excel文件
*/
public static function importExcel($filename, callable $callback) {
if (!file_exists($filename)) {
throw new Exception("文件{$filename}不存在", 404);
}
//读取文件
$fileType = PHPExcel_IOFactory::identify($filename);
$objReader = PHPExcel_IOFactory::createReader($fileType);
/** @var $objPHPExcel PHPExcel * */
$objPHPExcel = $objReader->load($filename);
$sheet = $objPHPExcel->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$result = [];
$keys = array_map('trim', $sheet->rangeToArray("A1:{$highestColumn}1", null, false, false)[0]);
for ($row = 2; $row <= $highestRow; $row++) {
$values = array_map('trim', $sheet->rangeToArray("A{$row}:{$highestColumn}{$row}", null, false, true)[0]);
foreach ($values as $key => $value) {
$column = self::int2column($key);
if (preg_match('/^=\w+.*\)$/', $value)) {
$values[$key] = $sheet->getCell("{$column}{$row}")->getOldCalculatedValue();
}
}
$result[] = call_user_func($callback, array_combine($keys, $values), $sheet, $row);
}
return $result;
}
/**
* 描述: 导出excel
*/
public static function exportExcel(callable $callback, $name = '') {
$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
//$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
call_user_func($callback, $objPHPExcel, $sheet);
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name . date('YmnHis') . '.xlsx"');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //xls 标准
$objWriter->save('php://output');
}
/**
* 描述: 整数转换为excel的列名 0 => A ,1 => B.... 27 => AA, 依次类推
*/
public static function int2column($i) {
if ($i > 26 * 26 + 25) {
throw new Exception('数值过大', 400);
}
$number = 65 + $i;
$start = intval(($number - 65) / 26);
$prefix = "";
if ($start >= 1) {
$prefix = chr(65 + $start - 1);
}
return "{$prefix}" . chr($number - 26 * $start);
}
/**
* 描述: excel 导入模板下载
*/
public static function excelImportTemplate(array $headers, $name = '') {
self::exportExcel(function (PHPExcel $objPHPExcel, PHPExcel_Worksheet $sheet) use ($headers) {
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//居中
$keys = array_keys($headers);
$rowCount = 1;
if ($keys[0] !== 0) {
foreach ($keys as $key => $title) {
$column = self::int2column($key);
$sheet->getColumnDimension($column)->setWidth(2.5 * (mb_strlen($title) + 1));
$sheet->setCellValue("{$column}{$rowCount}", $title);
}
$rowCount++;
}
$values = array_values($headers);
foreach ($values as $key => $title) {
$column = self::int2column($key);
if ($rowCount === 1) {
$sheet->getColumnDimension($column)->setWidth(2.5 * (mb_strlen($title) + 1));
}
$sheet->setCellValue("{$column}{$rowCount}", $title);
}
}, $name);
}
/**
* 描述: 处理Excel读取的日期数据
*/
public static function parseExcelGetDateValue($value, $default = '2000-01-01') {
if (preg_match('/\d{4}[\-\/]\d{1,2}[\-\/]\d{1,2}/', trim($value))) {// 2023-12-1
return date('Y-m-d', strtotime(trim($value)));
}
if (preg_match('/\d{2}[\-\/]\d{2}[\-\/]\d{2}/', trim($value))) {// 02-15-22
$value = preg_replace('/(\d{2})[\-\/](\d{2})[\-\/](\d{2})/', '$3-$1-$2', $value);
return date('Y-m-d', strtotime(trim($value)));
}
if (preg_match('/^(\d+)$/', $value) && $value > 0) {
return gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value));
}
return $default;
}
}