在php中使用换行符生成csv时出现小故障

I am simply generating a csv file based on data stored in a mysql table. The generated csv, when opened in excel, seems mostly ok, but whenever it has a newline character, excel puts the data on a new row. Any idea how to prevent that?

我只是根据存储在mysql表中的数据生成一个csv文件。生成的csv，在excel中打开时，似乎大部分没问题，但只要它有换行符，excel就会将数据放在新行上。知道怎么预防吗？

Sample data

样本数据

line 1 some data
another data

CSV generation code:

CSV生成代码：

header("Content-Type: text/csv; charset=UTF-8");
header("Content-Disposition: attachment; filename=\"".$MyFileName."\"");
$filename = $MyFileName;
$handle = fopen("temp_files/".$filename, "r");
$contents = fread($handle, filesize("temp_files/".$filename));
fclose($handle);
echo $contents;  
exit;

content snippet I used to get rid of new line(didn't work):

内容片段我曾经摆脱新行（没有工作）：

$pack_inst = str_replace(',',' ',$get_data->fields['pack_instruction']);
        $pack_inst = str_replace('\n',' ',$pack_inst);
        $pack_inst = str_replace('\r',' ',$pack_inst);
        $pack_inst = str_replace('\r\n',' ',$pack_inst);
        $pack_inst = str_replace('<br>',' ',$pack_inst);
        $pack_inst = str_replace('<br/>',' ',$pack_inst);
        $pack_inst = str_replace(PHP_EOL, '', $pack_inst);
        $pattern = '(?:[ \t\n\r\x0B\x00\x{A0}\x{AD}\x{2000}-\x{200F}\x{201F}\x{202F}\x{3000}\x{FEFF}]|&nbsp;|<br\s*\/?>)+';
        $pack_inst = preg_replace('/^' . $pattern . '|' . $pattern . '$/u', ' ', $pack_inst);
        $content .=','.$pack_inst;

2 个解决方案

#1

According to RFC 4180, if a column's content contains the row delimiter (\r\n), the column delimiter (,) or the string delimiter (") then you must enclose the content inside double quotes ". When you do that, you must escape all " characters inside the content by preceding them with another ". So the following CSV content:

根据RFC 4180，如果列的内容包含行分隔符（\ r \ n），列分隔符（，）或字符串分隔符（“），则必须将内容括在双引号内”。当你这样做时，你必须通过在内容之前使用另一个字符来转义内容中的所有“字符”。所以以下CSV内容：

1: OK,2: this "might" work but not recommended,"3: new
line","4: comma, and text","5: new
line and ""double"" double quotes"
1: Line 2

Will produce 2 rows of CSV data, first one containing 5 columns.

将生成2行CSV数据，第一行包含5列。

Having said that, have a look at fputcsv() function. It will handle most gory details for you.

话虽如此，看看fputcsv（）函数。它将为您处理大多数血腥细节。

#2

What you show is not the CSV generation code, it is simply the code that you have used to force a download to the browser. Regardless, the function that you need to sort this out is fputcsv(), which will automatically consider all sorts of edge cases that any code you write to convert tabular data to CSV format will likely not consider.

您显示的不是CSV生成代码，它只是您用来强制下载到浏览器的代码。无论如何，你需要对它进行排序的函数是fputcsv（），它将自动考虑各种边缘情况，你编写的任何代码将表格数据转换为CSV格式都可能不会考虑。

You say you are basing this on data in MySQL table, here is a basic framework for creating the CSV file, assuming the MySQLi extension used in a procedural manner:

您说这是基于MySQL表中的数据，这里是创建CSV文件的基本框架，假设MySQLi扩展以程序方式使用：

<?php

  // Connect to database and generate file name here
  $fileName = 'file.csv';

  // Get the data from the database
  $query = "
    SELECT *
    FROM table_name
    WHERE some_column = 'Some Value'
    ORDER BY column_name
  ";
  if (!$result = mysqli_query($db, $query)) {
    // The query failed
    // You may want to handle this with a more meaningful error message
    header('HTTP/1.1 500 Internal Server Error');
    exit;
  } else if (!mysqli_num_rows($result)) {
    // The query returned no results
    // You may want to handle this with a more meaningful error message
    header('HTTP/1.1 404 Not Found');
    exit;
  }

  // Create a temporary file pointer for storing the CSV file
  $tmpFP = fopen('php://temp', 'w+');

  // We'll keep track of how much data we write to the file
  $fileLength = 0;

  // Create a column head row and write first row to file
  $firstRow = mysqli_fetch_assoc($result);
  $fileLength += fputcsv($tmpFP, array_keys($firstRow));
  $fileLength += fputcsv($tmpFP, array_values($firstRow));

  // Write the rest of the rows to the file
  while ($row = mysqli_fetch_row($result)) {
    $fileLength += fputcsv($tmpFP, $row);
  }

  // Send the download headers
  header('Content-Type: text/csv; charset=UTF-8');
  header('Content-Disposition: attachment; filename="'.$fileName.'"');
  header('Content-Length: '.$fileLength);

  // Free some unnecessary memory we are using
  // The data might take a while to transfer to the client
  mysqli_free_result($result);
  unset($query, $result, $firstRow, $row, $fileName, $fileLength);

  // Prevent timeouts on slow networks/large files
  set_time_limit(0);

  // Place the file pointer back at the beginning
  rewind(tmpFP);

  // Serve the file download
  fpassthru($tmpFP);

  // Close the file pointer
  fclose($tmpFP);

  // ...and we're done
  exit;

#1

1: OK,2: this "might" work but not recommended,"3: new
line","4: comma, and text","5: new
line and ""double"" double quotes"
1: Line 2