如何用PHP避免Excel将CSV中的字符串自动转为日期

Excel会自动识别类似XX-XX格式的字符串为日期（比如10-14被转为Oct-14），要让这类内容以纯文本形式在Excel中展示，可通过以下几种PHP处理方案：

方案1：在字符串前添加单引号 #

在可能被识别为日期的字段前添加英文单引号'，Excel会自动隐藏单引号并将内容视为纯文本，这是兼容性最好的方案。

修改你代码中处理街道编号和单元号的部分：

// 处理地址街道编号
$changeStreetNumber = !empty($row['address_street_number']) ? "'" . $row['address_street_number'] : '';
// 处理单元号
$changeUnitNumber = !empty($row['property_adr_unit_number']) ? "'" . $row['property_adr_unit_number'] : '';

方案2：用="内容"包裹字符串 #

通过="原始内容"的格式包裹字段，强制Excel按文本解析内容，这种方式不会留下隐藏的单引号，但需要注意fputcsv的自动引号逻辑。

修改对应字段处理逻辑：

$changeStreetNumber = !empty($row['address_street_number']) ? '="' . $row['address_street_number'] . '"' : '';
$changeUnitNumber = !empty($row['property_adr_unit_number']) ? '="' . $row['property_adr_unit_number'] . '"' : '';

若使用此方案，需避免fputcsv重复添加引号，可手动拼接CSV行：

// 替代fputcsv的手动拼接逻辑
$line = implode($delimiter, array_map(function($item) {
    if (str_starts_with($item, '="')) {
        return $item;
    }
    return '"' . str_replace('"', '""', $item) . '"';
}, $lineData)) . "\n";
fwrite($f, $line);

完整修改后的代码示例（整合方案1） #

$delimiter = ",";
$fileName = "lead-contact-property" . date('d-m-y') . ".csv";
$f = fopen('csv_file/lead-contact-property.csv', 'w');

$fields = array(
    'Lead id', 'Contact id', 'Property id', 'Type', 'Source', 
    'Assignee Name', 'Sender Email', 'Spam Score','Is Spam','Subject',
    'Plain Body','Contact Name','Name Salutation','Name Addressee',
    'Contact Email','Primary Info Phone','Property Address Unit Number',
    'Address Street Number','Address Street Name','Address Suburb or Town',
    'Address Locality','Address state or Region','Address PostCode',
    'Address Country','Address Estate Name','Address Estate Stage',
    'Property Type','Audit Owned By','Audit Created At','Audit Created By',
    'Audit Last Modified At','Audit Last Modified By'
);
fputcsv($f, $fields, $delimiter);

while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
    $audtCreatedAt = date("d/m/Y H:i:s ", $row['audit__created_at']);
    $auditModifyAt = date("d/m/Y H:i:s ", $row['audit_last_modified_at']);
    
    // 处理可能被识别为日期的字段，添加单引号前缀
    $changeStreetNumber = !empty($row['address_street_number']) ? "'" . $row['address_street_number'] : '';
    $changeUnitNumber = !empty($row['property_adr_unit_number']) ? "'" . $row['property_adr_unit_number'] : '';
    
    $lineData = array(
        $row['lead_id'], $row['contact_id'], $row['property_id'], $row['type'], $row['source'],
        $row['assignee_name'], $row['sender_email'], $row['spam_score'], $row['is_spam'], $row['subject'],
        $row['plain_body'], $row['contact_name'], $row['name_salutation'], $row['name_addressee'],
        $row['contact_email'], $row['primary_info_phone'], $changeUnitNumber, $changeStreetNumber,
        $row['address_street_name'], $row['address_suburb_or_town'], $row['address_locality'],
        $row['address_state_or_region'], $row['address_postcode'], $row['address_country'],
        $row['address_estate_name'], $row['address_estate_stage'], $row['property_type'],
        $row['audit_owned_by'], $audtCreatedAt, $row['audit_created_by'], $auditModifyAt,
        $row['audit_last_modified_by']
    );
    fputcsv($f, $lineData, $delimiter);
}

fseek($f, 0);
fpassthru($f);

内容的提问来源于stack exchange，提问作者Vishal