工作中比较多地遇到导出excel的需求,我通常是用phpspread完成,然而有时候像导出身份证号,银行卡号,订单号这些比较长而且格式为纯数字的数据时往往会出现变成科学计数法的情况,设置为默认文本格式又会出现末尾数字变成0的情况

经过搜索和测试后总算是有了解决办法,我将其封装成了一个函数：

function CreateExcel($Data, $Header, $Path, $FileName,$LongNumberField=null)
{
    $SpreadSheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $Sheet = $SpreadSheet->getActiveSheet();
    if($LongNumberField===null){
        array_unshift($Data, $Header);
        $SpreadSheet->getDefaultStyle()->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
        $Sheet->fromArray($Data);
    }
    else{
        $HeaderCount=count($Header);
        for($i=0;$i<$HeaderCount;$i++){
            $Sheet->setCellValueByColumnAndRow($i+1,1,$Header[$i]);
        }
        $RowIndex=2;
        $DataCount=count($Data);
        for($i=0;$i<$DataCount;$i++){
            $ColumnIndex=1;
            foreach ($Data[$i] as $Key=>$Value){
                if(in_array($Key,$LongNumberField)){
                    $Sheet->setCellValueExplicitByColumnAndRow($ColumnIndex,$RowIndex,$Value,\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                }
                else{
                    $Sheet->setCellValueByColumnAndRow($ColumnIndex,$RowIndex,$Value);
                }
                $ColumnIndex++;
            }
            $RowIndex++;
        }
    }
    $Xlsx = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($SpreadSheet);
    $Xlsx->save($Path . $FileName);
}

　　示例：

$Data=[
        ['id'=>1,'name'=>'张三','bank_card'=>'123456789123456789'],
        ['id'=>2,'name'=>'李四','bank_card'=>'123456123456789789'],
];
$Header=['id','姓名','银行卡号'];
$Path='D:/www/test/';
$FileName='Export.xlsx';
$LongNumberField=['bank_card'];
CreateExcel($Data,$Header,$Path,$FileName,$LongNumberField);