php操作excel代码封装

<?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;
    }
}