phpspreadsheet导出Excel报错: Invalid numeric value for datatype Numeric

本文主要是介绍phpspreadsheet导出Excel报错: Invalid numeric value for datatype Numeric，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

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

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

Invalid numeric value for datatype Numeric

解决办法：

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

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

	/*** 导出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) 会导致报错: Invalid numeric value for datatype Numericif (substr($vv, -1) == "\n" || substr($vv, -1) == "\t") {$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 = $file_root . '/' . $file_folder; //绝对路径
$file_title = '用户数据导出-' . time();
$output_file_name = FileUtil::outputExcel($list, $field_map, $file_path, $file_title);