Экспорт в эксель php

Как сделать экспорт большой таблицы в Excel средствами PHP?

Добрый день, уважаемые пользователи.

Имеется таблица MySQL 70 столбцов, более 150 тыс. записей

А выборка может содержать 25 тыс., 60 тыс. записей.

Формат CSV не подходит, так как он не поддерживает редактирование формате ячеек.

Как быть подскажите?

$sheet->getDefaultStyle()->getBorders()->getTop()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);
$sheet->getDefaultStyle()->getBorders()->getBottom()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);
$sheet->getDefaultStyle()->getBorders()->getLeft()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);
$sheet->getDefaultStyle()->getBorders()->getRight()->applyFromArray(
array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(
‘rgb’ => ‘000000’
)
)
);

//Ширина столбцов
$sheet->getColumnDimension(‘B’)->setWidth(16);
.
$sheet->getColumnDimension(‘AS’)->setWidth(16);

//$sheet->getColumnDimension(‘B’)->setWidth(20);
$sheet->getStyle(1)->getAlignment()->setWrapText(true);
$sheet->getStyle(‘B1’)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet->getStyle(1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$sheet->setCellValueByColumnAndRow(0,1,iconv(«windows-1251», «UTF-8″,»Столбец 1»));
.
$sheet->setCellValueByColumnAndRow(70,1,iconv(«windows-1251», «UTF-8″,»Столбец 70»));

///////////////////////////////////////////////////////////////////////////
//Запись данных
$i=2;
$q=mysql_query(«SELECT * FROM t_base «.$dop_query.» LIMIT 10000″);
while ($r=mysql_fetch_array($q))
<
$sheet->setCellValueByColumnAndRow(0,$i,rep($r[‘cell_1’]));
.
$sheet->setCellValueByColumnAndRow(70,$i,rep($r[‘cell_70’]));
$i++;
>
///////////////////////////////////////////////////////////////////////////
//Сохранение данных в файл
$objWriter = new PHPExcel_Writer_Excel5($xls);
//$objWriter->setTempDir($_SERVER[‘DOCUMENT_ROOT’].’/ex/’);
$objWriter->save($_SERVER[‘DOCUMENT_ROOT’].$path.$file_name);

Источник

PHP – выгрузка данных в Excel из базы MySQL

У многих при работе с PHP в связке с MySQL возникает такая потребность, как экспорт данных из базы в формат xls, для того чтобы люди, которым нужны эти данные, обрабатывали их в программе Excel или просто пользователям было удобно смотреть эти данные. Недавно у меня возникла такая потребность и сегодня я расскажу, как это дело можно реализовать.

Сразу скажу, что этот способ достаточно простой, но данные выгружаются нормально.

Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:

Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.

До того как я пришел именно к такому варианту выгрузки я пробовал выгружать в формат csv, но получалось немного коряво, потом пробовал отрисовывать таблицу и сохранять ее с расширением xls, также получалось какая-то ерунда, способ, который я сейчас опишу, меня полностью устроил, и сейчас с Вами я им поделюсь.

Для начала приведу весь код, который я максимально прокомментировал, можете выделить его и сохранить с расширением php и пробовать, только не забудьте прописать настройки подключения к базе данных.

Экспорт данных из MySQL в Excel на PHP

А еще чтобы Вы понимали, какие данные я выгружаю, приведу пример простой таблицы в БД (у меня ее название test):

Тестовая таблица:

id	firstname	name
1	Иванов	Иван
2	Петров	Петр
2	Петров2	Петр2

Вся идея здесь заключается в функции pack(), которая упаковывает данные в бинарную строку, а мы в свою очередь последовательно заполняем эту строку данными, которые мы выгрузили из базы данных MySql с помощью обычной функции mysql_query().

Для того чтобы проверить работоспособность данного кода, с учетом того, что Вы настроили подключение к базе данных и создали аналогичную таблицу, можете послать такой запрос:

И у Вас должно выгрузиться две строки с id равным 2.

И теперь каждый, которому Вы разрешите выгружать данные может легко экспортировать их на свой локальный компьютер через web интерфейс. Данный способ удобен как для корпоративных пользователей, если Вы разрабатываете приложение для своей организации, так и для пользователей Вашего web сайта в Интернете. Надеюсь, данный способ Вам помог. Удачи!

Заметка! Если Вас интересует язык SQL, рекомендую пройти мой онлайн-курс по основам SQL, который ориентирован на изучение SQL как стандарта, таким образом, Вы сможете работать в любой системе управления базами данных. Курс включает много практики: онлайн-тестирование, задания и многое другое.

Источник

Обработка и оформление отчетов в 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. А также были продемонстрированы почти все возможные методы для работы со стилями.

Источник

PHPExcel и большие файлы

PHPExcel — отличная библиотека с огромным функционалом по работе с форматами xls, xlsx. Можно считывать, записывать, менять форматирование, задавать формулы, а из xlsx можно и картинки вытаскивать.

Чтение файла

25 000 строк) я использовал комплексное решение.

Во-первых, считываем файл не целиком, а по несколько строк. Это делается так:

Собственно класс chunkReadFilter — это то, что нам нужно. Устанавливаем его в качестве фильтра для чтения файла, и файл будет загружаться не целиком, а лишь определенное количество строк.

Во-вторых, кроме него еще используем такую полезную опцию как ReadDataOnly. Как следует из названия, она позволяет не загружать форматирование документа, высвобождая место для данных.

И, в-третьих, конечно же используем unset(). Это так же поможет высвободить память.

Но помимо нехватки памяти возникает другая проблема. На большинстве shared-хостингов у php-скриптов помимо ограничения на использование памяти, еще стоит ограничение на время выполнения. И крайне вероятно, что этого времени хватать не будет. Лично я обошел эту проблему при помощи сессий и повторяющихся ajax запросов. В коде представленном выше вы уже увидели использование сессий и завершающее «The End».

А вот код клиентской части.

Т.е. мы отправляем ajax-запрос нашему скрипту import_xls.php, ждем ответа, и, если ответ нас не устраивает, посылаем новый ajax-запрос. Встречал еще в сети решение без использования AJAX — при помощи редиректа в самом php. Обработка файла делится на малое количество строк и после этого вставляется код:

Но лично мне больше нравится решение с AJAX, потому что тут можно легко и просто добавить прогресс-бар и какие-нибудь другие рюшечки и плюшечки. Кстати, внимательный читатель заметил, что в моем коде простейший прогресс-бар уже реализован. Дополнительный немаловажный момент: в решение с AJAX не нужно заранее знать сколько строк скрипт сможет обработать за один проход.

Запись файла

Для записи файла в формате xls величиной так же около 25 000 строк крайне полезно использовать следующий код:

Можно еще поиграться с методами кеширования. Помимо кеширования во временной директории php еще поддерживается memcache:

А так же cache_to_discISAM.

UPDATE
Прошу у всех прощения за то, что код тут был представлен ужасно. Сразу не разобрался с подсветкой синтаксиса на Хабре (видимо, позднее время суток сказалось) и поторопился. Впредь не буду торопиться и буду умнее.

UPDATE2
Переделал JavaScript. Теперь он посылает запрос по таймауту в случае, если сервер не ответил.

Источник

Работа с Excel средствами PHP

PHPExcel — библиотека для создания и чтения данных из файлов формата OpenXML (который используется в MS Excel 2007). С ее помощью можно считывать из файлов, записывать в файлы, форматировать содержимое, работать с формулами и т.д. Для работы PHPExcel требуется версия PHP 5.2 или выше, с установленными библиотеками Zip, XML и GD2.

Установка PHPExcel

Первым делом библиотеку необходимо скачать. Для этого переходим на официальный сайт библиотеки и скачиваем архив PHPExcel-1.7.8.zip. После распаковки мы получим несколько файлов и папок:

Файлы — это различные описания по предыдущим версиям, лицензионное соглашение и очень краткая инструкция по установке. Далее, в папке Classes, содержится непосредственно сама библиотека PHPExcel — эту папку необходимо скопировать в корень нашего скрипта.

В папке Documentation содержится документация по библиотеке на английском языке. В папке Tests — примеры по использованию библиотеки.

Создание Excel-файла

Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:

Настройки листа книги Excel

Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.

Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:

Если Вы захотите указать активным какой то другой лист, то вначале его необходимо создать, при помощи метода:

Затем, по аналогии, указываем индекс и получаем объект активного листа.

Далее при помощи метода setTitle(‘Прайс лист’) задаем название нашего листа.

Если нужно, можно при печати выводить шапку и подвал листа:

Обратите внимание на передаваемые параметры:

Затем указываем настройки шрифта по умолчанию:

Наполнение документа данными

Для начала давайте зададим ширину столбцов (в символьных единицах), которые нам понадобятся:

Теперь заполним несколько ячеек текстом:

Далее давайте в ячейку D4 запишем текущую дату:

Стилизация данных

Теперь, по аналогии, применим стили к остальным ячейкам:

Сохранение документа

Осталось только сохранить наш документ:

Если нужно вывести документ в браузер

Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.

Добавление формул

Формула Excel — это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке Excel может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.

Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы. Формулы Excel начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.

PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:

Чтение Excel-файла

Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:

Источник