背景

PHPExcel_1.8.0导入excel，遇到trim无法处理的空格。

解决方案

$excelVal = preg_replace(“/(\s| | |\xc2\xa0)/”, ‘’, $excelVal);

完整代码

thinkphp5代码

function readExcel($file)
{
    require_once EXTEND_PATH . 'PHPExcel_1.8.0/Classes/PHPExcel.php';
    $reader = new PHPExcel_Reader_Excel2007();
    if (!$reader->canRead($file)) {
        $reader = new PHPExcel_Reader_Excel5();
        if (!$reader->canRead($file))
            exit(json_encode(['status' => 0, 'msg' => '请选择Excel文件']));
    }
    return $reader->load($file);
}

$excel = readExcel($_FILES['file']['tmp_name']);

$sheet = $excel->getSheet(0);
$maxRow = $sheet->getHighestRow();
$maxCol = $sheet->getHighestColumn();
if ($maxRow < 1) {
    output(0, 'EXCEL里面没有内容');
}

$rs = array();
for ($nowRow = 0; $nowRow < $maxRow; $nowRow++) {
    for ($nowCol = 0; $nowCol < Helper::xyChange($maxCol); $nowCol++) {
        $address = Helper::xyChange($nowCol + 1) . ($nowRow + 1);
        $excelVal = trim($sheet->getCell($address)->getValue());
        $excelVal = str_replace("\n", '', $excelVal);
        // 去除带空格，而trim去不掉的情况
        $excelVal = preg_replace("/(\s|\&nbsp\;| |\xc2\xa0)/", '', $excelVal);
        $rs[$nowRow][] = trim($excelVal);
    }
}

/**
 * excel 坐标转换 (按需求,扩充)
 */
public static function xyChange($var)
{
    $var = trim($var);
    $arr = array(
        'A' => 1,
        'B' => 2,
        'C' => 3,
        'D' => 4,
        'E' => 5,
        'F' => 6,
        'G' => 7,
        'H' => 8,
        'I' => 9,
        'J' => 10,
        'K' => 11,
        'L' => 12,
        'M' => 13,
        'N' => 14,
        'O' => 15,
        'P' => 16,
        'Q' => 17,
        'R' => 18,
        'S' => 19,
        'T' => 20,
        'U' => 21,
        'V' => 22,
        'W' => 23,
        'X' => 24,
        'Y' => 25,
        'Z' => 26,
        'AA' => 27,
        'AB' => 28,
        'AC' => 29,
        'AD' => 30,
        'AE' => 31,
        'AF' => 32,
        'AG' => 33,
        'AH' => 34,
        'AI' => 35,
        'AJ' => 36,
        'AK' => 37,
        'AL' => 38,
        'AM' => 39,
        'AN' => 40,
        'AO' => 41,
        'AP' => 42,
        'AQ' => 43,
        'AR' => 44,
        'AS' => 45,
        'AT' => 46,
        'AU' => 47,
        'AV' => 48,
        'AW' => 49,
        'AX' => 50,
        'AY' => 51,
        'AZ' => 52,
    );
    if (is_numeric($var)) {
        foreach ($arr as $k => $v) {
            if ($v == $var) {
                return $k;
            }
        }
        return false;
    } else {
        if (isset($arr[$var])) {
            return $arr[$var];
        }
        return false;
    }
}