php csv to excel
Как в 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, далее будет приведен ее текст.
Что делает стандартная функция импорта в CSV в PHP
Начиная с PHP версии 5.3 символ экранирования в этой функции можно задавать свой.
Итоговые функции работы с CSV
Как открыть Экселем CSV-файл в кодировке UTF-8
Чтобы Excel нормально открывал CSV-файлы в кодировке UTF-8 не достаточно просто записывать туда данные этой кодировке. Чтобы «подсказать» Экселю использовать юникод принято в начало файла вставлять 3х байтную последовательность: EF BB BF. Это не есть официальное решение, это просто особенность открытия Экселем CSV-файлов.
Для наглядности приведем кусок кода для PHP:
Далее уже экспортируем данные в кодировке UTF-8 при помощи функции my_fputcsv(). Excel отлично открывает такие файлы.
Важно! Несмотря на то, что файл откроется в кдировке utf-8, Excel его сохранит все равно в кодировке windows-1251.
Конвертация многостраничного xls/xslx в csv при помощи PHPExcel
В моих проектах часто нужно было собирать данные из разных источников в CSV формат, и пока не нужно было получать данные из нескольких страниц таблиц xls, мне хватало простого fgetcsv() / fputcsv(). Но вот наступил всё-таки тот день, когда передо мной была поставленна задача «получать данные со всех страниц документа». И, как водится, я начал искать готовое решение, чтобы не строить свой «велосипед». Но, к сожалению, именно того, что мне нужно было, я не нашёл: было похожее решение, которое выводило многостраничный документ на экран, но использовалась другая библиотека, которая, как я понял, не поддерживала формат xslx (Excel 2007 +). Ещё немного поискав другие варианты, я понял, что дело гиблое и решил разобраться с библиотекой самостоятельно. Совместив некоторые подсказки по работе с библиотекой PHPExcel в одно целое, я получил следующий скрипт. Итак, приступим.
Для начала нам понадобится сам PHPExcel. Сразу отмечу, что библиотека отлично ставится через composer, однако нигде не указана явно полная версия библиотеки. Методом подбора я указал версию 1.8 с добавлением признака «неточности».
В моём composer.json, которую я добавил в блок «require-dev»:<>, получилась вот такая запись:
На данный момент установилась версия 1.8.1. Так как библиотека PHPExcel наследует SPL, который есть в PHP, начиная с версии 5.3, то вместо стандартных обходов масива строк и ячеек документа при помощи foreach() я решил использовать Итераторы.
Подключаем библиотеку, загружаем документ и определяем некоторые первоначальные данные:
Далее получаем Итератор страниц, для обхода которых нам ненужно знать их количество:
Обход итератора производится при помощи вот такой несложной конструкции:
Аналогичным образом были получены и обходятся строки и ячейки документа. Как красиво получить данные из ячейки, я нашёл здесь же, на Хабре, в статье Универсальное чтение ячеек в PHPExcel. Я не буду подробно расписывать все проверки, у кого есть желание — можете прочитать в указанной статье.
Запись в CSV файл, я произвожу то же через эту библиотеку вот таким образом.
Наполнение объекта для записи в файл я покажу ниже в полном варианте скрипта. Единственное, что могу добавить: если вам нужно будет записывать даты заново в документы xls/xlsx и указать явное форматирование, то при подготовке объекта PHPExcel можно воспользоваться следующей конструкцией:
Где формат задаётся при помощи константы PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2. В данном случае это формат yyyy-mm-dd, который можно сразу использовать в запросе MySql.
Кстати, вот все доступные константы библиотеки:
В итоге у меня получился скрипт который пишет каждую страницу документа в отдельный csv-файл, при этом получает правильные значения ячеек независимо от наличия внешних данных и форматирует дату в подходящем формате.
На этапе тестирования все значения выводятся на экран, далее кому не нужно естественно вывод можно убрать.
Критика, дополнения и исправления приветствуются. Всем спасибо, и буду очень рад если моя статья кому-то поможет и сократит несколько часов работы.
Обработка и оформление отчетов в Excel на PHP
Не редко при разработке некоего проекта, возникает необходимость в формировании отчетной статистики. Если проект разрабатывается на Delphi, C# или к примеру, на С++ и под Windows, то тут проблем нет. Всего лишь необходимо воспользоваться COM объектом. Но дела обстоят иначе, если необходимо сформировать отчет в формате excel на PHP. И чтобы это творение функционировало на UNIX-подобных системах. Но, к счастью, не так все плохо. И библиотек для этого хватает. Я свой выбор остановил на PHPExcel. Я уже пару лет работаю с этой библиотекой, и остаюсь доволен. Поскольку она является кроссплатформенной, то не возникает проблем с переносимостью.
PHPExcel позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8.
Установка библиотеки
Для работы необходима версия PHP 5.2.0 или выше. А также необходимы следующие расширения: php_zip, php_xml и php_gd2. Скачать библиотеку можно отсюда.
С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:
Импорт данных из PHP в Excel
Рассмотрим пример по формированию таблицы умножения.
Далее нам необходимо получить наш *.xls файл. Здесь можно пойти двумя путями. Если предположим у вас интернет магазин, и клиент хочет скачать прайс лист, то будет лучше прибегнуть к такому выводу:
Здесь сформированные данные сразу “выплюнутся” в браузер. Однако, если вам нужно файл сохранить, а не “выбросить” его сразу, то не нужно выводить HTTP-заголовки и вместо “php://output” следует указать путь к вашему файлу. Помните что каталог, в котором предполагается создание файла, должен иметь права на запись. Это касается UNIX-подобных систем.
Рассмотрим еще на примере три полезные инструкции:
Также обратите внимание на следующие необходимые для работы с отчетом методы:
Как мы видим, вышеприведенные методы являются парными. Поэтому мы можем работать с ячейками используя строковое или числовое представление координат. Что конечно же является дополнительным преимуществом в работе.
Оформление отчета средствами PHP в Excel
Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.
Назначить стиль ячейке можно тремя способами:
Заливка
Значением параметра fill является массив со следующими необязательными параметрами:
Или можно использовать следующие методы:
Вставка изображений
Довольно редко, но бывает полезным произвести вставку изображения в отчет. Это может быть логотип, схема и т.д. Для работы нам понадобятся следующие методы:
Код демонстрирующий алгоритм вставки изображения приведен ниже:
Вот так выглядит отчет со вставленным изображением:
Шрифт
В качестве значения параметра font указывается массив, который содержит следующие необязательные параметры:
Или воспользоваться следующими методами:
Рамка
В качестве значения параметра borders указывается массив, который содержит следующие необязательными параметры:
Так же можно прибегнуть к использованию следующих методов:
$PHPExcel_Style->getBorders()->getLeft()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));
$PHPExcel_Style->getBorders()->getRight()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));
$PHPExcel_Style->getBorders()->getTop()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));
$PHPExcel_Style->getBorders()->getBottom()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));
$PHPExcel_Style->getBorders()->getDiagonal()->applyFromArray(array(‘style’ => PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));
$PHPExcel_Style->getBorders()->setDiagonalDirection(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′))).
Выравнивание
Значением параметра alignment является массив, который принимает на вход четыре необязательных параметра:
Или использовать следующие методы:
Формат представления данных
Параметр numberformat представляет собой массив, который включает только один параметр: code — формат данных ячейки.
Список возможных форматов
| FORMAT_GENERAL | General |
| FORMAT_TEXT | @ |
| FORMAT_NUMBER | 0 |
| FORMAT_NUMBER_00 | 0.00 |
| FORMAT_NUMBER_COMMA_SEPARATED1 | #,##0.00 |
| FORMAT_NUMBER_COMMA_SEPARATED2 | #,##0.00_- |
| FORMAT_PERCENTAGE | 0% |
| FORMAT_PERCENTAGE_00 | 0.00% |
| FORMAT_DATE_YYYYMMDD2 | yyyy-mm-dd |
| FORMAT_DATE_YYYYMMDD | yy-mm-dd |
| FORMAT_DATE_DDMMYYYY | dd/mm/yy |
| FORMAT_DATE_DMYSLASH | d/m/y |
| FORMAT_DATE_DMYMINUS | d-m-y |
| FORMAT_DATE_DMMINUS | d-m |
| FORMAT_DATE_MYMINUS | m-y |
| FORMAT_DATE_XLSX14 | mm-dd-yy |
| FORMAT_DATE_XLSX15 | d-mmm-yy |
| FORMAT_DATE_XLSX16 | d-mmm |
| FORMAT_DATE_XLSX17 | mmm-yy |
| FORMAT_DATE_XLSX22 | m/d/yy h:mm |
| FORMAT_DATE_DATETIME | d/m/y h:mm |
| FORMAT_DATE_TIME1 | h:mm AM/PM |
| FORMAT_DATE_TIME2 | h:mm:ss AM/PM |
| FORMAT_DATE_TIME3 | h:mm |
| FORMAT_DATE_TIME4 | h:mm:ss |
| FORMAT_DATE_TIME5 | mm:ss |
| FORMAT_DATE_TIME6 | h:mm:ss |
| FORMAT_DATE_TIME7 | i:s.S |
| FORMAT_DATE_TIME8 | h:mm:ss |
| FORMAT_DATE_YYYYMMDDSLASH | yy/mm/dd; @ |
| FORMAT_CURRENCY_USD_SIMPLE | «$»#,##0.00_-;@ |
| FORMAT_CURRENCY_USD | $#,##0_- |
| FORMAT_CURRENCY_EUR_SIMPLE | [$EUR ]#,##0.00_- |
Пример настройки для формата данных ячейки:
А можно и воспользоваться методом:
Защита ячеек
В качестве значения параметра protection выступает массив, который содержит два необязательных параметра:
Или использовать следующие методы:
Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это тремя способами. Первый способ заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray, класса PHPExcel_Style.
Далее мы применим созданный нами стиль к ячейкам excel.
Сейчас применим тот же стиль, но используя другую методику.
Вот что у нас получилось:
Для получения данных о стиле конкретной ячейки необходимо использовать один из следующих методов, который вернет экземпляра класса PHPExcel_Style:
А теперь рассмотрим третий способ назначения стиля ячейкам путем дублирования стиля. Пример использования представлен ниже (предполагается, что к ячейке “B2” применен некий стиль и мы его хотим продублировать для диапазона ячеек “F2:F10”):
Добавление комментариев
Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:
Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:
Вот какой комментарий мы должны получить:
Вставка ссылки
Вставка ссылок в ячейку тоже не вызывает каких-либо затруднений, что можно видеть из нижеописанного примера:
Так же в виде ссылки может быть использован, к примеру, email адрес: mailto:example@mail.com.
Чтение данных из Excel
Формировать отчеты и применять к ним стили это конечно отлично. Но на этом возможности библиотеки PHPExcel не заканчиваются. Ну что же, посмотрим на что она еще способна. А способна она еще и читать данные из файлов формата *.xls / *.xlsx.
С помощью библиотеки PHPExcel можно читать следующие форматы:
Для работы нам понадобятся объекты двух классов:
Для демонстрации выведем данные из таблицы с информацией об автомобилях.
Пример чтения файла представлен ниже:
Первый вариант
Второй вариант
В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet:
Другие полезные методы
Возможностей по работе с отчетами формата excel с использованием PHP как мы видим, достаточно много. Но мы рассмотрим еще несколько полезных методов, которые могут оказаться весьма полезны в работе:
Примечание: Методы stringFromColumnIndex и columnIndexFromString примечательны тем, что их можно использовать без создания объекта класса. Пример использования представлен ниже:
С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.
How to convert CSV file to Excel in PHP
2. EasyXLS on Linux, Mac, Windows using Java with PHP
If you opt for the COM+ version of EasyXLS, be sure that you have EasyXLS installed and registered on your machine.
To download the trial version of EasyXLS Excel Library, press the below button:
If you already own a license key, you may login and download EasyXLS from your account.
Check PHP.ini for [COM_DOT_NET] extension.
See also
Step 3: Verify if EasyXLS is registered
Check if EasyXLS component is present in Component Services.
See also
How to verify if EasyXLS is registered?
Step 4: Run PHP code that converts CSV file to Excel
Execute the following PHP code that converts CSV file to Excel.
For methods with same name but different parameters, only the first method overload retains the original name. Subsequent overloads are uniquely renamed by appending to the method name ‘_2’, ‘_3’, etc (method, method_2, method_3), an integer that corresponds to the order of declaration that can be found in EasyXLS.h, a file that comes with EasyXLS installation.
EasyXLS on Linux, Mac, Windows using Java with PHP
If you opt for the Java version of EasyXLS, a similar code as above requires PHP/Java Bridge between PHP and Java.
Step 1: Download and install EasyXLS Excel Library for Java
To download the trial version of EasyXLS Excel Library, press the below button:
If you already own a license key, you may login and download EasyXLS from your account.
Step 2: Install PHP/Java Bridge
Step 3: Setup EasyXLS library in Tomcat
Copy EasyXLS.jar into Tomcat installation path, lib folder.
Step 4: Run PHP code that converts CSV file to Excel
Execute the following PHP code that converts CSV file to Excel.
fputcsv
(PHP 5 >= 5.1.0, PHP 7, PHP 8)
fputcsv — Форматирует строку в виде CSV и записывает её в файловый указатель
Описание
Список параметров
Указатель на файл должен быть корректным и указывать на файл, успешно открытый функциями fopen() или fsockopen() (и всё ещё не закрытый функцией fclose() ).
Массив строк ( string ).
Дополнительный параметр separator устанавливает разделитель полей (только один однобайтовый символ).
Дополнительный параметр enclosure устанавливает ограничитель полей (только один однобайтовый символ).
Необязательный параметр escape_char задаёт экранирующий символ (не более одного однобайтового символа). Пустая строка ( «» ) отключает проприетарный механизм экранирования.
Возвращаемые значения
Возвращает длину записанной строки или false в случае возникновения ошибки.
Список изменений
| Версия | Описание |
|---|---|
| 7.4.0 | Теперь параметр escape_char может принимать пустую строку для отключения проприетарного механизма экранирования. |
Примеры
Пример #1 Пример использования fputcsv()
Вышеуказанный пример запишет в файл file.csv следующее:
Примечания
Замечание: Если у вас возникают проблемы с распознаванием PHP концов строк при чтении или создании файлов на Macintosh-совместимом компьютере, включение опции auto_detect_line_endings может помочь решить проблему.
Смотрите также
User Contributed Notes 26 notes
If you need to send a CSV file directly to the browser, without writing in an external file, you can open the output and use fputcsv on it..
If you need to save the output to a variable (e.g. for use within a framework) you can write to a temporary memory-wrapper and retrieve it’s contents:
Sometimes it’s useful to get CSV line as string. I.e. to store it somewhere, not in on a filesystem.
if you want make UTF-8 file for excel, use this:
Please note, that fputcsv ist not always enclosing strings with the enclosure character.
It seems that only strings containing at least one of the following characters are enclosed:
— the delimiter character
— the enclosure character
— the escape character
— \n (new line)
— \r (line feed)
— \t (tab)
— blank
I hope this saves you the hour it took me to get to the bottom of this behaviour.
Using fputcsv to output a CSV with a tab delimiter is a little tricky since the delimiter field only takes one character.
The answer is to use the chr() function. The ascii code for tab is 9, so chr(9) returns a tab character.
Utility function to output a mysql query to csv with the option to write to file or send back to the browser as a csv attachment.
the solution for how to solve the encoding problem while converting an array to csv file is below.
Alright, after playing a while, I’m confident the following replacement function works in all cases, including the ones for which the native fputcsv function fails. If fputcsv fails to work for you (particularly with mysql csv imports), try this function as a drop-in replacement instead.
INTO TABLE
my_table
FIELDS TERMINATED BY
‘,’
OPTIONALLY ENCLOSED BY
‘»‘
LINES TERMINATED BY
‘\n’
*/
I’ve created a function for quickly generating CSV files that work with Microsoft applications. In the field I learned a few things about generating CSVs that are not always obvious. First, since PHP is generally *nix-based, it makes sense that the line endings are always \n instead of \r\n. However, certain Microsoft programs (I’m looking at you, Access 97), will fail to recognize the CSV properly unless each line ends with \r\n. So this function changes the line endings accordingly. Secondly, if the first column heading / value of the CSV file begins with uppercase ID, certain Microsoft programs (ahem, Excel 2007) will interpret the file as being in the SYLK format rather than CSV, as described here: http://support.microsoft.com/kb/323626
This function accommodates for that as well, by forcibly enclosing that first value in quotes (when this doesn’t occur automatically). It would be fairly simple to modify this function to use another delimiter if need be and I leave that as an exercise to the reader. So quite simply, this function is used for outputting CSV data to a CSV file in a way that is safe for use with Windows applications. It takes two parameters + one optional parameter: the location of where the file should be saved, an array of data rows, and an optional array of column headings. (Technically you could omit the headings array and just include it as the first row of the data, but it is often useful to keep this data stored in different arrays in practice.)
I converted this from the PHP source code. This replicates PHP5 functionality exactly, whereas the other examples here do not.
if (! function_exists ( ‘fputcsv’ )) <
To produce RFC 4180 conforming output, do not use fputcsv but encode manually, like this: