Как в PHP делать импорт/экспорт CSV для MS Excel

Многие, работая с PHP и MySQL, сталкивались с проблемой экспорта/импорта данных в CSV файл, понятный для Excel. Функции fputcsv() и fgetcsv() работают не так, как нам хотелось бы. И открывая полученный CSV-файл в Экселе мы видим половину строки в одной ячейке, а другую часть строки во второй. Иногда такие ошибки интерпретации данных Экселем связаны с его ограничением количества данных в одной ячейке – 50 000 символов, если больше, то оставшиеся данные отображаются в новой строке.

Описание формата CSV для Excel

Программа  Microsoft Excel понимает CSV-файлы как разделенные ячейки, разделенные символом точка с запятой  (;), а строка заканчивается символом перевода строки. Если же в самой ячейке содержатся символы точка с запятой (;) либо перехода на новую строку, то такая ячейка обрамляется символом кавычек (").

Например:  
ячейка1; "ячейка2 с символом ; или в несколько строк"; ячейка3;

Если же в самой ячейке встречаются кавычки (”), то они удваиваются.

Например:  
Это"кавычки;  =>  Это""кавычки;

Что делает стандартная функция экспорта в CSV в PHP

Функция fputcsv ($csv_file , $array, $delimiter = ',', $enclosure = '"' ) выполняет экспорт массива $array в файл $csv_file. При этом в качестве разделителя по умолчанию используя запятую ($delimiter = ',') ,а в качестве обрамляющего символа – кавычки ($enclosure = '"').

Казалось бы, чтобы записать данные в формат CSV для MS Excel нужно всего лишь установить $delimiter = ';', а $enclosure оставить равным кавычкам (“), но в реальности случаются ошибки интерпретации файла Экселем.

Вот в чем подвох: функция fputcsv в качестве экранирующего символа – обратный слеш (\). И это изменить никак нельзя. То есть, если вы будете писать данные вида лалал\”лала, то при записи в файл этот символ кавычек не удвоится, потому что он экранирован обратным слешем, а значит, Эксель посчитает его границей ячейки, хотя это не так.

Выход: использовать свою функцию экспорта в CSV, далее будет приведен ее текст.

Что делает стандартная функция импорта в CSV в PHP

В PHP функция импорта из CSV-фала fgetcsv($csv_file, $length = 0, $delimiter = ',', $enclosure = '"', $escape = '\\') импортирует из CSV-файла $csv_file строка максимальной длины $length, если = 0, значит, она может быть любой. По умолчанию в качестве разделителя использует символ запятой (,), в качестве обрамляющего символа – кавычки ("), и символ экранирования – обратный слеш (\).

Начиная с PHP версии 5.3 символ экранирования в этой функции можно задавать свой.

Чтобы экспортировать данные с помощью PHP в формат CSV для Экселя, достаточно ко всему прочему в качестве экранирующего символа установить кавычки. То есть $delimiter = ';', $enclosure = '"', $escape = '"'.

Итоговые функции работы с CSV

function  my_fputcsv($fp, $csv_arr, $delimiter = ';', $enclosure = '"')
{
  // проверим, что на  входе массив
  if (!is_array($csv_arr))
  {
    return(false);
  }
  // обойдем все  элемены массива
  for ($i = 0, $n = count($csv_arr); $i < $n;  $i ++)
  {
    // если это не  число
    if (!is_numeric($csv_arr[$i]))
    {
      // вставим символ  ограничения и продублируем его в теле элемента
      $csv_arr[$i] =  $enclosure.str_replace($enclosure, $enclosure.$enclosure,  $csv_arr[$i]).$enclosure;
    }
    // если  разделитель - точка, то числа тоже экранируем
    if (($delimiter == '.') && (is_numeric($csv_arr[$i])))
    {
      $csv_arr[$i] =  $enclosure.$csv_arr[$i].$enclosure;
    }
  }
  // сольем массив в строку, соединив разделителем
  $str = implode($delimiter,  $csv_arr)."\n";
  fwrite($fp, $str);
  // возвращаем  количество записанных данных
  return strlen($str);
}

function  my_fgetcsv($fp, $length=0, $delimiter = ';', $enclosure = '"',  $escape=true)
{
  $csv_arr = fgetcsv($fp, $length, $delimiter,  $enclosure, $escape);
  return($csv_arr);
}

Как открыть Экселем CSV-файл в кодировке UTF-8

Чтобы Excel нормально открывал CSV-файлы в кодировке UTF-8 не достаточно просто записывать туда данные этой кодировке. Чтобы "подсказать" Экселю использовать юникод принято в начало файла вставлять 3х байтную последовательность: EF BB BF. Это не есть официальное решение, это просто особенность открытия Экселем CSV-файлов.

Для наглядности приведем кусок кода для PHP:

fwrite($f, "\xEF\xBB\xBF");

Далее уже экспортируем данные в кодировке UTF-8 при помощи функции my_fputcsv(). Excel отлично открывает такие файлы.

Важно! Несмотря на то, что файл откроется в кдировке utf-8, Excel его сохранит все равно в кодировке windows-1251.