phpspreadsheet导出Excel报错问题汇总

phpspreadsheet导出Excel报错问题汇总

使用 phpspreadsheet 导出Excel的一段代码如下：

$sheet->setCellValue($cellName[$kk] . ($key + 2), $vv);

换行符(\n)问题

如果某个字段值最后面包含 换行符(\n) 会导致报错：

Invalid numeric value for datatype Numeric

解决办法：

• 如果需要保留最后面的换行符，可以追加一个空白字符：

if (substr($vv, -1) == "\n" || substr($vv, -1) == "\t") {
     $vv .= " ";
}
$sheet->setCellValue($cellName[$kk] . ($key + 2), $vv);

• 如果不需要保留最后面的换行符，使用trim过滤即可：

$vv=trim($vv);
$sheet->setCellValue($cellName[$kk] . ($key + 2), $vv);

等号(=)问题

如果某个字段值最后面包含 等号(=) 会导致报错：

Worksheet!xxx -> Formula Error: An unexpected error occurred

原因分析：在 excel 中，如果某个单元格中的值是以 = 开头，则表示这个单元格是根据其他单元格的值计算出来的，= 后面需要跟着一个合法的表达式。但是此处我并不是要计算一个表达式，而是因为字段值以=开头了。那么，解决方案就是在 = 前面加个其他字符。代码如下：

if (strpos($vv, '=') === 0 ) {
    $vv = "'" . $vv; // 在 = 前面加个单引号
}

完整方法示例

使用phpspreadsheet 导出Excel的方法示例如下：

安装扩展：composer require phpoffice/phpspreadsheet

<?php

namespace app\common\utils;

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class FileUtil
{
	/**
     * 导出Excel文件
     * @param array $list 数据列表数组
     * @param array $field_map 数组字段数组
     * @param string $file_path 文件路径
     * @param string $filename 文件名称
     * @param bool $filename_append_now_time 文件名是否追加当前时间
     * @param string $suffix 文件后缀
     * @return false|string
     * @throws Exception
     */
    public static function outputExcel(array $list, array $field_map, string $file_path, string $filename, bool $filename_append_now_time = true, string $suffix = 'xlsx')
    {
        //处理 $list 数据列表
        $data = [];
        foreach ($list ?? [] as $v) {
            $item = [];
            foreach ($field_map as $field_key => $field_val) {
                //解析 $field_map 中 数组多级key的数据,需要在 $field_map 中定义多个key 以.分隔, 例如: user.info.name
                $field_key_split = explode('.', $field_key);
                $container = $v;
                for ($index = 0; $index <= 2; $index++) {
                    if (isset($field_key_split[$index]) && isset($container[$field_key_split[$index]])) {
                        $container = $container[$field_key_split[$index]];
                    }
                }
                if (is_array($container)) {
                    $container = '';
                }
                $item[$field_key] = $container;
            }
            //$data[] = array_values($item);
            $data[] = $item;
        }

        //表头A-Z列定义
        $cellName = [];
        for ($i = 'A'; $i <= 'Z'; $i++) {
            $cellName[] = $i;
        }
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //设置表头行
        $titleList = array_values($field_map);
        //设置表头单元格样式
        $styleArray = [
            'font' => [
                'bold' => true, // 字体加粗
                'color' => [
                    'rgb' => '0000ff',
                ],
            ]
        ];
        foreach ($titleList as $tkey => $tval) {
            $sheet->setCellValue($cellName[$tkey] . '1', $tval);
            $sheet->getStyle($cellName[$tkey] . '1')->applyFromArray($styleArray);
        }

        //设置表数据内容行和列
        foreach ($data ?? [] as $key => $val) {
            $val = array_values($val);
            foreach ($val as $kk => $vv) {
                if (is_string($vv)) { //对于字符串类型,防止较长的数字字符串被格式化为科学计数法
                    $sheet->setCellValueExplicitByColumnAndRow(($kk + 1), ($key + 2), $vv, DataType::TYPE_STRING);
                } else {
                    //导出的某个字段值里面包含换行符(\n)会导致报错,因此需要过滤前后的空格和换行符
                	$vv=trim($vv);
                	//导出的数据中如果存在 "=" 开头的字符串,会出错 Formula Error: An unexpected error occurred",因此需要对 "=" 特殊处理
                    if (strpos($vv, '=') === 0 ) {
                        $vv = "'" . $vv; // 在 = 前面加个单引号
                    }
                    $sheet->setCellValue($cellName[$kk] . ($key + 2), $vv);
                }
            }
        }

        //创建目录并写入Excel文件
        if (!is_dir($file_path)) {
            if (!mkdir($file_path, 0777, true)) {
                return false;
            }
        }
        if ($filename_append_now_time) {
            $filename .= '-' . date("YmdHis", time());
        }
        $filename .= '.' . strtolower($suffix);
        $objWriter = new Xlsx($spreadsheet);
        $objWriter->save($file_path . '/' . $filename);

        return $filename;
    }

}

调用部分示例：

$list = json_decode('[{"id":1,"username":"jihongchu","mobile":"177888","nickname":"王先生","avatar":"https://profile-avatar.csdnimg.cn/default.jpg","status":1,"time":{"last_login_time":"2024-03-22 14:42:14","add_time":"2024-03-22 14:42:14","update_time":"2024-03-27 10:47:57"}}]',true);

//设置导出的字段和表头
$field_map = [
    "id" => "ID",
    "username" => "用户名",
    "mobile" => "手机号",
    "nickname" => "昵称",
    "status" => "状态",
    //针对二维模型导出的字段定义
    "time.last_login_time" => "最后登录时间",
    "time.add_time" => "添加时间",
    "time.update_time" => "修改时间",
];
$file_path = '/Users/xxx/Downloads'; //绝对路径
$file_title = '用户数据导出-' . time();
$output_file_name = FileUtil::outputExcel($list, $field_map, $file_path, $file_title);

导出结果：