Php сохранить таблицу в excel. Простой способ передачи табличных данных из PHP в Excel

.
Способ, представленный в той заметке действительно очень прост, но не всегда может быть удобен.
Есть множество других способов передать табличные данные из PHP в Excel, я опишу тот, который показался мне наиболее простым и функциональным. Нужно особенно отметить, что что я не говорю о генерации xls файла, а лишь предлагаю пользователю открыть полученные данные при помощи Excel так, что пользователи не искушённые в программировании не заметят подлога.

Итак, первое что необходимо сделать - разместить на нашей страничке ссылку на скрипт генерирующий следующие заголовки:
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/x-msexcel");
header("Content-Disposition: attachment; filename=\"" . iconv("UTF-8", "CP1251", $object->getFileName()) . "\";");
header("Content-Transfer-Encoding:­ binary");
header("Content-Length: " . $object->getFileSize());

$object - сферический объект в вакууме, который каждый читатель реализует так, как ему больше нравится. Назначение геттеров getFileName() и getFileSize() понятно из названий. Здесь стоит выделить один неочевидный нюанс (спасибо за то что напомнил об этом) - getFileName() конечно может возвращать любое имя файла, но если вы хотите что бы браузер предложил открыть полученный контент в Excel, то расширение файла должно быть xls.
Ничего нового я пока не рассказал, все это придумано до меня, впрочем как и то что будет описано ниже.
Как верно было отмечено в комментариях к заметке , Excel гораздо быстрее работает с XML. Но самое главное преимущество, пожалуй, все же не в скорости, а в гораздо более широких возможностях. Углубляться в дебри я не стану, а лишь приведу простой пример, и ссылку на подробное описание всех тегов.

Итак, после того как заголовки сгенерированны, нам нужно отдать пользователю собственно данные. Я обычно заворачиваю генерацию таблицы в отдельный метод:
echo $object->getContent();

А таблицу генерирую при помощи Smarty:

{foreach from=$data.header item=caption} {$caption.columnName} {/foreach} {foreach from=$data.content item=row} {foreach from=$row item=col} {$col} {/foreach} {/foreach}
Как видно из кода, в шаблон передается массив $data содержащий два массива - строку заголовка таблицы и собственно данные.
Стоит отметить что использовать шаблонизатор только для генерации XML несколько накладно, и получить XML можно множеством других способов. В моем конкретном случае генерация XML это лишь маленькая плюшка в большом проекте, где без шаблонизатора не обойтись.

В качестве примера я привел простейшую таблицу, при желании можно манипулировать гораздо большим числом атрибутов. Это особенно приятно, если учесть, что для реализации не требуются никакие сторонние библиотеки.
Описанный метод несколько лет работает на одном проекте и ни один пользователь до сих пор не заподозрил, что открываемые им данные не являются документом MS Office.

Подробнее о структуре XML используемой в MS Excel можно почитать в

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

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

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

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

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

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

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

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

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

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

xlsData = pack("ssssss", 0x809, 0x08, 0x00,0x10, 0x0, 0x0); } // Если число function RecNumber($row, $col, $value){ $this->xlsData .= pack("sssss", 0x0203, 14, $row, $col, 0x00); $this->xlsData .= pack("d", $value); return; } //Если текст function RecText($row, $col, $value){ $len = strlen($value); $this->xlsData .= pack("s*", 0x0204, 8 + $len, $row, $col, 0x00, $len); $this->xlsData .= $value; return; } // Вставляем число function InsertNumber($value){ if ($this->countCol == $this->totalCol) { $this->countCol = 0; $this->countRow++; } $this->RecNumber($this->countRow, $this->countCol, $value); $this->countCol++; return; } // Вставляем текст function InsertText($value){ if ($this->countCol == $this->totalCol) { $this->countCol = 0; $this->countRow++; } $this->RecText($this->countRow, $this->countCol, $value); $this->countCol++; return; } // Переход на новую строку function GoNewLine(){ $this->countCol = 0; $this->countRow++; return; } //Конец данных function EndData(){ $this->xlsData .= pack("ss", 0x0A, 0x00); return; } // Сохраняем файл function SaveFile($fileName){ $this->fileName = $fileName; $this->SendFile(); } // Отправляем файл function SendFile(){ $this->EndData(); header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header ("Cache-Control: no-store, no-cache, must-revalidate"); header ("Pragma: no-cache"); header ("Content-type: application/x-msexcel"); header ("Content-Disposition: attachment; fileName=$this->fileName.xls"); print $this->xlsData; } } } if(isset($_GET["id"])) { //фильтруем данные $id = mysql_real_escape_string(stripslashes(trim(htmlspecialchars($_GET["id"],ENT_QUOTES)))); $filename = "Файл_с_id_".$id; // задаем имя файла $excel = new ExportToExcel(); // создаем экземпляр класса $sql="SELECT * FROM test.test where id = $id";//запрос к базе $rez=mysql_query($sql); $excel->InsertText("Идентификатор"); $excel->InsertText("Фамилия"); $excel->InsertText("Имя"); $excel->GoNewLine(); While($row=mysql_fetch_assoc($rez)){ $excel->InsertNumber($row["id"]); $excel->InsertText($row["firstname"]); $excel->InsertText($row["name"]); $excel->GoNewLine(); } $excel->SaveFile($filename); } ?>

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

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

Http://ваш_сайт/название _файла. php?id=2

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

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

(данные, полученные из базы данных mysql), чтобы преуспеть. Я использую Zend Framework. Мне нужно внести некоторые изменения в мои данные перед экспортом в excel. Фактически, мне действительно нужно создать ежемесячную кассовую книгу.

Я прочитал много документов, но оказался в беспорядке. Я действительно не понимаю, как мне начать. Мне действительно нужен PEAR? Нужно ли загружать любую библиотеку классов? Нет ли простого способа сделать это?

заранее спасибо

Я предлагаю вам использовать PHPExcel библиотеку PHPExcel . Он действительно мощный, поддерживает различные форматы, может делать визуальное форматирование и прост в использовании.

Вы можете узнать больше об этом на своей веб-странице: http://phpexcel.codeplex.com/ , (PHPExcel уже перемещен по адресу https://github.com/PHPOffice/PHPExcel)

Пример использования:

$objPHPExcel = new PHPExcel(); /** You can set many properties */ $objPHPExcel->getProperties()->setCreator("My company") ->setLastModifiedBy("John Doe") ->setTitle("Annual report") ->setSubject("Sales") ->setDescription("Annual sales report by John Doe") ->setCategory("Finance"); /** Choose one of sheets */ $activeSheet = $objPHPExcel->setActiveSheetIndex(0); /** Simply set value of cell */ $activeSheet->setCellValue("A1", "plural");

Вы можете делать гораздо больше, конечно, чтение файлов excel, установка визуальных стилей, создание графиков, выражений и многое другое.

Я написал отчет о вариации в инвентаре из 94 строк, в котором я беру данные из MySQL (6 кбайт + записей на таблицу), создаю многомерный массив из данных MySQL, вытащил из нескольких таблиц, а затем сбрасывал все в одну строку и генерировал.xls таким образом:

Не требуется никаких расширений.

Единственные предостережения в том, что я еще не понял, как выплюнуть.xls без Excel, сообщив, что данные могут быть повреждены – и я не пытался делать форматирование или что-то более сложное, чем просто «экспортировать» данные, Файл / данные в порядке, конечно, но он генерирует предупреждение из Excel.

EDIT: Я должен отметить, что «настройка» и «дамп» относятся к следующему: daniweb.com

Вы можете использовать библиотеку phpexcel . Это позволяет вам писать и читать из разных форматов файлов электронных таблиц

для интеграции с zend вы можете воспользоваться помощью следующей ссылки.

Вы можете использовать CSV, чтобы сделать импортируемый формат для excel. Это самый простой способ сделать это.

CSV выглядит так:

"my first cellcontent", "my second cell content", "my third cell content" "second line, first cell content", "etc", "etc

Каждая строка представляет строку Excel, вы помещаете содержимое ячейки между двойными кавычками и разделяете запятыми. Будьте осторожны, если у вас есть некоторые из ваших данных, это может сломать ваш CSV и создать нежелательные новые строки.

С небольшим количеством Google вы бы нашли это: http://www.the-art-of-web.com/php/dataexport/

Подготовка данных

$data = array(array("firstname" => "Mary", "lastname" => "Johnson", "age" => 25), array("firstname" => "Amanda", "lastname" => "Miller", "age" => 18), array("firstname" => "James", "lastname" => "Brown", "age" => 31), array("firstname" => "Patricia", "lastname" => "Williams", "age" => 7), array("firstname" => "Michael", "lastname" => "Davis", "age" => 43), array("firstname" => "Sarah", "lastname" => "Miller", "age" => 24), array("firstname" => "Patrick", "lastname" => "Miller", "age" => 27));

Первым шагом является вывод данных в формате с разделителями табуляции (CSV также можно использовать, но немного сложнее). Для этого мы используем следующий код:

Мы устанавливаем тип содержимого в text / plain, чтобы результат можно было более легко просмотреть в браузере. В противном случае, поскольку нет форматирования HTML, вывод будет отображаться как одна строка текста.

Первая строка вывода будет заголовками столбцов (в этом случае используются имена полей). Значения разделяются вкладкой \ t и строками с разрывом строки \ n. Результат должен выглядеть примерно так:

Firstname lastname age Mary Johnson 25 Amanda Miller 18 James Brown 31 Patricia Williams 7 Michael Davis 43 Sarah Miller 24 Patrick Miller 27

В этом коде есть слабость, которая может быть не сразу очевидной. Что делать, если одно из полей, которые должны быть выведены, уже содержит один или несколько символов табуляции или, что еще хуже, новую строку? Это вызовет весь процесс, так как мы полагаемся на эти символы, чтобы указать столбцы и разрывы строк.

Решение состоит в том, чтобы «избежать» символов табуляции. В этом случае мы заменим вкладки литеральными \ t и разрывами строк с литералом \ n, чтобы они не влияли на форматирование:

Теперь, прежде чем каждая строка будет эхом, любые символы табуляции заменяются «\ t», так что наши столбцы не разбиваются. Также любые разрывы строк внутри данных заменяются на «\ n».

.
Способ, представленный в той заметке действительно очень прост, но не всегда может быть удобен.
Есть множество других способов передать табличные данные из PHP в Excel, я опишу тот, который показался мне наиболее простым и функциональным. Нужно особенно отметить, что что я не говорю о генерации xls файла, а лишь предлагаю пользователю открыть полученные данные при помощи Excel так, что пользователи не искушённые в программировании не заметят подлога.

Итак, первое что необходимо сделать - разместить на нашей страничке ссылку на скрипт генерирующий следующие заголовки:
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/x-msexcel");
header("Content-Disposition: attachment; filename=\"" . iconv("UTF-8", "CP1251", $object->getFileName()) . "\";");
header("Content-Transfer-Encoding:­ binary");
header("Content-Length: " . $object->getFileSize());

$object - сферический объект в вакууме, который каждый читатель реализует так, как ему больше нравится. Назначение геттеров getFileName() и getFileSize() понятно из названий. Здесь стоит выделить один неочевидный нюанс (спасибо savostin за то что напомнил об этом) - getFileName() конечно может возвращать любое имя файла, но если вы хотите что бы браузер предложил открыть полученный контент в Excel, то расширение файла должно быть xls.
Ничего нового я пока не рассказал, все это придумано до меня, впрочем как и то что будет описано ниже.
Как верно было отмечено в комментариях к заметке о генерации xls в PHP , Excel гораздо быстрее работает с XML. Но самое главное преимущество, пожалуй, все же не в скорости, а в гораздо более широких возможностях. Углубляться в дебри я не стану, а лишь приведу простой пример, и ссылку на подробное описание всех тегов.

Итак, после того как заголовки сгенерированны, нам нужно отдать пользователю собственно данные. Я обычно заворачиваю генерацию таблицы в отдельный метод:
echo $object->getContent();

А таблицу генерирую при помощи Smarty:

{foreach from=$data.header item=caption} {$caption.columnName} {/foreach} {foreach from=$data.content item=row} {foreach from=$row item=col} {$col} {/foreach} {/foreach}
Как видно из кода, в шаблон передается массив $data содержащий два массива - строку заголовка таблицы и собственно данные.
Стоит отметить что использовать шаблонизатор только для генерации XML несколько накладно, и получить XML можно множеством других способов. В моем конкретном случае генерация XML это лишь маленькая плюшка в большом проекте, где без шаблонизатора не обойтись.

В качестве примера я привел простейшую таблицу, при желании можно манипулировать гораздо большим числом атрибутов. Это особенно приятно, если учесть, что для реализации не требуются никакие сторонние библиотеки.
Описанный метод несколько лет работает на одном проекте и ни один пользователь до сих пор не заподозрил, что открываемые им данные не являются документом MS Office.

Подробнее о структуре XML используемой в MS Excel можно почитать в